Início / dba / Perguntas / 30165
Accepted
孔夫子
Asked: 2012-12-12 03:34:45 +0800 CST

Por que o SQL Server não otimiza os UNIONs?

  • 772

Considere estas consultas ( SQL Fiddle ):

Consulta 1:

SELECT * INTO #TMP1 FROM Foo
UNION
SELECT * FROM Boo
UNION
SELECT * FROM Koo;

Consulta 2:

SELECT * INTO #TMP2 FROM Foo
UNION
SELECT * FROM Boo
UNION ALL
SELECT * FROM Koo;

Observe que Koo não se sobrepõe a Boo/Foo, então o resultado final é o mesmo. A questão é por que a primeira combinação UNION / UNION não é mesclada em uma única operação SORT?

sql-server performance

2 respostas

  1. Best Answer

    O otimizador de consulta possui operadores n-ários, embora o mecanismo de execução tenha um número bem menor. Para ilustrar, vou usar uma versão simplificada de suas tabelas - (SQL Fiddle) .

    SELECT DISTINCT
    number
INTO foo
FROM master..spt_values
WHERE 
    number < 1000;

SELECT DISTINCT
    number
INTO boo
FROM master..spt_values
WHERE 
    number between 300 and 1005;

SELECT DISTINCT
    number
INTO koo
FROM master..spt_values
WHERE 
    number > 1006;

ALTER TABLE dbo.foo ADD PRIMARY KEY (number);
ALTER TABLE dbo.boo ADD PRIMARY KEY (number);
ALTER TABLE dbo.koo ADD PRIMARY KEY (number);

    Dadas essas tabelas e dados, vamos olhar para a árvore de entrada para uma UNIONconsulta de três vias:

    SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_Union
    OUTPUT(COL: Union1006 )
    CHILD(QCOL: [f].number)
    CHILD(QCOL: [b].number)
    CHILD(QCOL: [k].number)
    LogOp_Project
        LogOp_Get TBL: dbo.foo(alias TBL: f)
        AncOp_PrjList 
    LogOp_Project
        LogOp_Get TBL: dbo.boo(alias TBL: b)
        AncOp_PrjList 
    LogOp_Project
        LogOp_Get TBL: dbo.koo(alias TBL: k)
        AncOp_PrjList

    O operador de união lógica tem uma saída e três entradas filhas. Após a otimização baseada em custo, a árvore física escolhida é uma união de mesclagem com três entradas:

    SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607);

PhyOp_MergeUnion
    PhyOp_Range TBL: dbo.foo(alias TBL: f)(1) ASC
    PhyOp_Range TBL: dbo.boo(alias TBL: b)(1) ASC
    PhyOp_Range TBL: dbo.koo(alias TBL: k)(1) ASC

    A saída do otimizador é retrabalhada em uma forma que o mecanismo de execução (sem união de mesclagem n-ária) pode manipular:

    Mesclar plano de união

    A reescrita pós-otimização desdobra o n-ário PhyOp_MergeUnionem vários operadores Merge Union. Observe como todo o custo estimado permanece associado ao operador de união 'original' - os outros têm uma estimativa de custo zero.

    O fato de o otimizador raciocinar sobre uniões usando operadores n-ários fornece uma explicação de por que ele não considera reescrever seu primeiro exemplo para o mesmo plano do segundo exemplo (a união de três vias é um único nó de árvore).

    A segunda razão é que não há restrições para impor a 'falta de sobreposição'. Antes que as restrições estejam em vigor, uma união entre booe koonão pode ser garantida para não produzir duplicatas, portanto, obtemos um plano de remoção de duplicatas (uma união de mesclagem neste caso):

    SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

    boo/koo sem restrições

    Adicionar as seguintes restrições garante que a condição de não sobreposição não possa ser violada sem invalidar os planos em cache para a consulta:

    ALTER TABLE dbo.foo WITH CHECK ADD CHECK (number < 1000);
ALTER TABLE dbo.boo WITH CHECK ADD CHECK (number BETWEEN 300 AND 1005);
ALTER TABLE dbo.koo WITH CHECK ADD CHECK (number > 1006);

    Agora é seguro para o otimizador simplesmente concatenar:

    boo/koo com restrições

    No entanto, mesmo com essas restrições em vigor, a consulta de união de três vias ainda aparece como três uniões porque o otimizador normalmente não considera a divisão de operadores n-ários para explorar alternativas. O operador n-ário é muito útil para manter o espaço de busca sob controle; separá-lo muitas vezes seria contraproducente devido ao objetivo do otimizador de encontrar um bom plano rapidamente.

    SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

    Mesclar plano de união com restrições

    Quando escrito como a UNIONe UNION ALL, um operador n-ário não pode mais ser usado (os tipos não correspondem), então a árvore tem nós separados:

    SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION ALL
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_UnionAll
    OUTPUT(COL: Union1007 )
    CHILD(COL: Union1004 )
    CHILD(QCOL: [k].number)

    LogOp_Union
        OUTPUT(COL: Union1004 )
        CHILD(QCOL: [f].number)
        CHILD(QCOL: [b].number)

        LogOp_Project
            LogOp_Get TBL: dbo.foo(alias TBL: f)
            AncOp_PrjList 

        LogOp_Project
            LogOp_Get TBL: dbo.boo(alias TBL: b)
            AncOp_PrjList 

    LogOp_Project
        LogOp_Get TBL: dbo.koo(alias TBL: k)
        AncOp_PrjList
    • 18

  2. O SQL Server possui operações de conjunto de 3 vias; o operador CONCATENATION aceita n entradas. Dadas, por exemplo, dez tabelas:

    CREATE TABLE Test01 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80)); 
CREATE TABLE Test02 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test03 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test04 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test05 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test06 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test07 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test08 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test09 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));
CREATE TABLE Test10 (SomeKey INTEGER NOT NULL, SomeAttribute VARCHAR(80));

    e uma consulta que une tudo para encontrar qualquer linha em cada tabela que tenha a mesma chave:

    SELECT * FROM
(
SELECT * FROM Test01 UNION ALL
SELECT * FROM Test02 UNION ALL
SELECT * FROM Test03 UNION ALL
SELECT * FROM Test04 UNION ALL
SELECT * FROM Test05 UNION ALL
SELECT * FROM Test06 UNION ALL
SELECT * FROM Test07 UNION ALL
SELECT * FROM Test08 UNION ALL
SELECT * FROM Test09 UNION ALL
SELECT * FROM Test10
) AS Bunch
WHERE SomeKey = 39;

    Veremos um plano de consulta que obtém as linhas correspondentes (com push down do predicado no operador TABLE SCAN) e concatena todos os resultados no SELECToperador.

    A razão pela qual você não obtém um plano mesclado e depois classificado é porque seria muito lento e a classificação não é necessária para implementar a UNIONoperação. Nas tabelas BOO, FOO e KOO, você declarou uma chave primária. Quando o acessador CLUSTERED INDEX SCAN enumera as linhas, elas são produzidas na ordem do índice clusterizado subjacente -- garantido. Concatenar dois conjuntos e, em seguida, classificar o resultado é muito mais lento do que usar o operador MERGE JOIN, e o operador MJ pode ser usado com muita facilidade, pois ambos os conjuntos são classificados e indexados.

    • 3

relate perguntas