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_at_gmail.com> To: <mark_at_bobak.net> Cc: <sjaffarhussain_at_gmail.com>; "Active DBA" <oracle-l_at_freelists.org> 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 Received on Wed Dec 22 2004 - 05:19:00 CST