Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Create primary key with descending index

HaniYSOct 22 2020

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?
This post has been answered by BEDE on Oct 22 2020
Jump to Answer

Comments

BEDE
Answer

So, the order has to do with the select statement, not with index creation.
The Oracle optimizer would know how to use the index in descending order when it is good to do so. In any event, there is also the SQL hint index_desc, which you may add to your query to indicate the optimizer to use that index in descending order. For SQL hints you should read, for instance: https://docs.oracle.com/cd/B13789_01/server.101/b10752/hintsref.htm.

Marked as Answer by HaniYS · Oct 22 2020
1 person found this helpful
mathguy

In any case - if you want the PK to be in decreasing order over time, don't change the PK, change the sequence. Use the INCREMENT BY clause, with a negative increment (-1 if you have no reason to choose a different value).
For example, if this is for a company with seven thousand employees currently, and you don't expect it to ever have 1 million employees, you could do something like this. (Some sequence values may remain unused, but you get the idea.)

create sequence myseq maxvalue 999999 minvalue 1 increment by -1;
Solomon Yakobson

And what this has to do with descending index? Index will be built in ascending order regardless of inserted values. And inserting values in descending order will cause index double in size. When inserted values are increasing they end up in last leaf block and when last leaf block becomes full Oracle does 90-10 split. Not the case when values are decreasing. So only 50-50 splits are performed and as a result index is left with 50% empty blocks which can not be reused:

drop sequence tbl_seq
/
drop table tbl purge
/
create table tbl(n number)
/
create index tbl_idx
  on tbl(
         n
        )
/
create sequence tbl_seq
/
insert
  into tbl
  select  tbl_seq.nextval
    from  dual
    connect by level <= 100000
/
commit
/
analyze index tbl_idx
  validate structure
/
select  lf_rows,
        lf_blks,
        pct_used
  from  index_stats
  where name = 'TBL_IDX'
/


   LF_ROWS    LF_BLKS   PCT_USED
---------- ---------- ----------
    100000        199        100


drop sequence tbl_seq
/
drop table tbl purge
/
create table tbl(n number)
/
create index tbl_idx
  on tbl(
         n
        )
/
create sequence tbl_seq
  maxvalue 100000
  minvalue 1
  increment by -1
/
insert
  into tbl
  select  tbl_seq.nextval
    from  dual
    connect by level <= 100000
/
commit
/
analyze index tbl_idx
  validate structure
/
select  lf_rows,
        lf_blks,
        pct_used
  from  index_stats
  where name = 'TBL_IDX'
/


   LF_ROWS    LF_BLKS   PCT_USED
---------- ---------- ----------
    100000        403         50

SY.

1 person found this helpful
Solomon Yakobson

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.

1 - 4

Post Details

Added on Oct 22 2020
#database, #index, #sql, #table
4 comments
2,604 views