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