Re: is rebuilding indexes necessary after import?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <stephenbooth.uk@xxxxxxxxx>, <mark@xxxxxxxxx>
- Date: Wed, 22 Dec 2004 11:30:59 -0000
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@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
