Skip to Main Content
  • Questions
  • NULL BIND VARIABLE CHANGES EXPLAIN PLAN IN SPITE OF STORED OUTLINE

Breadcrumb

May 4th

Question and Answer

Maria Colgan

Thanks for the question, Victor.

Asked: April 23, 2017 - 10:36 pm UTC

Last updated: May 15, 2017 - 5:54 pm UTC

Version: 11.2.3 Standard Edition

Viewed 1000+ times

You Asked

*** string too long, truncated *** (29311), ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Hello.

Problem:

When the bind variable for the organization code is null the optimzer will chose the index with the organization code column otherwise it will choose the index without the organization code column. This seems backwards. Even a stored outline won't stop this behaviour. We would like to always use the index (TC_TASK_I6) without the organization code for this one query. The only option for plan management under
standard Edition 11g appears top be Stored Outline according to the Oracle Documentation.

Thanks, Victor

Enviroment:
Oracle 11.0.3 Standard Edition
Linux

Problem SQl:

SELECT /*TESTCMP*/ TC_TASK.* FROM TC_TASK TC_TASK WHERE
TC_TASK.ORGANIZATION_CODE = :1 AND TC_TASK.SHIPMENT_KEY = :2 AND
( TC_TASK.TASK_STATUS IN ( :3 , :4 , :5 , :6 ) ) AND (
TC_TASK.TASK_TYPE IN ( :7 , :8 , :9 , :10 , :11 , :12 , :13 ,
:14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 ,
:24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 ,
:34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 ,
:44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 ,
:54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 ,
:64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 ,
:74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 ,
:84 , :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 ,
:94 , :95 , :96 , :97 , :98 , :99 , :100 , :101 ) )


Indexes: Columns:
TC_TASK_I6 SHIPMENT_KEY
TASK_STATUS

TC_TASK_I32

ORGANIZATION_CODE
TASK_ID
PARENT_TASK_ID


Stored Outline Hints: (outline name is INDEX_I6)

INDEX_I6 1 1 0 NUM_INDEX_KEYS(@"SEL$1" "TC_TASK"@"SEL$1" "TC_TASK_I6" 1)
INDEX_I6 1 1 1 INDEX_RS_ASC(@"SEL$1" "TC_TASK"@"SEL$1" ("TC_TASK"."SHIPMENT_KEY" "TC_TASK"."TASK_STATUS"))
INDEX_I6 1 1 0 OUTLINE_LEAF(@"SEL$1")
INDEX_I6 1 1 0 ALL_ROWS
INDEX_I6 1 1 0 DB_VERSION('11.2.0.3')
INDEX_I6 1 1 0 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
INDEX_I6 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS


Banner from v$version :
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

--------------------------------------------------------
-- DDL for Table TC_TASK (SQL DEVELOPER) :
--------------------------------------------------------

CREATE TABLE "TC_TASK"
( "TASK_KEY" CHAR(24 BYTE) DEFAULT ' ',
"TASK_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"ORGANIZATION_CODE" CHAR(24 BYTE) DEFAULT ' ',
"TASK_TYPE" CHAR(10 BYTE) DEFAULT ' ',
"ENTERPRISE_KEY" CHAR(24 BYTE) DEFAULT ' ',
"PARENT_TASK_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"IS_PARENT" CHAR(1 BYTE) DEFAULT 'N',
"PREDECESSOR_TASK_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"CONSOLIDATED_TASK_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"IS_SUMMARY_TASK" CHAR(1 BYTE) DEFAULT ' ',
"SOURCE_LOCATION_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"SOURCE_ZONE_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"SOURCE_AISLE" NUMBER(9,0) DEFAULT 0,
"TARGET_LOCATION_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"TARGET_ZONE_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"TARGET_AISLE" NUMBER(9,0) DEFAULT 0,
"SOURCE_SORT_SEQUENCE" NUMBER(9,0) DEFAULT 0,
"TARGET_SORT_SEQUENCE" NUMBER(9,0) DEFAULT 0,
"TASK_PRIORITY" NUMBER(10,0) DEFAULT 0,
"FINISH_NO_LATER_THAN" DATE DEFAULT sysdate,
"START_NO_EARLIER_THAN" DATE DEFAULT sysdate,
"ASSIGNED_TO_USER_ID" CHAR(50 BYTE) DEFAULT ' ',
"MANUALLY_ASSIGNED_USER_ID" CHAR(50 BYTE) DEFAULT ' ',
"ITEM_ID" CHAR(40 BYTE) DEFAULT ' ',
"PRODUCT_CLASS" CHAR(10 BYTE) DEFAULT ' ',
"UNIT_OF_MEASURE" VARCHAR2(40 BYTE) DEFAULT ' ',
"ITEM_CLASSIFICATION1" VARCHAR2(100 BYTE) DEFAULT ' ',
"ITEM_CLASSIFICATION2" VARCHAR2(100 BYTE) DEFAULT ' ',
"ITEM_CLASSIFICATION3" VARCHAR2(100 BYTE) DEFAULT ' ',
"SOURCE_LPN_NO" VARCHAR2(40 BYTE) DEFAULT ' ',
"SOURCE_LPN_TYPE" VARCHAR2(40 BYTE) DEFAULT ' ',
"TARGET_LPN_NO" VARCHAR2(40 BYTE) DEFAULT ' ',
"SUGGESTED_LPN_NO" VARCHAR2(40 BYTE),
"TARGET_LPN_TYPE" VARCHAR2(40 BYTE) DEFAULT ' ',
"TAG_NUMBER" VARCHAR2(120 BYTE) DEFAULT ' ',
"SERIAL_NO" VARCHAR2(40 BYTE) DEFAULT ' ',
"SEGMENT_TYPE" VARCHAR2(40 BYTE) DEFAULT ' ',
"SEGMENT" VARCHAR2(40 BYTE) DEFAULT ' ',
"INVENTORY_STATUS" CHAR(10 BYTE) DEFAULT ' ',
"QUANTITY" NUMBER(14,4) DEFAULT 0,
"OVER_PICKED_QUANTITY" NUMBER(14,4) DEFAULT 0,
"RECEIPT_HEADER_KEY" CHAR(24 BYTE) DEFAULT ' ',
"SHIP_BY_DATE" DATE DEFAULT sysdate,
"TASK_STATUS" CHAR(40 BYTE) DEFAULT ' ',
"FIFO_NO" NUMBER(9,0) DEFAULT 0,
"COUNTRY_OF_ORIGIN" VARCHAR2(40 BYTE) DEFAULT ' ',
"EQUIPMENT_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"EQUIPMENT_LOCATION_ID" VARCHAR2(40 BYTE) DEFAULT ' ',
"EXCEPTION_HOLD" CHAR(1 BYTE) DEFAULT ' ',
"HOLD_REASON_CODE" VARCHAR2(40 BYTE) DEFAULT ' ',
"HOLD_REASON_TEXT" VARCHAR2(254 BYTE) DEFAULT ' ',
"DEPENDENCY_HOLD" CHAR(1 BYTE) DEFAULT ' ',
"BATCH_HOLD" CHAR(1 BYTE) DEFAULT ' ',
"ORDER_NO" VARCHAR2(40 BYTE),
"RECEIPT_NO" VARCHAR2(40 BYTE) DEFAULT ' ',
"DOCUMENT_TYPE" VARCHAR2(40 BYTE) DEFAULT ' ',
"RELEASE_NO" NUMBER(5,0) DEFAULT 0,
"PRIME_LINE_NO" NUMBER(5,0) DEFAULT 0,
"SUB_LINE_NO" NUMBER(5,0) DEFAULT 0,
"ORDER_HEADER_KEY" CHAR(24 BYTE),
"ORDER_LINE_KEY" CHAR(24 BYTE),
"ORDER_RELEASE_KEY" CHAR(24 BYTE),
"WAVE_NO" VARCHAR2(40 BYTE) DEFAULT ' ',
"SHIPMENT_NO" CHAR(40 BYTE) DEFAULT ' ',
"CONTAINER_NO" VARCHAR2(40 BYTE) DEFAULT ' ',
"SHIPMENT_LINE_KEY" CHAR(24 BYTE) DEFAULT ' ',
"SHIPMENT_CONTAINER_KEY" CHAR(24 BYTE) DEFAULT ' ',
"SHIPMENT_KEY" CHAR(24 BYTE) DEFAULT ' ',
"BATCH_NO" VARCHAR2(40 BYTE) DEFAULT ' ',
"BATCH_KEY" CHAR(24 BYTE) DEFAULT ' ',
"SCAC" CHAR(24 BYTE) DEFAULT ' ',
"LOAD_NO" CHAR(40 BYTE) DEFAULT ' ',
"BOL_NO" VARCHAR2(40 BYTE),
"TRAILER_NO" VARCHAR2(40 BYTE),
"MANIFEST_NO" VARCHAR2(40 BYTE),
"MOVE_REQUEST_KEY" CHAR(24 BYTE) DEFAULT ' ',
"MOVE_REQUEST_NO" CHAR(40 BYTE) DEFAULT ' ',
"MOVE_REQUEST_LINE_KEY" CHAR(24 BYTE) DEFAULT ' ',
"COUNT_REQUEST_KEY" CHAR(24 BYTE) DEFAULT ' ',
"COUNT_PROGRAM_NAME" CHAR(24 BYTE) DEFAULT ' ',
"COUNT_REQUEST_NO" CHAR(40 BYTE) DEFAULT ' ',
"WORK_ORDER_NO" VARCHAR2(40 BYTE) DEFAULT ' ',
"WORK_ORDER_KEY" CHAR(24 BYTE),
"PRODUCTIVITY_KEY" CHAR(24 BYTE) DEFAULT ' ',
"START_TIME_STAMP" DATE,
"END_TIME_STAMP" DATE,
"REFERENCE_SORT_SEQUENCE" NUMBER(9,0) DEFAULT 0,
"HELD_FOR_INVENTORY_SHORTAGE" CHAR(1 BYTE) DEFAULT 'N',
"INV_ORG_TO_TRANSFER" CHAR(24 BYTE),
"ROOT_TASK_ID" VARCHAR2(40 BYTE),
"ASSIGNMENT_NO" VARCHAR2(40 BYTE),
"ASSIGN_STATUS" VARCHAR2(40 BYTE),
"LOCKID" NUMBER(5,0) DEFAULT 0,
"CREATETS" DATE DEFAULT sysdate,
"MODIFYTS" DATE DEFAULT sysdate,
"CREATEUSERID" VARCHAR2(40 BYTE) DEFAULT ' ',
"MODIFYUSERID" VARCHAR2(40 BYTE) DEFAULT ' ',
"CREATEPROGID" VARCHAR2(40 BYTE) DEFAULT ' ',
"MODIFYPROGID" VARCHAR2(40 BYTE) DEFAULT ' '
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "" ;
--------------------------------------------------------
-- DDL for Index EXTN_TC_TASK_I2
--------------------------------------------------------

CREATE INDEX "EXTN_TC_TASK_I2" ON "TC_TASK" ("ORGANIZATION_CODE", "IS_SUMMARY_TASK", "TASK_STATUS", "SUGGESTED_LPN_NO")
PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "" ;
--------------------------------------------------------
-- DDL for Index EXTN_TC_TASK_I3
--------------------------------------------------------

CREATE INDEX "EXTN_TC_TASK_I3" ON "TC_TASK" ("ORGANIZATION_CODE", "TASK_STATUS", "TASK_TYPE", "SHIPMENT_KEY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "" ;
--------------------------------------------------------
-- DDL for Index EXTN_TC_TASK_I4
--------------------------------------------------------

CREATE INDEX "EXTN_TC_TASK_I4" ON "TC_TASK" ("TASK_STATUS", "MODIFYTS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "" ;
--------------------------------------------------------
-- DDL for Index EXTN_TC_TASK_I5
--------------------------------------------------------

CREATE INDEX "EXTN_TC_TASK_I5" ON "TC_TASK" ("TASK_STATUS", "PRODUCTIVITY_KEY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "" ;
--------------------------------------------------------
-- DDL for Index EXTN_TC_TASK_I6
--------------------------------------------------------

CREATE INDEX "EXTN_TC_TASK_I6" ON "TC_TASK" ("TASK_STATUS", "ORGANIZATION_CODE", "TARGET_LPN_NO", "SOURCE_LPN_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE ...

and we said...

Hi Victor,

You actually asked two questions here:
1. Why does the Optimizer pick the index starting with organization_code, when the bind variable for the organization code is null?
2. How do you force the query to always use the EXTN_TC_TASK_I6 index on Standard Edition?

Let me begin by addressing your initial question “why does the Optimizer pick the index starting with organization_code, when the bind variable for the organization code is null?”

When the optimizer peeks the bind variable value for organization_code and finds it is null, it instantly knows that the selectivity of that predicate is 0 rows.

Why?

Because comparing a sting to a null string is not way to check if a character column is null. Since the selectivity of the leading column in the index starting with organization_code is 0, the cost to scan the index is extremely low since no rows will be accessed. Let me show you what I mean.

Create a table T. Insert two rows into it, one, which is null, and one, which has the value ‘a’.
SQL> create table t (col1 varchar2(3));

Table created.

SQL> insert into t values(null);

1 row created.

SQL> insert into t values('a');

1 row created.

SQL> commit;
                      
Commit complete.


Now if I query the table T where col1 is equal to a bind variable and the value of that bind variable is null, you will notice I actually get no rows returned, even though one of the rows in the table is null.

SQL> variable b varchar2(3);
SQL> exec :b :=null;

PL/SQL procedure successfully completed.

SQL> select * from t where col1 = :b;

no rows selected


That’s because if you want to check if a character column is null you must use the IS NULL syntax and not col=:b. For example,
SQL> select * from t where col1 is null;

COL
---


Let’s now move on to your second question. “How do you force the query to always use the EXTN_TC_TASK_I6 index?”

As you correctly pointed out, your only option to guarantee plan stability in 11.2.0.3 standard edition is to take advantage of a stored outline or to use a hint in the statement.

First I recommend you use a hint in the SQL statement to confirm it will actual use your preferred index.

SELECT /*+ INDEX_RS_ASC(TC_TASK EXTN_TC_TASK_I6) */ TC_TASK.*
FROM      TC_TASK TC_TASK
 WHERE TC_TASK.ORGANIZATION_CODE = :1
 AND       TC_TASK.SHIPMENT_KEY = :2
 AND      ( TC_TASK.TASK_STATUS IN ( :3 , :4 , :5 , :6 ) )
 AND      ( TC_TASK.TASK_TYPE IN ( :7 , :8 , ……….:101));


Once you confirm it will use your preferred index, you need to create the stored outline for the SQL statement without the hint.

If the stored outline is created correctly and the initialization parameter USE_STORED_OUTLINES is set to the correct stored outline category, then the stored outline should be used regardless of the bind variable value used in the statement.

To confirm the stored outline is created correctly you can query the data dictionary tables USER_OUTLINES and USER_OUTLINE_HINTS.

In USER_OUTLINES confirm that the outline in question is actually used by checking the value of the USED column.

SQL> select name, category, used from user_outlines;

NAME      CATEGORY   USED
-------------------------------------------------  -------------------------------- ------------------------
SYS_OUTLINE_17051217475568301 DEFAULT    USED


In USER_OUTLINE_HINTS check that the index you actually want is specified in the hints by examining the order in which the columns are listed in the INDEX_RS_ASC hint.

SQL> select node, stage, Join_pos, hint from user_outline_hints where name='SYS_OUTLINE_17051217475568301'

NODE STAGE  JOIN_POS   HINT
---- ----- ---------- --------------------------
1  1  0  INDEX_RS_ASC(@"SEL$1"   
"TC_TASK"@"SEL$1" ("TC_TASK"."TASK_STATUS" "TC_TASK"."ORGANIZATION_CODE" "TC_TASK"."TARGET_LPN_NO" "TC_TASK"."SOURCE_LPN_NO"))
1  1      0     OUTLINE_LEAF(@"SEL$1")
1  1      0     ALL_ROWS
1  1      0     DB_VERSION('12.2.0.2')
1  1      0     OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
1  1      0     IGNORE_OPTIM_EMBEDDED_HINTS
6 rows selected.


Finally you can confirm that your particular statement is using the stored outline by checking the notes section under the explain plan or by querying the outline_category column in the V$SQL view.


SQL> select sql_text, outline_category
 From v$SQL
 Where sql_text like ‘SELECT TC_TASK.* %’;



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library