Kevin,
As someone mentioned earlier, the first query will not use the index because
indexes don't contain NULL values. In the absence of a WHERE clause, a FULL
table scan is the only option possible.
The second query seems to be utilizing ROWIDs in the subquery, but what good
is a column on the inner-table containing ROWID values from the outer-table
in a correlated subquery? Expecting ROWID access against the inner table
would be reasonable if you were accessing the inner table by ROWID, but you
are using ROWID values from the outer table to access a column on the inner
table containing ROWID values. No possibility of ROWID access. The way the
statement is written, the inner query is a "correlated subquery". It will
be executed once for each row retrieved from the outer query, using the
referenced ROWID value from each row retrieved by the outer row. Only
"uncorrelated subqueries" execute first and return their result back to the
outer query, which is perhaps the effect you were expecting? An
"uncorrelated subquery", as the name suggests, cannot contain a reference
from the outer-query, so that it can execute first...
The optimizer isn't being "stubborn" -- it's just correct.
Hope this helps...
-Tim
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, May 31, 2002 1:32 PM
Well, it's time to call for the cavalry.
I have a table where the optimizer stubbornly insists on doing full table
scans for practically every operation in spite of the fact that full table
scans have gruesome performance. Every hint I have tried has either been
ignored or doesn't help (and yes, I have used hints before and have
carefully checked my syntax). The only way I have gotten the optimizer to
even use an index on one query was to jam the session settings
OPTIMIZER_INDEX_CACHING=100 plus OPTIMIZER_INDEX_COST_ADJ=1 which is not a
healthy way to do things. I would appreciate some help in psychoanalyzing
the optimizer.
Oracle 8.1.7.3 on Solaris 2.8, all files on a single volume RAID-5 array (I
know, I know but I can't do anything about it at the moment).
MDMA_INPUT_FILE is a high transaction table used for data loading and
validation. The table is badly denormalized due to decisions made long
before I started working here. It has 15 indexes to support the validation
GUI (yes, I know, lots of indexes on a high transaction table is insane and
I have plans to deal with that in a month or two). At the moment, the table
holds over 800K rows. The table has been analyzed.
Here are a couple examples of loony optimizer behavior:
###############################################
SELECT Record_Type, Archive_Input_File
FROM MDMA_Input_File
GROUP BY Record_Type, Archive_Input_File
SELECT STATEMENT Hint=CHOOSE 162 7500
SORT GROUP BY 162 6 K 7500
TABLE ACCESS FULL MDMA_INPUT_FILE 839 K 31 M 1882
There is a valid index where these two columns are the first of 5 columns.
I've tried most permutations of INDEX hints and they are all ignored.
Actually, this statement stemmed from working around a problem of reading
the full table ordered by the 5 index columns. The optimizer chose to do a
full table scan plus sort (with resultant RAID-5 ugly performance).
Apparently, it feels the sort would be quicker than index access -- which
might be true on a non-IO bound system.
###############################################
UPDATE mdma_input_file mif
SET partial_day_hold = :b1,
ok_to_process = :b2,
vee_usage_end = to_date(:b3)
WHERE EXISTS (SELECT 1
FROM st_vee_input_file
WHERE mif.rowid=mdma_rowid)
st_vee_input_file is a session temporary table with 96 rows.
UPDATE STATEMENT Hint=CHOOSE 41 K 1882
UPDATE MDMA_INPUT_FILE
FILTER
TABLE ACCESS FULL MDMA_INPUT_FILE 41 K 409 K 1882
TABLE ACCESS FULL ST_VEE_INPUT_FILE 82 574 7
Unless I can figure this out, I foresee reading the temp table into an array
then doing the update in a forall loop. Shouldn't have to do this much
coding to work around the optimizer.
Thanks for any help you can give (or sympathy if help is unavailable),
Kevin Kennedy
First Point Energy Corporation
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: kkennedy
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
