Re: O9i: general index question

On Thu, 29 Mar 2007 16:15:50 +0200, Andreas Mosmann <mosmann_at_expires-31-03-2007.news-group.org> wrote:

>Andreas Mosmann schrieb am 29.03.2007 in
><1175157630.21_at_user.newsoffice.de>:
>
>I remembered that in 2 Tables connected by join or where must exist
>corresponding indexes. So I created an index on a table with 5 rows.
>The result was funny.
>
>1.) if I do not use the small table TBNEBENANLAGEN (5 rows) the query
>needs 0.0?? sec for my 8 rows (an Index was used). But I need the values
>from table TBNEBENANLAGEN.
>2.) After I created the corresponding index on TBNEBENANLAGEN and joined
>the table the explain plan told me that there are full table scans on
>all tables but: it took about 4 secs (instead of 2000)
>
>So I should rethink using indexes at all. (just kidding).
>But can anyone explain:
>- If it is useful for CBO to chose an index if there is no join to table
>Table TBNEBENANLAGEN why it decides not to use the same if the join
>clause is the only additional clause and the conection column is a part
>of this index?
>- I understand a hash join needs corresponding indexes. But should not
>be range index scan with found values in a few rows of table
>TBNEBENANLAGEN be faster than a full table scan on TBBAEUME and a
>cartesian product?
>
>> Oracle 9.2.0.7i
>
>> actual Explain Plan
>> PARENT_ID ID OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE IO_COST BYTE TEMP_SPACE
>> -1 0 SELECT STATEMENT 15 316
>> 0 1 SORT ORDER BY 15 316
>> 1 2 TABLE ACCESS BY INDEX ROWID TBBAEUME 3 192
>> 2 3 NESTED LOOPS 13 316
>> 3 4 MERGE JOIN CARTESIAN 10 124
>> 4 5 MERGE JOIN CARTESIAN 8 102
>> 5 6 MERGE JOIN CARTESIAN 6 84
>> 6 7 MERGE JOIN CARTESIAN 4 65
>> 7 8 TABLE ACCESS FULL TBNEBENANLAGEN 2 46
>> 7 9 BUFFER SORT 2 1501
>> 9 10 TABLE ACCESS FULL TSINSTITUTIONEN 2 1501
>> 6 11 BUFFER SORT 4 1501
>> 11 12 TABLE ACCESS FULL TSINSTITUTIONEN 2 1501
>> 5 13 BUFFER SORT 6 2286
>> 13 14 TABLE ACCESS FULL TSNUTZER 2 2286
>> 4 15 BUFFER SORT 8 8514
>> 15 16 TABLE ACCESS FULL TZCODES 2 8514
>> 3 17 INDEX RANGE SCAN XTBBAEUME_TEST_NEBENANLAGEN4 NON-UNIQUE 2
>
>> Many thanks
>> Andreas Mosmann

Yours is a typical case of Compulsive Index Disorder. If you have a small table, one single index look up will cost 1 read for the index header
1 read for the index leaf block
1 read for the data block
At least 3 different reads.
Even with db_file_multiblock_read_count set to 8, Oracle can read the entire table using 1 scattered read, opposed to 3 sequential reads.

Do you want to make your application dead slow and unscalable?

Apart from that you should use dbms_xplan.display to format explain plan results. They look neater and provide more info.

I would also like to stress that queries without the Sqlserver JOIN junk are much more readable. This is one reason why you aren't getting responses, no experienced Oracle developer is using that crap.

-- 
Sybrand Bakker
Senior Oracle DBA