Hi all,
Let's consider following case:
create table tmp as select rownum id, 0 sign from dual connect by level <= 100;
create index tmp_i on tmp(id,sign);
create table t as
select mod(rownum,2) id, mod(rownum,3) val
from dual
connect by level <= 100000;
begin
dbms_stats.gather_table_stats (
user,
'T',
estimate_percent => null,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => true
);
end;
/
begin
dbms_stats.gather_table_stats (
user,
'TMP',
estimate_percent => null,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => true
);
end;
/
As you can see it scans TMP_I 50 000 times for statement with max (irrespective of distinct in subquery).
Is there any way to enforce CBO to make SORT UNIQUE for max as well as for count so that it scans TMP_I only 3 times?
SQL> select --+ leading(t) use_nl(t tmp)
2 max(id)
3 from tmp tmp
4 where tmp.sign = 0
5 and tmp.id in (select val from t where id = 1);
MAX(ID)
----------
2
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.14 | 159 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.14 | 159 |
| 2 | NESTED LOOPS | | 1 | 49750 | 33333 |00:00:00.13 | 159 |
|* 3 | TABLE ACCESS FULL| T | 1 | 50000 | 50000 |00:00:00.02 | 156 |
|* 4 | INDEX RANGE SCAN | TMP_I | 50000 | 1 | 33333 |00:00:00.07 | 3 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID"=1)
4 - access("TMP"."ID"="VAL" AND "TMP"."SIGN"=0)
SQL> select --+ leading(t) use_nl(t tmp)
2 max(id)
3 from tmp tmp
4 where tmp.sign = 0
5 and tmp.id in (select distinct val from t where id = 1);
MAX(ID)
----------
2
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.14 | 159 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.14 | 159 |
| 2 | NESTED LOOPS | | 1 | 49750 | 33333 |00:00:00.13 | 159 |
|* 3 | TABLE ACCESS FULL| T | 1 | 50000 | 50000 |00:00:00.01 | 156 |
|* 4 | INDEX RANGE SCAN | TMP_I | 50000 | 1 | 33333 |00:00:00.07 | 3 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID"=1)
4 - access("TMP"."ID"="VAL" AND "TMP"."SIGN"=0)
SQL> select --+ leading(t) use_nl(t tmp)
2 count(id)
3 from tmp tmp
4 where tmp.sign = 0
5 and tmp.id in (select val from t where id = 1);
COUNT(ID)
----------
2
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 158 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 158 | | | |
| 2 | NESTED LOOPS | | 1 | 3 | 2 |00:00:00.03 | 158 | | | |
| 3 | SORT UNIQUE | | 1 | 50000 | 3 |00:00:00.03 | 156 | 2048 | 2048 | 2048 (0)|
|* 4 | TABLE ACCESS FULL| T | 1 | 50000 | 50000 |00:00:00.01 | 156 | | | |
|* 5 | INDEX RANGE SCAN | TMP_I | 3 | 1 | 2 |00:00:00.01 | 2 | | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"=1)
5 - access("TMP"."ID"="VAL" AND "TMP"."SIGN"=0)
I can't figure out why SORT UNIQUE is absent for statement with max.
PS. 11gR2