Hello everybody!
In one table there is a index desc and when i do select is more long than when i do a select and the table only have normaly index.
Someone say me why?¿?¿
Thank you very much.
Printable View
Hello everybody!
In one table there is a index desc and when i do select is more long than when i do a select and the table only have normaly index.
Someone say me why?¿?¿
Thank you very much.
what is the select statement and on what column(s) do u create the index.
The index is:Quote:
Originally posted by gpsingh
what is the select statement and on what column(s) do u create the index.
CREATE INDEX idx_fotos_ag_doc_desc ON fotos (agencia, cod_doc desc) tablespace INDX_ORA STORAGE (INITIAL 8M NEXT 4M PCTINCREASE 0);
and select:
select count(*) from fotos WHERE AGENCIA='EFE' order by cod_doc desc;
Why is better the asc index than desc??
Thanks.
To casenjoc:
U may get EXECUTION PLAN of both queries and then
reserch this problem yourself.
Check how oracle use indexes (or not use) in both queries.
Good luck.
Try this and let me know the results.
select count(agencia) from fotos WHERE AGENCIA='EFE' order by cod_doc desc;
I hope the column is highly selective .
how many records does your table have and whats the selectivity of the columns on which index are created..
cheese
anandkl
i tried with :
select count(agencia) from fotos WHERE AGENCIA='EFE' order by cod_doc desc;
and is better.
Thanks.
i tried explain plan for select ..
and utlxpls, with asc index are:
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 10 | 2 | | |
| SORT AGGREGATE | | 1 | 10 | | | |
| INDEX RANGE SCAN |IDX_FOTOS | 70 | 700 | 2 | | |
--------------------------------------------------------------------------------
and desc index are;
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 10 | 3 | | |
| SORT AGGREGATE | | 1 | 10 | | | |
| TABLE ACCESS BY INDEX RO|FOTOS | 70 | 700 | 3 | | |
| INDEX RANGE SCAN |IDX_FOTOS | 70 | | 2 | | |
--------------------------------------------------------------------------------
i don´t know interpretate but i think that is better the asc index.Why?¿?, i don´t know.
Thaks.