忽略SQL改造等价性

最新推荐文章于 2022-12-18 23:15:08 发布

Leon_Jinhai_Sun

最新推荐文章于 2022-12-18 23:15:08 发布

阅读量168

点赞数

本文链接：https://blog.csdn.net/Leon_Jinhai_Sun/article/details/103640210

版权

忽略SQL改造等价性（看似等价其实不等）

请看SQL写法不等价的相关例子

in与范围写法

drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id,object_type);
UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;
UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;
COMMIT;
set linesize 266
set pagesize 1
alter session set statistics_level=all ;


select  /*+index(t,idx_object_id)*/ * from t  where object_TYPE='TABLE'  AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |   2925 |00:00:00.03 |    1103 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |      1 |   2126 |   2925 |00:00:00.03 |    1103 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |      1 |    320 |   2925 |00:00:00.02 |     730 |
-------------------------------------------------------------------------------------------------------

select  /*+index(t,idx_object_id)*/ * from t t where object_TYPE='TABLE'  AND  OBJECT_ID IN (20,21);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  INLIST ITERATOR             |                |      1 |        |   2920 |00:00:00.01 |     563 |
|   2 |   TABLE ACCESS BY INDEX ROWID| t              |      2 |   2592 |   2920 |00:00:00.01 |     563 |
|*  3 |    INDEX RANGE SCAN          | IDX1_OBJECT_ID |      2 |      1 |   2920 |00:00:00.01 |     214 |
--------------------------------------------------------------------------------------------------------

你眼中只有快吗

---看官们，续集来这瞧瞧如下试验

drop table t purge;
create table t as select * from dba_objects;
update t set object_id =null where rownum<=2;
set autotrace off
select count(*) from t;
select count(object_id) from t;

--哎呀我的天，两个语句不等价，又如何谈性能呢，所以我们不能说必须要用COUNT(列）代替COUNT(*)，因为两者并不等价。
--记住，调优改写，要是等价改写！

看似不等，其实相等

begin
select count(*) into v_cnt from t1 ;
if v_cnt>0 
then  …A逻辑….
else 
then  …B逻辑…..
End;

我来翻译一下这段需求：
      获取t1 表的记录数，判断是否大于0，如果大于0走A逻辑，否则就走B逻辑。
       因此代码就如上所示来实现了。真正的需求是这样吗？
      其实应该是这样的：只要T1表有记录就走A逻辑，否则走B逻辑。
两者有区别吗？其实区别还是很大的，前者可是强调获取记录数，我们是不是一定要遍历整个表得出一个记录数才知道是否大于0？

真正需求的理解可以让我们这样实现，只要从T1表中成功获取到第一条记录，就可以停止检索了，表示该表有记录了，难道事实不是这样？

因此原先的SQL1 从Select count(*) from t1; 被改造为：
Select count(*) from t1 where rownum=1; 

begin
select count(*) into v_cnt from t1 where rownum=1;
if v_cnt=1 
then  …A逻辑….
else 
then  …B逻辑…..
End;