Re: O9i: general index question

From: Andreas Mosmann <mosmann_at_expires-31-03-2007.news-group.org>
Date: Fri, 30 Mar 2007 09:42:34 +0200
Message-ID: <1175240554.24@user.newsoffice.de>

sybrandb_at_hccnet.nl schrieb am 29.03.2007 in <pjbo03pp3oi48m4k5fi1n9nod2cos1k7nf_at_4ax.com>:

> 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

|   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

Received on Fri Mar 30 2007 - 02:42:34 CDT