Re: I HAVE A PROBLEM!!!

This is another example where composite indexes provide poor performance.

in your

select * from articulos where empresa = 1 order by descripcion

Oracle performs an full index scan.

Drop all indexes on this table

create index i_table_empresa on <table> (empresa);
create index i_table_codigo on <table> (codigo);
create index i_table_descripcion on <table> (descripcion);

select * from articulos where empresa = 1 order by descripcion

This will be much faster.

"Manuel Fco. Fernández Ponce" <mfdez_at_sgie.com> wrote in message 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 26 2000 - 13:48:54 CDT