Re: is rebuilding indexes necessary after import?

Notes in-line.


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@xxxxxxxxx>
To: <mark@xxxxxxxxx>
Cc: <sjaffarhussain@xxxxxxxxx>; "Active DBA" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, December 22, 2004 8:29 AM
Subject: Re: is rebuilding indexes necessary after import?



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.  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.

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 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

Other related posts: