Improving query with implicit type conversion

From: McPeak, Matt <vxsmimmcp_at_subaru.com>
Date: Tue, 9 Sep 2014 20:12:14 +0000
Message-ID: <D7864FA3E7830B428CB2A5A5301B63EE01BC84D665_at_S7041VA005.soa.soaad.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 09 2014 - 22:12:14 CEST