Re: create index based on another index

My crystal ball says you are creating the index online, this requires reading from the table rather than any existing index (this also is prevented from direct path reads). I don’t think there is anything you can do about this other than be patient, it’s online so shouldn’t be too much of a bother, so long as you have the temp.

If it suites your requirements then you may be able to achieve partitioning without any work. Partition exchanging the table into a partition in a new table (use the create table for exchange syntax) which is locally indexed with the same indexes is quick. You just have to have a slight downtime window to do the table renames, and your partitioning scheme must allow for all existing data to fit in one partition.

Thanks,
Andy

On Wed, Dec 14, 2022 at 6:44 AM, 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