tuvham
08-08-2001, 05:22 AM
I have a very annoying problem with indexes that perhaps someone out there can help me with?
I have large fact table (3700 mb) with a lot of bitmap indexes and one pk index on it.
Its a totally plain table but with some chained rows (583349 of 17446252).
The primary key b-tree index has columns (in order) a,b,c.
A bitmap index has column a indexed.
The sql statement is: select b,a,d,c,f from x where a = 87;
Value "a" ranges from 1 to 150 and this particular statement returns 0 rows.
Optimizer mode is CHOOSE but oracle does a full table scan and ignores the index.
The table and all indexes is analyzed compute. It always work when i use index hints in the statement,
then the response time drops from 2-4 minutes to a second.
I´ve tried this:
1. Since we already got column a indexed in the pk index, why don´t oracle use that?
1a. tried to "select b,a from x where a = 87;". It works! Oracle uses the pk index.
1b. tried to "select a,b from x where a = 87;". It works! Oracle uses the pk index.
1c. tried to "select b,a,d from x where a = 87;". It works! Oracle uses the pk index.
1d. tried to "select b,a,d,e from x where a = 87;". It doesn´t work! Oracle ignores the pk index.
2. Adding histograms to column a since the distribution is very skewed.
This actually works! The optimizer uses the bitmap index on column a after adding histogram.
BUT we use bindvariables so then we cant use histograms.
3. Lets try optimizer_index_cost_adj and optimizer_index_caching
cost_adj set to 30, index_caching set to 95
What happens is that the optimizer uses the pk index but somehow it does a fts on the table anyway:
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39495 Card=1586598 B
ytes=26972166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'X' (Cost=3949
5 Card=1586598 Bytes=26972166)
2 1 INDEX (RANGE SCAN) OF 'PK_X' (UNIQUE) (Cost=8
6 db block gets
471931 consistent gets
465812 physical reads
4. Lets try to create a b-tree index on column a (with compute statistics in create statement)
The optimzer doesn´t use it!
4a. Computes statistics (compute) for the new index and the table. Doesn´t work, fts.
4b. Changes optimizer_index_cost_adj to 30. Works (uses the new index), but does read through the whole table anyway.
4c. Tries to hint the new index in the sql statement. It works perfectly, 3 consistent reads, no phys.
4d. Tries to hint the pk index in the sql statement. It works fine, 26 consistent reads, 3 phys.
4e. Creates histograms for column a. It works, 3 cons gets, no phys. But i cant use this because of bind variables.
4f. Drops the new index because it's really not needed, we´ve got indexing in the pk. It still works, 553 consistent gets, 13 phys reads.
4g. Creates the new index again, this time with comress. Doesn´t work! FTS.
4h. Tries to alter the sqlstatement (where a = 56), maybe oracle uses some old execution plan? Doesn´t work, fts.
4i. Tries optimizer_index_cost_adj again. Same as before, reads whole table.
4j. Tries to hint again. Works just fine, uses the new index, 21 cons reads, 5 phys.
SUMMARY: whenever i use hints and histograms everything is fine.
But I want the optimizer to do this work for me,
we don´t want to use hints and since we use bind variables, histograms don´t work.
Why does´nt the optimizer use the indexes?
I have large fact table (3700 mb) with a lot of bitmap indexes and one pk index on it.
Its a totally plain table but with some chained rows (583349 of 17446252).
The primary key b-tree index has columns (in order) a,b,c.
A bitmap index has column a indexed.
The sql statement is: select b,a,d,c,f from x where a = 87;
Value "a" ranges from 1 to 150 and this particular statement returns 0 rows.
Optimizer mode is CHOOSE but oracle does a full table scan and ignores the index.
The table and all indexes is analyzed compute. It always work when i use index hints in the statement,
then the response time drops from 2-4 minutes to a second.
I´ve tried this:
1. Since we already got column a indexed in the pk index, why don´t oracle use that?
1a. tried to "select b,a from x where a = 87;". It works! Oracle uses the pk index.
1b. tried to "select a,b from x where a = 87;". It works! Oracle uses the pk index.
1c. tried to "select b,a,d from x where a = 87;". It works! Oracle uses the pk index.
1d. tried to "select b,a,d,e from x where a = 87;". It doesn´t work! Oracle ignores the pk index.
2. Adding histograms to column a since the distribution is very skewed.
This actually works! The optimizer uses the bitmap index on column a after adding histogram.
BUT we use bindvariables so then we cant use histograms.
3. Lets try optimizer_index_cost_adj and optimizer_index_caching
cost_adj set to 30, index_caching set to 95
What happens is that the optimizer uses the pk index but somehow it does a fts on the table anyway:
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39495 Card=1586598 B
ytes=26972166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'X' (Cost=3949
5 Card=1586598 Bytes=26972166)
2 1 INDEX (RANGE SCAN) OF 'PK_X' (UNIQUE) (Cost=8
6 db block gets
471931 consistent gets
465812 physical reads
4. Lets try to create a b-tree index on column a (with compute statistics in create statement)
The optimzer doesn´t use it!
4a. Computes statistics (compute) for the new index and the table. Doesn´t work, fts.
4b. Changes optimizer_index_cost_adj to 30. Works (uses the new index), but does read through the whole table anyway.
4c. Tries to hint the new index in the sql statement. It works perfectly, 3 consistent reads, no phys.
4d. Tries to hint the pk index in the sql statement. It works fine, 26 consistent reads, 3 phys.
4e. Creates histograms for column a. It works, 3 cons gets, no phys. But i cant use this because of bind variables.
4f. Drops the new index because it's really not needed, we´ve got indexing in the pk. It still works, 553 consistent gets, 13 phys reads.
4g. Creates the new index again, this time with comress. Doesn´t work! FTS.
4h. Tries to alter the sqlstatement (where a = 56), maybe oracle uses some old execution plan? Doesn´t work, fts.
4i. Tries optimizer_index_cost_adj again. Same as before, reads whole table.
4j. Tries to hint again. Works just fine, uses the new index, 21 cons reads, 5 phys.
SUMMARY: whenever i use hints and histograms everything is fine.
But I want the optimizer to do this work for me,
we don´t want to use hints and since we use bind variables, histograms don´t work.
Why does´nt the optimizer use the indexes?