Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, S.

Asked: July 15, 2006 - 5:03 pm UTC

Last updated: July 19, 2006 - 8:07 am UTC

Version: 10G

Viewed 1000+ times

You Asked

Dear Tom,

Current application background: Batch processing with > 90 million records per day * 10 tables.

Q1.Please suggest if Index unusable, disable before Insert and rebuild Index after Insert completion would really help.
Q2. Please suggest if the Analyze table and Index partitons (daily partition) would really help after daily batch process.

I am big fan of yours since 2000.
My Lingo Got Oracle trouble Goto ASK TOM.
Great work and Thank you very much.

-sp




and Tom said...

q) the only possible answer given only part of the equation (how many records exist) is:

a) it depends. It might help, it might hurt, it might have NO effect.


for example, you have 90 million records. Batch process updates columns that are not indexed, but uses indexes to find these rows. Disabling indexes would be a really bad idea.

for example, you have 90 million records. You delete 45million of them, you insert 45 million new ones. You do not use indexes to locate the records (you are deleting 50%, full scans rule the day). disabling and rebuilding indexes may be very beneficial.

for example, you have 90 million records. You do a mix of the above two scenarios to the various tables. Sometimes disable+rebuild => good, others => bad.


It depends.


q2) the entire goal of gathering statistics (let's not call it analyze as people then might assume we mean the analyze command when we mean the dbms_stats package) is to give the optimizer correct information about the data. If your batch process radically changes the data - AND the query plans should change from what they currently are - re-gathering statistics would be recommended. But it would always be a good idea to make sure you can restore the old ones (to get back to where you were) since gathering statistics is pretty much designed to *change query plans*. If you don't want the plans to change.....

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Will this condition hold the same for global index too!!

S.Karthikeyan, July 16, 2006 - 11:59 pm UTC

thats great,

It was back i knew we have index for partition and we can partition the index, but oracle is very brilliant to have more classifcation under this partition index as global and local index..

We are now facing problem with a table having 50 million records with a primary key column with global partitioned index.

Recently we had been on a large insert, There is great back log in performance,

Our team had decided to rebuild the index locally.

May i get to knew the possibilities and syntax for that.

where can i get the real concept and other information behind it..

Tom Kyte
July 17, 2006 - 1:20 pm UTC

you can only "rebuild locally" (assume you mean "turned it into a LOCAL index") if the partition key and the primary key were one in the same.

And the syntax is fully documented for doing so - juse create index .... LOCAL;

The question i posed if for oracle version 8.1.7.4

S.Karthikeyan, July 17, 2006 - 12:04 am UTC

sorry for not mentioning the version...

I need to move from global partition index to local partition index in short..

Which is efficient here...

what will be the case if it is indexed on unique key..

Tom Kyte
July 17, 2006 - 1:22 pm UTC

there is insufficient "data" in this comment to comment on...

rebuild index local - version 8.1.7.4

S.Karthikeyan, July 18, 2006 - 9:48 am UTC

thanks a lot for your reply,

I have a table with 50 million records, which is partitioned.

The partition key is the primary key column also. It has a index built globally, in order to maintain the uniqueness between the values in key column.

Now there is big down in performance, recently we had gone for a bulk insert. After which we had not rebuilt the index due to the complexity of the database.

But had done dbms_stats analyze for 1%.

Now we have decided to rebuild the index locally.

but the primary key column does'nt allow to delete the index , in order to create the index locally.

in the previous reply you have mentioned for build local syntax.. which i am aware of ..

what should i do now to delete the index and recreate locally in a primary key column.

Tom Kyte
July 19, 2006 - 8:07 am UTC

you would simply drop(or disable) and create(or re-enable) the primary key index.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.