-
I have a partitioned table which is partitioned by date field.
PK has a global index, other indexes are local indexes.
When I try to select from one of the partition and looked at the execution plan it uses a index, same query with the different date and different partition, looked the execution plan which uses a full table scan.
Also, looked at the status of the indexes, which are in usable state.
I rebuild the index and try to run the query and results were same as before (Full table scan instead of index).
Please let me know, what could be the problem....
Thanks..
-
Any suggetion?
Please let me know....
Thanks..
-
a) When you say "uses an index", do you mean a local or global index ?
b) Is the index quite selective ?
c) Do the two partitions have roughly the same amount of data ?
d) Is it a bitmap index or a btree index ?
e) Have you analyzed the table as a single unit or have you analyzed each partition separately ? Have you done a "for all indexed columns" to get the histogram data ?
-
You have to query the columns PARTITION_START and PARTITION_STOP from PLAN_TABLE.
Use
Use UTLXPLS.SQL whenever partitioned table is used for explain plan.
-
Hi :
Still having a problem...Don't know what do to?
a) When you say "uses an index", do you mean a local or global index ?
=> Local Index...
b) Is the index quite selective ?
=> Yes
c) Do the two partitions have roughly the same amount of data ?
=> Yes. Also, I tried with deleting the records.. Did not help much..
d) Is it a bitmap index or a btree index ?
=> btree
e) Have you analyzed the table as a single unit or have you analyzed each partition separately ? Have you done a "for all indexed columns" to get the histogram data ?
=>Analyzed each partition separately..
Thanks.
-
Bookmark Fixed font Go to End
Doc ID: Note:139707.1
Subject: "Update Global Indexes" Allows Automatic Global Index Maintenance During DDL
Type: BULLETIN
Status: REVIEWED
Content Type: TEXT/PLAIN
Creation Date: 12-APR-2001
Last Revision Date: 08-OCT-2001
*************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************
PURPOSE
-------
This bulletin explains:
1. The new clause "UPDATE GLOBAL INDEXES" and when it can be used in
operations performing DDL on table partitions, depending on
the type of partitioning and the type of DDL performed:
* range
* hash
* range hash (composite)
* list
2. Which performance considerations would lead to choosing REBUILD INDEX
rather than use UPDATE GLOBAL INDEXES.
SCOPE & APPLICATION
-------------------
For all DBAs maintaining partitioned tables owning GLOBAL indexes.
First 4 tables are created to perform tests.
1. Set up a RANGE partitioned table and a GLOBAL index:
SQL> create table orders (
2 order_no number,
3 part_no varchar2(40),
4 ord_date date
5 )
6 partition by range (ord_date)
7 (partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
8 partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
9 partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
10 partition Q4 values less than (TO_DATE('03-JAN-2000','DD-MON-YYYY'))
11 );
Table created.
SQL> create index orders_global_idx
2 on orders(ord_date)
3 global partition by range (ord_date)
4 (partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
5 partition GLOBAL2 values less than (TO_DATE('01-SEP-1999','DD-MON-YYYY')),
6 partition GLOBAL3 values less than (TO_DATE('01-DEC-2000','DD-MON-YYYY')),
7 partition GLOBAL4 values less than (MAXVALUE)
8 );
Index created.
SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
2 part_name , status
3 from dba_ind_partitions
4 where index_name= 'ORDERS_GLOBAL_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
ORDERS_GLOBAL_IDX GLOBAL1 USABLE
ORDERS_GLOBAL_IDX GLOBAL2 USABLE
ORDERS_GLOBAL_IDX GLOBAL3 USABLE
ORDERS_GLOBAL_IDX GLOBAL4 USABLE
SQL> insert into orders values (1,100,TO_DATE('02-FEB-1999','DD-MON-YYYY'));
2. Set up a HASH partitioned table and a GLOBAL index:
SQL> CREATE TABLE emp_hpart(
2 empno NUMBER(4) NOT NULL,
3 ename VARCHAR2(10),
4 sal NUMBER(7,2))
5 PARTITION BY HASH(sal)
6 (PARTITION H1, PARTITION H2, PARTITION H3, PARTITION H4);
Table created.
SQL> CREATE INDEX emp_global_HASH_idx ON emp_hpart(ename)
2 GLOBAL PARTITION BY RANGE (ename)
3 (PARTITION p1 VALUES LESS THAN ('N') ,
4 PARTITION p2 VALUES LESS THAN (MAXVALUE));
Index created.
SQL> select substr(index_name,1,20) index_name,
2 substr(partition_name,1,20) part_name,status
3 from dba_ind_partitions
4 where index_name= 'EMP_GLOBAL_HASH_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
EMP_GLOBAL_HASH_IDX P1 USABLE
EMP_GLOBAL_HASH_IDX P2 USABLE
SQL> insert into emp_hpart values (1,'AAA',100);
3. Set up a COMPOSITE partitioned table and a GLOBAL index:
SQL> CREATE TABLE emp_composite(
2 empno NUMBER(4) NOT NULL,
3 ename VARCHAR2(10),
4 sal NUMBER(6))
5 PARTITION BY RANGE(empno)
6 SUBPARTITION BY HASH(sal) SUBPARTITIONS 4
7 (PARTITION p1 VALUES LESS THAN (50),
8 PARTITION p2 VALUES LESS THAN (100),
9 PARTITION p3 VALUES LESS THAN (150),
10 PARTITION p4 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> CREATE INDEX emp_global_composite_idx ON emp_composite(ename)
2 GLOBAL PARTITION BY RANGE (ename)
3 (PARTITION p1 VALUES LESS THAN ('N') ,
4 PARTITION p2 VALUES LESS THAN (MAXVALUE));
Index created.
SQL> select substr(index_name,1,20) index_name,
2 substr(partition_name,1,20) part_name,status
3 from dba_ind_partitions
4 where index_name= 'EMP_GLOBAL_COMPOSITE_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
EMP_GLOBAL_COMPOSITE P1 USABLE
EMP_GLOBAL_COMPOSITE P2 USABLE
SQL> insert into emp_composite values (1,'AAA',100);
4. Set up a LIST partitioned table and a GLOBAL index:
SQL> CREATE TABLE locations (
2 location_id NUMBER, street_address VARCHAR2(80), postal_code CHAR(12),
3 city VARCHAR2(80), state_province CHAR(2), country_id VARCHAR2(20))
4 PARTITION BY LIST (state_province)
5 (PARTITION region_east
6 VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ'),
7 PARTITION region_west
8 VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO'),
9 PARTITION region_south
10 VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
11 PARTITION region_central
12 VALUES ('OH','ND','SD','MO','IL','MI',NULL,'IA'));
Table created.
SQL> create index loc_global_idx
2 on locations (state_province)
3 global partition by range (state_province)
4 (partition p1 values less than ('NV'),
5 partition p2 values less than (maxvalue));
Index created.
SQL> INSERT INTO locations VALUES
2 ( 1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT');
1 row created.
SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
2 part_name , status
3 from dba_ind_partitions
4 where index_name= 'LOC_GLOBAL_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
LOC_GLOBAL_IDX P1 USABLE
LOC_GLOBAL_IDX P2 USABLE
*** *******************************************************
*** Version 8/8i : Without the UPDATE GLOBAL INDEXES clause
*** *******************************************************
SQL> select substr(index_name,1,20) index_name,
2 substr(partition_name,1,20) part_name,status
3 from dba_ind_partitions
4 where index_name= 'ORDERS_GLOBAL_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
ORDERS_GLOBAL_IDX GLOBAL1 USABLE
ORDERS_GLOBAL_IDX GLOBAL2 USABLE
ORDERS_GLOBAL_IDX GLOBAL3 USABLE
ORDERS_GLOBAL_IDX GLOBAL4 USABLE
SQL> ALTER TABLE orders DROP PARTITION q2;
Table altered.
SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
2 part_name, status
3 from dba_ind_partitions
4 where index_name= 'ORDERS_GLOBAL_IDX' order by partition_name;
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
ORDERS_GLOBAL_IDX GLOBAL1 UNUSABLE
ORDERS_GLOBAL_IDX GLOBAL2 UNUSABLE
ORDERS_GLOBAL_IDX GLOBAL3 UNUSABLE
ORDERS_GLOBAL_IDX GLOBAL4 UNUSABLE
=> In version pre-9i, any DDL operation on a partition of a partitioned table
would invalidate the use of GLOBAL indexes, if the corresponding partition
isn't empty.
=> In 9i, the UPDATE GLOBAL INDEXES clause allows automatic maintenance of
GLOBAL indexes while performing DDL operations on a partition of the table.
Some combinations are nevertheless not allowed.
*** *****************************************************
*** UPDATE GLOBAL INDEXES clause and ADD PARTITION clause
*** *****************************************************
1. RANGE partitioned tables:
SQL> ALTER TABLE orders ADD PARTITION q5
2 values less than (TO_DATE('03-JUN-2000','DD-MON-YYYY'))
3 UPDATE GLOBAL INDEXES;
ALTER TABLE orders ADD PARTITION q5
*
ERROR at line 1:
ORA-30564: Index maintainence clause not allowed for ADD partition to RANGE
partitioned tables
The clause UPDATE GLOBAL INDEXES is allowed only for adding a
=> partition to a HASH partitioned table
=> subpartition to a composite partitioned table
---------------------------------------------------------
| Use the conventional way for RANGE partitioned tables |
---------------------------------------------------------
SQL> ALTER TABLE orders ADD PARTITION q5
2 values less than (TO_DATE('03-JUN-2000','DD-MON-YYYY'));
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
ORDERS_GLOBAL_IDX GLOBAL1 USABLE
ORDERS_GLOBAL_IDX GLOBAL2 USABLE
ORDERS_GLOBAL_IDX GLOBAL3 USABLE
ORDERS_GLOBAL_IDX GLOBAL4 USABLE
2. HASH partitioned tables:
--------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with HASH partitioned tables |
| or the global index is left UNUSABLE |
--------------------------------------------------------------
SQL> ALTER TABLE emp_hpart ADD PARTITION q5
2 UPDATE GLOBAL INDEXES;
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
EMP_GLOBAL_HASH_IDX P1 USABLE
EMP_GLOBAL_HASH_IDX P2 USABLE
3. COMPOSITE partitioned tables:
-------------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables |
| or the global index is left UNUSABLE |
-------------------------------------------------------------------
SQL> ALTER TABLE emp_composite MODIFY PARTITION p1 add subpartition h5
2 UPDATE GLOBAL INDEXES;
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
EMP_GLOBAL_COMPOSITE P1 USABLE
EMP_GLOBAL_COMPOSITE P2 USABLE
4. LIST partitioned tables
--------------------------------------------------------
| Use the conventional way for LIST partitioned tables |
--------------------------------------------------------
SQL> alter table locations ADD
2 partition nomansland values ('XX');
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
LOC_GLOBAL_IDX P1 USABLE
LOC_GLOBAL_IDX P2 USABLE
*** ******************************************************
*** UPDATE GLOBAL INDEXES clause and DROP PARTITION clause
*** ******************************************************
1. RANGE partitioned tables:
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE orders DROP PARTITION q2 UPDATE GLOBAL INDEXES;
Table altered.
2. HASH partitioned tables:
No DROP allowed with HASH partitioned tables.
Use COALESCE instead. (See at the end of the bulletin)
3. COMPOSITE partitioned tables:
-------------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables |
| or the global index is left UNUSABLE |
-------------------------------------------------------------------
SQL> ALTER TABLE emp_composite DROP PARTITION p2
2 UPDATE GLOBAL INDEXES;
Table altered.
4. LIST partitioned tables:
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with LIST partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> alter table locations DROP PARTITION
2 region_south
3 UPDATE GLOBAL INDEXES;
Table altered.
*** *******************************************************
*** UPDATE GLOBAL INDEXES clause and SPLIT PARTITION clause
*** *******************************************************
1. RANGE partitioned tables
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE orders SPLIT PARTITION q3 AT
2 (TO_DATE('15-SEP-1999','DD-MON-YYYY'))
3 INTO (PARTITION q3_1, PARTITION q3_2)
4 UPDATE GLOBAL INDEXES;
Table altered.
2. HASH partitioned tables
No SPLIT allowed with HASH partitioned tables.
Use ADD instead. (See above)
3. COMPOSITE partitioned tables
-------------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables |
| or the global index is left UNUSABLE |
-------------------------------------------------------------------
SQL> ALTER TABLE emp_composite SPLIT PARTITION p2 AT (80)
2 INTO (PARTITION p2_1, PARTITION p2_2)
3 UPDATE GLOBAL INDEXES;
Table altered.
4. LIST partitioned tables:
--------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with LIST partitioned tables |
| or the global index is left UNUSABLE |
--------------------------------------------------------------
SQL> alter table locations SPLIT PARTITION region_east
2 VALUES ('MA','NJ')
3 INTO (PARTITION region_east_1, PARTITION region_east_2)
4 UPDATE GLOBAL INDEXES;
Table altered.
*** *******************************************************
*** UPDATE GLOBAL INDEXES clause and MERGE PARTITION clause
*** *******************************************************
1. RANGE partitioned tables
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE orders MERGE PARTITIONS q2, q3 INTO PARTITION q3
2 UPDATE GLOBAL INDEXES;
Table altered.
2. HASH partitioned tables
No MERGE allowed with HASH partitioned tables.
Use COALESCE instead. (See at the end of the bulletin)
3. COMPOSITE partitioned tables
-------------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables |
| or the global index is left UNUSABLE |
-------------------------------------------------------------------
SQL> ALTER TABLE emp_composite MERGE PARTITIONS p1, p2
2 INTO PARTITION p2
3 UPDATE GLOBAL INDEXES;
Table altered.
4. LIST partitioned tables:
--------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with LIST partitioned tables |
| or the global index is left UNUSABLE |
--------------------------------------------------------------
SQL> alter table locations MERGE PARTITIONS region_east,region_west
2 INTO PARTITION region_north
3 UPDATE GLOBAL INDEXES;
Table altered.
*** **********************************************************
*** UPDATE GLOBAL INDEXES clause and EXCHANGE PARTITION clause
*** **********************************************************
1. RANGE partitioned tables
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE orders EXCHANGE PARTITION q3 WITH TABLE t_orders
2 UPDATE GLOBAL INDEXES;
Table altered.
If GLOBAL indexes exist on the TABLE of exchange, they are left UNUSABLE:
SQL> create index t_orders_global_idx
2 on t_orders(ord_date)
3 global partition by range (ord_date)
4 (partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY',
5 partition GLOBAL2 values less than (TO_DATE('01-SEP-1999','DD-MON-YYYY',
6 partition GLOBAL3 values less than (TO_DATE('01-DEC-1999','DD-MON-YYYY',
7 partition GLOBAL4 values less than (MAXVALUE) );
Index created.
SQL> ALTER TABLE orders EXCHANGE PARTITION q3 WITH TABLE t_orders
2 UPDATE GLOBAL INDEXES;
Table altered.
SQL> @sel
INDEX_NAME PART_NAME STATUS
-------------------- -------------------- --------
T_ORDERS_GLOBAL_IDX GLOBAL1 UNUSABLE
T_ORDERS_GLOBAL_IDX GLOBAL2 UNUSABLE
T_ORDERS_GLOBAL_IDX GLOBAL3 UNUSABLE
T_ORDERS_GLOBAL_IDX GLOBAL4 UNUSABLE
ORDERS_GLOBAL_IDX GLOBAL1 USABLE
ORDERS_GLOBAL_IDX GLOBAL2 USABLE
ORDERS_GLOBAL_IDX GLOBAL3 USABLE
ORDERS_GLOBAL_IDX GLOBAL4 USABLE
2. HASH partitioned tables
--------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with HASH partitioned tables |
| or the global index is left UNUSABLE |
--------------------------------------------------------------
SQL> ALTER TABLE emp_hpart EXCHANGE PARTITION H1 WITH TABLE t_emp_hpart
2 UPDATE GLOBAL INDEXES;
Table altered.
3. COMPOSITE partitioned tables
SQL> ALTER TABLE emp_composite EXCHANGE PARTITION p1 WITH TABLE t_emp_composite;
ALTER TABLE emp_composite EXCHANGE PARTITION p1 WITH TABLE t_emp_composite
*
ERROR at line 1:
ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table
-------------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables |
| or the global index is left UNUSABLE |
-------------------------------------------------------------------
SQL> ALTER TABLE emp_composite EXCHANGE SUBPARTITION SYS_SUBP286
2 WITH TABLE t_emp_composite
3 UPDATE GLOBAL INDEXES;
Table altered.
4. LIST partitioned tables:
--------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with LIST partitioned tables |
| or the global index is left UNUSABLE |
--------------------------------------------------------------
SQL> ALTER TABLE locations EXCHANGE PARTITION region_east
2 WITH TABLE t_locations
3 UPDATE GLOBAL INDEXES;
Table altered.
*** ******************************************************
*** UPDATE GLOBAL INDEXES clause and MOVE PARTITION clause
*** ******************************************************
1. RANGE partitioned tables
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE orders MOVE PARTITION q3 TABLESPACE example
2 UPDATE GLOBAL INDEXES;
Table altered.
2. HASH partitioned tables
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with HASH partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE emp_hpart MOVE PARTITION H1 TABLESPACE example
2 UPDATE GLOBAL INDEXES;
Table altered.
3. COMPOSITE partitioned tables
SQL> ALTER TABLE emp_composite MOVE PARTITION p1 TABLESPACE example;
ALTER TABLE emp_composite MOVE PARTITION p1 TABLESPACE example
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition
SQL> ALTER TABLE emp_composite MOVE PARTITION p1 TABLESPACE example
2 UPDATE GLOBAL INDEXES;
ALTER TABLE emp_composite MOVE PARTITION p1 TABLESPACE example
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition
4. LIST partitioned tables:
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with LIST partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE locations MOVE PARTITION region_east
2 TABLESPACE TS_DATA1
3 UPDATE GLOBAL INDEXES;
Table altered.
*** **********************************************************
*** UPDATE GLOBAL INDEXES clause and TRUNCATE PARTITION clause
*** **********************************************************
1. RANGE partitioned tables
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with RANGE partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE orders TRUNCATE PARTITION q3
2 UPDATE GLOBAL INDEXES;
Table truncated.
2. HASH partitioned tables
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with HASH partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE emp_hpart TRUNCATE PARTITION H1
2 UPDATE GLOBAL INDEXES;
Table truncated.
3. COMPOSITE partitioned tables
-------------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with COMPOSITE partitioned tables |
| or the global index is left UNUSABLE |
-------------------------------------------------------------------
SQL> ALTER TABLE emp_composite TRUNCATE PARTITION p1
2 UPDATE GLOBAL INDEXES;
Table truncated.
4. LIST partitioned tables:
---------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with LIST partitioned tables |
| or the global index is left UNUSABLE |
---------------------------------------------------------------
SQL> ALTER TABLE locations TRUNCATE PARTITION region_east
2 UPDATE GLOBAL INDEXES;
Table truncated.
*** **********************************************************
*** UPDATE GLOBAL INDEXES clause and COALESCE PARTITION clause
*** **********************************************************
1. RANGE partitioned tables:
Not available with range partitioned tables.
2. HASH partitioned tables:
--------------------------------------------------------------
| Use UPDATE GLOBAL INDEXES way with HASH partitioned tables |
| or the global index is left UNUSABLE |
--------------------------------------------------------------
SQL> ALTER TABLE emp_hpart COALESCE PARTITION
2 UPDATE GLOBAL INDEXES;
Table altered.
3. COMPOSITE partitioned tables
Not available with composite partitioned tables.
4. LIST partitioned tables
Not available with list partitioned tables.
*** ***********************************************************************
*** Which performance considerations would lead you to choose REBUILD INDEX
*** rather than use UPDATE GLOBAL INDEXES
*** ***********************************************************************
If UPDATE GLOBAL INDEXES is used:
* Partition DDL operations will take longer to complete because the global
indexes that were previously marked invalid will now be updated
* DROP, TRUNCATE, EXCHANGE will no longer be fast, though data-dictionary only
operations because a scan of all rows in the partition will be done
* Updates to the global index will be logged, hence, redo and rollback will be
generated
* Update index is favorable when the amount of row work is low
* Update index ensures application performance does not drastically fall until
the index is rebuilt
If INDEX REBUILD is used:
* Rebuilding the entire index will make the index more efficient.
* Rebuilding the index allows the user to reorganize the index
.
--------------------------------------------------------------------------------
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
