Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-12832

Cannot create index ACT_IDX_JOB_HANDLER on Oracle with extended data types

XMLWordPrintable

      Environment

      • Oracle 12 and higher
      • Default block size of 8,000 byte

      Description

      Creating the Camunda schema fails at the index ACT_IDX_JOB_HANDLER, if Oracle >= 12 is configured to create extended data types for character columns (including NVARCHAR2). This lifts the historic limitation of varchar fields to be restricted to 4,000 bytes, which depending on the encoding in use could not hold every possible value. With the larger field values, index keys can become too large for Oracle to handle, as one key must fit into a single block.

      Steps to reproduce

      Observed Behavior

      • Index creation fails, explaining that the maximum key length for the index is exceeded (maximum length is 6398)

      Expected behavior

      • The index and the entire schema can be created without problems

      Root Cause

      • On Oracle, an index key must fit into one block
      • With the default block size of 8,000 bytes, the keys of the index may not exceed 6,398 bytes
      • The index is defined on ACT_RU_JOB(HANDLER_TYPE_,HANDLER_CFG_). HANDLER_TYPE_ is NVARCHAR2(255), HANDLER_CFG_(2000)
      • For example with UTF-8 encoding, HANDLER_CFG_ will consume up to 8,000 bytes

      Solution Ideas

      Oracle's recommendations for solutions can be found at https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html -> Creating an Index on an Extended Data Type Column

      1. Instead of indexing the raw column, we can create a function-based index that hashes the column value on ACT_RU_JOB(HANDLER_TYPE_, STANDARD_HASH(HANDLER_CFG_)). According to Oracle docs, the index can still be used by the query optimizer if HANDLER_CFG_ is used in equality or IN predicates
      2. Instead of indexing the raw column, we can create an index on a substring of the column so that the index is overall below the mark of 6,398 bytes. In that case, the index can still be used for equality, IN and range predicates.

      Option 1 looks preferable. With option 2, we would need to carefully evaluate that the field values never actually exceeds the chosen substring length (this may be true as of now, see hints for what we currently store in the field).

      Hints

      Function-based indexes

      • Function-based indexes cannot index null values. STANDARD_HASH of null returns a non-null value. Also, since the index is composite and HANDLER_TYPE_ is never null, this should probably be ok either way

      Query

      • HANDLER_CFG_ is used in one query (besides the select by id and update queries): Job.xml selectJobsByConfiguration.
        • Usages:
          • If there is an async job for the triggering of an event subscription and that event subscription is deleted while jobs exist
          • Deleting the timer start jobs when a process definition is deleted via API
          • Replacing the timer start event jobs when a new version of a process is deployed
        • The HANDLER_TYPE_ and HANDLER_CFG_ fields are only used in equality predicates
      • Extended data types are only supported with Oracle >= 12
      • Function-based indexes also work on Oracle 11

      Usage of HANDLER_CFG_

      • Maps to JobEntity#jobHandlerConfigurationRaw
      • Written from JobHandlerConfiguration#toCanonicalString
      • JobHandlerConfiguration has the following implementations (second part is the content that the configurations write):
        • AsyncContinuationConfiguration: <atomic operation>$<transitionId>
        • BatchJobConfiguration: <configurationByteArrayId>
        • BatchMonitorJobConfiguration: <batchId>
        • BatchSeedJobConfiguration: <batchId>
        • EventSubscriptionJobConfiguration: <eventSubscriptionId>
        • HistoryCleanupJobHandlerConfiguration: {"countEmptyRuns": <int>, "immediatelyDue": <boolean>, "minuteFrom": <int>, "minuteTo": <int>}
        • JobDefinitionSuspensionStateConfiguration: {"by": <string: currently max 22 chars>, "jobDefinitionId": <string: id NVARCHAR2(64)>, "processDefinitionId": <string: id NVARCHAR2(64)>, "processDefinitionKey": <string: id NVARCHAR2(255)>, "processDefinitionTenantId": <string: custom id; NVARCHAR2(64)>, "includeJobs": <boolean>}
        • ProcessDefinitionSuspensionStateConfiguration {"by": <string: currently max 22 chars>, "processDefinitionId": <string: id, NVARCHAR2(64)>, "processDefinitionKey": <string: id, NVARCHAR2(255)>, "processDefinitionTenantId": <string: custom id, NVARCHAR2(64)>, "includeProcessInstances": <boolean>}
        • TimerJobConfiguration <timerElementKey; activity id of any size>$taskListener~<timerElementSecondaryKey; task listener id of any size>$followUpJobCreated

      Additional sources

        This is the controller panel for Smart Panels app

            [CAM-12832] Cannot create index ACT_IDX_JOB_HANDLER on Oracle with extended data types

            Thorben Lindhauer added a comment -

            I verified that the index is useful for our case with the following procedure:

            Setup

            Artificial Test Data:

            • 50,000 rows in ACT_RU_JOB
            • 9 different handler types with uniform distribution
            • Unique handler configuration values
            • Tested on Portainer with Oracle 19

            Set up schema

            Current definition of job table plus all indexes except for ACT_IDX_JOB_HANDLER

            create table ACT_RU_JOB (
    ID_ NVARCHAR2(64) NOT NULL,
    REV_ INTEGER,
    TYPE_ NVARCHAR2(255) NOT NULL,
    LOCK_EXP_TIME_ TIMESTAMP(6),
    LOCK_OWNER_ NVARCHAR2(255),
    EXCLUSIVE_ NUMBER(1,0) CHECK (EXCLUSIVE_ IN (1,0)),
    EXECUTION_ID_ NVARCHAR2(64),
    PROCESS_INSTANCE_ID_ NVARCHAR2(64),
    PROCESS_DEF_ID_ NVARCHAR2(64),
    PROCESS_DEF_KEY_ NVARCHAR2(255),
    RETRIES_ INTEGER,
    EXCEPTION_STACK_ID_ NVARCHAR2(64),
    EXCEPTION_MSG_ NVARCHAR2(2000),
    FAILED_ACT_ID_ NVARCHAR2(255),
    DUEDATE_ TIMESTAMP(6),
    REPEAT_ NVARCHAR2(255),
    REPEAT_OFFSET_ NUMBER(19,0) DEFAULT 0,
    HANDLER_TYPE_ NVARCHAR2(255),
    HANDLER_CFG_ NVARCHAR2(2000),
    DEPLOYMENT_ID_ NVARCHAR2(64),
    SUSPENSION_STATE_ INTEGER DEFAULT 1 NOT NULL,
    JOB_DEF_ID_ NVARCHAR2(64),
    PRIORITY_ NUMBER(19,0) DEFAULT 0 NOT NULL,
    SEQUENCE_COUNTER_ NUMBER(19,0),
    TENANT_ID_ NVARCHAR2(64),
    CREATE_TIME_ TIMESTAMP(6),
    primary key (ID_)
);

create index ACT_IDX_JOB_EXECUTION_ID on ACT_RU_JOB(EXECUTION_ID_);
create index ACT_IDX_JOB_PROCINST on ACT_RU_JOB(PROCESS_INSTANCE_ID_);
create index ACT_IDX_JOB_TENANT_ID on ACT_RU_JOB(TENANT_ID_, 0);
create index ACT_IDX_JOB_JOB_DEF_ID on ACT_RU_JOB(JOB_DEF_ID_);
create index ACT_IDX_JOB_EXCEPTION on ACT_RU_JOB(EXCEPTION_STACK_ID_);
create index ACT_IDX_JOB_HANDLER_TYPE ON ACT_RU_JOB(HANDLER_TYPE_);

            Generate handler types

            CREATE TABLE handler_type (NAME NVARCHAR2(255), primary key (NAME));
INSERT INTO handler_type(NAME) SELECT dbms_random.string('l', 10) FROM dual connect by level <= 9;

            Generate job data

            BEGIN
  FOR i IN 1..50000 loop
    insert into ACT_RU_JOB ( 
      ID_, TYPE_, LOCK_OWNER_, LOCK_EXP_TIME_, EXCLUSIVE_, 
      EXECUTION_ID_, PROCESS_INSTANCE_ID_, PROCESS_DEF_ID_, PROCESS_DEF_KEY_, RETRIES_, 
      EXCEPTION_STACK_ID_, EXCEPTION_MSG_, FAILED_ACT_ID_, DUEDATE_, REPEAT_, 
      REPEAT_OFFSET_, HANDLER_TYPE_, HANDLER_CFG_, DEPLOYMENT_ID_, SUSPENSION_STATE_, 
      JOB_DEF_ID_, PRIORITY_, SEQUENCE_COUNTER_, TENANT_ID_, CREATE_TIME_, 
      REV_ ) 
    values (
    (SELECT dbms_random.string('l', 32) FROM dual), 'timer', null, null, 1, 
      NULL, null, 'startTimerEventExample:1:3', 'startTimerEventExample', 3, 
      null, null, null, null, null, 
      0, (SELECT NAME FROM (SELECT * FROM handler_type ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum = 1), (SELECT dbms_random.string('l', 20) FROM dual), '1', 1, 
      '4', 0, 1, null, null, 1);
  END LOOP;
END;

            Test Query

            select * from ACT_RU_JOB
      where HANDLER_TYPE_ = 'saxxrbaxjp'
      and TENANT_ID_ is null
      and HANDLER_CFG_ = 'rjsqkfhilklyiwsobvib';

            Where the String literals should be replaced with some valid values of the generated Strings

            Evaluated index options:

            Query plans generated via

            EXPLAIN PLAN FOR <query>

SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));

            Option 1: No additional index

            Query Plan:

            |PLAN_TABLE_OUTPUT                                                                                   |
|----------------------------------------------------------------------------------------------------|
|Plan hash value: 1103516986                                                                         |
|                                                                                                    |
|----------------------------------------------------------------------------------------------------|
|| Id  | Operation                           | Name                     | Rows  | Bytes | Cost (%CPU)|
|----------------------------------------------------------------------------------------------------|
||   0 | SELECT STATEMENT                    |                          |    32 |   193K|    41   (0)|
||*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ACT_RU_JOB               |    32 |   193K|    41   (0)|
||*  2 |   INDEX RANGE SCAN                  | ACT_IDX_JOB_HANDLER_TYPE |  6024 |       |    41   (0)|
|----------------------------------------------------------------------------------------------------|
                                                                                 |
|----------------------------------------------------------------------------------------------------|
|Predicate Information (identified by operation id):                                                 |
|---------------------------------------------------                                                 |
|                                                                                                    |
|   1 - filter("TENANT_ID_" IS NULL AND "HANDLER_CFG_"=U'rjsqkfhilklyiwsobvib')                      |
|   2 - access("HANDLER_TYPE_"=U'saxxrbaxjp')                                                        |

            The index ACT_IDX_JOB_HANDLER_TYPE is used and config filtering is applied on the results.

            Option 2: With ACT_IDX_JOB_HANDLER

            create index ACT_IDX_JOB_HANDLER on ACT_RU_JOB(HANDLER_TYPE_,HANDLER_CFG_);

            Query Plan:

            |PLAN_TABLE_OUTPUT                                                                                   |
|----------------------------------------------------------------------------------------------------|
|Plan hash value: 2788157170                                                                         |
|                                                                                                    |
|----------------------------------------------------------------------------------------------------|
|| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Tim|
|----------------------------------------------------------------------------------------------------|
||   0 | SELECT STATEMENT                    |                     |    32 |   193K|    32   (0)| 00:|
||*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ACT_RU_JOB          |    32 |   193K|    32   (0)| 00:|
||*  2 |   INDEX RANGE SCAN                  | ACT_IDX_JOB_HANDLER |    32 |       |     3   (0)| 00:|
|----------------------------------------------------------------------------------------------------|
                                                                           |
|----------------------------------------------------------------------------------------------------|
|Predicate Information (identified by operation id):                                                 |
|---------------------------------------------------                                                 |
|                                                                                                    |
|   1 - filter("TENANT_ID_" IS NULL)                                                                 |
|   2 - access("HANDLER_TYPE_"=U'saxxrbaxjp' AND "HANDLER_CFG_"=U'rjsqkfhilklyiwsobvib')             |

            ACT_IDX_JOB_HANDLER is used

            Option 3: With new hash-based index

            create index ACT_IDX_JOB_HANDLER_ALT ON ACT_RU_JOB(HANDLER_TYPE_, STANDARD_HASH(HANDLER_CFG_));

            Query Plan:

            |PLAN_TABLE_OUTPUT                                                                                   |
|----------------------------------------------------------------------------------------------------|
|Plan hash value: 4089271614                                                                         |
|                                                                                                    |
|----------------------------------------------------------------------------------------------------|
|| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)||
|----------------------------------------------------------------------------------------------------|
||   0 | SELECT STATEMENT                    |                         |    32 |   194K|     4   (0)||
||*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ACT_RU_JOB              |    32 |   194K|     4   (0)||
||*  2 |   INDEX RANGE SCAN                  | ACT_IDX_JOB_HANDLER_ALT |     1 |       |     3   (0)||
|----------------------------------------------------------------------------------------------------|
                                                                                  |
|----------------------------------------------------------------------------------------------------|
|Predicate Information (identified by operation id):                                                 |
|---------------------------------------------------                                                 |
|                                                                                                    |
|   1 - filter("TENANT_ID_" IS NULL AND "HANDLER_CFG_"=U'rjsqkfhilklyiwsobvib')                      |
|   2 - access("HANDLER_TYPE_"=U'saxxrbaxjp' AND STANDARD_HASH("HANDLER_CFG_")=HEXTORAW('5FAAABE29FB7|
|              2695756607B7134242696C24B'))

            The new index is used by hashing the handler config first. Out of the results, the true config value must be checked again (due to potential hash collisions).

            Double-check: Both indexes

            When both indexes are in place, the new ACT_IDX_JOB_HANDLER_ALT was still used. I'm not confident though that ACT_IDX_JOB_HANDLER_ALT is universally better. I used an artificial data set and didn't investigate things like query plan caching.

            Thorben Lindhauer added a comment - I verified that the index is useful for our case with the following procedure: Setup Artificial Test Data: 50,000 rows in ACT_RU_JOB 9 different handler types with uniform distribution Unique handler configuration values Tested on Portainer with Oracle 19 Set up schema Current definition of job table plus all indexes except for ACT_IDX_JOB_HANDLER create table ACT_RU_JOB ( ID_ NVARCHAR2(64) NOT NULL, REV_ INTEGER, TYPE_ NVARCHAR2(255) NOT NULL, LOCK_EXP_TIME_ TIMESTAMP(6), LOCK_OWNER_ NVARCHAR2(255), EXCLUSIVE_ NUMBER(1,0) CHECK (EXCLUSIVE_ IN (1,0)), EXECUTION_ID_ NVARCHAR2(64), PROCESS_INSTANCE_ID_ NVARCHAR2(64), PROCESS_DEF_ID_ NVARCHAR2(64), PROCESS_DEF_KEY_ NVARCHAR2(255), RETRIES_ INTEGER, EXCEPTION_STACK_ID_ NVARCHAR2(64), EXCEPTION_MSG_ NVARCHAR2(2000), FAILED_ACT_ID_ NVARCHAR2(255), DUEDATE_ TIMESTAMP(6), REPEAT_ NVARCHAR2(255), REPEAT_OFFSET_ NUMBER(19,0) DEFAULT 0, HANDLER_TYPE_ NVARCHAR2(255), HANDLER_CFG_ NVARCHAR2(2000), DEPLOYMENT_ID_ NVARCHAR2(64), SUSPENSION_STATE_ INTEGER DEFAULT 1 NOT NULL, JOB_DEF_ID_ NVARCHAR2(64), PRIORITY_ NUMBER(19,0) DEFAULT 0 NOT NULL, SEQUENCE_COUNTER_ NUMBER(19,0), TENANT_ID_ NVARCHAR2(64), CREATE_TIME_ TIMESTAMP(6), primary key (ID_) ); create index ACT_IDX_JOB_EXECUTION_ID on ACT_RU_JOB(EXECUTION_ID_); create index ACT_IDX_JOB_PROCINST on ACT_RU_JOB(PROCESS_INSTANCE_ID_); create index ACT_IDX_JOB_TENANT_ID on ACT_RU_JOB(TENANT_ID_, 0); create index ACT_IDX_JOB_JOB_DEF_ID on ACT_RU_JOB(JOB_DEF_ID_); create index ACT_IDX_JOB_EXCEPTION on ACT_RU_JOB(EXCEPTION_STACK_ID_); create index ACT_IDX_JOB_HANDLER_TYPE ON ACT_RU_JOB(HANDLER_TYPE_); Generate handler types CREATE TABLE handler_type (NAME NVARCHAR2(255), primary key (NAME)); INSERT INTO handler_type(NAME) SELECT dbms_random.string( 'l' , 10) FROM dual connect by level <= 9; Generate job data BEGIN FOR i IN 1..50000 loop insert into ACT_RU_JOB ( ID_, TYPE_, LOCK_OWNER_, LOCK_EXP_TIME_, EXCLUSIVE_, EXECUTION_ID_, PROCESS_INSTANCE_ID_, PROCESS_DEF_ID_, PROCESS_DEF_KEY_, RETRIES_, EXCEPTION_STACK_ID_, EXCEPTION_MSG_, FAILED_ACT_ID_, DUEDATE_, REPEAT_, REPEAT_OFFSET_, HANDLER_TYPE_, HANDLER_CFG_, DEPLOYMENT_ID_, SUSPENSION_STATE_, JOB_DEF_ID_, PRIORITY_, SEQUENCE_COUNTER_, TENANT_ID_, CREATE_TIME_, REV_ ) values ( (SELECT dbms_random.string( 'l' , 32) FROM dual), 'timer' , null , null , 1, NULL, null , 'startTimerEventExample:1:3' , 'startTimerEventExample' , 3, null , null , null , null , null , 0, (SELECT NAME FROM (SELECT * FROM handler_type ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum = 1), (SELECT dbms_random.string( 'l' , 20) FROM dual), '1' , 1, '4' , 0, 1, null , null , 1); END LOOP; END; Test Query select * from ACT_RU_JOB where HANDLER_TYPE_ = 'saxxrbaxjp' and TENANT_ID_ is null and HANDLER_CFG_ = 'rjsqkfhilklyiwsobvib' ; Where the String literals should be replaced with some valid values of the generated Strings Evaluated index options: Query plans generated via EXPLAIN PLAN FOR <query> SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=> 'ALL +OUTLINE' )); Option 1: No additional index Query Plan: |PLAN_TABLE_OUTPUT | |----------------------------------------------------------------------------------------------------| |Plan hash value: 1103516986 | | | |----------------------------------------------------------------------------------------------------| || Id | Operation | Name | Rows | Bytes | Cost (%CPU)| |----------------------------------------------------------------------------------------------------| || 0 | SELECT STATEMENT | | 32 | 193K| 41 (0)| ||* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ACT_RU_JOB | 32 | 193K| 41 (0)| ||* 2 | INDEX RANGE SCAN | ACT_IDX_JOB_HANDLER_TYPE | 6024 | | 41 (0)| |----------------------------------------------------------------------------------------------------| | |----------------------------------------------------------------------------------------------------| |Predicate Information (identified by operation id): | |--------------------------------------------------- | | | | 1 - filter( "TENANT_ID_" IS NULL AND "HANDLER_CFG_" =U 'rjsqkfhilklyiwsobvib' ) | | 2 - access( "HANDLER_TYPE_" =U 'saxxrbaxjp' ) | The index ACT_IDX_JOB_HANDLER_TYPE is used and config filtering is applied on the results. Option 2: With ACT_IDX_JOB_HANDLER create index ACT_IDX_JOB_HANDLER on ACT_RU_JOB(HANDLER_TYPE_,HANDLER_CFG_); Query Plan: |PLAN_TABLE_OUTPUT | |----------------------------------------------------------------------------------------------------| |Plan hash value: 2788157170 | | | |----------------------------------------------------------------------------------------------------| || Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim| |----------------------------------------------------------------------------------------------------| || 0 | SELECT STATEMENT | | 32 | 193K| 32 (0)| 00:| ||* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ACT_RU_JOB | 32 | 193K| 32 (0)| 00:| ||* 2 | INDEX RANGE SCAN | ACT_IDX_JOB_HANDLER | 32 | | 3 (0)| 00:| |----------------------------------------------------------------------------------------------------| | |----------------------------------------------------------------------------------------------------| |Predicate Information (identified by operation id): | |--------------------------------------------------- | | | | 1 - filter( "TENANT_ID_" IS NULL) | | 2 - access( "HANDLER_TYPE_" =U 'saxxrbaxjp' AND "HANDLER_CFG_" =U 'rjsqkfhilklyiwsobvib' ) | ACT_IDX_JOB_HANDLER is used Option 3: With new hash-based index create index ACT_IDX_JOB_HANDLER_ALT ON ACT_RU_JOB(HANDLER_TYPE_, STANDARD_HASH(HANDLER_CFG_)); Query Plan: |PLAN_TABLE_OUTPUT | |----------------------------------------------------------------------------------------------------| |Plan hash value: 4089271614 | | | |----------------------------------------------------------------------------------------------------| || Id | Operation | Name | Rows | Bytes | Cost (%CPU)|| |----------------------------------------------------------------------------------------------------| || 0 | SELECT STATEMENT | | 32 | 194K| 4 (0)|| ||* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ACT_RU_JOB | 32 | 194K| 4 (0)|| ||* 2 | INDEX RANGE SCAN | ACT_IDX_JOB_HANDLER_ALT | 1 | | 3 (0)|| |----------------------------------------------------------------------------------------------------| | |----------------------------------------------------------------------------------------------------| |Predicate Information (identified by operation id): | |--------------------------------------------------- | | | | 1 - filter( "TENANT_ID_" IS NULL AND "HANDLER_CFG_" =U 'rjsqkfhilklyiwsobvib' ) | | 2 - access( "HANDLER_TYPE_" =U 'saxxrbaxjp' AND STANDARD_HASH( "HANDLER_CFG_" )=HEXTORAW('5FAAABE29FB7| | 2695756607B7134242696C24B')) The new index is used by hashing the handler config first. Out of the results, the true config value must be checked again (due to potential hash collisions). Double-check: Both indexes When both indexes are in place, the new ACT_IDX_JOB_HANDLER_ALT was still used. I'm not confident though that ACT_IDX_JOB_HANDLER_ALT is universally better. I used an artificial data set and didn't investigate things like query plan caching.

            Thorben Lindhauer added a comment - - edited

            How does the new index work?

            It looks like the new index creates a virtual column in the table with a system generated name, in my example this is called SYS_NC00028$. This is confirmed by the following result of select * from user_tab_cols where table_name = 'ACT_RU_JOB' (I have removed all-null columns from the result):

            TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HIDDEN_COLUMN VIRTUAL_COLUMN SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID HISTOGRAM QUALIFIED_COL_NAME USER_GENERATED DEFAULT_ON_NULL IDENTITY_COLUMN COLLATION
            ----------------- --------------------- -------------- ------------- ---------------- ------------ -------- ------------ --------------- ------------ ------------------- --------------------- ------------ ---------- ------------ ------------ --------- ------------- ------------- ------------- -------------- ------------------ ------------------- --------------- --------------------- -------------- --------------- --------------- ---------------
            ACT_RU_JOB JOB_DEF_ID_ NVARCHAR2 128     Y 22     NCHAR_CS 64 NO NO   64 C NO YES NO NO 22 22 NONE JOB_DEF_ID_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB PRIORITY_ NUMBER 22 19 0 N 23 2 0     NO NO   0   NO YES NO NO 23 23 NONE PRIORITY_ YES NO NO  
            ACT_RU_JOB SEQUENCE_COUNTER_ NUMBER 22 19 0 Y 24         NO NO   0   NO YES NO NO 24 24 NONE SEQUENCE_COUNTER_ YES NO NO  
            ACT_RU_JOB TENANT_ID_ NVARCHAR2 128     Y 25     NCHAR_CS 64 NO NO   64 C NO YES NO NO 25 25 NONE TENANT_ID_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB CREATE_TIME_ TIMESTAMP(6) 11   6 Y 26         NO NO   0   NO YES NO NO 26 26 NONE CREATE_TIME_ YES NO NO  
            ACT_RU_JOB SYS_NC00027$ NUMBER 1   0 Y   1 0     NO NO   0   NO YES YES YES   27 NONE SYS_NC00027$ NO NO NO  
            ACT_RU_JOB SYS_NC00028$ RAW 20     Y   29 STANDARD_HAS     NO NO   0   NO YES YES YES   28 NONE SYS_NC00028$ NO NO NO  
            ACT_RU_JOB DUEDATE_ TIMESTAMP(6) 11   6 Y 15         NO NO   0   NO YES NO NO 15 15 NONE DUEDATE_ YES NO NO  
            ACT_RU_JOB REPEAT_ NVARCHAR2 510     Y 16     NCHAR_CS 255 NO NO   255 C NO YES NO NO 16 16 NONE REPEAT_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB REPEAT_OFFSET_ NUMBER 22 19 0 Y 17 1 0     NO NO   0   NO YES NO NO 17 17 NONE REPEAT_OFFSET_ YES NO NO  
            ACT_RU_JOB HANDLER_TYPE_ NVARCHAR2 510     Y 18     NCHAR_CS 255 NO NO   255 C NO YES NO NO 18 18 NONE HANDLER_TYPE_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB HANDLER_CFG_ NVARCHAR2 4000     Y 19     NCHAR_CS 2000 NO NO   2000 C NO YES NO NO 19 19 NONE HANDLER_CFG_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB DEPLOYMENT_ID_ NVARCHAR2 128     Y 20     NCHAR_CS 64 NO NO   64 C NO YES NO NO 20 20 NONE DEPLOYMENT_ID_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB SUSPENSION_STATE_ NUMBER 22   0 N 21 2 1     NO NO   0   NO YES NO NO 21 21 NONE SUSPENSION_STATE_ YES NO NO  
            ACT_RU_JOB PROCESS_INSTANCE_ID_ NVARCHAR2 128     Y 8     NCHAR_CS 64 NO NO   64 C NO YES NO NO 8 8 NONE PROCESS_INSTANCE_ID_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB PROCESS_DEF_ID_ NVARCHAR2 128     Y 9     NCHAR_CS 64 NO NO   64 C NO YES NO NO 9 9 NONE PROCESS_DEF_ID_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB PROCESS_DEF_KEY_ NVARCHAR2 510     Y 10     NCHAR_CS 255 NO NO   255 C NO YES NO NO 10 10 NONE PROCESS_DEF_KEY_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB RETRIES_ NUMBER 22   0 Y 11         NO NO   0   NO YES NO NO 11 11 NONE RETRIES_ YES NO NO  
            ACT_RU_JOB EXCEPTION_STACK_ID_ NVARCHAR2 128     Y 12     NCHAR_CS 64 NO NO   64 C NO YES NO NO 12 12 NONE EXCEPTION_STACK_ID_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB EXCEPTION_MSG_ NVARCHAR2 4000     Y 13     NCHAR_CS 2000 NO NO   2000 C NO YES NO NO 13 13 NONE EXCEPTION_MSG_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB FAILED_ACT_ID_ NVARCHAR2 510     Y 14     NCHAR_CS 255 NO NO   255 C NO YES NO NO 14 14 NONE FAILED_ACT_ID_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB ID_ NVARCHAR2 128     N 1     NCHAR_CS 64 NO NO   64 C NO YES NO NO 1 1 NONE ID_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB REV_ NUMBER 22   0 Y 2         NO NO   0   NO YES NO NO 2 2 NONE REV_ YES NO NO  
            ACT_RU_JOB TYPE_ NVARCHAR2 510     N 3     NCHAR_CS 255 NO NO   255 C NO YES NO NO 3 3 NONE TYPE_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB LOCK_EXP_TIME_ TIMESTAMP(6) 11   6 Y 4         NO NO   0   NO YES NO NO 4 4 NONE LOCK_EXP_TIME_ YES NO NO  
            ACT_RU_JOB LOCK_OWNER_ NVARCHAR2 510     Y 5     NCHAR_CS 255 NO NO   255 C NO YES NO NO 5 5 NONE LOCK_OWNER_ YES NO NO USING_NLS_COMP
            ACT_RU_JOB EXCLUSIVE_ NUMBER 22 1 0 Y 6         NO NO   0   NO YES NO NO 6 6 NONE EXCLUSIVE_ YES NO NO  
            ACT_RU_JOB EXECUTION_ID_ NVARCHAR2 128     Y 7     NCHAR_CS 64 NO NO   64 C NO YES NO NO 7 7 NONE EXECUTION_ID_ YES NO NO USING_NLS_COMP

            The column is defined as part of the table (confer TABLE_NAME). It is also virtual (confer VIRTUAL_COLUMN).

            Note that we also already have a virtual column with name SYS_NC00027$. This is used for the index create index ACT_IDX_JOB_TENANT_ID on ACT_RU_JOB(TENANT_ID_, 0); where I think it represents the 0 constant.

            Thorben Lindhauer added a comment - - edited How does the new index work? It looks like the new index creates a virtual column in the table with a system generated name, in my example this is called SYS_NC00028$ . This is confirmed by the following result of select * from user_tab_cols where table_name = 'ACT_RU_JOB' (I have removed all-null columns from the result): TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HIDDEN_COLUMN VIRTUAL_COLUMN SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID HISTOGRAM QUALIFIED_COL_NAME USER_GENERATED DEFAULT_ON_NULL IDENTITY_COLUMN COLLATION ----------------- --------------------- -------------- ------------- ---------------- ------------ -------- ------------ --------------- ------------ ------------------- --------------------- ------------ ---------- ------------ ------------ --------- ------------- ------------- ------------- -------------- ------------------ ------------------- --------------- --------------------- -------------- --------------- --------------- --------------- ACT_RU_JOB JOB_DEF_ID_ NVARCHAR2 128     Y 22     NCHAR_CS 64 NO NO   64 C NO YES NO NO 22 22 NONE JOB_DEF_ID_ YES NO NO USING_NLS_COMP ACT_RU_JOB PRIORITY_ NUMBER 22 19 0 N 23 2 0     NO NO   0   NO YES NO NO 23 23 NONE PRIORITY_ YES NO NO   ACT_RU_JOB SEQUENCE_COUNTER_ NUMBER 22 19 0 Y 24         NO NO   0   NO YES NO NO 24 24 NONE SEQUENCE_COUNTER_ YES NO NO   ACT_RU_JOB TENANT_ID_ NVARCHAR2 128     Y 25     NCHAR_CS 64 NO NO   64 C NO YES NO NO 25 25 NONE TENANT_ID_ YES NO NO USING_NLS_COMP ACT_RU_JOB CREATE_TIME_ TIMESTAMP(6) 11   6 Y 26         NO NO   0   NO YES NO NO 26 26 NONE CREATE_TIME_ YES NO NO   ACT_RU_JOB SYS_NC00027$ NUMBER 1   0 Y   1 0     NO NO   0   NO YES YES YES   27 NONE SYS_NC00027$ NO NO NO   ACT_RU_JOB SYS_NC00028$ RAW 20     Y   29 STANDARD_HAS     NO NO   0   NO YES YES YES   28 NONE SYS_NC00028$ NO NO NO   ACT_RU_JOB DUEDATE_ TIMESTAMP(6) 11   6 Y 15         NO NO   0   NO YES NO NO 15 15 NONE DUEDATE_ YES NO NO   ACT_RU_JOB REPEAT_ NVARCHAR2 510     Y 16     NCHAR_CS 255 NO NO   255 C NO YES NO NO 16 16 NONE REPEAT_ YES NO NO USING_NLS_COMP ACT_RU_JOB REPEAT_OFFSET_ NUMBER 22 19 0 Y 17 1 0     NO NO   0   NO YES NO NO 17 17 NONE REPEAT_OFFSET_ YES NO NO   ACT_RU_JOB HANDLER_TYPE_ NVARCHAR2 510     Y 18     NCHAR_CS 255 NO NO   255 C NO YES NO NO 18 18 NONE HANDLER_TYPE_ YES NO NO USING_NLS_COMP ACT_RU_JOB HANDLER_CFG_ NVARCHAR2 4000     Y 19     NCHAR_CS 2000 NO NO   2000 C NO YES NO NO 19 19 NONE HANDLER_CFG_ YES NO NO USING_NLS_COMP ACT_RU_JOB DEPLOYMENT_ID_ NVARCHAR2 128     Y 20     NCHAR_CS 64 NO NO   64 C NO YES NO NO 20 20 NONE DEPLOYMENT_ID_ YES NO NO USING_NLS_COMP ACT_RU_JOB SUSPENSION_STATE_ NUMBER 22   0 N 21 2 1     NO NO   0   NO YES NO NO 21 21 NONE SUSPENSION_STATE_ YES NO NO   ACT_RU_JOB PROCESS_INSTANCE_ID_ NVARCHAR2 128     Y 8     NCHAR_CS 64 NO NO   64 C NO YES NO NO 8 8 NONE PROCESS_INSTANCE_ID_ YES NO NO USING_NLS_COMP ACT_RU_JOB PROCESS_DEF_ID_ NVARCHAR2 128     Y 9     NCHAR_CS 64 NO NO   64 C NO YES NO NO 9 9 NONE PROCESS_DEF_ID_ YES NO NO USING_NLS_COMP ACT_RU_JOB PROCESS_DEF_KEY_ NVARCHAR2 510     Y 10     NCHAR_CS 255 NO NO   255 C NO YES NO NO 10 10 NONE PROCESS_DEF_KEY_ YES NO NO USING_NLS_COMP ACT_RU_JOB RETRIES_ NUMBER 22   0 Y 11         NO NO   0   NO YES NO NO 11 11 NONE RETRIES_ YES NO NO   ACT_RU_JOB EXCEPTION_STACK_ID_ NVARCHAR2 128     Y 12     NCHAR_CS 64 NO NO   64 C NO YES NO NO 12 12 NONE EXCEPTION_STACK_ID_ YES NO NO USING_NLS_COMP ACT_RU_JOB EXCEPTION_MSG_ NVARCHAR2 4000     Y 13     NCHAR_CS 2000 NO NO   2000 C NO YES NO NO 13 13 NONE EXCEPTION_MSG_ YES NO NO USING_NLS_COMP ACT_RU_JOB FAILED_ACT_ID_ NVARCHAR2 510     Y 14     NCHAR_CS 255 NO NO   255 C NO YES NO NO 14 14 NONE FAILED_ACT_ID_ YES NO NO USING_NLS_COMP ACT_RU_JOB ID_ NVARCHAR2 128     N 1     NCHAR_CS 64 NO NO   64 C NO YES NO NO 1 1 NONE ID_ YES NO NO USING_NLS_COMP ACT_RU_JOB REV_ NUMBER 22   0 Y 2         NO NO   0   NO YES NO NO 2 2 NONE REV_ YES NO NO   ACT_RU_JOB TYPE_ NVARCHAR2 510     N 3     NCHAR_CS 255 NO NO   255 C NO YES NO NO 3 3 NONE TYPE_ YES NO NO USING_NLS_COMP ACT_RU_JOB LOCK_EXP_TIME_ TIMESTAMP(6) 11   6 Y 4         NO NO   0   NO YES NO NO 4 4 NONE LOCK_EXP_TIME_ YES NO NO   ACT_RU_JOB LOCK_OWNER_ NVARCHAR2 510     Y 5     NCHAR_CS 255 NO NO   255 C NO YES NO NO 5 5 NONE LOCK_OWNER_ YES NO NO USING_NLS_COMP ACT_RU_JOB EXCLUSIVE_ NUMBER 22 1 0 Y 6         NO NO   0   NO YES NO NO 6 6 NONE EXCLUSIVE_ YES NO NO   ACT_RU_JOB EXECUTION_ID_ NVARCHAR2 128     Y 7     NCHAR_CS 64 NO NO   64 C NO YES NO NO 7 7 NONE EXECUTION_ID_ YES NO NO USING_NLS_COMP The column is defined as part of the table (confer TABLE_NAME ). It is also virtual (confer VIRTUAL_COLUMN ). Note that we also already have a virtual column with name SYS_NC00027$ . This is used for the index create index ACT_IDX_JOB_TENANT_ID on ACT_RU_JOB(TENANT_ID_, 0); where I think it represents the 0 constant.

            Thorben Lindhauer added a comment -

            Updated analysis

            Oracle 11 doesn't support the function STANDARD_HASH, so we have to find an alternative. The substring-based solution seems viable, so we define the index as create index ACT_IDX_JOB_HANDLER on ACT_RU_JOB(HANDLER_TYPE_, SUBSTR(HANDLER_CFG_, 1, 1850));. 1850 is fine as the cutoff for the following reasons:

            I verified on Oracle 11 that the substring index is still used in the query. The data payload was similar as above, however I created 5,000 job entries where the handle config exceeded 1850 characters. We then get a query plan like so:

            |PLAN_TABLE_OUTPUT                                                                                   |
|----------------------------------------------------------------------------------------------------|
|Plan hash value: 1770443241                                                                         |
|                                                                                                    |
|----------------------------------------------------------------------------------------------------|
|| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time  |
|----------------------------------------------------------------------------------------------------|
||   0 | SELECT STATEMENT            |                          |     1 |  8056 |     4   (0)| 00:00:|
||*  1 |  TABLE ACCESS BY INDEX ROWID| ACT_RU_JOB               |     1 |  8056 |     4   (0)| 00:00:|
||*  2 |   INDEX RANGE SCAN          | ACT_IDX_JOB_HANDLER_ALT2 |     1 |       |     3   (0)| 00:00:|
|----------------------------------------------------------------------------------------------------|
|                                                                                                    |
|Predicate Information (identified by operation id):                                                 |
|---------------------------------------------------                                                 |
|                                                                                                    |
|   1 - filter("TENANT_ID_" IS NULL AND "HANDLER_CFG_"=U'eftrunslbpggbgfmgjimmlrzzwvwbsljmztoqbti    |
|              dkiedgumrhhygxddbonvgdunpaushzujtqqpsmlqdcuhqpmnnhjxeowskcwlgpwlyynyjpufqhdwshbchepmor|
|              ahgaehteontxwyvsgmxugyprloxkxwhrpjwxouhwrjkkpxwzzyshfrtmwdkceawewqulsdoxvievvlrlnqsdob|
|              afvialxvhxdbdlxktdfsbaqjyuenhplwnadvwoguovesmbxgkelwfrxzzzvhajruygxaaefhyjkncnqjzpjmgj|
|              cbsmgcojowjhfoiypfugsmhygbxydvtbldziqesnmttlxdloeyerlwkidqkjxikzajqvsbmbyosqqsnkzcpore|
|              ckhqfxqdmxicpxtcsaypmuoutikfeznbyahwqdnmzxqgbcrnlqezxcgwtsvkxdelxpyfahpsyowowghezpecbt|
|              momckgfjfaouinbshamywbdqsethznpldjrhnmoepswjhykadtmdubnftgkjuuujjfbvapxxtjieaorvyfktoc|
|              yyfhoiyxcvhvpfarmudixkgzgrdgylxrholpwptbasoeirpbwtqdtuyfqiomuvpkjskmvusnmrnfbwadltkrgv|
|              kzreliojlejvqzibcyearmigsmdgcexxqfjnepmwxtmxvezzygrcuuqdlvlnnmgbjtivpuyzsqxnrifrsvjwzn|
|              mvslzyyrxppldvuequknaiejnimrnxgfmpyqheiqkyidjvgqpkecbcqadecmvfwjfobkaduatsqkeladtrtoaw|
|              mjojybridvmfoqdxwwbvbdzzpenjiuqfbeucnrlwmftonknrxdxliypcobthwhoeagibipujbujpnmrmiluqtf|
|              lcpwzhazyhawlgpwmnypmwugtskhowwnxzlnurmgtvnflxmsbizmvwtfuaeoexnvhmjgxbdfilvythyuajuksq|
|              hwmjzpyjtukpdszvjyywngnhbkfmbgfqihiswweudfawismjozvbfqhjhrvavdacbsiapeqndytvyaqpkbnhwn|
|              yjwxwdwryoxeqyzstjhrrzsawcrqimschptvdnhjqllmlyisnajrdpevloofpotgeqzulhbkutecxaskcbimcw|
|              blgwfjcitoubpxfopwrslrzhxanoirewelzdkuswpcnsuchtdslosqczmukobheyzteasykfyfkfthabtzcbih|
|              wssmbszcxvumxdaftnpjoapfewmuwgvvycsvzadqvwofkwwzxhwqykvapfgxyuxvymhwdewojepyxlfbejkuqr|
|              ivbnbsohllovvbctoqsoltjgkauygiiszrmydtipuzpmsyhwpkmgvvatwwlhawzlcbtzuljjqhberqczkgebdi|
|              ajlnjqffsnlnldsgcqjefkhqzzdltlgqqmrbgiedupopmecppojvtjnaipydclvjadoslxkxgxurokfoomgmuu|
|              tdixbmmvmdnittpxbfqwgnjcttbfxwrzacpqxskaquuongwdwvfanvxmwjdyjevmfcyrmaxrzllttfgpogflsp|
|              xtgzguvwgrthbdclqikbthksvnyjflnxertaabluyzstsoicazirnxosgejmgjtkinicfofohcumwsuvmgasjb|
|              mbvxdoricddjpiotlkfpliuepvjqtkvueqjzgleokeczkddhnsxwnalcwfnwzmcgtmiwusivohpwlyldognhkg|
|              qkvewmcbamszyxflladj')                                                                |
|   2 - access("HANDLER_TYPE_"=U'tdxghbccdt' AND                                                     |
|              SUBSTR("HANDLER_CFG_",1,1850)=U'eftrunslbpggbgfmgjimmlrzzwvwbsljmztoqbtidkiedgumrhhygx|
|              paushzujtqqpsmlqdcuhqpmnnhjxeowskcwlgpwlyynyjpufqhdwshbchepmorcetnbybhccahgaehteontxwy|
|              loxkxwhrpjwxouhwrjkkpxwzzyshfrtmwdkceawewqulsdoxvievvlrlnqsdobemmhzsueaoafvialxvhxdbdl|
|              yuenhplwnadvwoguovesmbxgkelwfrxzzzvhajruygxaaefhyjkncnqjzpjmgjvmzzdxyypmcbsmgcojowjhfo|
|              gbxydvtbldziqesnmttlxdloeyerlwkidqkjxikzajqvsbmbyosqqsnkzcporejouksomdnmckhqfxqdmxicpx|
|              tikfeznbyahwqdnmzxqgbcrnlqezxcgwtsvkxdelxpyfahpsyowowghezpecbtxstcgngsxwmomckgfjfaouin|
|              sethznpldjrhnmoepswjhykadtmdubnftgkjuuujjfbvapxxtjieaorvyfktocqxpbqhgiozyyfhoiyxcvhvpf|
|              grdgylxrholpwptbasoeirpbwtqdtuyfqiomuvpkjskmvusnmrnfbwadltkrgvwywzcqlilkkzreliojlejvqz|
|              smdgcexxqfjnepmwxtmxvezzygrcuuqdlvlnnmgbjtivpuyzsqxnrifrsvjwznbcauarbtygmvslzyyrxppldv|
|              nimrnxgfmpyqheiqkyidjvgqpkecbcqadecmvfwjfobkaduatsqkeladtrtoawgpubccjfzpmjojybridvmfoq|
|              penjiuqfbeucnrlwmftonknrxdxliypcobthwhoeagibipujbujpnmrmiluqtfipgnkjzzbnlcpwzhazyhawlg|
|              tskhowwnxzlnurmgtvnflxmsbizmvwtfuaeoexnvhmjgxbdfilvythyuajuksqsrzpbzozcahwmjzpyjtukpds|
|              bkfmbgfqihiswweudfawismjozvbfqhjhrvavdacbsiapeqndytvyaqpkbnhwnzqydtraeweyjwxwdwryoxeqy|
|              wcrqimschptvdnhjqllmlyisnajrdpevloofpotgeqzulhbkutecxaskcbimcwxtdhatcbqublgwfjcitoubpx|
|              xanoirewelzdkuswpcnsuchtdslosqczmukobheyzteasykfyfkfthabtzcbihvqgkrmcxvpwssmbszcxvumxd|
|              ewmuwgvvycsvzadqvwofkwwzxhwqykvapfgxyuxvymhwdewojepyxlfbejkuqrvnhysxubmtivbnbsohllovvb|
|              kauygiiszrmydtipuzpmsyhwpkmgvvatwwlhawzlcbtzuljjqhberqczkgebdilwnyfeqibxajlnjqffsnlnld|
|              zzdltlgqqmrbgiedupopmecppojvtjnaipydclvjadoslxkxgxurokfoomgmuurzbqeiuamutdixbmmvmdnitt|
|              ttbfxwrzacpqxskaquuongwdwvfanvxmwjdyjevmfcyrmaxrzllttfgpogflspmxmspgxfnwxtgzguvwgrthbd|
|              vnyjflnxertaabluyzstsoicazirnxosgejmgjtkinicfofohcumwsuvmg')                          |

            Note that the parameter for predicate 2 is actually a substring of the actual parameter that is cut off at exactly 1850 characters.

            Thorben Lindhauer added a comment - Updated analysis Oracle 11 doesn't support the function STANDARD_HASH , so we have to find an alternative. The substring-based solution seems viable, so we define the index as create index ACT_IDX_JOB_HANDLER on ACT_RU_JOB(HANDLER_TYPE_, SUBSTR(HANDLER_CFG_, 1, 1850)); . 1850 is fine as the cutoff for the following reasons: For NVARCHAR2 fields, ORacle supports two character sets: UTF8 and AL16UTF16 (see https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/choosing-character-set.html#GUID-4E12D991-C286-4F1A-AFC6-F35040A5DE4F ) UTF8 is up to 3 bytes per character in Oracle, AL16UTF16 up to two (see https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/supporting-multilingual-databases-with-unicode.html#GUID-6B549B3B-90DE-478B-A183-553D1A018996 ) With UTF8, we can define a maximum index key size of 255 (HANDLER_TYPE_) plus 1874 (HANDLER_CFG_) characters (because (255 +1874) * 3 = 6387, which is close to the maximum key length) I verified on Oracle 11 that the substring index is still used in the query. The data payload was similar as above, however I created 5,000 job entries where the handle config exceeded 1850 characters. We then get a query plan like so: |PLAN_TABLE_OUTPUT | |----------------------------------------------------------------------------------------------------| |Plan hash value: 1770443241 | | | |----------------------------------------------------------------------------------------------------| || Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |----------------------------------------------------------------------------------------------------| || 0 | SELECT STATEMENT | | 1 | 8056 | 4 (0)| 00:00:| ||* 1 | TABLE ACCESS BY INDEX ROWID| ACT_RU_JOB | 1 | 8056 | 4 (0)| 00:00:| ||* 2 | INDEX RANGE SCAN | ACT_IDX_JOB_HANDLER_ALT2 | 1 | | 3 (0)| 00:00:| |----------------------------------------------------------------------------------------------------| | | |Predicate Information (identified by operation id): | |--------------------------------------------------- | | | | 1 - filter( "TENANT_ID_" IS NULL AND "HANDLER_CFG_" =U'eftrunslbpggbgfmgjimmlrzzwvwbsljmztoqbti | | dkiedgumrhhygxddbonvgdunpaushzujtqqpsmlqdcuhqpmnnhjxeowskcwlgpwlyynyjpufqhdwshbchepmor| | ahgaehteontxwyvsgmxugyprloxkxwhrpjwxouhwrjkkpxwzzyshfrtmwdkceawewqulsdoxvievvlrlnqsdob| | afvialxvhxdbdlxktdfsbaqjyuenhplwnadvwoguovesmbxgkelwfrxzzzvhajruygxaaefhyjkncnqjzpjmgj| | cbsmgcojowjhfoiypfugsmhygbxydvtbldziqesnmttlxdloeyerlwkidqkjxikzajqvsbmbyosqqsnkzcpore| | ckhqfxqdmxicpxtcsaypmuoutikfeznbyahwqdnmzxqgbcrnlqezxcgwtsvkxdelxpyfahpsyowowghezpecbt| | momckgfjfaouinbshamywbdqsethznpldjrhnmoepswjhykadtmdubnftgkjuuujjfbvapxxtjieaorvyfktoc| | yyfhoiyxcvhvpfarmudixkgzgrdgylxrholpwptbasoeirpbwtqdtuyfqiomuvpkjskmvusnmrnfbwadltkrgv| | kzreliojlejvqzibcyearmigsmdgcexxqfjnepmwxtmxvezzygrcuuqdlvlnnmgbjtivpuyzsqxnrifrsvjwzn| | mvslzyyrxppldvuequknaiejnimrnxgfmpyqheiqkyidjvgqpkecbcqadecmvfwjfobkaduatsqkeladtrtoaw| | mjojybridvmfoqdxwwbvbdzzpenjiuqfbeucnrlwmftonknrxdxliypcobthwhoeagibipujbujpnmrmiluqtf| | lcpwzhazyhawlgpwmnypmwugtskhowwnxzlnurmgtvnflxmsbizmvwtfuaeoexnvhmjgxbdfilvythyuajuksq| | hwmjzpyjtukpdszvjyywngnhbkfmbgfqihiswweudfawismjozvbfqhjhrvavdacbsiapeqndytvyaqpkbnhwn| | yjwxwdwryoxeqyzstjhrrzsawcrqimschptvdnhjqllmlyisnajrdpevloofpotgeqzulhbkutecxaskcbimcw| | blgwfjcitoubpxfopwrslrzhxanoirewelzdkuswpcnsuchtdslosqczmukobheyzteasykfyfkfthabtzcbih| | wssmbszcxvumxdaftnpjoapfewmuwgvvycsvzadqvwofkwwzxhwqykvapfgxyuxvymhwdewojepyxlfbejkuqr| | ivbnbsohllovvbctoqsoltjgkauygiiszrmydtipuzpmsyhwpkmgvvatwwlhawzlcbtzuljjqhberqczkgebdi| | ajlnjqffsnlnldsgcqjefkhqzzdltlgqqmrbgiedupopmecppojvtjnaipydclvjadoslxkxgxurokfoomgmuu| | tdixbmmvmdnittpxbfqwgnjcttbfxwrzacpqxskaquuongwdwvfanvxmwjdyjevmfcyrmaxrzllttfgpogflsp| | xtgzguvwgrthbdclqikbthksvnyjflnxertaabluyzstsoicazirnxosgejmgjtkinicfofohcumwsuvmgasjb| | mbvxdoricddjpiotlkfpliuepvjqtkvueqjzgleokeczkddhnsxwnalcwfnwzmcgtmiwusivohpwlyldognhkg| | qkvewmcbamszyxflladj') | | 2 - access( "HANDLER_TYPE_" =U 'tdxghbccdt' AND | | SUBSTR( "HANDLER_CFG_" ,1,1850)=U'eftrunslbpggbgfmgjimmlrzzwvwbsljmztoqbtidkiedgumrhhygx| | paushzujtqqpsmlqdcuhqpmnnhjxeowskcwlgpwlyynyjpufqhdwshbchepmorcetnbybhccahgaehteontxwy| | loxkxwhrpjwxouhwrjkkpxwzzyshfrtmwdkceawewqulsdoxvievvlrlnqsdobemmhzsueaoafvialxvhxdbdl| | yuenhplwnadvwoguovesmbxgkelwfrxzzzvhajruygxaaefhyjkncnqjzpjmgjvmzzdxyypmcbsmgcojowjhfo| | gbxydvtbldziqesnmttlxdloeyerlwkidqkjxikzajqvsbmbyosqqsnkzcporejouksomdnmckhqfxqdmxicpx| | tikfeznbyahwqdnmzxqgbcrnlqezxcgwtsvkxdelxpyfahpsyowowghezpecbtxstcgngsxwmomckgfjfaouin| | sethznpldjrhnmoepswjhykadtmdubnftgkjuuujjfbvapxxtjieaorvyfktocqxpbqhgiozyyfhoiyxcvhvpf| | grdgylxrholpwptbasoeirpbwtqdtuyfqiomuvpkjskmvusnmrnfbwadltkrgvwywzcqlilkkzreliojlejvqz| | smdgcexxqfjnepmwxtmxvezzygrcuuqdlvlnnmgbjtivpuyzsqxnrifrsvjwznbcauarbtygmvslzyyrxppldv| | nimrnxgfmpyqheiqkyidjvgqpkecbcqadecmvfwjfobkaduatsqkeladtrtoawgpubccjfzpmjojybridvmfoq| | penjiuqfbeucnrlwmftonknrxdxliypcobthwhoeagibipujbujpnmrmiluqtfipgnkjzzbnlcpwzhazyhawlg| | tskhowwnxzlnurmgtvnflxmsbizmvwtfuaeoexnvhmjgxbdfilvythyuajuksqsrzpbzozcahwmjzpyjtukpds| | bkfmbgfqihiswweudfawismjozvbfqhjhrvavdacbsiapeqndytvyaqpkbnhwnzqydtraeweyjwxwdwryoxeqy| | wcrqimschptvdnhjqllmlyisnajrdpevloofpotgeqzulhbkutecxaskcbimcwxtdhatcbqublgwfjcitoubpx| | xanoirewelzdkuswpcnsuchtdslosqczmukobheyzteasykfyfkfthabtzcbihvqgkrmcxvpwssmbszcxvumxd| | ewmuwgvvycsvzadqvwofkwwzxhwqykvapfgxyuxvymhwdewojepyxlfbejkuqrvnhysxubmtivbnbsohllovvb| | kauygiiszrmydtipuzpmsyhwpkmgvvatwwlhawzlcbtzuljjqhberqczkgebdilwnyfeqibxajlnjqffsnlnld| | zzdltlgqqmrbgiedupopmecppojvtjnaipydclvjadoslxkxgxurokfoomgmuurzbqeiuamutdixbmmvmdnitt| | ttbfxwrzacpqxskaquuongwdwvfanvxmwjdyjevmfcyrmaxrzllttfgpogflspmxmspgxfnwxtgzguvwgrthbd| | vnyjflnxertaabluyzstsoicazirnxosgejmgjtkinicfofohcumwsuvmg') | Note that the parameter for predicate 2 is actually a substring of the actual parameter that is cut off at exactly 1850 characters.

              Unassigned Unassigned
              thorben.lindhauer Thorben Lindhauer
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: