Skip to Main Content
  • Questions

  • primary key on with augmented index

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, sagi.

Asked: December 20, 2010 - 2:09 pm UTC

Last updated: December 21, 2010 - 7:16 am UTC

Version: 10.2

Viewed 1000+ times

You Asked

Hi Tom ,
I was wondering if there is a way in oracle to do the following

create an index on 3 columns of a table , an use the same index to enforce a PK on the 2 first columns

e.g.(not the best example)

emp table
(fname varchar,lname varchar,hiredate date,col4 number)

fname and lname are pk , however, since most of thw queries also check the value of the hiredate field , I would like to use the same index for both the pk and the selects. tried to think about FBI , but didnt see any way out.

sagsag

and Tom said...

You don't need to do anything special - as long as the primary key columns are on the leading edge of the index - it may include any columns you like.

ops$tkyte%ORA11GR2> create table t
  2  ( x int,
  3    y int,
  4    z int,
  5    data varchar2(20),
  6    constraint t_pk primary key(x,y) using index (create index t_pk on t(x,y,z) )
  7  )
  8  /

Table created.

ops$tkyte%ORA11GR2> select column_name from user_ind_columns where index_name = 'T_PK';

COLUMN_NAME
-------------------------------------------------------------------------------
X
Y
Z

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Learned something new!

Robert, December 20, 2010 - 4:08 pm UTC


Can't get too "fancy" with index.

Shannon Severance, December 20, 2010 - 4:25 pm UTC

At least as of 10g R2 the index needs to be pretty plain vanilla to use for enforcing a primary key constraint:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t 
  2  ( x int,
  3    y int,
  4    z int,
  5    data varchar2(20),
  6    constraint t_pk primary key (x, y) using index (create index t_pk on t(x, y desc, z))
  7  )
  8  /
create table t
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.


SQL> create table t
  2  ( x int,
  3    y int,
  4    z int,
  5    data varchar2(20),
  6    constraint t_pk primary key (x, y) using index (create unique index t_pk on t(x, y, z))
  7  )
  8  /
create table t
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.


Tom Kyte
December 20, 2010 - 4:28 pm UTC

yes, you need a "normal" index in all releases.


right to the point

sagi yaron, December 21, 2010 - 2:19 am UTC


Any risks with this technique?

A reader, December 21, 2010 - 6:42 am UTC

I've considered this construct on a few occasions, but haven't implemented it. Are there any risks to creating a PK (or other unique constraint) using a subset of a unique index?
Tom Kyte
December 21, 2010 - 7:16 am UTC

you'll never get an index unique scan on the index - but that should be OK in general

ops$tkyte%ORA11GR2> create table t
  2  ( x int,
  3    y int,
  4    z int,
  5    data varchar2(20),
  6    constraint t_pk primary key (x, y) using index (create index t_pk on t(x, y, z))
  7  )
  8  /

Table created.

ops$tkyte%ORA11GR2> exec gen_data( 'T', 10000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', cascade => true );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * From t where x = 1 and y = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3772518221

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    42 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    42 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_PK |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=1 AND "Y"=2)

ops$tkyte%ORA11GR2> alter table t drop constraint t_pk drop index;

Table altered.

ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(x,y);

Table altered.

ops$tkyte%ORA11GR2> select * From t where x = 1 and y = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1303508680

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    42 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    42 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=1 AND "Y"=2)

ops$tkyte%ORA11GR2> set autotrace off

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.