Re: I HAVE A PROBLEM!!!

From: Big H <ihwh_at_iname.com>
Date: Tue, 19 Sep 2000 22:44:53 +0200
Message-ID: <8q8jl4$jjn$1@nereid.worldonline.nl>

IMHO it is rather simple.
Oracle can do an index sort, but it has to read all the index blocks in memory (viz. SGA) in order to be able to sort. If the init.ora parameter sort_area_size is 'cramped' and the settings for the temp(orary) tablespace are not in sync you have another bottleneck to worry about, because Oracle is 'crippled' in performing its task. What may help the index sort (but you need to try that out!!!) that you make your query look like

SELECT *

FROM      ARTICULOS
WHERE    EMPRESA         = 1
AND         DESCRIPCION > ''

ORDER BY DESCRIPCION This may provide the optimizer the 'clue' to use index_002 instead of looking what to do.

Kind regards,

H.

"Manuel Fco. Fernández Ponce" <mfdez_at_sgie.com> schreef in bericht news:8pvpoo$jms$1_at_diana.bcn.ttd.net...
> I`M WORKING WITH ORACLE8 FOR NT.
>
> I HAVE CREATED ONE TABLE LIKE THIS
>
> ID NUMBER(9)
> EMPRESA NUMBER(9)
> CODIGO VARCHAR2(10)
> DESCRIPCION VARCHAR2(40)
> ...
> MORE FIELDS...
> ...
> INDEX
>
> INDEX_001 FIELDS(EMPRESA,CODIGO)
> INDEX_002 FIELDS(EMPRESA,DESCRIPCION)
>
> WHEN I HAVE A SELECT LIKE THIS:
>
> SELECT * FROM ARTICULOS
> WHERE EMPRESA=1
> ORDER BY DESCRIPCION
>
> IT TAKES A LOT OF TIME TO GIVE ME THE FIRST ROWS.
>
> AND IF I DO:
>
> SELECT * FROM ARTICULOS
> WHERE EMPRESA=1
>
> IT TAKES A FEW TIME.
>
> AND IF I DROP INDEXES IT TAKE THE SAME TIME OR LESS.
>
> WHAT HAPPEND, PLEASE??
>
>
>
>
>
Received on Tue Sep 19 2000 - 15:44:53 CDT