Re: I HAVE A PROBLEM!!!

Steve :

 Here are my views on your reply.

> select * from articulos where empresa = 1
> order by descripcion

 In this case, Oracle would use either one of the indexes since the  leading part of the index is referred in the WHERE clause.

 If the table volume is less, then the Cost based optimizer would ignore  the index and would perform a Full table scan because it will be  faster than an index scan. We have to take this on a case to case  basis. If the table volume is good, then the optimizer decides to go  for the index (in Cost based, u have to analyze tables and indexes for  the optimizer to make the right decision).

 So your conclusion of "Oracle performs an full index scan..." is NOT  always TRUE.

In article <8qqr3r$ght$1_at_spiney.sierra.com>,   "Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote:
> 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??
> >
> >
> >
> >
> >
>
>

--
Regards
Rajagopal Venkataramany


Sent via Deja.com http://www.deja.com/
Before you buy.