ORDER BY
, GROUP BY
, DISTINCT
, Set operations (eg. UNION
),
Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a
sort operation requires rows in the same order as the index, then
Oracle may read the table rows via the index. A sort operation is not
necessary since the rows are returned in sorted order.Despite all of the instances listed above where a sort is performed, I have only seen three cases where a sort is actually avoided.
GROUP BY
1 select src_sys, sum(actl_expns_amt), count(*) 2 from ef_actl_expns 3 where src_sys = 'CDW' 4 and actl_expns_amt > 0 5* group by src_sys ------------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT GROUP BY NOSORT | | |* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS | |* 3 | INDEX RANGE SCAN | EF_AEXP_PK | ------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ACTL_EXPNS_AMT">0) 3 - access("SRC_SYS"='CDW')
Note theNOSORT
qualifier in Step 1.
ORDER BY
1 select * 2 from ef_actl_expns 3 where src_sys = 'CDW' 4 and actl_expns_amt > 0 5* order by src_sys ------------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------------ | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS | |* 2 | INDEX RANGE SCAN | EF_AEXP_PK | ------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ACTL_EXPNS_AMT">0) 2 - access("SRC_SYS"='CDW')
Note that there is no SORT operation, despite theORDER BY
clause. Compare this to the following:
1 select * 2 from ef_actl_expns 3 where src_sys = 'CDW' 4 and actl_expns_amt > 0 5* order by actl_expns_amt ------------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT ORDER BY | | |* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS | |* 3 | INDEX RANGE SCAN | EF_AEXP_PK | ------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ACTL_EXPNS_AMT">0) 3 - access("SRC_SYS"='CDW')
DISTINCT
1 select distinct src_sys 2 from ef_actl_expns 3 where src_sys = 'CDW' 4* and actl_expns_amt > 0 ------------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT UNIQUE NOSORT | | |* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| EF_ACTL_EXPNS | |* 3 | INDEX RANGE SCAN | EF_AEXP_PK | ------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ACTL_EXPNS_AMT">0) 3 - access("SRC_SYS"='CDW')
Again, note theNOSORT
qualifier.
No comments:
Post a Comment