Plan Problem with Partition Top N

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):

1
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> 
About these ads

One Response to Plan Problem with Partition Top N

  1. Testo says:

    I ran into the exact same problem. Did you figure out why it is the case?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: