FIRST_ROWS优化模式语言排序模糊匹配问题

于 2012-03-09 16:16:00 发布
阅读量37 收藏
点赞数
原文链接：https://my.oschina.net/chen106106/blog/47152
版权

标题比较长，不过只有这样才能把问题描述清楚。

问题详细描述为，在FIRST_ROWS优化模式下，将会话排序和比较方式设置为语义模式，即忽略大小写模式，对字段进行LIKE模糊查询，可能导致错误的结果。

关于大小写不敏感的查询的详细描述，可以参考：http://yangtingkun.itpub.net/post/468/460324


下面直接看问题的现象：

SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE INDEX IND_T1_NAME ON T1(NAME);

索引已创建。

SQL> INSERT INTO T1 SELECT ROWNUM, CHR(64 + ROWNUM)
2 FROM ALL_OBJECTS WHERE ROWNUM <= 26;

已创建26行。

SQL> COMMIT;

提交完成。

SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC;

会话已更改。

SQL> ALTER SESSION SET NLS_SORT = BINARY_CI;

会话已更改。

SQL> SELECT * FROM T1 WHERE NAME LIKE 'a%';

ID NAME
---------- ------------------------------
1 A

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE 'a%';

未选定行

只要修改上面提到的关键点中的任意一个，就不会产生这个错误的现象：

SQL> SELECT /*+ ALL_ROWS */ * FROM T1 WHERE NAME LIKE 'a%';

ID NAME
---------- ------------------------------
1 A

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME = 'a';

ID NAME
---------- ------------------------------
1 A

SQL> ALTER SESSION SET NLS_SORT = BINARY;

会话已更改。

SQL> ALTER SESSION SET NLS_COMP = BINARY;

会话已更改。

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE 'A%';

ID NAME
---------- ------------------------------
1 A

SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC;

会话已更改。

SQL> ALTER SESSION SET NLS_SORT = BINARY_CI;

会话已更改。

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE 'A%';

ID NAME
---------- ------------------------------
1 A

SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE 'a';

未选定行

通过上面的几个查询可以看到，问题和FIRST_ROWS，LIKE操作以及基于语义的排序直接相关，下面看看Oracle在异常情况下采用了何种执行计划：

SQL> SET AUTOT ON EXP
SQL> SELECT /*+ FIRST_ROWS */ * FROM T1 WHERE NAME LIKE 'a';

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3350237141

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1 | 30 | 3 (34)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| SYS_C006622 | 1 | 30 | 33 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME" LIKE 'a')
2 - access(ROWID=ROWID)
3 - access("NAME" LIKE 'a')

Note
-----
- dynamic sampling used for this statement

由于索引中并不包含语义查询的结果，因此Oracle这里必须访问表才能得到最终的结果，因此这个执行计划是错误的：

SQL> SELECT * FROM T1 WHERE NAME = 'a';

ID NAME
---------- ------------------------------
1 A

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100')
)

Note
-----
- dynamic sampling used for this statement

SQL> SELECT /*+ INDEX(T1) */ * FROM T1 WHERE NAME = 'a';

ID NAME
---------- ------------------------------
1 A

执行计划
----------------------------------------------------------
Plan hash value: 159298173

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 827 (1)| 00:00:10 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 30 | 827 (1)| 00:00:10 |
| 2 | INDEX FULL SCAN | SYS_C006622 | 26 | | 26 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') )

Note
-----
- dynamic sampling used for this statement

上面的两个执行计划已经说明了问题的关键，Oracle对于语义的排序无法通过索引获取，必须要访问表或者相应的函数索引，详细描述可以参考文章开头部分给出的链接。

而采用了FIRST_ROWS优化模式后，当操作为LIKE时，Oracle优化器选择了错误的执行计划进行了优化，采用索引的范围扫描代替了表，从而引发了错误：

SQL> SELECT /*+ INDEX_JOIN(T1 IND_T1_NAME SYS_C006622) */ *
2 FROM T1
3 WHERE NAME LIKE 'a';

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3350237141

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1 | 30 | 3 (34)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| SYS_C006622 | 1 | 30 | 33 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME" LIKE 'a')
2 - access(ROWID=ROWID)
3 - access("NAME" LIKE 'a')

Note
-----
- dynamic sampling used for this statement

现在没有使用FIRST_ROWS，而采用HINT也达到了相同的效果。而解决这个问题的方法就是通过HINT来避免索引范围扫描的发生。

SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

会话已更改。

SQL> SELECT * FROM T1 WHERE NAME LIKE 'a';

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3350237141

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 35 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 30 | 35 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IND_T1_NAME | 1 | 30 | 3 (34)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| SYS_C006622 | 1 | 30 | 33 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME" LIKE 'a')
2 - access(ROWID=ROWID)
3 - access("NAME" LIKE 'a')

Note
-----
- dynamic sampling used for this statement

SQL> SELECT /*+ FULL(T1) */ * FROM T1 WHERE NAME LIKE 'a';

ID NAME
---------- ------------------------------
1 A

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME" LIKE 'a')

Note
-----
- dynamic sampling used for this statement

SQL> SELECT /*+ NO_INDEX(T1) */ * FROM T1 WHERE NAME LIKE 'a';

ID NAME
---------- ------------------------------
1 A

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME" LIKE 'a')

Note
-----
- dynamic sampling used for this statement

查询metalink，Oracle在Doc ID: Note:5252496.8明确说明了这个bug，这个bug会在Oracle10.2.0.4和11.1.0.6中被Fixed。

 

 
原文链接： http://blog.csdn.net/newhappy2008/article/details/2316044

转载于:https://my.oschina.net/chen106106/blog/47152

chenqunan3231
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PHP中mysqli_affected_rows作用行数返回值分析
10-25
主要介绍了PHP中mysqli_affected_rows作用行数返回值,实例分析了普通模式与oop模式的用法,具有一定的参考借鉴价值,需要的朋友可以参考下
mysql_num_rows VS COUNT 效率问题分析
12-19
在SQL查询中，统计表中的记录数是常见的需求，MySQL提供了两种主要的方法：`mysql_num_rows` 和 `COUNT(*)`。然而，这两种方法在效率上有显著的差异，尤其是在处理大量数据时。 `mysql_num_rows` 是MySQL的一个函数...
优化器模式 - FIRST_ROWS 与 ALL_ROWS
程序人生
07-01 2422
What circumstances we use ALL_ROWS and what circumstances we use FIRST_ROWS optimizer mode? This article is written in oracle9i.First_rows attempts to optimize the query to get the very first row back
隐式数据类型转换 & TABLE ACCESS FULL
lwei_998的专栏
03-21 4411
<br />最近一个新功能上线后，发现某个SQL本应该走索引，但实际走了全表扫描。<br /> <br />罪魁祸首是：隐式类型转换。虽然这个问题已经强调过很多次了，但难免还会有漏网之鱼。<br /> <br /> <br />下面模拟一下跟踪过程：<br />SQL> drop table t purge;<br />表已删除。<br />SQL> create table t (object_id varchar2(10) );<br />表已创建。<br />SQL> insert into t (s
PHP+MySQL使用mysql_num_rows实现模糊查询图书信息功能
10-18
主要介绍了PHP+MySQL使用mysql_num_rows实现模糊查询图书信息功能,涉及php使用mysql的like查询语句进行模糊查询以及mysql_num_rows进行结构统计的相关操作技巧,需要的朋友可以参考下
001_99rows_拓扑优化_结构拓扑优化_
10-04
总的来说，"001_99rows_拓扑优化_结构拓扑优化_"资料提供了一个学习和实践拓扑优化的好起点，通过分析和运行"001_99rows.m"，不仅可以掌握基本的拓扑优化概念，还能熟悉MATLAB在该领域的应用，为更复杂的问题解决...
思维进化算法优化BP神经网络——非线性函数拟合.rar
07-26
神经网络的matlab案例，本案例介绍如下： 技术深度：案例详细介绍了如何使用MATLAB进行深度学习模型的构建、训练和测试。 实际应用：通过具体的图像识别任务，展示模型的实际应用效果，让你直观感受神经网络的强大能力。 代码解析：提供完整的MATLAB代码，并对关键部分进行详细注释，帮助你理解每一步的工作原理。 优化策略：探讨不同的训练策略和参数调整方法，优化模型性能。
穿山甲水平井导向系统用户手册
07-26
国内一款高水平的水平井地质导向系统，双向拟合，结合三维地质模型进行三维导向
基于YOWOv2的视频动作检测python源码+模型+训练源码+项目说明（使用UCF24数据集）.zip
07-26
【1】项目代码完整且功能都验证ok，确保稳定可靠运行后才上传。欢迎下载使用！在使用过程中，如有问题或建议，请及时私信沟通，帮助解答。 【2】项目主要针对各个计算机相关专业，包括计科、信息安全、数据科学与大数据技术、人工智能、通信、物联网等领域的在校学生、专业教师或企业员工使用。 【3】项目具有较高的学习借鉴价值，不仅适用于小白学习入门进阶。也可作为毕设项目、课程设计、大作业、初期项目立项演示等。 【4】如果基础还行，或热爱钻研，可基于此项目进行二次开发，DIY其他不同功能，欢迎交流学习。 【注意】 项目下载解压后，项目名字和项目路径不要用中文，否则可能会出现解析不了的错误，建议解压重命名为英文名字后再运行！有问题私信沟通，祝顺利！ 基于YOWOv2的视频动作检测python源码+模型+训练源码+项目说明（使用UCF24数据集）.zip基于YOWOv2的视频动作检测python源码+模型+训练源码+项目说明（使用UCF24数据集）.zip基于YOWOv2的视频动作检测python源码+模型+训练源码+项目说明（使用UCF24数据集）.zip基于YOWOv2的视频动作检测python
【SCI1区】Matlab实现三角测量拓扑聚合优化器TTAO-Transformer-GRU故障诊断算法研究.rar
07-26
1.版本：matlab2014/2019a/2021a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
从个人角度看什么是加密算法
07-26
个人文章，有需要的下载：从个人角度看什么是加密算法
ADS1293 stm32 驱动源码（调用HAL库）
最新发布
07-26
STM32单片机 调用HAL库配置ADS1293， 读取 ADS1293寄存器和ADC数据的驱动代码
c语言基础测试，学习一周测试题
07-26
适合学习一周c语言，查缺补漏，可弥补自己知识上的欠缺。
matlab_基于四种风速，随机风，基本风，阵风，渐变风的组合风速预测_rezip1.zip
07-26
在风能领域，准确预测风速对于风电场的运行与管理至关重要。Matlab作为一个强大的数学计算和数据分析平台，被广泛应用于风速预测模型的构建。本文将深入探讨基于四种风速——随机风、基本风、阵风和渐变风的组合风速预测技术。 我们来理解这四种风速类型： 1. **随机风**：随机风是指风速呈现出随机性的变化，通常由大气湍流引起。在建模中，通常通过统计方法如高斯分布或Weibull分布来模拟这种不确定性。 2. **基本风**：基本风速是指在无特定扰动条件下的平均风速，它是长期观测结果的平均值，通常用于结构设计和风能评估。 3. **阵风**：阵风是短时间内风速显著增强的现象，对建筑物和风力发电机造成的主要威胁之一。阵风的预测涉及到风的脉动特性分析。 4. **渐变风**：渐变风是指风速随时间和空间逐渐变化的过程，常见于风向转变或地形影响下的风场变化。 在Matlab中，利用这四种风速类型进行组合预测，可以提高预测的准确性。预测模型可能包括以下几个步骤： 1. **数据收集与预处理**：收集历史风速数据，包括随机风、基本风、阵风和渐变风的数据，进行异常值检测、缺失值填充以及数据标准化。 2. **特征工程**：提取风速变化的相关特征，如平均值、标准差、极值、频率分布等，这些特征可能对预测有重要影响。 3. **模型选择**：可以选择多种预测模型，如时间序列分析（ARIMA、状态空间模型等）、机器学习算法（线性回归、决策树、支持向量机、神经网络等）或深度学习模型（LSTM、GRU等）。 4. **模型训练**：利用历史数据训练选定的模型，调整模型参数以优化性能，例如通过交叉验证来避免过拟合。 5. **模型验证与评估**：使用独立的测试集验证模型预测效果，常见的评估指标有均方误差（MSE）、平均绝对误差（MAE）和决定系数（R²）。 6. **组合预测**：结合四种风速的不同模型预测结果，可以采用加权平均、集成学习（如bagging、boosting）等方式，以提升整体预测精度。 7. **实时更新与动态调整**：实际应用中，模型需要不断接收新的风速数据并进行在线更新，以适应风场环境的变化。 通过以上步骤，可以构建一个综合考虑各种风速特性的预测系统，这对于风电场的功率输出预测、风电设备的维护计划以及电网调度都具有重要价值。然而，需要注意的是，每个风场的地理环境、气候条件和设备状况都有所不同，因此模型的建立应根据实际情况进行定制和优
信道编码大作业，基于matlab对比其介绍了卷积码、Turbo码和LDPC码的误码率性能（仿真生成在同一张图中），含源码
07-26
主要适用于在校本科生、研究生毕业设计或期末大作业，基于蒙特卡洛仿真方法论，介绍了卷积码、Turbo码和LDPC码，以相同的码率仿真了3种编码，并对比了其误码率性能（仿真生成在同一张图中），其中ber_compare.m 实现的是作图功能 其他三个文件夹里面的程序是卷积码、turbo码、LDPC码3种编码方式误码率仿真程序 请先运行3个文件夹中的程序，然后再运行ber_compare.m，即可得到图像。 word文档中是实验报告。
基于C#的权限管理及工作流系统（各类数据库SQL适配）.zip
07-26
基于C#的权限管理及工作流系统（各类数据库SQL适配）.zip
【创新发文无忧】Matlab实现斑马优化算法ZOA-Kmean-Transformer-GRU故障诊断算法研究.rar
07-26
1.版本：matlab2014/2019a/2021a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 5.作者介绍：某大厂资深算法工程师，从事Matlab算法仿真工作10年；擅长智能优化算法、神经网络预测、信号处理、元胞自动机等多种领域的算法仿真实验，更多仿真源码、数据集定制私信+。 替换数据可以直接使用，注释清楚，适合新手
oracle的分页查询优化器钟怎么使用 ROWS 或 FIRST_ROWS(n) 提示来指定优化器返回行数。
05-30
在 Oracle 中，可以通过使用 ROWS 或 FIRST_ROWS(n) 提示来指定优化器返回行数，以优化分页查询的性能。 以下是使用 ROWS 提示的示例： ```sql SELECT /*+ ROWS 10 */ * FROM table_name WHERE column_name >= ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值