Performance issue

Hello friends,

we have a big partition table ,with two columns as local nonunique indexes(on each partition) . Recently we drop this
local index and put a nonunique global index on one column .

But one user is facing problem on quering this table with date in WHERE clause after putting global index.

Eg : select * from tablea where date between '03-FEB-2001' and '09-FEB-2001' and col1 like 'ABC%'

here col1 is globel index column .
date is the partition column

This query gives some rows and hangs for ever !

we tried with below query
select * from tab1 where date between
to_date('03-FEB-2001','DD-MON-YYYY') and to_date('09-FEB-2001','DD-MON-YYYY') and col1 like 'ABC%'

This time it gives result in 3 miniues which is not acceptable to user .

We have checked the following .

Problem is not related with index hint

There is no block corruption in global index because we are able to get the result by workaround query.

validated the index structure and statistics are stored in SYS.INDEX_STATS table. 2.2GB is allocated at present 1.8GB is used space. Could not find any thing wrong in statistics.


Thanks in advance
tom

"Like" clause causes index NOT to be used.

I could see index hit in explain plan .

INDEX (RANGE SCAN) OF GL_xy_col1_IDX (NON-UNIQUE)
(Cost=4 Card=4)

thanks

Incorrect. The LIKE *will* use the index if it can.

WHERE x LIKE 'ABC%' will consider the index
WHERE x LIKE '%ABC% will not consider the index.

- Chris

Correction:
Like clause causes index to be ignored when pattern match character "%" is the first character in the like clause.
Try putting your Col1 clause first.

So what is the complete plan? General guideline - when asking for query performance help, include actual query and explain plan.

- Chris

Sorry, JDoyle, but I fear I must correct you again - the order of the WHERE clause actually has no impact on the CBO optimizer.

- Chris

Here is the actual query and explian plan

select df_num from finx_tab where tst_date between '03-FEB-2001' and '09-FEB-2001' and hd_num like 'KLX%' .

SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4 Bytes=168)

HASH JOIN (Cost=57 Card=4 Bytes=168)
TABLE ACCESS (FULL) OF PD_IN (Cost=2 card=9 Bytes=144)

TABLE ACCESS (BY GLOBAL INDEX ROWID) OF FD_TABLE (Cost=54 Card=4 Bytes=104)

INDEX (RANGE SCAN) OF GL_DF_NUM_IDX (NON-UNIQUE) (Cost=4 Card=4) .

Oy! All that and I'm still not getting it. :(

Unfortunately, I'm also not 100% up on the differences in the CBO's handling of local vs global partitioned indexes.

Hopefully someone else will chime in with the answer, but until then:

What is PD_IN?

- Chris

select df_num from finx_tab where tst_date between '03-FEB-2001' and '09-FEB-2001' and df_num like 'KLX%' .

SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4 Bytes=168)

HASH JOIN (Cost=57 Card=4 Bytes=168)
TABLE ACCESS (FULL) OF PD_IN (Cost=2 card=9 Bytes=144)

TABLE ACCESS (BY GLOBAL INDEX ROWID) OF FD_TABLE (Cost=54 Card=4 Bytes=104)

INDEX (RANGE SCAN) OF GL_DF_NUM_IDX (NON-UNIQUE) (Cost=4 Card=4) .




here finx_tab is a view based on the partition table called FD_TABLE and a non partitioned table PD_IN .


thanks