-
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
-
something interesting .
when i run above query , it select arount 30 rows , then i t hangs for ever .
-
Okay, not to be too difficult, but I would then need to see the view's query as well, please.
- Chris
-
Chris,
Here is view query ,
select /*+ INDEX(FD_TABLE) */ df_num,col2,col3, col4
from fd_table
where test_date between to_date('18-JAN-2001','DD-MON-YYYY') and to_date('17-FEB-2001','DD-MON-YYYY')
The above hint we put when local index was there on the table and it is still there with global index.
thanks
-
Okay, we obviously must be mis-communicating somewhere here.
In order for me to help you diagnose this query, I will need *the entire query*.
What you originally posted as a query:
select df_num from finx_tab where tst_date between '03-FEB-2001' and '09-FEB-2001' and df_num like 'KLX%' .
You further said that the following plan was generated from this statement:
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) .
Unfortunately, neither FD_TABLE nor PD_IN were referenced in the query.
Then you tell me they came from the view. Ah, finx_tab is actually a view. I asked for the view definition and you gave me:
select /*+ INDEX(FD_TABLE) */ df_num,col2,col3, col4
from fd_table
where test_date between to_date('18-JAN-2001','DD-MON-YYYY') and to_date('17-FEB-2001','DD-MON-YYYY')
Now, there are 2 problems with this last reply:
- There is *still* no reference to PD_IN. Obviously, there is still a piece you are not giving me.
- When replacing the view into the query, we end up with:
SELECT
---DF_NUM
FROM
---(
---SELECT /*+ INDEX(FD_TABLE) */
------DF_NUM,
------COL2---,
------COL3---,
------COL4
---FROM
------FD_TABLE
---WHERE
------TEST_DATE---BETWEEN---TO_DATE('18-JAN-2001','DD-MON-YYYY')
------------------AND------TO_DATE('17-FEB-2001','DD-MON-YYYY')
---)
WHERE
---TST_DATE---BETWEEN---'03-FEB-2001'
------------AND------'09-FEB-2001'---AND
---DF_NUM---LIKE------'KLX%'
Which has all sorts of problems, including the fact that the date range is restricted twice and there is no column tst_date being returned from the innermost query, so how can it appear in the outermost WHERE clause?
It is starting to look like I may not be able to help you, but I will give this one more try. If you want me to be able to attempt to even start diagnosing this particular problem, you will need to provide:
- ALL the SQL for this issue:
- The EXACT original query
- The EXACT definition of all involved views
- The EXACT explain plan for the query
- Any and all relevant info.
- FD_TABLE is a table partitioned by TEST_DATE (what ranges?) with a global non-unique index on DF_NUM. Is there any other relevant info we should know about this table. What were the exact indexes like before, when performance was okay? Why did you switch them?
- What is PD_IN? What are its indexes like? Did they change? If so, why?
I hate to sound like such a stickler, but this is obviously taking way too long to get the the root of the problem.
- Chris
