Kevin, Fair enough... but ouch on that query!
Rachel --- kkennedy <[EMAIL PROTECTED]> wrote: > 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 <[EMAIL PROTECTED]> 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: [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). > > > __________________________________________________ > 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: [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). __________________________________________________ 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: [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).
