| From: | Steve Adams |
| Date: | 01-Sep-2000 03:41 |
| Subject: | Bitmap-Index and Optimizer |
OK, here is the relevant section of the 10053 trace ...
SINGLE TABLE ACCESS PATH
Column: DATUM Col#: 5 Table: GESCH Alias: A
NDV: 8 NULLS: 0 DENS: 3.7559e-06
Column: TAG Col#: 6 Table: GESCH Alias: A
NDV: 8 NULLS: 0 DENS: 3.7559e-06
TABLE: GESCH ORIG CDN: 133123 CMPTD CDN: 2071
Access path: tsc Resc: 63 Resp: 63
Access path: index (equal)
INDEX#: 75959 TABLE: GESCH
CST: 4 IXSEL: 1.2496e-01 TBSEL: 1.2496e-01
Access path: index (equal)
INDEX#: 75900 TABLE: GESCH
CST: 4 IXSEL: 1.2445e-01 TBSEL: 1.2445e-01
******** Bitmap access path rejected ********
Cost: 158 Selectivity: 0
Not believed to be index-only.
BEST_CST: 63.00 PATH: 2 Degree: 1
This is where the bitmap access path is rejected in favour of a full table scan.
Oracle first looks at the selectivity of the two predicates against this table
and estimates that 2071 rows will be fetched. That is not right. Your TKPROF
output below shows that only 25 rows satisfied these predicates. The reason that
the bitmap plan got rejected was that Oracle thought that to fetch the 2071 rows
it would need to access 150 table blocks individually as well as 4 blocks from
each of the bitmap indexes. Compared to that the 63 multiblock reads for a full
table scan look attractive.
Oracle got it wrong, because it was not aware of the strong negative correlation
between the two predicates. That correlation made their combined selectivity
that much better than Oracle could know. Oracle assumed no correlation between
the predicates (as it must) and thus computed the cardinality of the single
table access path incorrectly.
The moral of the story is that you need a concatenated index for Oracle to
become aware of such predicate correlations.
-----Original Message-----
Sent: Friday, 1 September 2000 2:41
I hope this try is OK.
I modified the query with a table alias
-----Original Message-----
The first query, the one I am interested in was already in the shared pool, so
the trace file did not show the generation of a query execution plan for that
statement. Could you please try again and modify the SQL statement slightly with
a comment or some white space so that it will have to be parsed again. Also
please set event 10053 only. Please do not set event 10046 or sql_trace.
-----Original Message-----
Sent: Friday, 1 September 2000 2:14
Thank you for your fast response, here you will have the trace-file.
It's compressed with WinZip.
-----Original Message-----
Sent: Friday, 1 September 2000 0:48
It is really hard to answer questions like this without a 10053 trace.
You can enable the trace in a new session with
alter session set events '10053 trace name context forever';
If you could then re-run the first test and send me the trace file, I'll try to
explain what went wrong.
-----Original Message-----
Sent: Thursday, 31 August 2000 19:44
Just a "simple question" about the behaviour of the oracle8i (8.1.6)
optimizer....
I've got only 2 test tables :=
a) gesch 133.000 rows / columns := k_gesch (primary key), tag ( cardinality 8
with bitmap-index), datum ( cardinality 8 with bitmap index) and some more
b) daten1 133.000 rows / columns := d1_key (foreign-key to gesch.k_gesch)
with a unique index. (named fore_1) and some more.....
All table have been analyzed by :
"analyze table compute statistics for all indexed columns for all
indexes;"
My query :
select daten1.*
from daten1, gesch
where gesch.DATUM = 4
and gesch.Tag=6
and gesch.K_GESCH = daten1.D1_KEY;
Without any hints the explain plan :=
********************************************************************************
select daten1.*
from daten1, gesch
where gesch.DATUM = 4
and gesch.Tag=6
and gesch.K_GESCH = daten1.D1_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.26 0.29 0 0 20 0
Fetch 1 1.43 5.71 213 1124 1607 25
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.69 6.00 213 1124 1627 25
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 160 (CORESY)
Rows Row Source Operation
------- ---------------------------------------------------
25 MERGE JOIN
25 SORT JOIN
2062 TABLE ACCESS FULL GESCH
25 SORT JOIN
133123 TABLE ACCESS FULL DATEN1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
25 MERGE JOIN
25 SORT (JOIN)
2062 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'GESCH'
25 SORT (JOIN)
133123 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DATEN1'
No usage of any index ?!?!? => I've changed the orainit-parameter
"hash_join_enabled" to false (somebody told me caused by the usage of
bitmap-indexes) But that isn't what I want.
Tuning the query with hints, tkprof explained:
********************************************************************************
select /*+ ORDERED USE_NL(gesch,daten1) index_combine(gesch) */ daten1.*
from gesch, daten1
where gesch.DATUM = 4
and gesch.Tag=6
and gesch.K_GESCH = daten1.D1_KEY
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 93 0 25
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.02 0 93 0 25
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 160 (CORESY)
Rows Row Source Operation
------- ---------------------------------------------------
25 NESTED LOOPS
25 TABLE ACCESS BY INDEX ROWID GESCH
25 BITMAP CONVERSION TO ROWIDS
1 BITMAP AND
3 BITMAP INDEX SINGLE VALUE
3 BITMAP INDEX SINGLE VALUE
25 TABLE ACCESS BY INDEX ROWID DATEN1
49 INDEX UNIQUE SCAN (object id 76014)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
25 NESTED LOOPS
25 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'GESCH'
25 BITMAP CONVERSION (TO ROWIDS)
1 BITMAP AND
3 BITMAP INDEX (SINGLE VALUE) OF 'BR'
3 BITMAP INDEX (SINGLE VALUE) OF 'BB1'
25 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'DATEN1'
49 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'FORE_1' (UNIQUE)
********************************************************************************
That's fast and what I supposed the optimizer should do...
Have you got an answer of this behaviour ?