Hi Rachel,
Actually, the group by is because I only want one copy of each key. You see the original query:
SELECT <<stuff>> FROM MDMA_INPUT_FILE
WHERE <<unindexed_value>>=1
ORDER BY record_type, archive_input_file, meter_identifier, units, data_time_stamp;
did a full table scan and a gruesome sort taking 45 minutes or more.
By selecting the first two keys in the aforementioned query and building an outer loop and doing
SELECT <<stuff>> FROM MDMA_INPUT_FILE
WHERE <<unindexed_value>>=1
and record_type=outer_record_type
and archive_input_file=outer_archive_input_file
ORDER BY record_type, archive_input_file, meter_identifier, units, data_time_stamp;
I was able to force use of the index, avoid writing the sort stuff out to a bandwidth limited device, and cut the run time better than in half.
I'm likely to be punished in this life or the next but I do what I gotta do.
Kevin
-----Original Message-----
Sent: Friday, May 31, 2002 1:07 PM
To: Multiple recipients of list ORACLE-L
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: 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).
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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: 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).
Received on Fri May 31 2002 - 16:54:58 CDT
