Why doesnB4t it use the index??

[Michael Schulte zur Sur]

Hi,

I am using Oracle 8.1.6.2.0 and the following query does a full table scan in spite that there is an index, which sorts already as needed. The optimizer is set to choose.
Anyone see why?

Michael

SELECT ROWID, suapos.*
FROM suapos
WHERE basman_nr = :ph0
AND basman_nl = :ph1
ORDER BY basman_nr, basman_nl, suakpf_beznr, suapos_posnr

[Yogeeraj_1]

hi,

are your statistics up-to-date?

Post output of:
select index_name, num_rows, sample_size, last_analyzed, user_stats, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key
from user_indexes
where table_name = 'SUAPOS';


can you post execution plan?

set autotrace traceonly
then run your query.

revert
Yogeeraj

[Michael Schulte zur Sur]

Hi Yogeeraj,

attached with the first message you`ll find a report for the index, I hoped he would take.
Here the output of your query.
I somewhat can??t get the explain plan copyied from sql analyze, but the does this:
1. select statement
2. order by
3. table access(full)


INDEX_NAME NUM_ROWS SAMPLE_SIZE LAST_ANA USE BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- -------- --- ---------- ----------- ------------- ----------------------- -----------------------
SUAPOS_1 77407 0 16.09.03 NO 1 160 77407 1 1
SUAPOS_2 77407 0 16.09.03 NO 1 171 14911 1 1
SUAPOS_3 77407 0 16.09.03 NO 1 207 77055 1 1
SUAPOS_UIND 77407 0 16.09.03 NO 1 162 77407 1 1

[Jean-Luc Oudart]

I got the issue once.

I had a fairly small table with an index and eventhough the query was based on the index , oracle was doing full scan.

It appeared that we had a db_file_multiblock_read_count setup to 16 and the optimizer to go for the FTS all the time.

We tricked it when we reloaded the table with PCTFREE 50.

Rgds,
Jean-Luc

[Michael Schulte zur Sur]

Hi Jean-Luc
db_file_multiblock_read_count = 32
sql analyze thinks, the FTS results in aproximately 400 lines, though the table has 77000 lines. This doesn??t make much sense to me. Ok, the table is around 12mb, but it still is causing a lot of disk access, which in my oppinion is a waste.

thanks for your thoughts

Michael

[Mariani Alberto]

We had a problem like this just yesterday: our Oracle gurus traced a query that took a long time to run, and they found it did a FTS even though we have 15 indexes on the table.

The problem turned out to be the table degree, which was set to 2.
Even if our optimizer mode is set to RULE, it did an FTS. We solved the problem setting the table degree to 1.

HTH.

Bye,
Alberto

[Yogeeraj_1]

hi,

Maybe the CBO looked at this table and figured out that it would retrieve > 50 percent of the rows. Doing this via an index would be slow; i would have to read an index block and then process each of the rows on it - and for every other row, i would be doing a database block get, to get the row data. It would be much more efficient just to read every row in the block and find the 50 percent (or more) of the rows we are going to process...

What is the value of db_file_multiblock_read_count ?

Can you post the tkprof output of the query?

- alter session set sql_trace=true;
- n query
- exit
- tkprof
- post just the relevant portions of the tkprof

regards
Yogeeraj

[Michael Schulte zur Sur]

Here the result, I hope, it the interesting part.

Michael

PARSING IN CURSOR #1 len=135 dep=0 uid=20 oct=3 lid=20 tim=313323287 hv=19492339
5 ad='14ed7f90'
select rowid, suapos.* from suapos
where basman_nr = '1' and basman_nl = '1'
order by basman_nr, basman_nl, suakpf_beznr, suapos_posnr
END OF STMT
PARSE #1:c=2,e=3,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=313323287
EXEC #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=313323288
FETCH #1:c=65,e=66,p=985,cr=989,cu=5,mis=0,r=0,dep=0,og=4,tim=313323354
*** 2003-09-19 18:49:28.307
XCTEND rlbk=0, rd_only=1
STAT #1 id=1 cnt=0 pid=0 pos=0 obj=0 op='SORT ORDER BY '
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=4956 op='TABLE ACCESS FULL SUAPOS '

[Brian Crabtree]

Please run the following:

select index_name,column_name from dba_ind_columns where table_name = 'SUAPOS' order by index_name,column_name;

Thanks,

Brian

[Michael Schulte zur Sur]

Hi everybody,

I am on vaccation now, so I won´t be able to look into it for a while.

Micha

[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