Create primary key with descending index

HaniYS · Oct 22, 2020 4:29AM

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?

BEDE · Oct 22, 2020 4:35AM

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.

mathguy · Oct 22, 2020 4:49AM

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 · Oct 22, 2020 11:28AM

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.

BEDE · Oct 22, 2020 4:35AM

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.

mathguy · Oct 22, 2020 4:49AM

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 · Oct 22, 2020 11:28AM

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.

Solomon Yakobson · Oct 22, 2020 11:56AM

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.

Forum Stats

Quick Links

Categories

Create primary key with descending index

Best Answers

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Create primary key with descending index

Best Answers

Answers