Re: create index based on another index

It should also be noted that there's a typing error in my original comment: “so the fast full scan with sort would always be far cheaper than a full scan with sort”
should have been
“so the fast full scan with sort would always be far cheaper than a full scan *WITHOUT* sort”

And as Mark says "cheaper" means "the cost Oracle would calculate".

Regards
Jonathan Lewis

On Thu, 15 Dec 2022 at 13:39, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> When JL writes “so the fast full scan with sort would always be far
> cheaper than a full scan with sort” it should be stressed that the
> adjective “cheaper” applies to only the assessment of the CBO and of course
> the n log n of even the best sort plus the subsequent run merges, when a
> full scan could just write its output in a stream.
>
>
>
> But hey, pretend you can always use all available resources in parallel
> doing something extremely wasteful to reduce the elapsed time instead of
> doing it the actual cheapest way at the rate at which a stream of output
> can be written, AND, that doing piecewise generation into partitions (that
> can be appended by linking tails with the next head even if you don’t want
> to end up with partitions) can’t be parallelized just as much with n serial
> jobs with a simpler type of parallelization.
>
>
>
> Sigh. Trying to explain that to folks who are certain doing parallel
> exactly one way is like trying to explain screw drivers to someone who only
> knows about hammers.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Thursday, December 15, 2022 7:41 AM
> *To:* laurentiu.oprea06_at_gmail.com
> *Cc:* ORACLE-L (oracle-l_at_freelists.org)
> *Subject:* 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