Re: Performance problem with SQL query

From: <Mark.Wright_at_bristol.ac.uk>
Date: Wed, 17 Sep 2003 10:04:11 GMT
Message-ID: <HLCryz.BsE@bath.ac.uk>

Todd Barry <tbarry2000_at_xhotmailx.com> wrote:
: Any guesses to what the CBO likes about the PK_FSMT_JRMIS_POINTS
: index? Is it related to what you called "options blah" in your first
: message? Since this index doesn't include the ID column, it seems
: there must be something about it that is related to other parts of the
: WHERE clause.

Hi Todd,
yes indeed, the PK is a 5 column index, of which the first two are present in the query where clause. I can see why it thinks that using it is better than a partition scan (even though it is actually slower). The main issue is: there is another 3 column index of which *all* column are in the where clause. This is the one it needs to use, but wont for some reason.

: Would you be able to post the actual SQL that you are trying?

I have changed the names of the table and columns...

SELECT SUM(NVL(column7,0))
FROM table1

WHERE   column1 = in_1
AND     column2 = in_2
AND     column6 = 'R'
AND     NVL(column5,'XXX') NOT LIKE '%ABC%'
AND     id IN (
	SELECT  id
	FROM    THE(
		SELECT  CAST(in_3 AS id_tab)
		FROM    dual));

TYPES:
id      VARCHAR2(100)

column1 DATE
column2 DATE
column3 NUMBER(9)

column4 VARCHAR2(15)
column5 VARCHAR2(9)
column6 VARCHAR2(1)

column7 NUMBER(7,2)

ALTER TABLE table1
 ADD CONSTRAINT PK_FSMT_JRMIS_POINTS
  PRIMARY KEY (column1, column2, column3, column4, column5) LOCAL (options, blah);

CREATE INDEX INDX_N3_JRMIS_POINTS ON
  table1(column1, column2, id)
LOCAL (options, blah);

I'm thinking that the CAST around the id column is making the optimizer think it cant use that second index. Is there any way to force it? Cheers for you help...
Mark

-- 

Received on Wed Sep 17 2003 - 05:04:11 CDT