Improving query with implicit type conversion
- From: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Sep 2014 20:12:14 +0000
Hi!
I have a query in a 3rd party package that looks like this:
SELECT devl_project_id
FROM cz_devl_projects cdp
WHERE deleted_flag = 0
AND SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref),
':',
-1,
1)
+ 1, 100) = :b1
The query is taking longer to run than I would like.
I tried creating a function based index on SUBSTR(orig_sys_ref.... +1, 100)
(see above), but it looks like the calling code is using a numeric value for
:b1, meaning Oracle is doing an implicit type conversion.
So, I thought to create the FBI on TO_NUMBER(SUBSTR(orig_sys_ref... +1, 100)),
but it doesn't improve things.
I'm curious: why would this query (below) use the index...
SELECT devl_project_id
FROM cz_devl_projects cdp
WHERE deleted_flag = 0
AND to_Number(SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref),
':',
-1,
1)
+ 1, 100)) = to_number(:b1)
... and this query ...
SELECT devl_project_id
FROM cz_devl_projects cdp
WHERE deleted_flag = 0
AND SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref),
':',
-1,
1)
+ 1, 100) = to_number(:b1)
...not use the index? I guess the simple answer is that the implicit type
conversion isn't just sticking a TO_NUMBER() around the expression. Is there
any way to figure out the expression I'd need to index to get this to work?
When I look at the predicate information in DBMS_XPLAN, I get this:
SQL_ID 1xu0zvvngwqsh, child number 0
-------------------------------------
SELECT cdp.devl_project_id,ROWID FROM cz_devl_projects cdp WHERE
deleted_flag = 0 AND SUBSTR (orig_sys_ref, INSTR (TRIM
(orig_sys_ref), ':',
-1,
1) + 1, 100) = 12345
Plan hash value: 379709735
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0
|00:00:00.29 | 6809 |
|* 1 | TABLE ACCESS FULL| CZ_DEVL_PROJECTS | 1 | 21 | 0
|00:00:00.29 | 6809 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter((TO_NUMBER(SUBSTR("ORIG_SYS_REF",INSTR(TRIM("ORIG_SYS_REF"),':',-1,1)+1,10
0))=12345 AND TO_NUMBER("DELETED_FLAG")=0))
So, I don't know what else to try except for TO_NUMBER(). Any thoughts? I
cannot change the query syntax.
Thanks,
Matt
Other related posts:
