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).
