Hi Gary,
Didn't try ROWID hint before, did just now. No plan change.
I'm starting to re-code the update to use the subquery to bulk load a PL/SQL table
then do the update in a forall loop. Seems silly and will be significantly less
readable but....
Probably won't finish before I need to boogey for the weekend (I may be a DBA when I'm
not developing but I don't have a DBA attitude about weekends).
Thanks for the suggestions so far.
Kevin
-----Original Message-----
Sent: Friday, May 31, 2002 3:09 PM
To: Multiple recipients of list ORACLE-L
Did you try a rowid hint?
Gary
-----Original Message-----
Sent: Friday, May 31, 2002 5:49 PM
To: Multiple recipients of list ORACLE-L
Good point on the first query. The fields are nullable even though they
logically should not be. I can feel several of my brain cells coming back
to life.
On the second query, I would dearly love for the optimizer to figure out
that the subquery is a list of rowids and that rowid access is the preferred
method of reaching out and touching rows. Maybe I'm just expecting too
much......
Kevin
-----Original Message-----
Sent: Friday, May 31, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
Kevin,
The optimizer can't use an index on the first query, as it would miss any
rows where Record_Type and Archive_Input_File are null, and hence not in the
index. Are Record_Type and Archive_Input_File columns defined as not null?
As for the 2nd query, what is the index which you expect it to use?
Gary
Gary Kirsh
Next Extent Consulting
-----Original Message-----
Sent: Friday, May 31, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L
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: Kirsh, Gary
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: 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: Kirsh, Gary
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: 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).
