| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Stubborn Table
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
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
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
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: kkennedy_at_firstpoint.com 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: ListGuru_at_fatcity.com (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: Tim_at_SageLogix.com 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: ListGuru_at_fatcity.com (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).Received on Sat Jun 01 2002 - 09:03:17 CDT
 |
 |