oracle的where条件过滤时是先执行前面的判断语句还是后面的判断语句! CBO(基于COST的优化器)会根据统计信息自动选择执行顺序。如果采用CBO那么两种写法没有区别。但是,RBO(基于规则的优化器)会严格从右至左进行过滤。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 RBO下也不一定是严格按从右至左进行过滤。测试如下:SQL> create table test_a tablespace users as select * from all_objects;Table created.SQL> create table test_b tablespace users as select * from all_objects;Table created.SQL> set autotrace traceonly;SQL> select /*+ rule */ b.object_name, b.owner from test_a a, test_b b where a.object_id>10000 and a.object_id=b.object_id;19090 rows selected.Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'TEST_B' 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'TEST_A'Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1626 consistent gets 1618 physical reads 0 redo size 637714 bytes sent via SQL*Net to client 14503 bytes received via SQL*Net from client 1274 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 19090 rows processedSQL> select /*+ rule */ b.object_name, b.owner from test_a a, test_b b where a.object_id=b.object_id and a.object_id>10000;19090 rows selected.Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'TEST_B' 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'TEST_A'Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1626 consistent gets 0 physical reads 0 redo size 637714 bytes sent via SQL*Net to client 14503 bytes received via SQL*Net from client 1274 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 19090 rows processedSQL> a.id>1000过滤在先,只有两个表的table scan后才有表连接,可以从执行计划中看出. 数据库设计 to_char(sysdate……)的参数设置 如何创建一个存储过程,它在指定的秒数内不响应?? 数据表备份的问题 我是菜鸟,求教,急! to 数据删除或插入的问题 请问oracle一用户下的所有表的统计问题! DB2移植到ORACLE,日期处理问题 Oracle数据库实例和数据文件的问题求助 请大家帮忙看一下,十万火机。谢谢 一个关于rownum的问题 关于oracle监听器的问题
SQL> create table test_a tablespace users as select * from all_objects;Table created.SQL> create table test_b tablespace users as select * from all_objects;Table created.SQL> set autotrace traceonly;
SQL> select /*+ rule */ b.object_name, b.owner from test_a a, test_b b where a.object_id>10000 and a.object_id=b.object_id;19090 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'TEST_B'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'TEST_A'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1626 consistent gets
1618 physical reads
0 redo size
637714 bytes sent via SQL*Net to client
14503 bytes received via SQL*Net from client
1274 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
19090 rows processedSQL> select /*+ rule */ b.object_name, b.owner from test_a a, test_b b where a.object_id=b.object_id and a.object_id>10000;19090 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'TEST_B'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'TEST_A'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1626 consistent gets
0 physical reads
0 redo size
637714 bytes sent via SQL*Net to client
14503 bytes received via SQL*Net from client
1274 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
19090 rows processedSQL>