Create primary key with descending index — oracle-tech

    Forum Stats

  • 3,702,035 Users
  • 2,239,551 Discussions
  • 7,835,727 Comments

Discussions

Create primary key with descending index

HaniYSHaniYS Posts: 980 Bronze Badge

Hi

I use Oracle Enterprise 19, i have table with primary key 1 column as sequence, i need to change the index of primary key to be descending sorting rather than ascending sorting because most of query that i do from the table will be sort by primary key descending, any suggestion?

Best Answers

Answers

  • Solomon YakobsonSolomon Yakobson Posts: 17,773 Black Diamond

    In addition, in most cases, ASC/DESC makes no diff since B-tree index blocks have liks to both next and previous so optimizer can read index either in ascending or in descending order to avoid sort:

    SQL> explain plan for select * from tbl where n between 99 and 199 order by n desc;
    
    
    Explained.
    
    
    SQL> select * from table(dbms_xplan.display);
    
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3099095014
    
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |   101 |  1313 |     1   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN DESCENDING| TBL_IDX |   101 |  1313 |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    
    
       1 - access("N">=99 AND "N"<=199)
    
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    
    17 rows selected.
    
    
    SQL>
    


    SY.

Sign In or Register to comment.