Re: create index based on another index

Thanks a lot for the answers.

Indeed I was checking now and the online clause is the one causing the table scan instead of range scan.

A big problem in my case is the fact that it has to generate a huge amount of temp for the sorting operation.

Why for the index build it can`t use an index full scan to avoid the sorting operation and is always using a FFS.

THank you.

În mie., 14 dec. 2022 la 18:13, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> 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