Re: is rebuilding indexes necessary after import?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 22 Dec 2004 11:30:59 -0000
Message-ID: <00e601c4e819$b5fb2ca0$6702a8c0@Primary>

Oops,

Sorry, no notes in line with last email. But there are some in this one.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated Sept 19th

• Original Message ----- From: "stephen booth" <stephenbooth.uk_at_gmail.com>

That begs the question, when is the create index statement issued? 'Create table, create index, import rows' and 'create table, import rows, create index' could lead to different results in terms of index efficiency.

• True, but I think most of the people in Oracle development
• worked out that you need to postpone index creation as long
• as possible when importing data. Even things like PKs are
• created but not enabled

If the order of the data is such that the leading edge of the index is monotonically increasing or decreasing then you'll probably get an unbalanced and inefficient index (the same as you do with any increasing indexed value) if the index is created before the before the rows are imported and isn't reverse key.

• You don't get unbalanced B-tree indexes. You will get an
• index where every leaf block is 50% empty if you insert data
• in monotonic decreasing order - but (except for some version
• in 9i that had a bug) you get 100% packing for monotonic increasing
• order.

Off the top of my head I don't know the order, I'll have to check next time I get the opportunity.

Personally I import without indexes (for non-trivial data volumes) then create them as: it's usually quicker; I can give users access to the system as soon as the data is in and create the indexes whilst they're on or later when the system is quiet (it runs like a dog with three legs until the indexes are created

• Not a good simile - I've seen a dog with three legs running,
• (some sort of terrier) and it was amazing how fast it could go.

                                                                    but at 

least I'm not in
violation of my SLA, which gives me a time limit to get the system available but doesn't specify performance once it's available); I know the indexes are in as good a state as I can get them.

Stephen
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 22 2004 - 05:26:25 CST