-
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.
-
Originally posted by gpsingh
what is the select statement and on what column(s) do u create the index.
The index is:
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
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|