Re: B-Tree to Partitioned index

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_at_yahoo.co.uk> To: <oracle-l_at_freelists.org> 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

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