Partitioned table with unpartitioned global index?

[grjohnson]

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]

[eyen]

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.

[grjohnson]

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.

[marist89]

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?

[grjohnson]

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,