Oracle-L: RE: Stubborn Table

From: Kirsh, Gary <gary.kirsh_at_gs.com>
Date: Fri, 31 May 2002 12:19:29 -0800
Message-ID: <F001.004716BB.20020531121929@fatcity.com>

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kirsh, Gary
INET: gary.kirsh_at_gs.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------