Re: O9i: general index question

sybrandb@xxxxxxxxx schrieb am 29.03.2007 in <pjbo03pp3oi48m4k5fi1n9nod2cos1k7nf@xxxxxxx>:

Yours is a typical case of Compulsive Index Disorder.
and how to order? Or did I misunderstand you?

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.
this is surely correct for the small table, but not for the big one joined. That is why I did not try to build an index on the small table. but Oracle seems to need it for its CBO.

Do you want to make your application dead slow and unscalable?
No, exactly this I wanted to change and had no more idea how to.

Apart from that you should use dbms_xplan.display to format explain
plan results. They look neater and provide more info.
Thank you, nice hint. the actual execution plan displayed by dbms_xplan.display is

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37266 | 12M| | | 3368 |
| 1 | SORT ORDER BY | | 37266 | 12M| 25M| | 3368 |
| 2 | HASH JOIN | | 37266 | 12M| | | 1518 |
| 3 | TABLE ACCESS FULL | TSNUTZER | 127 | 2286 | | | 2 |
| 4 | HASH JOIN | | 37271 | 11M| | | 1514 |
| 5 | TABLE ACCESS FULL | TZCODES | 387 | 8514 | | | 2 |
| 6 | HASH JOIN | | 37272 | 10M| | | 1509 |
| 7 | TABLE ACCESS FULL | TSINSTITUTIONEN | 79 | 1501 | | | 2 |
| 8 | HASH JOIN | | 37272 | 9M| | | 1505 |
| 9 | TABLE ACCESS FULL | TSINSTITUTIONEN | 79 | 1501 | | | 2 |
| 10 | HASH JOIN | | 37273 | 9536K| | | 1501 |
| 11 | TABLE ACCESS FULL| TBNEBENANLAGEN | 1 | 70 | | | 2 |
| 12 | TABLE ACCESS FULL| TBBAEUME | 447K| 81M| | | 1487 |
------------------------------------------------------------------------------------

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.
Sorry, I did not know this. But my first and still unanswered question is a general one: What way I have to go to find out which composition of query/index give results in the fastest time? If I have an existing query and table situation, how to create matching indexes? Where can I read about it?
I can not imagine that all the experienced oracle developers only work on try and error. if you have f.e. 6 columns mentioned in JOIN, WHERE an maybe GROUP BY clause there are 720 possibilities.

Thanks for your answer
Andreas Mosmann

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