Oracle 空绑定变量更改尽管有存储的大纲，但解释计划

*** 字符串太长，已截断 *** (29311)，ORA-06502: PL/SQL: 数字或值错误: 字符串缓冲区太小
你好。

问题:

当组织代码的绑定变量为空时，optimzer将选择具有组织代码列的索引，否则它将选择不具有组织代码列的索引。这似乎是倒退。即使是存储的轮廓也不会阻止这种行为。我们希望始终使用索引 (TC_TASK_I6)，而不使用此查询的组织代码。计划管理的唯一选项
标准版11g根据Oracle文档显示为顶部存储轮廓。

谢谢，维克多

环境:
Oracle 11.0.3标准版
Linux

问题SQl:

从TC_TASK中选择/* TESTCMP */ TC_TASK.* 其中
TC_TASK.ORGANIZATION_CODE = :1和TC_TASK.SHIPMENT_KEY = :2和
(TC_TASK.TASK_STATUS IN (:3，: 4，: 5，: 6 ) ) 和 (
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)


索引: 列:
TC_TASK_I6装运 _key
任务 _ 状态

TC_TASK_I32

组织代码
任务id
父母 _ 任务 _ 标识


存储的轮廓提示 :( 轮廓名称为INDEX_I6)

索引 _ i6 1 1 0 NUM_INDEX_KEYS(@ "SEL $1" "TC_TASK" @ "SEL $1" "TC_TASK _i6" 1)
INDEX_I6 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”)
索引 _ i6 1 1 0所有行
INDEX_I6 1 1 0 DB_VERSION('11.2.0.3 ')
索引 _ i6 1 1 0优化 _ 功能 _ 启用 ('11.2.0.3 ')
索引 _ i6 1 1 0忽略 _ 优化 _ 嵌入 _ 提示


横幅从v $ 版本:
Oracle数据库11g版本11.2.0.3.0-64位生产
PL/SQL版本11.2.0.3.0-生产
“核心11.2.0.3.0生产”
适用于Linux的TNS: 版本11.2.0.3.0-生产
NLSRTL版本11.2.0.3.0-生产

-
-- 表TC_TASK (SQL开发人员) 的DDL:
-

创建表 “TC_TASK”
(“TASK_KEY” CHAR(24字节) 默认 “”，
"TASK_ID" VARCHAR2(40字节) 默认 ''，
"ORGANIZATION_CODE" CHAR(24字节) 默认 “”，
"TASK_TYPE" CHAR(10字节) 默认 “”，
"ENTERPRISE_KEY" CHAR(24字节) 默认 “”，
"PARENT_TASK_ID" VARCHAR2(40字节) 默认 ''，
"IS_PARENT" CHAR(1字节) 默认 'N'，
"Prefessor_task_id" VARCHAR2(40字节) 默认 ''，
"合并任务id" VARCHAR2(40字节) 默认 ''，
"IS_SUMMARY_TASK" CHAR(1字节) 默认 “”，
"SOURCE_LOCATION_ID" VARCHAR2(40字节) 默认 ''，
"SOURCE_ZONE_ID" VARCHAR2(40字节) 默认 ''，
“SOURCE_AISLE” 编号 (9,0) 默认为0，
"TARGET_LOCATION_ID" VARCHAR2(40字节) 默认 ''，
"TARGET_ZONE_ID" VARCHAR2(40字节) 默认 ''，
“目标通道” 编号 (9,0) 默认为0，
“SOURCE_SORT_SEQUENCE” 编号 (9,0) 默认为0，
"TARGET_SORT_SEQUENCE" 编号 (9,0) 默认为0，
“任务 _ 优先级” 编号 (10,0) 默认为0，
“完成 _ 不 _ 稍后 _ 比” 日期默认系统日期，
“开始日期” 默认系统日期，
"ASSIGNED_TO_USER_ID" CHAR(50字节) 默认 “”，
"手动 _ assigned_user_id" CHAR(50字节) 默认 “”，
“ITEM_ID” 字符 (40字节) 默认 “”，
"PRODUCT_CLASS" CHAR(10字节) 默认 “”，
“测量单位” VARCHAR2(40字节) 默认 “”，
"ITEM_CLASSIFICATION1" VARCHAR2(100字节) 默认 ''，
"ITEM_CLASSIFICATION2" VARCHAR2(100字节) 默认 ''，
"ITEM_CLASSIFICATION3" VARCHAR2(100字节) 默认 ''，
"SOURCE_LPN_NO" VARCHAR2(40字节) 默认 ''，
"Source_lpn _ 类型" VARCHAR2(40字节) 默认 ''，
"目标 _ lpn _ 否" VARCHAR2(40字节) 默认 ''，
"SUGGESTED_LPN_NO" VARCHAR2(40字节)，
"目标 _ lpn _ 类型" VARCHAR2(40字节) 默认 ''，
"TAG_NUMBER" VARCHAR2(120字节) 默认 “”，
"SERIAL_NO" VARCHAR2(40字节) 默认 ''，
"分段类型" VARCHAR2(40字节) 默认 ''，
“段” VARCHAR2(40字节) 默认 “”，
“库存 _ 状态” CHAR(10字节) 默认 “”，
“数量” 编号 (14,4) 默认0，
“OVER_PICKED_QUANTITY” 数字 (14,4) 默认为0，
“RECEIPT_HEADER_KEY” 字符 (24字节) 默认 “”，
“装运日期” 日期默认系统日期，
"TASK_STATUS" CHAR(40字节) 默认 “”，
“FIFO_NO” 编号 (9,0) 默认为0，
“原始国家/地区” VARCHAR2(40字节) 默认 “”，
“设备标识” VARCHAR2(40字节) 默认 “”，
“设备 _ 位置 _ 标识” VARCHAR2(40字节) 默认 “”，
"EXCEPTION_HOLD" CHAR(1字节) 默认值 ''，
"保留 _ 原因 _ 代码" VARCHAR2(40字节) 默认 ''，
"HOLD_REASON_TEXT" VARCHAR2(254字节) 默认 “”，
"DEPENDENCY_HOLD" CHAR(1字节) 默认值 ''，
"BATCH_HOLD" CHAR(1字节) 默认 “”，
“订单 _ 否” VARCHAR2(40字节)，
"RECEIPT_NO" VARCHAR2(40字节) 默认 ''，
“文档类型” VARCHAR2(40字节) 默认 “”，
“RELEASE_NO” 编号 (5,0) 默认为0，
“PRIME_LINE_NO” 编号 (5,0) 默认为0，
“SUB_LINE_NO” 编号 (5,0) 默认为0，
“订单标题” 字符 (24字节)，
“订单 _ 行 _ 键” 字符 (24字节)，
“订单释放密钥” 字符 (24字节)，
"WAVE_NO" VARCHAR2(40字节) 默认 ''，
"SHIPMENT_NO" CHAR(40字节) 默认值 ''，
"容器 _ 否" VARCHAR2(40字节) 默认 ''，
“运输 _ 线 _ 键” 字符 (24字节) 默认 “”，
“装运容器密钥” 字符 (24字节) 默认 “”，
"SHIPMENT_KEY" CHAR(24字节) 默认 “”，
"BATCH_NO" VARCHAR2(40字节) 默认 ''，
“BATCH_KEY” CHAR(24字节) 默认 “”，
"SCAC" CHAR(24字节) 默认 “”，
"LOAD_NO" CHAR(40字节) 默认值 ''，
“BOL_NO” VARCHAR2(40字节)，
"TRAILER_NO" VARCHAR2(40字节)，
"MANIFEST_NO" VARCHAR2(40字节)，
“移动请求键” 字符 (24字节) 默认 “”，
"MOVE_REQUEST_NO" CHAR(40字节) 默认值 ''，
“移动 _ 请求 _ 行 _ 键” 字符 (24字节) 默认 “”，
"COUNT_REQUEST_KEY" CHAR(24字节) 默认 “”，
"COUNT_PROGRAM_NAME" CHAR(24字节) 默认 “”，
"COUNT_REQUEST_NO" CHAR(40字节) 默认值 ''，
“工作 _ 订单 _ 否” VARCHAR2(40字节) 默认 “”，
“工作订单密钥” 字符 (24字节)，
“PRODUCTIVITY_KEY” CHAR(24字节) 默认 “”，
“开始时间戳” 日期，
“结束时间戳” 日期，
“REFERENCE_SORT_SEQUENCE” 编号 (9,0) 默认为0，
"Held_for_inventory_短缺" CHAR(1字节) 默认 'N'，
“Inv_org_to _transfer” 字符 (24字节)，
“根 _ 任务 _ id” VARCHAR2(40字节)，
“分配 _ 否” VARCHAR2(40字节)，
"ASSIGN_STATUS" VARCHAR2(40字节)，
“LOCKID” 号 (5,0) 默认为0，
“创建日期” 默认系统日期，
“MODIFYTS” 日期默认系统日期，
"CREATEUSERID" VARCHAR2(40字节) 默认 ''，
"MODIFYUSERID" VARCHAR2(40字节) 默认 ''，
"创建progid" VARCHAR2(40字节) 默认 ''，
"MODIFYPROGID" VARCHAR2(40字节) 默认值''
) 分段立即创建
PCTFREE 10 pct二手40 INITRANS 1 MAXTRANS 255
NOCOMPRESS日志记录
存储 (初始131072下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
Pct增加0自由主义者1自由主义者组1
BUFFER_POOL默认FLASH_CACHE默认CELL_FLASH_CACHE默认)
表空间 “”;
-
-索引EXTN_TC_TASK_I2的DDL
-

在 “TC_TASK” 上创建索引 “EXTN_TC_TASK_I2” (“组织 _ 代码” 、 “is _ 摘要 _ 任务” 、 “任务 _ 状态” 、 “建议 _ lpn _ 否”)
PCTFREE 10 INITRANS 20 MAXTRANS 255计算统计
存储 (初始65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
Pct增加0自由主义者1自由主义者组1
BUFFER_POOL默认FLASH_CACHE默认CELL_FLASH_CACHE默认)
表空间 “”;
-
-索引EXTN_TC_TASK_I3的DDL
-

在 “TC_TASK” 上创建索引 “EXTN_TC_TASK_I3” (“组织 _ 代码” 、 “任务 _ 状态” 、 “任务 _ 类型” 、 “运输 _ 密钥”)
PCTFREE 10 INITRANS 2 MAXTRANS 255计算统计
存储 (初始65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
Pct增加0自由主义者1自由主义者组1
BUFFER_POOL默认FLASH_CACHE默认CELL_FLASH_CACHE默认)
表空间 “”;
-
-索引EXTN_TC_TASK_I4的DDL
-

在 “TC_TASK” (“TASK_STATUS”，“MODIFYTS”) 上创建索引 “EXTN_TC_TASK_I4”
PCTFREE 10 INITRANS 2 MAXTRANS 255计算统计
存储 (初始65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
Pct增加0自由主义者1自由主义者组1
BUFFER_POOL默认FLASH_CACHE默认CELL_FLASH_CACHE默认)
表空间 “”;
-
-索引EXTN_TC_TASK_I5的DDL
-

在 “TC_TASK” (“TASK_STATUS”，“PRODUCTIVITY_KEY”) 上创建索引 “EXTN_TC_TASK_I5”
PCTFREE 10 INITRANS 2 MAXTRANS 255计算统计
存储 (初始65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
Pct增加0自由主义者1自由主义者组1
BUFFER_POOL默认FLASH_CACHE默认CELL_FLASH_CACHE默认)
表空间 “”;
-
-索引EXTN_TC_TASK_I6的DDL
-

在 “任务” 上创建索引 “EXTN_TC_TASK_I6” (“任务 _ 状态” 、 “组织 _ 代码” 、 “目标 _ lpn_no” 、 “来源 _ lpn_no”)
PCTFREE 10 INITRANS 2 MAXTRANS 255计算统计
存储 (初始65536下一个1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
Pct增加0自由主义者1自由主义者组1
BUFFER_POOL默认FLASH_CACHE默认CELL_FLASH_CACHE默认)
表空间...

嗨，维克多，

你实际上在这里问了两个问题:
1.当组织代码的绑定变量为null时，为什么优化器会选择以organization_code开头的索引？
2.如何强制查询始终在标准版上使用EXTN_TC_TASK_I6索引？

让我首先解决您最初的问题: “当组织代码的bind变量为null时，为什么优化器选择以organization_code开头的索引？”

当优化器窥视organization_code的bind变量值并发现它为null时，它立即知道该谓词的选择性为0行。

为什么？

因为将sting与空字符串进行比较不是检查字符列是否为空的方法。由于以organization_code开头的索引中前导列的选择性为0，因此扫描索引的成本极低，因为不会访问任何行。让我告诉你我的意思。

创建表T。在其中插入两行，一行为null，一行为值为 “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.

现在，如果我查询表T其中col1等于一个bind变量，并且该bind变量的值为null，您会注意到我实际上没有返回任何行，即使表中的行之一为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

这是因为如果要检查字符列是否为null，则必须使用is NULL语法而不是col =:b。例如，

SQL> select * from t where col1 is null;

COL
---

现在让我们继续讨论你的第二个问题。“如何强制查询始终使用EXTN_TC_TASK_I6索引？”

正如您正确指出的那样，确保11.2.0.3标准版中计划稳定性的唯一选择是利用存储的大纲或在语句中使用提示。

首先，我建议您在SQL语句中使用提示来确认它将实际使用您的首选索引。

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));

一旦您确认它将使用您的首选索引，您需要为没有提示的SQL语句创建存储的大纲。

如果正确创建了存储的大纲，并且初始化参数use_stored_outlets设置为正确的存储大纲类别，则无论语句中使用的绑定变量值如何，都应使用存储的大纲。

要确认存储的大纲创建正确，可以查询数据字典表user_outline和USER_OUTLINE_HINTS。

在user_outlets中，通过检查使用的列的值来确认所讨论的大纲实际上是使用的。

SQL> select name, category, used from user_outlines;

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

在USER_OUTLINE_HINTS中，通过检查列在INDEX_RS_ASC提示中列出的顺序，检查是否在提示中指定了您实际想要的索引。

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.

最后，您可以通过检查explain计划下的notes部分或通过查询V $ SQL视图中的outline_category列来确认您的特定语句正在使用存储的大纲。

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