PK ON A LOCAL INDEX

is it advisable 2 create a primary key on a local index. if so, what r the advantages and disadvantages on creating those kinds of indexes.

Are you taking about creating a primary key on a local index in a partitioned table?

1.
create table rb_tran_hist
(seqno number, tran_Date date .........)
partition by range (tran_date)
(partion rth_jan values less than
(to_date('02012001','ddmmyyyy')) tablespace ts_part_jan,
.
.
.
partion rth_dec values less than (to_date('01012002','ddmmyyyy')) tablespace ts_part_dec)

2.
create index idx_seqtrand on rb_Tran_hist
(seqno,tran_date)
local
(partition rth_jan tablespace ts_part_jan_idx,
.
.
.
partition rth_dec tablespace ts_part_dec_idx)

3.
alter table rb_tran_hist
add constraint idx_seqtrand primary key (seqno, tran_date)
using index;


what I want 2 know is, y do u have to create the primary key on the localised index where in u can define a unique index on that partitioned table, and also take note of the name of the local index and the primary key constraint, they both have the same name.

Our former DBA configured this and I could not c his point y he did this....
can anyone explain it 2 me?

Hi,

What your DBA did was only generating the primary key using an index. I don't think he made a separate index with the same name. It will also show up in dba_indexes (and other dict tables).

Can you tell me if idx_seqtrand is unique?

You can only create a local primary key if the partition key is part of the PK constraint.
(Which is the case with tran_date).

Greetings

tycho

no, the idx_seqtrand is non-unique, that's what i'm wondering. he's enforcing a non-unique key on that table, where in u can enforce a unique key on that table, right???

so what's the use of creating another constraint (PK)???

isnt' that an additional overhead to oracle.

gurus can someone explain it to me so I can understand what oracle is behaving when enforcing this kind of constraints....


tnx very much......

1.
create table rb_tran_hist
(seqno number, tran_Date date .........)
partition by range (tran_date)
(partion rth_jan values less than
(to_date('02012001','ddmmyyyy')) tablespace ts_part_jan,
.
.
.
partion rth_dec values less than (to_date('01012002','ddmmyyyy')) tablespace ts_part_dec)

2.
create index idx_seqtrand on rb_Tran_hist
(seqno,tran_date)
local
(partition rth_jan tablespace ts_part_jan_idx,
.
.
.
partition rth_dec tablespace ts_part_dec_idx)

3.
alter table rb_tran_hist
add constraint idx_seqtrand primary key (seqno, tran_date)
using index;

As far as I'm concerned, this IS the ONLY way to create a LOCAL INDEXED PRIMARY KEY, becasue, your primay key on a partitioned table, HAS to be a. The unique key AND, b). the partition column name, IN ORDER for you to be able to

a). Truncate and partition
b). Drop a partition

WITHOUT making the primary KEY unusable.

What your dba has perfomed is 100% correct. IF you create a PK INDEX ONLY on JUST the sequence, a GLOBAL index is created and when a partition is dropped or truncate, it will make you PRIMARY key 'UNUSABLE', and will have to be rebuild every time.

What the dba has completed, is a ability to DROP or TRUNCATE a partition without affecting any other part of the primary key.


Hope this help.

Cheers,

how would I know if oracle is using partition pruning? is there any views where I can find it????

and tnx for the enlightenment.... johnson

Hi Antonv,

Firts this statement is incorrect.

alter table rb_tran_hist
add constraint idx_seqtrand primary key (seqno, tran_date)
using index;

It has to be.

alter table rb_tran_hist
add constraint idx_seqtrand primary key (seqno, tran_date)
using index LOCAL;

If you want to know if oracle is using partition pruning you have to use the plan_table.

Greetigs Tycho