Re: O9i: general index question

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

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de