Re: O9i: general index question

On Thu, 29 Mar 2007 16:15:50 +0200, Andreas Mosmann
<mosmann@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Andreas Mosmann schrieb am 29.03.2007 in
<1175157630.21@xxxxxxxxxxxxxxxxxx>:

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
.

Relevant Pages

  • Re: delete performance from a standalone sql versus the same call from a procedure
    ... We have resolved the performance issue by tweaking the SQL. ... the 'IN' clause and an EXISTS clause as far as the plan is concerned. ...
    (comp.databases.oracle.server)
  • Re: Use of SELECT TOP x (Are you there, Kalen...heheh)
    ... My guess is that the stored procedure is using a plan ... removing a WHERE clause and adding TOP 100 ... the response time remained subsecond. ...
    (microsoft.public.sqlserver.programming)
  • Re: O9i: general index question
    ... read for the index header ... read for the index leaf block ... no experienced Oracle developer is using that crap. ...
    (comp.databases.oracle.misc)
  • Re: Filter in the Join vs Filter in the Where
    ... One thing to note is that there can be a difference in results when choosing between a filter in the WHERE and a filter in an OUTER JOIN. ... filtering the rows to be returned, or filtering the rows to be joined.) You want to be sure that Bob is aware of this. ... WHERE clause ... it is the execution plan that will tell the ...
    (microsoft.public.sqlserver.programming)
  • Re: default select order
    ... returned they should be coded with an ORDER BY clause ... ... guarantee from oracle that it won't change order otherwise. ... Execution Plan ... same just because the SQL statements are the same since there are two ...
    (comp.databases.oracle.server)