Why doesnB4t it use the index??

[Michael Schulte zur Sur]

I just came into the office for one hour and here the output.

INDEX_NAME
------------------------------
COLUMN_NAME

SUAPOS_UIND
BASMAN_NL

SUAPOS_UIND
BASMAN_NR

SUAPOS_UIND
SUAKPF_BEZNR

SUAPOS_UIND
SUAPOS_POSNR

SUAPOS_1
BASMAN_NL

SUAPOS_1
BASMAN_NR

SUAPOS_1
SUAKPF_BEZNR

SUAPOS_1
SUAPOS_POSNR

SUAPOS_2
BASMAN_NL

SUAPOS_2
BASMAN_NR

SUAPOS_2
SUAKPF_BEZNR

SUAPOS_2
SUAPOS_STATUS

SUAPOS_3
BASMAN_NL

SUAPOS_3
BASMAN_NR

SUAPOS_3
SUAKPF_BEZNR

SUAPOS_3
SUAPOS_AWBZZZ


16 Zeilen ausgewählt

[Hein van den Heuvel]

hmmm,

A small variation on Brian's request would make the output more readable, more useable:

column COLUMN_NAME format a20;
column INDEX_NAME format a15;
select INDEX_NAME, COLUMN_POSITION "Pos", COLUMN_NAME from ALL_IND_COLUMNS
where TABLE_NAME = 'SUAPOS' order by INDEX_NAME, COLUMN_POSITION
/

[Michael Schulte zur Sur]

Thanks for the advice.
I will be gone again til Oct. 20th

INDEX_NAME Pos COLUMN_NAME
--------------- ---------- --------------------
SUAPOS_UIND 1 BASMAN_NR
SUAPOS_UIND 2 BASMAN_NL
SUAPOS_UIND 3 SUAKPF_BEZNR
SUAPOS_UIND 4 SUAPOS_POSNR
SUAPOS_1 1 SUAPOS_POSNR
SUAPOS_1 2 BASMAN_NR
SUAPOS_1 3 BASMAN_NL
SUAPOS_1 4 SUAKPF_BEZNR
SUAPOS_2 1 SUAPOS_STATUS
SUAPOS_2 2 BASMAN_NR
SUAPOS_2 3 BASMAN_NL
SUAPOS_2 4 SUAKPF_BEZNR
SUAPOS_3 1 SUAPOS_AWBZZZ
SUAPOS_3 2 BASMAN_NR
SUAPOS_3 3 BASMAN_NL
SUAPOS_3 4 SUAKPF_BEZNR

[Tim D Fulford]

Hi

Off slightly track here, but there are some VEY good reasons why the SQL engine may not pik the an indexed route.
1 - variations in index is too small, e.g. male/female.
2 - Estimate for number of rows retrieved is quite large > 25% of table
3 - Table is very small. An index scan could be SLOWEER!
4 - Statistics out of date (this is really an error)
Regards

Tim

[Steve Bear_1]

You can HINT the query using the composite index to force the use of the index while tracing the query you can generate an explain plan so you can see what the cost of the index use is.

SELECT /*+ INDEX (SUPAS SUAPOS_UIND) */ ROWID, suapos.*
FROM suapos
WHERE basman_nr = :ph0
AND basman_nl = :ph1
ORDER BY basman_nr, basman_nl, suakpf_beznr, suapos_posnr

[Eric Ladner]

Here's something to try.

select count(*) from suapos;
select count(*) from suapos where basman_nr = 'whatever' and basman_nl = 'whatever';

Compare the two counts. If the count from the second query is a large percentage of the first count, a FTS is justified.

If not, try reanalyzing the indexes:

analyze table suapos estimate statistics sample 30 percent;
analyze table suapos compute statistics for all indexes;
analyze table suapos compute statistics for all indexed columns;

The combination of those three analyzes will give you detailed statistics on the indexes down to the column level (including data distribution inside each column).

[Stan_17]

Hi,

Its very hard to tell without the output of explain plan, but from what you have posted, it looks like you have the defaults set for optimizer_index_cost_adj=100 and optimizer_index_caching=0 parameters which makes oracle to think single block read and multi block read both costs the same. This is not true most of the cases especially in OLTP systems. Besides, you have db_file_multi_block_read_count (dbfmrc) set to 32 which makes full table scan lot cheaper than single block index read.


Query returning < 15% of rows from a dataset means it has to use index -- this is absolutely in-correct. Its all depends on how the data in placed in the block.

for e.g, if each of basman_nr and basman_nl row found in all available table blocks say 20, then which one oracle would prefer? definitely it would go for full table scan as its easy to read all 20 blocks in one dbfmrc with cost of 1 as opposed to indexes where the cost is 21 (20 single data block read + 1 leaf block read)


Anyway, 10053 trace should give you an idea of why full table scan was cheaper than index.

hth,
Stan

[Michael Schulte zur Sur]

Hi Eric,

I did, what you proposed and I found out, that basman_nr has only 6 different values and basman_nl only 28. SQL Analyze says, it expects 478 lines from 76650 rows, which is significant less than 1%. Cost for first rows would use Index. Cost for all rows however not. Rule based would use Index. If I delete statistics for this table and indices only, would it use rule and would this be advisable?

Michael