Re: Help: Optimizer hints

It seems that the optimizert 'tries very hard' to switch to ALL_ROWS optimisation when you use CTAS. (Which is quite reasonable, really, since the table is not created until all the rows exist).

To get around this, I usually put the tables in the order I want them visited, use the ORDERED hint and then use the USE_NL hint very liberally. This usually works, though sometimes a very explicit index hint (including name) helps.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Gerard M. Averill wrote in message <79cjo1$t5m$1_at_news.doit.wisc.edu>...
>I was wondering if anyone could explain why a well-optimized query (when
run
>as a stand-alone SELECT statement) isn't so when run as a CREATE TABLE AS
>SELECT statement. Specifically, all my nice efficient index RANGE SCANs
and
>UNIQUE SCANs (as revealed by the EXPLAIN PLAN command) are all replaced
with
>FULL SCANs on the indexes, and the execution time goes from a "second" to
>20 seconds. (Three of the tables involved have statistics, three others
>don't, yet even the ones with statistics don't use a UNIQUE SCAN. I'm
using
>INDEX() hints to force the use of indexes.)
Received on Fri Feb 05 1999 - 08:49:52 CST