Início / dba / Perguntas / 339487
Accepted
John K. N.
Asked: 2024-05-14 17:01:43 +0800 CST

Extra NESTED LOOP / INNER JOIN causando aviso NO JOIN PREDICATE

  • 772

Eu tenho as seguintes tabelas em meu banco de dados.

Tabela de Sobrenomes

CREATE TABLE [dbo].[LastNames](
    [LastNameID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_LastNames_LastName] ON [dbo].[LastNames]
(
    [LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Tabela de nomes

CREATE TABLE [dbo].[FirstNames](
    [FirstNameID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_FirstNames_FirstName] ON [dbo].[FirstNames]
(
    [FirstName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Tabela PessoasAnon

CREATE TABLE [dbo].[PersonsAnon](
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [LastNameID] [int] NOT NULL,
    [FirstNameID] [int] NOT NULL,
    [Info1] [bit] NULL,
    [Info2] [char](1) NULL,
    [Info3] [nchar](50) NULL,
    [AdressID] [int] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_PersonsAnon_PersonID_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
    [PersonID] ASC,
    [LastNameID] ASC,
    [FirstNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NIX_PersonsAnon_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
    [LastNameID] ASC,
    [FirstNameID] ASC
)
INCLUDE([PersonID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Geração de dados

Para aqueles que possuem um banco de dados AdventureWorks2014 disponível, usei as seguintes instruções para preencher as tabelas:

INSERT INTO dbo.LastNames (LastName) SELECT DISTINCT app.LastName FROM AdventureWorks2016.Person.Person AS app

INSERT INTO dbo.FirstNames (FirstName) SELECT DISTINCT app.FirstName FROM AdventureWorks2016.Person.Person AS app

INSERT INTO dbo.PersonsAnon (LastNameID, FirstNameID)
SELECT ln.LastNameID, fn.FirstNameID FROM LastNames ln CROSS APPLY FirstNames fn

A declaração

A seguinte declaração foi usada para ver que tipo de plano de execução seria criado:

SELECT pa.PersonID,
       fn.FirstName,
       ln.LastName,
       pa.Info2
FROM   PersonsAnon AS pa
       JOIN LastNames AS ln
            ON  pa.LastNameID = ln.LastNameID
       JOIN FirstNames AS fn
            ON  pa.FirstNameID = fn.FirstNameID
WHERE  ln.LastName LIKE 'Pet%'
       AND fn.FirstName LIKE 'John%'
ORDER BY
       ln.LastName,
       fn.FirstName;

O Plano de Execução de Consulta

Colar o plano

Questões

Por que o JOIN é realizado entre Sobrenomes e Nomes mesmo que não haja predicado JOIN? O que está fazendo com que o QO JUNTE as duas tabelas?

Situação Gráfica

Aqui está o que parece:

Plano de execução de consulta com predicado e aviso JOIN

E os detalhes mostram:

Detalhes do predicado JOIN do plano de execução de consulta mostrando o aviso NO JOIN PREDICATE

Artigos já consultados

sql-server

1 respostas

  1. Best Answer

    O otimizador decidiu que sua consulta era uma consulta estrela seletiva em tabelas de fatos e dimensões .

    O diferencial é a presença de StarJoinInfoatributos nas operadoras join do plano (exceto o produto cartesiano, infelizmente). Do meu artigo StarJoinInfo em Planos de Execução :

    A presença de StarJoinInfoindica que o SQL Server aplicou uma de um conjunto de otimizações direcionadas a consultas seletivas de esquema em estrela. Essas otimizações estão disponíveis no SQL Server 2005, em todas as edições (não apenas na Enterprise). Observe que 'seletivo' aqui se refere ao número de linhas obtidas da tabela de fatos. A combinação de predicados dimensionais em uma consulta ainda pode ser seletiva mesmo quando seus predicados individuais qualificam um grande número de linhas.

    Uma das estratégias disponíveis é “Produto Cartesiano mais Pesquisa de Índice Multicoluna”. A ideia é aplicar um predicado separado a cada uma das tabelas de dimensão , pegar o produto cartesiano dos resultados e usá-lo para buscar ambas as chaves do índice de múltiplas colunas na tabela de fatos . O plano de consulta então executa uma pesquisa (RID ou chave) na tabela de fatos usando identificadores de linha das operações anteriores.

    Em outras palavras:

    1. Encontre nomes que começam com "John" e o ID associado
    2. Encontre sobrenomes que começam com "Pet" e o ID associado
    3. Produza o produto cartesiano dos resultados das etapas 1 e 2
    4. Pesquise PersonsAnon usando o índice de várias colunas em IDs para obter os resultados da etapa 3
    5. Execute uma pesquisa para recuperar 'Info2'

    Se você seguir a lógica, verá que essa estratégia retorna os resultados solicitados pela especificação da consulta.

    Embora tenha como objetivo acelerar consultas seletivas de esquema estrela (e floco de neve) em data warehousing, o otimizador pode aplicar essas técnicas sempre que detectar um conjunto adequado de tabelas e junções. A heurística usada para detectar consultas em estrela é bastante ampla, portanto você pode encontrar formas de plano com StarJoinInfoestruturas em praticamente qualquer tipo de banco de dados.

    • 11

relate perguntas