Re: B-Tree to Partitioned index
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <hamcdc@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Dec 2004 09:02:24 -0000
Your hypothesis (2) about an always null third column
is not safe. There are cases where Oracle can use the
number of distinct keys in the index to work out the
cost of a nested loop join, and other cases where it uses
the selectivity of the separate columns of the index -
the numbers are often different.
If you added a third 'always null' column, you would
disable the option for using distinct_keys, and could
therefore affect more plans than you might expect.
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
Public Appearances - schedule updated Dec 23rd 2004
----- Original Message -----
From: "Connor McDonald" <hamcdc@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, December 30, 2004 1:07 AM
Subject: Re: B-Tree to Partitioned index
Some (untried, untested) ideas...
1) Create the new index online with a trailing additional column in it.
This way you get to leave
your current index on there until the new one has completed building.
2) Add a new column "X" to the table that is always null. Then same concept
as (1) but using "X"
as the additional column. The new index is then "closer" to the original
one in terms of size and
(theoretically) its impact on execution plans should be reduced.
3) If your table is partitioned and your new index is going to be locally
partitioned, check out
DBMS_PCLXUTIL for some nice ways of building the index in a time-efficient
manner.
hth
connor
--
//www.freelists.org/webpage/oracle-l
Other related posts:
