CBO, bitmap indexes and plans that do extra work?

From: Gail <gmitchell_at_bbn.com>
Date: 15 Sep 2006 14:03:13 -0700
Message-ID: <1158354193.027151.299140@b28g2000cwb.googlegroups.com>

I could use some help in understanding what the Oracle optimizer
(10.2.0.2) is doing - here's one example.

Say I have a tree of information, and a fact_table relating elements of the tree to other information (not relevant to this example). The data model includes tables:

Nodes - each row has an id and other information, including 1 ancestor node id; rowcount approx. 100,000

Trace_flat - (id, top_id, lvl1_id, lvl2_id,...lvlN_id) built from Nodes, each row corresponds to one row of Nodes and maintains a node's id and the path of ids of ancestors of that node (fixed maximum depth tree) some lvlX_id = id for every row

Fact_table - (data_files_id, node_id, etc.) Maintains mXn relationships between Trace_flat and other tables in the schema
(including a data_files table). data_files_id is the only non-null
field. Rowcount approx 3 million.

create index ix_trace_flat_top_id on trace_flat(top_id);
create unique index pk_trace_flat on trace_flat(id);
create bitmap index bi_fact_trace on fact_table(node_id);

create bitmap index bji_fact_trace_top_id on fact_table
(trace_flat.top_id)

from fact_table, trace_flat where fact_table.node_id = trace_flat.id;

Consider the following queries. I want to use the bitmap indexes
(although the optimizer gives better cost estimates without, the
queries in practice run significantly faster with):

1. Count the number of data_files related to nodes in the 98818 hierarchy

select /*+ index (ft) */ count(distinct ft.data_files_id ) from fact_table ft,

       trace_flat t1
where t1.top_id = 98818 and

         ft.node_id = t1.ID;

The plan (with the hint) looks good and the query blazes. Without the hint it takes a few seconds (unacceptable):

Operation                                                 Object Name
               Rows        Bytes
SELECT STATEMENT Optimizer Mode=ALL_ROWS                          1
  SORT GROUP BY
                1               5
    TABLE ACCESS BY INDEX ROWID        FACT_TABLE               523 K
       2 M
      BITMAP CONVERSION TO ROWIDS
        BITMAP MINUS
          BITMAP INDEX SINGLE VALUE         BJI_FACT_TRACE_TOP_ID
          BITMAP INDEX SINGLE VALUE         BI_FACT_TRACE

B. Count the number of data_files related to nodes in the 98818 hierarchy or the 98820 hierarchy.

select /*+ index (ft) */ count(distinct ft.data_files_id ) from fact_table ft,

       trace_FLAT t1
where (t1.top_id = 98818 or t1.top_id = 98820) and

         ft.node_id = t1.ID;

SELECT STATEMENT Optimizer Mode=ALL_ROWS

    1
  SORT GROUP BY

                           1           15
    HASH JOIN
                         1 M	  14 M
      VIEW
index$_join$_002                     21 K     210 K
        HASH JOIN
          INLIST ITERATOR
            INDEX RANGE SCAN                       IX_TRACE_FLAT_TOP_ID
       21 K      210 K
          INDEX FAST FULL SCAN                    PK_TRACE_FLAT
          21 K     210 K
      TABLE ACCESS BY INDEX ROWID         FACT_TABLE
       1 M       4 M
        BITMAP CONVERSION TO ROWIDS
          BITMAP MINUS
            BITMAP OR
              BITMAP INDEX SINGLE VALUE       BJI_FACT_TRACE_TOP_ID

              BITMAP INDEX SINGLE VALUE       BJI_FACT_TRACE_TOP_ID

            BITMAP INDEX SINGLE VALUW        BI_FACT_TRACE

QUESTION 1: How does the optimizer compute the cardinality (rows)? In actuality, ~8000 fact_table rows participate in result A and ~15,000 in result B - so the estimate for access to fact_table is off in the absolute, but ok in relative terms in that it's 1:2. I ran dbms_stats.gather_schema_stats(ownname => NULL, options => 'GATHER', cascade => TRUE); but it doesn't change the plan. Is there something else I should do statistics-wise?

QUESTION 2: In query plan B., what is the purpose of accessing the indexes on the Trace_flat table (ix_trace_flat_top_id and pk_trace_flat)? i.e., Why does the optimizer plan build index$_join$_002 to do a hash join with the result from bitmap indexing into the fact_table? why doesn't the previous plan, with an additional bitmap OR, suffice?

Thanks in advance.
Gail

p.s. sorry about the formatting of the plans...i tried! Received on Fri Sep 15 2006 - 16:03:13 CDT