CODEWITHSUNDEEP
sqlindexinggroup-byaggregatesql-execution-plan
GhostOfPerdition
GhostOfPerdition

Reputation: 63

SQL Server - Aggregate with group by is faster than aggregate without

I'm having difficulty figuring out why a particular execution plan is forced in one situation, and not in the other. Example:

select min(COLUMN) from TABLE where FK_COLUMN = 1;

vs.

select min(COLUMN) from TABLE where FK_COLUMN = 1 group by FK_COLUMN;

The first produces an execution plan with an index scan, while in the second the scan is replaced with a seek. Further adding to my confusion is the fact that this doesn't happen on every column on the table - for some columns I don't need the group by in order for the seek to be produced. I also note that the slow condition is only occurring for certain foreign key values - ones that return no rows only, but not ALL values that return no rows produce the unfavorable plan. What gives?

Upvotes: 3

Views: 481

Answers (1)

Lakshmanan Chidambaram
Lakshmanan Chidambaram

Reputation: 133

I go with @Robbie Dee as the chosen execution plan by Oracle CBO doesn't mean it is the best way but the most optimised way in most circumstances. Also, the execution plan can change based on the way the columns and rows are getting stored(basically the size of the table).

Consider an EMP table that is primary keyed on EMP_ID column. If we include the primary key column in search we would expect an INDEX RANGE SCAN but we may get FULL TABLE ACCESS in explain plan. This is the data access path chosen by Oracle CBO as it knows that the size of the table is too small that it doesn't require to do the search based on primary key.

Hth...

Upvotes: 1

Related Questions