Re: oracle optimizer

With a RULE hint the optimizer allways will use a index if possible (the index has to start with fields that are in your WHERE clause).

When using cost based, the optimizer calculates the IO needed for retrieval by index against retrieval by a full table scan. Possible considerations:

• the selectiveness of the index (estimated from the statistics)
• the high water mark of the table (the full table scan will read up to this point)
• how many blocks are read in one IO during a full table scan (parameter db_file_mutliblcok_read_count)

Reading via an index has disadvantages as compared to a full table scan: - you have to read index blocks
- table blocks may be retrieved randomly physically, causing a lot of disk head movement

For these reasons the estimated selectivity of the index has to be very good to beat the speed of an full table scan.

Jaap.

José Antonio Morcillo Valenciano heeft geschreven in bericht <38993BE6.1B95A2B3_at_gtt.es>...
>Hi to every body in the group!
> I have some doubts about the way in wich oracle optimized 'SELECT's
>and the use and not use of the HINTS /*+ RULE */ /*+ COST */
>/* CHOOSE */ and the default one.
> I'm working in an enviroment where there are three schemas: one is
>where we are working and the last is where the users work. We
>run a SELECT yesterday in the two schemas and in the first one
>we get:
>
>SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=19 Bytes=1235)
> TABLE ACCESS (BY INDEX ROWID) OF ESTI_ESTADISTICAS (Cost=23 Card=19
>Bytes=1235)
> INDEX (RANGE SCAN) OF UQ_ESTADIS_ESTA (UNIQUE) (Cost=11 Card=19)
>
>and the statement is :
> select id_esti
> from esti_estadisticas
> where ejer_contab_esti = 1999
> and mes_esti = 12
> and id_orga = 1
> and cod_tipo_esti = 3
>
>
>and in the last schema we get:
>SELECT STATEMENT Optimizer=CHOOSE (Cost=498 Card=457 Bytes=29705)
> TABLE ACCESS (FULL) OF ESTI_ESTADISTICAS (Cost=498 Card=457
>Bytes=29705)
>
>
>we have the same index in the two and we run analize table in the last
>one where we have
>170000 records and in the first we have 130000
>
>Why in the second one is doing a FULL and in the first one no?? if we
>include the HINT /*+ RULE */ we don' t get a FULL. I don't understand
>why this happends
>
>
>
>I'm very sorry for my little english!
> thank you ver much for you time!
>
> Jose Antonio Morcillo Valenciano, jamv_at_gtt.es
>
Received on Thu Feb 03 2000 - 11:52:37 CST