Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> CBO, bitmap indexes and plans that do extra work?
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
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):
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
![]() |
![]() |