Plan Problem with Partition Top N
March 9, 2012 1 Comment
Yesterday I was having issues with a poor choice of plan by the optimizer when trying to do a top N of a partitioned table.
SQL> CREATE TABLE t1
2 (col1 varchar2(1) not null
3 ,col2 number not null
4 ,col3 varchar2(50) not null)
5 PARTITION BY LIST (col1)
6 (
7 PARTITION P1 VALUES ('A'),
8 PARTITION P2 VALUES ('B'),
9 PARTITION P3 VALUES ('C'),
10 PARTITION P4 VALUES ('D')
11 );
Table created.
SQL> INSERT
2 INTO t1
3 SELECT DECODE(MOD(ROWNUM,4),1,'A',2,'B',3,'C',0,'D')
4 , 100000000+ROWNUM
5 , LPAD('X',50,'X')
6 FROM DUAL
7 CONNECT BY ROWNUM <= 1000000;
1000000 rows created.
SQL> CREATE UNIQUE INDEX i1 ON T1 (col2,col1) LOCAL;
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T1');
PL/SQL procedure successfully completed.
Now COL2 is unique but to be a unique index on a partitioned table, I have to include the partition key.
I want the top N of COL2 in descending order.
SQL> select /*+ gather_plan_statistics */
2 *
3 from (select col2 from t1 t order by col2 desc)
4 where rownum <=10;
COL2
----------
101000000
100999999
100999998
100999997
100999996
100999995
100999994
100999993
100999992
100999991
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID cwt7s3cmmw3vb, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from (select col2 from t1
t order by col2 desc) where rownum <=10
Plan hash value: 738905059
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.49 | 2698 |
|* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.49 | 2698 |
| 2 | VIEW | | 1 | 1000K| 10 |00:00:00.49 | 2698 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 1000K| 10 |00:00:00.49 | 2698 |
| 4 | PARTITION LIST ALL | | 1 | 1000K| 1000K|00:00:00.65 | 2698 |
| 5 | INDEX FAST FULL SCAN| I1 | 4 | 1000K| 1000K|00:00:00.27 | 2698 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
Seems a strange option to me to do a FAST FULL SCAN and in the process visiting 2698 buffers.
Wouldn’t we much rather this did a descending index scan?
SQL> select /*+ gather_plan_statistics */
2 *
3 from (select /*+ index_desc (t i1) */ col2 from t1 t order by col2 desc)
4 where rownum <=10;
COL2
----------
101000000
100999999
100999998
100999997
100999996
100999995
100999994
100999993
100999992
100999991
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID f1ytxtp8bdgx6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from (select /*+
index_desc (t i1) */ col2 from t1 t order by col2 desc) where rownum
<=10
Plan hash value: 2521435439
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 12 |
|* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 12 |
| 2 | VIEW | | 1 | 1000K| 10 |00:00:00.01 | 12 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 1000K| 10 |00:00:00.01 | 12 |
| 4 | PARTITION LIST ALL | | 1 | 1000K| 40 |00:00:00.01 | 12 |
|* 5 | COUNT STOPKEY | | 4 | | 40 |00:00:00.01 | 12 |
| 6 | INDEX FULL SCAN DESCENDING| I1 | 4 | 1000K| 40 |00:00:00.01 | 12 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
5 - filter(ROWNUM<=10)
I’m glad that actually kicked in there as I wanted because on the real-world example that this relates to, it was being distinctly stubborn and at one point I thought I might have to resort to something distinctly ugly like:
SQL> select /*+ gather_plan_statistics */
2 *
3 from (select col2
4 from (select col2
5 from t1 partition (p1) t
6 order by col2 desc)
7 where rownum <=10
8 union all
9 select col2
10 from (select col2
11 from t1 partition (p2) t
12 order by col2 desc)
13 where rownum <=10
14 union all
15 select col2
16 from (select col2
17 from t1 partition (p3) t
18 order by col2 desc)
19 where rownum <=10
20 union all
21 select col2
22 from (select col2
23 from t1 partition (p4) t
24 order by col2 desc)
25 where rownum <=10
26 order by col2 desc)
27 where rownum <=10;
COL2
----------
101000000
100999999
100999998
100999997
100999996
100999995
100999994
100999993
100999992
100999991
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 8y7d958mpah49, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from (select col2
from (select col2 from t1 partition (p1) t
order by col2 desc) where rownum <=10 union all
select col2 from (select col2 from t1 partition
(p2) t order by col2 desc) where rownum <=10
union all select col2 from (select col2
from t1 partition (p3) t order by col2 desc)
where rownum <=10 union all select col2 from
(select col2 from t1 partition (p4) t
order by col2 desc) where rownum <=10 order by col2 desc)
where rownum <=10
Plan hash value: 1726521473
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 12 |
|* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 12 |
| 2 | VIEW | | 1 | 40 | 10 |00:00:00.01 | 12 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 40 | 10 |00:00:00.01 | 12 |
| 4 | UNION-ALL | | 1 | | 40 |00:00:00.01 | 12 |
|* 5 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 3 |
| 6 | VIEW | | 1 | 250K| 10 |00:00:00.01 | 3 |
| 7 | PARTITION LIST SINGLE | | 1 | 250K| 10 |00:00:00.01 | 3 |
| 8 | INDEX FULL SCAN DESCENDING| I1 | 1 | 250K| 10 |00:00:00.01 | 3 |
|* 9 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 3 |
| 10 | VIEW | | 1 | 250K| 10 |00:00:00.01 | 3 |
| 11 | PARTITION LIST SINGLE | | 1 | 250K| 10 |00:00:00.01 | 3 |
| 12 | INDEX FULL SCAN DESCENDING| I1 | 1 | 250K| 10 |00:00:00.01 | 3 |
|* 13 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 3 |
| 14 | VIEW | | 1 | 250K| 10 |00:00:00.01 | 3 |
| 15 | PARTITION LIST SINGLE | | 1 | 250K| 10 |00:00:00.01 | 3 |
| 16 | INDEX FULL SCAN DESCENDING| I1 | 1 | 250K| 10 |00:00:00.01 | 3 |
|* 17 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 3 |
| 18 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 3 |
| 19 | PARTITION LIST SINGLE | | 1 | 250K| 10 |00:00:00.01 | 3 |
| 20 | INDEX FULL SCAN DESCENDING| I1 | 1 | 250K| 10 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
5 - filter(ROWNUM<=10)
9 - filter(ROWNUM<=10)
13 - filter(ROWNUM<=10)
17 - filter(ROWNUM<=10)
But then the stubbornness disappeared and I couldn’t reproduce.
Conclusion – I must have been doing something stupid.
On a related note, whilst I was messing about, I noticed this.
Spot the trivial difference (not that it matters):
SQL> select * 2 from (select col2 from t1 t order by col2 desc) 3 where rownum <= 10; ........ SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ SQL_ID 8uy80z5da45ct, child number 0 ------------------------------------- select * from (select col2 from t1 t order by col2 desc) where rownum <= 10 Plan hash value: 3155368986 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 10 | 130 | 3 (0)| 00:00:01 | | 3 | INDEX FULL SCAN DESCENDING| I1 | 1000K| 6835K| 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10) 21 rows selected. SQL> select * 2 from (select /*+ index_desc(t i1) */ col2 from t1 t order by col2 desc) 3 where rownum <= 10; ........ SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------ SQL_ID 3asrnmxg6bqsj, child number 0 ------------------------------------- select * from (select /*+ index_desc(t i1) */ col2 from t1 t order by col2 desc) where rownum <= 10 Plan hash value: 3155368986 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 10 | 130 | 3 (0)| 00:00:01 | | 3 | INDEX FULL SCAN DESCENDING| I1 | 10 | 70 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10) 21 rows selected. SQL>
OraStory 
I ran into the exact same problem. Did you figure out why it is the case?