Bitmap Conversion to Rowids

Ursprünglich hatte ich in diesem Eintrag eine umfangreiche Erläuterung zu Problemen des Zusammenspiels von Partition Elimination und lokalen Indizes geliefert, die nur einen kleinen Fehler hatte: das geschilderte Problem gibt es nicht (bzw. es handelte sich nur um einen Costing Effekt, der sich durch einen Hint übersteuern lässt, was mir eigentlich hätte auffallen sollen ...). Um die Menge sinnfreier Beiträge im Internet nicht zu vergrößern, habe ich diese Passage wieder gelöscht und komme gleich zum zweiten Teil meiner Beobachtungen, von dem ich hoffe, dass er etwas solider ist ... Dabei geht es um ein die Kosten der Operation BITMAP CONVERSION TO ROWIDS.

Zunächst der Testaufbau (in 11.2.0.1):

drop table t_partitioned;

create table t_partitioned (
    id  number
  , startdate date
  , col1 number
  , padding varchar2(50)
)
partition by range(startdate)
(
  partition t_partitioned_20120522 values less than (to_date('23.05.2012', 'dd.mm.yyyy'))
, partition t_partitioned_20120523 values less than (to_date('24.05.2012', 'dd.mm.yyyy'))
, partition t_partitioned_maxvalue values less than (maxvalue)
);

insert into t_partitioned
select rownum id
     , to_date('22.05.2012', 'dd.mm.yyyy') - 1 + trunc(rownum/500000) startdate
     , mod(rownum, 4) col1
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 1000000;

-- wiederholt, bis die Tabelle 64M rows erreicht.
insert into t_partitioned
select * from t_partitioned;

commit;

create bitmap index t_partitioned_col1_start_bix on t_partitioned(col1, startdate) local;

exec dbms_stats.gather_table_stats(user, 't_partitioned')

Auf dieser Datenbasis setze ich zwei harmlose Queries ab, die - so weit ich sehe - semantisch identisch sind: zunächst lasse ich die distinkten COL1-Werte für einen Tag zählen. Danach eine kleine Umformulierung, bei der die distinkten Werte in einer Subquery ermittelt werden und das Zählen in einer Rahmen-Query erfolgt:

select /*+ monitor test 1 */ 
       count(distinct col1)
  from t_partitioned 
 where startdate = to_date('22.05.2012', 'dd.mm.yyyy');

select /*+ monitor test 2 */
       count(*)
  from (select distinct col1
          from t_partitioned
         where startdate = to_date('22.05.2012', 'dd.mm.yyyy'));

Man sollte annehmen, dass der CBO mit beiden Varianten gut umgehen könnte, aber das ist nicht der Fall, wie die Ergebnisse des SQL Monitor (DBMS_SQLTUNE.REPORT_SQL_MONITOR) zeigen:

-- Test 1
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor test 1 */ count(distinct col1) from t_partitioned where startdate = to_date('22.05.2012', 'dd.mm.yyyy')

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  DBADMIN (67:178)
 SQL ID              :  3mxmz941azrgx
 SQL Execution ID    :  16777216
 Execution Started   :  05/22/2012 21:48:43
 First Refresh Time  :  05/22/2012 21:48:43
 Last Refresh Time   :  05/22/2012 21:48:47
 Duration            :  4s
 Module/Action       :  SQL*Plus/-
 Service             :  testdb
 Program             :  sqlplus.exe
 Fetch Calls         :  1

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    4.09 |    3.54 |     0.55 |     1 |   5868 |   62 |  46MB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=3761143426)
==================================================================================================================================================================================================
| Id |             Operation              |             Name             |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity |      Activity Detail       |
|    |                                    |                              | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |        (# samples)         |
==================================================================================================================================================================================================
|  0 | SELECT STATEMENT                   |                              |         |      |         1 |     +4 |     1 |        1 |      |       |       |          |                            |
|  1 |   SORT AGGREGATE                   |                              |       1 |      |         1 |     +4 |     1 |        1 |      |       |       |          |                            |
|  2 |    VIEW                            | VW_DAG_0                     |    2602 | 5361 |         1 |     +4 |     1 |        4 |      |       |       |          |                            |
|  3 |     HASH GROUP BY                  |                              |    2602 | 5361 |         4 |     +1 |     1 |        4 |      |       |  840K |    50.00 | Cpu (2)                    |
|  4 |      PARTITION RANGE SINGLE        |                              |    2602 | 5360 |         3 |     +2 |     1 |      32M |      |       |       |          |                            |
|  5 |       BITMAP CONVERSION TO ROWIDS  |                              |    2602 | 5360 |         3 |     +2 |     1 |      32M |      |       |       |    25.00 | Cpu (1)                    |
|  6 |        BITMAP INDEX FAST FULL SCAN | T_PARTITIONED_COL1_START_BIX |         |      |         3 |     +2 |     1 |     5836 |   62 |  46MB |       |    25.00 | db file scattered read (1) |
==================================================================================================================================================================================================

-- Test 2
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor test 2 */ count(*) from (select distinct col1 from t_partitioned where startdate = to_date('22.05.2012', 'dd.mm.yyyy'))

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  DBADMIN (67:178)
 SQL ID              :  512z4htdq43cn
 SQL Execution ID    :  16777216
 Execution Started   :  05/22/2012 21:48:49
 First Refresh Time  :  05/22/2012 21:48:49
 Last Refresh Time   :  05/22/2012 21:48:49
 Duration            :  .019299s
 Module/Action       :  SQL*Plus/-
 Service             :  testdb
 Program             :  sqlplus.exe
 Fetch Calls         :  1

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.02 |    0.02 |     0.00 |     1 |   5868 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=4286208786)
=======================================================================================================================================================================
| Id |             Operation             |             Name             |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                   |                              | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
=======================================================================================================================================================================
|  0 | SELECT STATEMENT                  |                              |         |      |         1 |     +0 |     1 |        1 |       |          |                 |
|  1 |   SORT AGGREGATE                  |                              |       1 |      |         1 |     +0 |     1 |        1 |       |          |                 |
|  2 |    VIEW                           |                              |    2602 | 5361 |         1 |     +0 |     1 |        4 |       |          |                 |
|  3 |     HASH UNIQUE                   |                              |    2602 | 5361 |         1 |     +0 |     1 |        4 |  840K |          |                 |
|  4 |      PARTITION RANGE SINGLE       |                              |    2602 | 5360 |         1 |     +0 |     1 |     5836 |       |          |                 |
|  5 |       BITMAP INDEX FAST FULL SCAN | T_PARTITIONED_COL1_START_BIX |    2602 | 5360 |         1 |     +0 |     1 |     5836 |       |          |                 |
=======================================================================================================================================================================

Also über 4 Sekunden Laufzeit für Fall 1 und Sekundenbruchteile für Fall 2. Dabei sind die Kosten beider Versionen identisch und auch die Zahl der Buffer Gets. Der entscheidende Unterschied liegt offenbar darin, dass im Test 1 sehr viel Zeit in die - anscheinend überflüssige - BITMAP CONVERSION TO ROWIDS gesteckt wird; Test 2 kommt jedenfalls offenbar ohne dieses umständliche Auspacken von 32M Sätzen aus. Dass die BITMAP CONVERSION TO ROWIDS für große Datenmengen kostspielig sein kann, hat übrigens Greg Rahn gelegentlich erläutert. Warum der CBO diese relativ harmlose Transformation nicht durchführt, ist mir allerdings nicht klar.

Nachtrag 05.06.2012: Gestern habe ich den Fall im OTN-Forum präsentiert und von Jonathan Lewis folgende Antwort bekommen: "I think it's just one of those things where the optimizer code is not consistent across all cases." Außerdem liefert ein ein ähnliches Beispiel mit einer IN Subquery.