RE: create index based on another index
Date: Thu, 15 Dec 2022 08:38:37 -0500
Message-ID: <397301d9108a$89ce7300$9d6b5900$_at_rsiz.com>
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
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.
Cc: ORACLE-L (oracle-l_at_freelists.org)
Subject: Re: create index based on another index
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 15 2022 - 14:38:37 CET