 UPDATE GLOBAL INDEXES vs. UPDATE INDEXES [message #439363] |
Fri, 15 January 2010 19:46  |
transplant
Messages: 3
Registered: January 2010
|
Junior Member |
|
|
Hi,
I am trying to understand the difference between UPDATE GLOBAL INDEXES and UPDATE INDEXES. What is the difference?
I am familiar with UPDATE GLOBAL INDEXES but not UPDATE INDEXES.
I know the former is used to rebuild a global index online during a partition maintenance operation.
Thanks.
|
|
|
|
| Re: UPDATE GLOBAL INDEXES vs. UPDATE INDEXES [message #439364 is a reply to message #439363] |
Fri, 15 January 2010 19:53  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/partconc.htm
GOOGLE is your friend, but only when you actually use it!
Maintenance of Global Partitioned Indexes
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE
These indexes can be maintained by appending the clause UPDATE INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:
* The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
* The index doesn't have to be rebuilt after the operation.
[Updated on: Fri, 15 January 2010 19:54] Report message to a moderator |
|
|
|
| Re: UPDATE GLOBAL INDEXES vs. UPDATE INDEXES [message #439365 is a reply to message #439364] |
Fri, 15 January 2010 20:23 |
transplant
Messages: 3
Registered: January 2010
|
Junior Member |
|
|
I did read that link before posting.
>>These indexes can be maintained by appending the clause UPDATE INDEXES to the SQL statements for the operation.
Isn't that the same as saying:
ALTER TABLE ... UPDATE GLOBAL INDEXES;
I guess I have still not got the exact difference between the usage of "UPDATE GLOBAL INDEXES" and "UPDATE INDEXES".
I am trying to exchange a partition on a table that has global index and local index.. on exchange using the EXCHANGE PARTITION ...UPDATE GLOBAL INDEXES the global index becomes USABLE but the local index partitions go UNUSABLE.
So, was wondering if using UPDATE INDEXES (without the word GLOBAL) will be of any use?
Thanks
|
|
|
|
| Re: UPDATE GLOBAL INDEXES vs. UPDATE INDEXES [message #439366 is a reply to message #439363] |
Fri, 15 January 2010 20:29 |
transplant
Messages: 3
Registered: January 2010
|
Junior Member |
|
|
Here is what I am doing:
SQL> CREATE TABLE XYZ
2 (
3 ID NUMBER NOT NULL,
4 col1 number,
5 col2 number,
6 col3 number
7 )
8 PARTITION by range(id)
9 (
10 PARTITION XYZ_0 values less than (1),
11 PARTITION XYZ_1 values less than (2),
12 PARTITION XYZ_2 values less than (3),
13 PARTITION XYZ_3 values less than (4)
14 );
Table created.
SQL>
SQL> --global ix
SQL> CREATE UNIQUE INDEX XYZ_PK ON XYZ( ID) GLOBAL;
Index created.
SQL> ALTER TABLE XYZ ADD CONSTRAINT XYZ_PK PRIMARY KEY (ID) USING INDEX;
Table altered.
SQL>
SQL> --local ix
SQL> CREATE INDEX XYZ_IX1 ON XYZ(col1) LOCAL;
Index created.
SQL>
SQL> drop TABLE XXX;
Table dropped.
SQL> CREATE TABLE XXX
2 (
3 ID NUMBER NOT NULL,
4 col1 number,
5 col2 number,
6 col3 number
7 );
Table created.
SQL> CREATE UNIQUE INDEX XXX_PK ON XXX(ID) ;
Index created.
SQL> ALTER TABLE XXX ADD CONSTRAINT XXX_PK PRIMARY KEY (ID) USING INDEX;
Table altered.
SQL> CREATE INDEX XXX_IX1 ON XXX(col1) ;
Index created.
SQL>
SQL> ALTER TABLE XYZ EXCHANGE PARTITION XYZ_2 WITH TABLE XXX EXCLUDING INDEXES WITHOUT VALIDATION update indexes;
Table altered.
SQL> select index_name, partition_name,status
2 from user_ind_partitions
3 where index_name IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='XYZ') order by 1,2;
INDEX_NAME PARTITION_ STATUS
------------------------------ ---------- ------------------------
XYZ_IX1 XYZ_0 USABLE
XYZ_IX1 XYZ_1 USABLE
XYZ_IX1 XYZ_2 UNUSABLE
XYZ_IX1 XYZ_3 USABLE
Is there some way to get the local index partition to usable state with same EXCHANGE PARTITION command?
Thanks
[Updated on: Fri, 15 January 2010 20:31] Report message to a moderator |
|
|
|
| Re: UPDATE GLOBAL INDEXES vs. UPDATE INDEXES [message #439854 is a reply to message #439366] |
Tue, 19 January 2010 13:43 |
abadir
Messages: 1
Registered: January 2010
Location: Atlanta
|
Junior Member |
|
|
Think of UPDATE GLOBAL INDEXES to be a subset of UPDATE INDEXES. For example, let us assume that you have a table with global as well as local partitioned indexes, and you try to split one of the non-empty partitions of the table. If you include UPDATE GLOBAL INDEXES clause, then the global indexes on the table will not be marked UNUSABLE, but all non-empty local index partitions will be. If you include UPDATE INDEXES clause, then your local as well as global indexes will not be marked UNUSABLE. If you don't include any of the two, the partition maintenance will invalidate all your indexes (partitioned indexes)
Hope this helps.
SAY
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
