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

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

问题详细描述为,在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_ROWSLIKE操作以及基于语义的排序直接相关,下面看看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

查询metalinkOracleDoc 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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
众所周知,人工智能是当前最热门的话题之一, 计算机技术与互联网技术的快速发展更是将对人工智能的研究推向一个新的高潮。 人工智能是研究模拟和扩展人类智能的理论与方法及其应用的一门新兴技术科学。 作为人工智能核心研究领域之一的机器学习, 其研究动机是为了使计算机系统具有人的学习能力以实现人工智能。 那么, 什么是机器学习呢? 机器学习 (Machine Learning) 是对研究问题进行模型假设,利用计算机从训练数据中学习得到模型参数,并最终对数据进行预测和分析的一门学科。 机器学习的用途 机器学习是一种通用的数据处理技术,其包含了大量的学习算法。不同的学习算法在不同的行业及应用中能够表现出不同的性能和优势。目前,机器学习已成功地应用于下列领域: 互联网领域----语音识别、搜索引擎、语言翻译、垃圾邮件过滤、自然语言处理等 生物领域----基因序列分析、DNA 序列预测、蛋白质结构预测等 自动化领域----人脸识别、无人驾驶技术、图像处理、信号处理等 金融领域----证券市场分析、信用卡欺诈检测等 医学领域----疾病鉴别/诊断、流行病爆发预测等 刑侦领域----潜在犯罪识别与预测、模拟人工智能侦探等 新闻领域----新闻推荐系统等 游戏领域----游戏战略规划等 从上述所列举的应用可知,机器学习正在成为各行各业都会经常使用到的分析工具,尤其是在各领域数据量爆炸的今天,各行业都希望通过数据处理与分析手段,得到数据中有价值的信息,以便明确客户的需求和指引企业的发展。
众所周知,人工智能是当前最热门的话题之一, 计算机技术与互联网技术的快速发展更是将对人工智能的研究推向一个新的高潮。 人工智能是研究模拟和扩展人类智能的理论与方法及其应用的一门新兴技术科学。 作为人工智能核心研究领域之一的机器学习, 其研究动机是为了使计算机系统具有人的学习能力以实现人工智能。 那么, 什么是机器学习呢? 机器学习 (Machine Learning) 是对研究问题进行模型假设,利用计算机从训练数据中学习得到模型参数,并最终对数据进行预测和分析的一门学科。 机器学习的用途 机器学习是一种通用的数据处理技术,其包含了大量的学习算法。不同的学习算法在不同的行业及应用中能够表现出不同的性能和优势。目前,机器学习已成功地应用于下列领域: 互联网领域----语音识别、搜索引擎、语言翻译、垃圾邮件过滤、自然语言处理等 生物领域----基因序列分析、DNA 序列预测、蛋白质结构预测等 自动化领域----人脸识别、无人驾驶技术、图像处理、信号处理等 金融领域----证券市场分析、信用卡欺诈检测等 医学领域----疾病鉴别/诊断、流行病爆发预测等 刑侦领域----潜在犯罪识别与预测、模拟人工智能侦探等 新闻领域----新闻推荐系统等 游戏领域----游戏战略规划等 从上述所列举的应用可知,机器学习正在成为各行各业都会经常使用到的分析工具,尤其是在各领域数据量爆炸的今天,各行业都希望通过数据处理与分析手段,得到数据中有价值的信息,以便明确客户的需求和指引企业的发展。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值