Hi
I have a simple query with an additional index (oracle)
SQL_ID gxmnctbrw28pv
--------------------
SELECT /*DISTINCT*/ /*+ FIRST_ROWS( 10000 ) */ t7.PARENTINSTANCEID ,
t7.PARENTSTEPTYPE , t7.INSTANCEID , t7.CUSTOMID , t7.PROCESSKEY ,
t7.PROCESSKEY PROCESSKEYDECODE, t25.TYPE , t25.PROCESSLABEL ,
t7.MODELVERSION , t7.FIRSTSTATUS , t7.FIRSTTIME , t7.STATUS LASTSTATUS,
t7.AUDITTIMESTAMP LASTTIME, t25.TRACKINGENABLED , t25.DEPLOYMENTVERSION
FROM PRA_PROCESS_RECENT t7, WMPROCESSDEFINITION t25 WHERE
t7.PROCESSKEY = t25.PROCESSKEY AND t25.DEPLOYMENTVERSION = (SELECT
MAX(t2.DEPLOYMENTVERSION) FROM WMPROCESSDEFINITION t2 WHERE
t7.PROCESSKEY = t2.PROCESSKEY AND t7.MODELVERSION = t2.MODELVERSION )
AND (t7.PARENTSTEPTYPE <> 3 OR t7.PARENTSTEPTYPE IS null) AND
(t7.AUDITTIMESTAMP <= :v0 ) AND (UPPER(t7.PARENTINSTANCEID) LIKE :v1
) ORDER BY t7.AUDITTIMESTAMP DESC
Plan hash value: 1265791542
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 92094 (100)| |
| 1 | NESTED LOOPS | | 10001 | 2910K| 92094 (1)| 00:18:26 |
| 2 | NESTED LOOPS | | 19955 | 2910K| 92094 (1)| 00:18:26 |
| 3 | NESTED LOOPS | | 19955 | 3858K| 75138 (1)| 00:15:02 |
| 4 | TABLE ACCESS BY INDEX ROWID| PRA_PROCESS_RECENT | 327K| 57M| 43354 (1)| 00:08:41 |
| 5 | INDEX RANGE SCAN | IGDX | 26414 | | 20518 (1)| 00:04:07 |
| 6 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | FILTER | | | | | |
| 8 | SORT AGGREGATE | | 1 | 67 | | |
| 9 | INDEX RANGE SCAN | WMPRCSSDFNTN_PKX | 6 | 402 | 2 (0)| 00:00:01 |
| 10 | INDEX RANGE SCAN | WMPRCSSDFNTN_PKX | 1 | | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | WMPROCESSDEFINITION | 1 | 100 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
but the problem starts when I check what the bind values ??are, it always starts and ends with “%”, which leads to read all distinct key values in index .
Peeked Binds (identified by position):
1 - :V0 (TIMESTAMP): [Not Printable]
2 - :V1 (CHAR(30), CSID=32): ‘%88F7FAE0-AFF7-47AD-8724-BB51FE14CDBA%’
How can I create index for such condition , or can I disable such wildcard search ?