Partitioned table with unpartitioned global index?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Partitioned table with unpartitioned global index?

  1. #1
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    SOLARIS 7 Oracle 9.2 (sorry eyen)

    I been searching on some information for the following. Now I understand the concepts of Local, Local-Prefixed and Global Partitioned Indexes on a partitioned table, but what about the concept of a GLOBAL unpartitioned index on a paritioned table. For example...

    CREATE TABLE PARTITION_TEST
    ( ID VARCHAR2(40),
    NAME VARCHAR2(32),
    POF VARCHAR2(4),
    MONTHC CHAR(2))
    PARTITION BY RANGE (MONTHC)
    (PARTITION MONTH1 VALUES less than (2)
    TABLESPACE DATA_SML PCTFREE 10,
    PARTITION MONTH2 VALUES less than (3)
    TABLESPACE DATA_SML PCTFREE 10,
    PARTITION MONTH3 VALUES less than (4)
    TABLESPACE DATA_SML PCTFREE 10);

    INSERT INTO PARTITION_TEST VALUES('1','ANDREA','AAAA',1);
    INSERT INTO PARTITION_TEST VALUES('2','GREG','GGGG',2);
    INSERT INTO PARTITION_TEST VALUES('2','RICHARD','AAAA',3);

    COMMIT;

    --Create LOCAK primary key index

    CREATE INDEX PARTITION_TEST_PK ON PARTITION_TEST(ID, MONTHC) LOCAL (
    PARTITION PART_TEST_MONTH_1_PK TABLESPACE INDEX_SML PCTFREE 5,
    PARTITION PART_TEST_MONTH_2_PK TABLESPACE INDEX_SML PCTFREE 5,
    PARTITION PART_TEST_MONTH_3_PK TABLESPACE INDEX_SML PCTFREE 5);

    -- Create GLOBAL patitioned index
    CREATE INDEX PARTITION_TEST_NAME_IDX ON PARTITION_TEST(NAME) GLOBAL PARTITION BY RANGE (NAME)
    ( PARTITION PART_TEST_NAME_F_PK VALUES LESS THAN ('F') TABLESPACE INDEX_SML PCTFREE 5,
    PARTITION PART_TEST_MONTH_Q_PK VALUES LESS THAN ('Q') TABLESPACE INDEX_SML PCTFREE 5,
    PARTITION PART_TEST_MONTH_MAX_PK VALUES LESS THAN (MAXVALUE) TABLESPACE INDEX_SML);

    -- Create global (unpartitioned index)

    CREATE INDEX PART_TEST_GLOBAL_IDX ON PARTITION_TEST(POF) GLOBAL;

    View index details...

    SQL> SELECT INDEX_NAME, PARTITION_NAME, HIGH_VALUE,STATUS FROM USER_IND_PARTITI
    ONS;

    INDEX_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    HIGH_VALUE
    --------------------------------------------------------------------------------
    STATUS
    --------
    PARTITION_TEST_NAME_IDX PART_TEST_MONTH_Q_PK
    'Q'
    USABLE

    PARTITION_TEST_NAME_IDX PART_TEST_MONTH_MAX_PK
    MAXVALUE
    USABLE

    PARTITION_TEST_PK PART_TEST_MONTH_1_PK
    2
    USABLE

    PARTITION_TEST_PK PART_TEST_MONTH_2_PK
    3
    USABLE

    PARTITION_TEST_PK PART_TEST_MONTH_3_PK
    4
    USABLE

    PARTITION_TEST_NAME_IDX PART_TEST_NAME_F_PK
    'F'
    USABLE


    SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME,STATUS, PARTITIONED FROM USER_IND
    EXES
    WHERE INDEX_NAME = 'PART_TEST_GLOBAL_IDX'; 2

    INDEX_NAME INDEX_TYPE
    ------------------------------ ---------------------------
    TABLE_NAME STATUS PAR
    ------------------------------ -------- ---
    PART_TEST_GLOBAL_IDX NORMAL
    PARTITION_TEST VALID NO

    I just wanted to know... on a "small" (100,000 records)partitioned table (just say it has to be partitioned), what is the DISADVANTAGE of having a GLOBAL unpartitioned INDEX as opposed to a GLOBAL PARTITIONED index. Considering the UPDATE GLOBAL INDEXES can be included in partition manipulation to maintain index availablility.

    Thanks,


    [Edited by grjohnson on 07-30-2002 at 12:48 AM]
    OCP 8i, 9i DBA
    Brisbane Australia

  2. #2
    Join Date
    Jun 2000
    Posts
    117
    How the heck did you get 9.3?

    Well anyway, it depends on how often you need to rebuild the index.

    Think of it this way rebuilding a particular piece of a partition index will often be faster than a complete rebuild. Since Globally partitioned indexes are of "range" type then rebuilding a partition of the index is faster.
    E. Yen
    OCP DBA 8, 8i, 9i

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    eyen, the index rebuild would only occur once a month, when partition manupulatuion (i.e. drop/truncate) would be required.

    I mean on a table of only 100,000, it'd expect this rebuild time to be quite small.
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by grjohnson

    I just wanted to know... on a "small" (100,000 records)partitioned table (just say it has to be partitioned), what is the DISADVANTAGE of having a GLOBAL unpartitioned INDEX as opposed to a GLOBAL PARTITIONED index. Considering the UPDATE GLOBAL INDEXES can be included in partition manipulation to maintain index availablility.
    On a small table, I wouldn't expect there to be either an advantage nor a disadvantage as far as performance goes. Yes, I think using UPDATE GLOBAL INDEXES would speed your rebuild, but how long can a 100K row table take to index?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Jeff, thanks for the feedback, and as to how long to rebuild a 100K index? I'm think, not very, but that'll have to be tested later.


    Thanks,
    OCP 8i, 9i DBA
    Brisbane Australia

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