-
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
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|