Re: create index based on another index

I mentioned the strategy of creating the index with all its partition unusable then rebuilding each partition in turn because of the time it would take to build the whole index in one go if you were locking the table. The same strategy applied to the TEMP space. If you had 1024 partitions (say) you would "only" be building an index on 25GB of rows each time. The number is a little extreme, but if you're only every querying for "column = constant" and not doing a range scan on the column then the benefits might far outweigh the costs.

The article that I'm sure I've written somewhere did examine the annoying detail that Oracle would use only an index fast full scan (or tablescan) to create an index from an index despite a statement I had made many years ago that it could use an index full scan. The problem is that Oracle estimates the cost of "select key_value, rowid from table" and doesn't allow anything like the nornal cost of sorting - so the fast full scan with sort would always be far cheaper than a full scan with sort. (I may even have found that Oracle didn't even try to cost for a full scan)

Regards
Jonathan Lewis

On Thu, 15 Dec 2022 at 05:32, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

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

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