Home » SQL & PL/SQL » SQL & PL/SQL » What update index does? (10.2g)
What update index does? [message #355523] Fri, 24 October 2008 11:09 Go to next message
Messages: 49
Registered: August 2008
We have paritioned tables but index are not partitioned. Now if I perform any DDL operation of partitioned table like Drop, exchange partition then oracle invalidates the partition. However if we do UPDATE INDEXES clause then oracle does not make indexes unusable.

Now my question is if we use UPDATE INDEXES clause then what oracle actually do? Does it recreate indexes or what it does?
Re: What update index does? [message #355526 is a reply to message #355523] Fri, 24 October 2008 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65088
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the statement you execute.
Post what you did to state what you posted.
Use SQL*Plus and copy and paste your session.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Re: What update index does? [message #355591 is a reply to message #355526] Sat, 25 October 2008 20:37 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In my experience, Oracle maintains its global indexes upon table partition operations fairly efficiently. I have not seen it attempt to fully rebuild a global index when you act on table partitions.

If you perform a SQL Trace and TKPROF of the operations that interest you, you will see what Oracle does. I have done this on EXCHANGE PARTITION and seen some freaky SQL statements that act directly on indexes - not the tables themselves.

Ross Leishman
Previous Topic: Getting The Mail Id
Next Topic: Order by clause difference
Goto Forum:

Current Time: Wed Jul 26 18:09:06 CDT 2017

Total time taken to generate the page: 0.09555 seconds