Rachel, you have an hawk eye, I had missed it (well, it has been a long
day at the end of a long week). What I see, however, in the second
statement is an EXISTS which adds nothing. A simple, uncorrelated IN
*might* yield a better result since the other table is small. I am not
sure that the CBO will choose an index access, but the odds would
definitely be higher.
Rachel Carmichael wrote:
>
> your first statement
>
> SELECT Record_Type, Archive_Input_File
> FROM MDMA_Input_File
> GROUP BY Record_Type, Archive_Input_File
>
> why GROUP BY and not ORDER BY? I mean, what are you grouping?
>
> I *think*, vague recollections, of reading that group by will force a
> full table scan. would be interesting to see plan if you change group
> by to order by
>
> --- kkennedy <kkennedy_at_firstpoint.com> wrote:
> > 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: Stephane Faroult
INET: sfaroult_at_oriole.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 Fri May 31 2002 - 17:03:26 CDT
