Re: create index based on another index

Not a stupid question, and one with a slightly subtle answer.

If you want to create the index on-line Oracle HAS to read the table because it wants to create a journal of all changes to the table that it will apply to the new index when it is (nearly) complete. If you don't mind locking the table while the index is built then Oracle will take a cost-based decision about whether to build the new index from a tablescan or an index fast full scan. So if (say) the table has been declared parallel (N) and the index hasn't, then it's possible that the cost of a parallel tablescan will be lower than the cost of a serial index fast full scan.

I thought I'd published a blog note that made a few comments about this, but I can't find it, so it might have been an old oracle-L article. I wanted to check whether null/not null might also make a difference to the choice of path (i.e. blocking an index-only path even for the locking case).

One thought (going all the way back to 8i) is that you could create the partitioned index UNUSABLE and invisible, then rebuild one partition at a time over a period of several nights. Cost/Benefit analysis is left as an exercise - and might be affected by the number of partitions.

Regards
Jonathan Lewis

On Wed, 14 Dec 2022 at 14:44, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Hello,
>
> I have a situation where an index on a single column is too big (around
> 25TB) and needs to be partitioned. The plan is to create another index
> (global partitioned by hash) on the same column, invisible, then drop the
> other non-partitioned index and make the partitioned index visible.
>
> I was thinking now, when the second index is created on the same column,
> why is not reading the existing index rather is scanning the table in the
> creation process?
>
> Thanks (and sorry if sounds like a stupid question)
>
>

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