where条件顺序不同、性能不同示例探讨(where条件加判断)

昨天在书上看到SQL语句优化时，where条件顺序不同，性能不同，这个建议在OracleG版本还合适吗？方式1优于方式2？ 方式1： 方式2： g中测试结果证明是一样的。 Microsoft Windows [版本 5.2.] (C) 版权所有 - Microsoft Corp. C:Documents and SettingsAdministrator>sqlplus / as sysdba SQL*Plus: Release .2.0.1.0 - Production on 星期六 5月 :: Copyright (c) , , Oracle. All rights reserved. 连接到: Oracle Database g Enterprise Edition Release .2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> alter system flush shared_pool; 系统已更改。 SQL> alter system flush buffer_cache; 系统已更改。 SQL> set autotrace on; SQL> select * 2 from COUNTRIES c, 3 REGIONS r 4 where c.REGION_ID=r.REGION_ID and c.REGION_ID='4'; REGIONS r * 第 3 行出现错误: ORA-: 表或视图不存在 SQL> select * 2 from hr.COUNTRIES c, 3 hr. REGIONS r 4 where c.REGION_ID=r.REGION_ID and c.REGION_ID='4'; CO COUNTRY_NAME REGION_ID REGION_ID -- ---------------------------------------- ---------- ---------- REGION_NAME ------------------------- EG Egypt 4 4 Middle East and Africa IL Israel 4 4 Middle East and Africa KW Kuwait 4 4 Middle East and Africa CO COUNTRY_NAME REGION_ID REGION_ID -- ---------------------------------------- ---------- ---------- REGION_NAME ------------------------- NG Nigeria 4 4 Middle East and Africa ZM Zambia 4 4 Middle East and Africa ZW Zimbabwe 4 4 Middle East and Africa 已选择6行。 执行计划 ---------------------------------------------------------- Plan hash value: -------------------------------------------------------------------------------- ---------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU)| Time | -------------------------------------------------------------------------------- ---------------- | 0 | SELECT STATEMENT | | 6 | | 2 (0)| :: | | 1 | NESTED LOOPS | | 6 | | 2 (0)| :: | | 2 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | | 1 (0)| :: | |* 3 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 (0)| :: | |* 4 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 6 | | 1 (0)| :: | -------------------------------------------------------------------------------- ---------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("R"."REGION_ID"=4) 4 - filter("C"."REGION_ID"=4) 统计信息 ---------------------------------------------------------- recursive calls 0 db block gets consistent gets physical reads 0 redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client sorts (memory) 0 sorts (disk) 6 rows processed SQL> ############# SQL> alter system flush shared_pool; 系统已更改。 SQL> alter system flush buffer_cache; 系统已更改。 select * from hr.COUNTRIES c, hr. REGIONS r where c.REGION_ID='4' 6 and c.REGION_ID=r.REGION_ID; CO COUNTRY_NAME REGION_ID REGION_ID -- ---------------------------------------- ---------- ---------- REGION_NAME ------------------------- EG Egypt 4 4 Middle East and Africa IL Israel 4 4 Middle East and Africa KW Kuwait 4 4 Middle East and Africa CO COUNTRY_NAME REGION_ID REGION_ID -- ---------------------------------------- ---------- ---------- REGION_NAME ------------------------- NG Nigeria 4 4 Middle East and Africa ZM Zambia 4 4 Middle East and Africa ZW Zimbabwe 4 4 Middle East and Africa 已选择6行。 执行计划 ---------------------------------------------------------- Plan hash value: -------------------------------------------------------------------------------- ---------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU)| Time | -------------------------------------------------------------------------------- ---------------- | 0 | SELECT STATEMENT | | 6 | | 2 (0)| :: | | 1 | NESTED LOOPS | | 6 | | 2 (0)| :: | | 2 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | | 1 (0)| :: | |* 3 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 (0)| :: | |* 4 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 6 | | 1 (0)| :: | -------------------------------------------------------------------------------- ---------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("R"."REGION_ID"=4) 4 - filter("C"."REGION_ID"=4) 统计信息 ---------------------------------------------------------- recursive calls 0 db block gets consistent gets physical reads 0 redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client sorts (memory) 0 sorts (disk) 6 rows processed SQL>

推荐整理分享where条件顺序不同、性能不同示例探讨(where条件加判断)，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:where条件中的判断,where条件后面加group,where条件1=1,where条件表达式,where条件表达式,where条件表达式,where条件表达式,where条件语句,内容如对您有帮助，希望把文章链接给更多的朋友！

如何将sql执行的错误消息记录到本地文件中实现过程 其实大家都知道sql语句的错误信息都可以在sys.messages表里面找到如：如果在执行语句在try...catch中我们可以通过以下方法获取错误信息。sql语句如下：BEG

获取SQL Server表字段的各种属性实例代码 --SQLServerSELECTa.nameAS字段名,CASEWHENEXISTS(SELECT1FROMsysobjectsWHERExtype='PK'ANDparent_obj=a.idANDnameIN(SELECTnameFROMsysindexesWHEREindidIN(SELECTindidFROMsysindexkeysWHEREid=a.idANDcol

SQLServer日志清空语句(sql,sql,sql) SQLServer日志清空方法在查询分析器中顺序执行以下三步，其中databasename为你的数据库文件名sql日志清空可以将gimoo.ldf文件变得很小，方便备份数据库