Geek asks and answers

The query optimizer cannot use the predicate of the past drive? TIPS also do not work

This is the diagram:

enter image description here

And this is sql, which, as I understand it, is too complicated for SQL Optimizer:

SELECT * FROM 
(
    select pp.Id as PaymentPartId,  b.Id as BudgetId, grouping(bp.ID) as g1 , sum(pp.Amount) PaymentsSum, sum(bp.Amount) BudgetSum
    from  Projects pr 
            inner join Payments p      ON pr.Id = p.ProjectID
            inner join PaymentParts pp ON p.Id = pp.PaymentId
            inner join Budgets b       ON pr.Id = b.ProjectID
            inner join Budgetparts bp  ON b.Id = bp.BudgetId
    group by pp.Id, b.Id, rollup(bp.ID)
)  x
WHERE   x.PaymentPartId = 777

SQLFIDDLE: http://sqlfiddle.com/#!6/aa74e/11 (with auto- generated data)

What I expect: the execution plan should contain an index search on x.PaymentPartId . What for? Since this query is equivalent to:

select pp.Id as PaymentPartId,  b.Id as BudgetId,  grouping(bp.ID) as g1, sum(pp.Amount) PaymentsSum, sum(bp.Amount)  BudgetSum
from  Projects pr 
        inner join Payments p      ON pr.Id = p.ProjectID
        inner join PaymentParts pp ON p.Id = pp.PaymentId
        inner join Budgets b       ON pr.Id = b.ProjectID
        inner join Budgetparts bp  ON b.Id = bp.BudgetId
WHERE   pp.Id = 777
group by pp.Id, b.Id, rollup(bp.ID)

... and the last query uses index search.

But SQL Optimizer not only refuses to use the index, but also ignores all the hints (I suggest you expire with sqlfiddle - this is really interesting).

, : , SQL Server Optimizer ? , rollup - , sql WHOLE.

P.S. , ", ": (sqlfiddle !).

+3

More articles:


All Articles