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后才有表连接,可以从执行计划中看出. 江湖救急,大侠们赶快帮帮忙啊 求助:oracle 11g的trace文件这么多是不是有问题 oracle字段不够长如何解决? 请教一条SQL的写法 在线等!!!!!!!! 如何恢复drop掉的表啊?????????????????????????? 有关oracle中通过角色授权给用户的问题 一个SQL语句 [菜鸟问题]关于SELECT的问题 这条SQL语句如何写? 请教一问题(有关管理员密码),将高分答谢 一个关于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>