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@xxxxxxxxx>
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)