DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: index desc

  1. #1
    Join Date
    Mar 2002
    Posts
    10

    Arrow

    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.

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    what is the select statement and on what column(s) do u create the index.

  3. #3
    Join Date
    Mar 2002
    Posts
    10

    Arrow

    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.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  5. #5
    Join Date
    Feb 2001
    Posts
    389
    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 .

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    how many records does your table have and whats the selectivity of the columns on which index are created..

    cheese
    anandkl
    anandkl

  7. #7
    Join Date
    Mar 2002
    Posts
    10

    Arrow

    i tried with :
    select count(agencia) from fotos WHERE AGENCIA='EFE' order by cod_doc desc;
    and is better.
    Thanks.



  8. #8
    Join Date
    Mar 2002
    Posts
    10

    Arrow

    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
  •  


Click Here to Expand Forum to Full Width