Re: Performance problem with SQL query

My guess is that the clustering factor on that second index is rather high. Have you tried lowering it. One quick way of doing that is to delete the statistics on that index. The CBO then uses defaults for the missing statistics and the default for clustering index is 800.

Originally posted by Mark Wright

> 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

>

-- 


--
Posted via http://dbforums.com