oracle optimizer

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 - 02:27:18 CST