2

I can create a primary key for a table and then reverse it with the command

alter index T_PK rebuild reverse online;

How can I create it reversed?

  • For my own information: why would I want to create a reversed index? – kevinsky Sep 8 '15 at 16:07
  • When the PK is a sequence, it can cause contention when newly added rows all hit the same block. Reversing the index solves that problem. orafaq.com/node/2907 – Mark Harrison Sep 8 '15 at 16:10
2

The primary key index (including index parameters) can be specified with the using index clause. You can include the reversed specification there.

create table T(X number primary key
                 using index (create unique index T_PK on T(X) reverse));
  • You probably want index to be unique as well in this case - .... using index (create unique index ....) – a1ex07 Sep 8 '15 at 22:48
  • 2
    I checked that, Oracle will take care of that automatically since it's a primary key. – Mark Harrison Sep 8 '15 at 23:30
  • @ Mark Harrison: Not exactly. Unique constraint can be supported by non-unique index (as well as by unique which is default) , but presence of unique constraint does not make index unique . . You can test it by dropping constraint and keeping index. – a1ex07 Sep 9 '15 at 14:26
  • @a1ex07, you're right. Thanks, and answer updated. – Mark Harrison Sep 9 '15 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.