PK ON A LOCAL INDEX
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: PK ON A LOCAL INDEX

  1. #1
    Join Date
    Feb 2001
    Posts
    82
    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.

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Are you taking about creating a primary key on a local index in a partitioned table?
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Feb 2001
    Posts
    82
    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?

  4. #4
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    PK on partitioned table

    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

  5. #5
    Join Date
    Feb 2001
    Posts
    82
    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.

  6. #6
    Join Date
    Feb 2001
    Posts
    82
    gurus can someone explain it to me so I can understand what oracle is behaving when enforcing this kind of constraints....


    tnx very much......

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203

    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,

    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    Feb 2001
    Posts
    82
    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

  9. #9
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width