IBM webMethods Hybrid Integration

IBM webMethods Hybrid Integration

Join this online group to communicate across IBM product users and experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.

 View Only
Back to discussions
Expand all | Collapse all

wildcard condition in query on PRA_PROCESS_RECENT

  • 1.  wildcard condition in query on PRA_PROCESS_RECENT

    Posted Tue December 10, 2019 02:21 AM

    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 ?


    #Integration-Server-and-ESB
    #webMethods
    #webMethods-General


Related Content