由于版本升级引发的SQL语句故障（续）

最新推荐文章于 2024-06-12 19:31:55 发布
最新推荐文章于 2024-06-12 19:31:55 发布
阅读量106 收藏
点赞数
文章标签： 数据库 数据结构与算法

 

在之前的文章《由于版本升级引发的SQL语句故障》一文中，笔者介绍了由于版本从9i上升到10g，引起原先报表group by语句行为差异问题和解决方法。一个朋友提出另一种策略，笔者也在这里面讨论一下。

 

1、索引分组列

 

笔者文章中介绍的方法，其实大都是通过修改SQL语句或者隐含参数的策略。一个朋友提出建议将分组列加索引，从而实现执行计划恢复到sort group by。

那位朋友提出的SQL语句案例是：select empno, count(*) from scott.emp group by empno;。

笔者刚开始想到这样的策略有一些合理之处。当加入索引之后，对分组列的检索可以放在索引对象。索引本身叶子节点是有序的，这样时候Hash Group By的需求就不是那么大。也许会出现sort group by的动作。

如果这样的策略能够成功，我们也就不需要修改隐含参数这个大动作了。Oracle中，确定任何结论一定建立在特定版本下的实验基础上。

下面进行实验。

 

2、环境准备设置

 

我们选择Oracle 10g进行实验，创建更有代表性的数据表T。

 

SQL> select * from v$version;

BANNER

------------------------

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

 

SQL> desc t;

Name           Type          Nullable Default Comments

-------------- ------------- -------- ------- --------

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

（篇幅原因，有省略……）

 

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     52677

 

--收集统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

3、Group By实验

 

我们首先看一下Oracle的默认行为。

 

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 47235625

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    22 |   132 |   167   (5)| 00:00:03 |

|   1 |  HASH GROUP BY     |      |    22 |   132 |   167   (5)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T    | 52677 |   308K|   162   (2)| 00:00:02 |

---------------------------------------------------------------------------

 

9 rows selected

 

和我们之前的结论相同，Oracle依据成本因素，选择了hash group by动作，对全表数据进行内部小堆排序。

按照思路，我们给分组列添加索引。

 

--给owner列添加索引

SQL> create index idx_t_owner on t(owner);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 47235625

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    23 |   138 |   167   (5)| 00:00:03 |

|   1 |  HASH GROUP BY     |      |    23 |   138 |   167   (5)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T    | 52677 |   308K|   162   (2)| 00:00:02 |

---------------------------------------------------------------------------

 

9 rows selected

 

问题出现了，给owner列添加索引，也没有带来sort group by。索引没有走到。强制呢？

 

SQL> explain plan for select /*+index(t)*/owner, count(*) from t group by owner;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 47235625

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    23 |   138 |   167   (5)| 00:00:03 |

|   1 |  HASH GROUP BY     |      |    23 |   138 |   167   (5)| 00:00:03 |

|   2 |   TABLE ACCESS FULL| T    | 52677 |   308K|   162   (2)| 00:00:02 |

---------------------------------------------------------------------------

 

9 rows selected

 

 

使用index hint依然没有效果。

冷静下来，这样是有问题的。空值null就是一个重点！空值是不会进入单键值索引叶子节点的，但是group by是会group空值的。

如果要让group by和index合力作用，需要让Oracle得到“背书”，也就是not null描述！

 

 

SQL> alter table t modify owner not null;

Table altered

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3139300238

--------------------------------------------------------------------------------

| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |             |    23 |   138 |    34  (18)| 00:00

|   1 |  HASH GROUP BY        |             |    23 |   138 |    34  (18)| 00:00

|   2 |   INDEX FAST FULL SCAN| IDX_T_OWNER | 52677 |   308K|    29   (4)| 00:00

--------------------------------------------------------------------------------

 

9 rows selected

 

 

Oracle得到认可，说这个列上不能有空。形成的新执行计划出现索引，而且进行的是index fast full scan。Oracle认为：既然承认这个字段不能出现空值，那么所有的列值一定会出现在叶子节点上！所以“数数叶子节点，没问题！”。

但是后面，“数”的动作，Oracle还是选择了Hash Group By。没有出现Sort Group By。

至此，笔者感觉结论已经有了：在一般情况下，如果没有额外的声明“非空、主键”，即使已经给分组列加索引，Oracle路径也不会走。即使走到Oracle索引路径，sort group by也不会出现。

 

4、sort group by nosort

 

回到那个朋友的案例，先看看那个SQL。

 

select empno, count(*) from scott.emp group by empno;

 

这个分组字段太特殊了，是主键（非空、唯一）。这个语句其实没有什么含义，完全可以被下面取代。

 

Select empno, 1 from scott.emp group by empno;

 

我们看看Oracle是怎么认为的。

 

 

SQL> explain plan for select empno, count(*) from scott.emp group by empno;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1749432681

-------------------------------------------------------------------------------

| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |        |    14 |    56 |     1   (0)| 00:00:01 |

|   1 |  SORT GROUP BY NOSORT|        |    14 |    56 |     1   (0)| 00:00:01 |

|   2 |   INDEX FULL SCAN    | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

9 rows selected

 

 

的确走了索引路径，而且我们也看到了sort group by动作。但是注意：这个sort group by后面还有一个nosort。

在很多时候，Oracle CBO是非常聪明和狡猾的。有一种篮球中的用语叫“假动作”。Oracle在这里面好像是sort group by，但是实际上，它根本就没有排序，因为它知道：不需要排序。

Empno列为主键列：非空开启了索引路径，唯一开启了group by取值为1。在CBO的世界里，统计量大于RBO规则，比统计量成本更高的是约束和声明！主键、外键、非空这些，都是CBO工作的硬约束，比计算的cost值优先级高得多！

所以，这个SQL执行计划中虽然出现了Sort Group by，但是没有意义。

 

5、结论

 

CBO的世界是非常复杂的。过去RBO 15个规则确定执行计划已经不复存在了。近年来，统计量、索引、执行计划深入人心，一些朋友对执行计划也开始认识到位。但是，数据表定义、列约束是比这些更加重要的要素！Oracle CBO越智能，越是会向“捷径”找出路。

这就要求我们，特别是开发设计人员一定要谨慎细心，描述好每个字段和索引。才能形成最好的执行计划。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-1063391/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-1063391/

ciqu9915
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
由于版本升级引发的SQL语句故障
ciqu9915的博客
12-13 152
Oracle作为目前市场占有率最高的数据库服务器产品，其发展是稳定且快速的。每一个版本的变化，都会带来很多新特性、新功能。我们在使用这些特性的同时，同样也面对一个升级和迁移的问题。 就SQL语句...
Oracle分析函数多层使用时的一个bug及其解决方案
07-08 160
前几天因为客户数据统计错误，引起强烈反应，加班加点终于把问题解决。但是最终结果却出人意料，经排查，确定并非我们代码的问题，而是Oracle的一个bug。bug号及描述：6840911 Wrongs results fr...
mysql版本导致的查询问题
渴望变强
02-22 368
数据库查询时出了个以前没见过的问题： sql语句： SELECT question.id AS 'question.id', question.company_id AS 'question.company_id', question.robot_id AS 'question.robot_id', question.category_id AS 'question.category_id...
SQL Server 常用SQL语句（收藏版）
huang714的专栏
06-09 3859
1. sqlserver查看实例级别的信息，使用SERVERPROPERTY函数 select SERVERPROPERTY ('propertyname') 2. 查看实例级别的某个参数XX的配置 select * from sys.configurations where name='XX' 3. 更改实例级别的某个参数XX的值 sp_configure 'XX','0' RECONFIGURE WITH OVERRIDE sp_configure显示或更改当前服务器的全局配置设置。
oracle常用sql语句
是时候了fighting的博客
08-19 2720
– 首先，以超级管理员的身份登录oracle sqlplus sys/bjsxt as sysdba –然后，解除对scott用户的锁 alter user scott account unlock; –那么这个用户名就能使用了。 –(默认全局数据库名orcl) 1、select ename, sal * 12 from emp; –计算年薪 2、select
SQL Server 的几个故障
cashcat的技术专栏
10-08 4402
经常维护数据库，遇到一些问题，解决后将之记录下来，方便大家查看。 1）SQL Server服务突然无法启动       问题描述：SQL Server之前一直运行没有问题，突然某天无法启动了      问题分析：该问题多半是因为更改了操作系统的登录密码造成了。所以要更新sqlserver的账户登录密码。当然，最好的方式就是就是上面的觪决办法了。 查看控制面板－>管理工具->事件查
pg大版本升级之pg_upgrade
HighGO
03-22 2876
本文主要介绍pg_upgrade这一工具，以pg12升级至pg14为例，给读者演示这一工具如何使用及使用中的注意事项。
Flink从1.7到1.12版本升级汇总
微信搜：import_bigdata，大数据领域硬核原创作者
08-25 1656
点击上方蓝色字体，选择“设为星标”回复”面试“获取更多惊喜 一 .前言最进再看官方flink提供的视频教程,发现入门版本因为时间关系都是基于1.7.x讲解的. 在实际操作中跟1....
SQLServer2008查询性能优化 2/2
03-02
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
SQLServer2008查询性能优化 1/2
03-02
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
SQLServer系统数据库恢复.pdf
02-27
SQLServer系统数据库恢复 系统数据库恢复 标签：SQL SERVER/MSSQL SERVER/数据库/DBA/故障恢复/master 概述 概述 SQL Server 维护⼀组系统级数据库（称为"系统数据库"），这些数据库对于服务器实例的运⾏⾄关重要。...
SQL.Server.2008管理员必备指南.part4.rar（4/4）
05-04
　2.3.4 升级SQL Server 2008版本 45 　2.3.5 卸载SQL Server 45 　第3章 管理外围安全、访问以及网络配置 46 　3.1 管理SQL Server的组件功能 46 　3.2 配置SQL Server服务 49 　3.2.1 管理服务配置 50 　3.2.2 ...
flink-sql所有语法详解-1.13
第一片心意的博客
08-02 6216
本文为 flink 1.13官网中 flink sql 所有语法部分内容的翻译整理。
azure不支持哪些语句 sql_排查 Azure SQL 数据库的常见连接问题 - Azure SQL Database | Microsoft Docs...
weixin_33264203的博客
12-31 1097
您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.排查 Azure SQL 数据库和 Azure SQL 托管实例的连接问题和其他问题Troubleshooting connectivity issues and other errors with Azure SQL Da...
Spring Security实现用户认证四：使用JWT与Redis实现无状态认证
最新发布
不做菜虚困的博客
06-12 773
在基本的通信流程中，我们一般采用Session去存储用户的认证状态。在Spring Security实现用户认证三中讲过，在拿到前端传输过来的用户名和密码之后，会有专门的过滤器处理这部分的需求，并且对认证成功的用户生成Token且存储在Session中。在下次发起请求时，直接从Session中取出同用户名的token进行密码哈希的比较要认证用户。对于无状态认证，则我们的认证不依赖与服务器端存储的Session的状态。所以无状态认证需要我们每次从前端传输一个包含完整认证信息的Token到服务器端进行自定义的认
jmeter性能优化之mysql监控sql慢查询语句分析
回家吃月饼的学习交流地
06-09 875
jmeter性能之sql慢查询语句分析
binlog2sql离线
07-13
这对于故障恢复、数据迁移以及数据库版本升级等场景都非常有用。 总结起来，binlog2sql 离线的含义是指在没有网络连接的情况下，手动复制和解析 MySQL 数据库的 binlog 文件，使用 binlog2sql 工具生成 SQL 语句，...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值