where条件顺序不同、性能不同示例探讨
2022-11-12 09:44:55
内容摘要
这篇文章主要为大家详细介绍了where条件顺序不同、性能不同示例探讨，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！昨天在书上看到SQL语
文章正文
这篇文章主要为大家详细介绍了where条件顺序不同、性能不同示例探讨，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
昨天在书上看到SQL语句优化时，where条件顺序不同，性能不同，这个建议在Oracle11G版本还合适吗？方式1优于方式2？方式1：代码如下:
select a.*
from students s,
class c
where
s.id = c.id
s.id = 'xxxxxxxx'
方式2：
代码如下:
select a.*
from students s,
class c
where
s.id = 'xxxxxxxx'
s.id = c.id
10g中测试结果证明是一样的。Microsoft Windows [版本 5.2.3790](C) 版权所有 1985-2003 Microsoft Corp.C:\Documents and Settings\Administrator>sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 星期六 5月 11 17:48:55 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> alter system flush shared_pool;系统已更改。SQL> alter system flush buffer_cache;系统已更改。SQL> set autotrace on;SQL> select *2 from COUNTRIES c,3 REGIONS r4 where c.REGION_ID=r.REGION_ID and c.REGION_ID='4';REGIONS r*第 3 行出现错误:ORA-00942: 表或视图不存在SQL> select *2 from hr.COUNTRIES c,3 hr. REGIONS r4 where c.REGION_ID=r.REGION_ID and c.REGION_ID='4';CO COUNTRY_NAME REGION_ID REGION_ID-- ---------------------------------------- ---------- ----------REGION_NAME-------------------------EG Egypt 4 4Middle East and AfricaIL Israel 4 4Middle East and AfricaKW Kuwait 4 4Middle East and AfricaCO COUNTRY_NAME REGION_ID REGION_ID-- ---------------------------------------- ---------- ----------REGION_NAME-------------------------NG Nigeria 4 4Middle East and AfricaZM Zambia 4 4Middle East and AfricaZW Zimbabwe 4 4Middle East and Africa已选择6行。执行计划----------------------------------------------------------Plan hash value: 4030513296------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6 | 168 | 2(0)| 00:00:01 || 1 | NESTED LOOPS | | 6 | 168 | 2(0)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1(0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0(0)| 00:00:01 ||* 4 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 6 | 84 | 1(0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - access("R"."REGION_ID"=4)4 - filter("C"."REGION_ID"=4)统计信息----------------------------------------------------------628 recursive calls0 db block gets127 consistent gets20 physical reads0 redo size825 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client13 sorts (memory)0 sorts (disk)6 rows processedSQL>#############SQL> alter system flush shared_pool;系统已更改。SQL> alter system flush buffer_cache;系统已更改。select *from hr.COUNTRIES c,hr. REGIONS rwherec.REGION_ID='4'6 and c.REGION_ID=r.REGION_ID;CO COUNTRY_NAME REGION_ID REGION_ID-- ---------------------------------------- ---------- ----------REGION_NAME-------------------------EG Egypt 4 4Middle East and AfricaIL Israel 4 4Middle East and AfricaKW Kuwait 4 4Middle East and AfricaCO COUNTRY_NAME REGION_ID REGION_ID-- ---------------------------------------- ---------- ----------REGION_NAME-------------------------NG Nigeria 4 4Middle East and AfricaZM Zambia 4 4Middle East and AfricaZW Zimbabwe 4 4Middle East and Africa已选择6行。执行计划----------------------------------------------------------Plan hash value: 4030513296------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6 | 168 | 2(0)| 00:00:01 || 1 | NESTED LOOPS | | 6 | 168 | 2(0)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1(0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0(0)| 00:00:01 ||* 4 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 6 | 84 | 1(0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - access("R"."REGION_ID"=4)4 - filter("C"."REGION_ID"=4)统计信息----------------------------------------------------------656 recursive calls0 db block gets131 consistent gets22 physical reads0 redo size825 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client13 sorts (memory)0 sorts (disk)6 rows processedSQL>
注：关于where条件顺序不同、性能不同示例探讨的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
