大表在前，下表在后的说法真的对吗详解程序员

我自己以前在网上，看到说oracle是从下到上，从右到左访问，因此我们写sql时应该把大表放在前面，小表放在后面。

今天我看了一个教程，然后自己做了实验，发现这种说法是有问题的。

下面，让我用事实来说话。

首先，准备数据

drop table tab_big;
drop table tab_small;
create table tab_big as select * from dba_objects where rownum<=30000;
create table tab_small as select * from dba_objects where rownum<=10;
set autotrace traceonly
set timing on

下面进行测试：

SQL> select count(*) from tab_big,tab_small;

COUNT(*)
———-
300000

已用时间: 00: 00: 00.07

执行计划
———————————————————-
Plan hash value: 177389953

—————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 1137 (1)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MERGE JOIN CARTESIAN| | 331K| 1137 (1)| 00:00:14 |
| 3 | TABLE ACCESS FULL | TAB_SMALL | 10 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 33181 | 1135 (1)| 00:00:14 |
| 5 | TABLE ACCESS FULL | TAB_BIG | 33181 | 113 (0)| 00:00:02 |
—————————————————————————

Note
—–
– dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
0 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from tab_small,tab_big ;

COUNT(*)
———-
300000

已用时间: 00: 00: 00.05

执行计划
———————————————————-
Plan hash value: 177389953

Note
—–
– dynamic sampling used for this statement (level=2)

下面对上面两句加上/*+rule*/注释：

SQL> select /*+rule*/ count(*) from tab_big,tab_small ;

COUNT(*)
———-
300000

已用时间: 00: 00: 00.03

执行计划
———————————————————-
Plan hash value: 41936691

—————————————–
| Id | Operation | Name |
—————————————–
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_SMALL |
| 4 | TABLE ACCESS FULL| TAB_BIG |
—————————————–

Note
—–
– rule based optimizer used (consider using cbo)

统计信息
———————————————————-
0 recursive calls
0 db block gets
4213 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+rule*/ count(*) from tab_small,tab_big ;

COUNT(*)
———-
300000

已用时间: 00: 00: 00.33

执行计划
———————————————————-
Plan hash value: 2127005654

—————————————–
| Id | Operation | Name |
—————————————–
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_BIG |
| 4 | TABLE ACCESS FULL| TAB_SMALL |
—————————————–

Note
—–
– rule based optimizer used (consider using cbo)

统计信息
———————————————————-
0 recursive calls
0 db block gets
90421 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

可以看到，加注释后表的放置顺序对性能就有影响了。

结论：写sql时，在RBO中是需要考虑表的访问顺序的，但是在CBO中不需要考虑，因为CBO是基于代价的优化器，哪种方式的代价小，优化器就会使用哪种方式。

网上有一句原话是这样的：

WHERE子句中的连接顺序．：
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

下面我们来做实验：

准备数据：

SQL> create table t1 as select * from dba_objects;

表已创建。

SQL> create table t2 as select rownum id ,dbms_random.string(‘b’, 50) n ,data_ob

ject_id data_id from dba_objects where rownum<=10000;

表已创建。

SQL> set autotrace traceonly

SQL> set linesize 1000

SQL> set timing on

SQL>

下面开始实验：

SQL> select * from t1,t2 where t1.object_id=29 and t2.data_id>8;

已选择2085行。

已用时间: 00: 00: 00.18

执行计划
———————————————————-
Plan hash value: 787647388

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 18650 | 39M| 540 (1)| 00:00:07 |
| 1 | MERGE JOIN CARTESIAN| | 18650 | 39M| 540 (1)| 00:00:07 |
|* 2 | TABLE ACCESS FULL | T1 | 11 | 2277 | 283 (1)| 00:00:04 |
| 3 | BUFFER SORT | | 1638 | 3244K| 257 (1)| 00:00:04 |
|* 4 | TABLE ACCESS FULL | T2 | 1638 | 3244K| 23 (0)| 00:00:01 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – filter(“T1”.”OBJECT_ID”=29)
4 – filter(“T2″.”DATA_ID”>8)

Note
—–
– dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
412 recursive calls
0 db block gets
1291 consistent gets
0 physical reads
0 redo size
156639 bytes sent via SQL*Net to client
1933 bytes received via SQL*Net from client
140 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2085 rows processed

SQL> select * from t1,t2 where t2.data_id>8 and t1.object_id=29 ;

已选择2085行。

已用时间: 00: 00: 00.19

执行计划
———————————————————-
Plan hash value: 787647388

Predicate Information (identified by operation id):
—————————————————

2 – filter(“T1”.”OBJECT_ID”=29)
4 – filter(“T2″.”DATA_ID”>8)

Note
—–
– dynamic sampling used for this statement (level=2)

统计信息
———————————————————-
7 recursive calls
0 db block gets
1251 consistent gets
0 physical reads
0 redo size
156639 bytes sent via SQL*Net to client
1933 bytes received via SQL*Net from client
140 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2085 rows processed

看，使用CBO，并没有连接顺序的带来的性能问题，性能基本一致。

SQL> select /*+rule*/ * from t1,t2 where t2.data_id>8 and t1.object_id=29 ;

已选择2085行。

已用时间: 00: 00: 19.87

执行计划
———————————————————-
Plan hash value: 4016936828

———————————–
| Id | Operation | Name |
———————————–
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
|* 2 | TABLE ACCESS FULL| T2 |
|* 3 | TABLE ACCESS FULL| T1 |
———————————–

Predicate Information (identified by operation id):
—————————————————

2 – filter(“T2″.”DATA_ID”>8)
3 – filter(“T1”.”OBJECT_ID”=29)

Note
—–
– rule based optimizer used (consider using cbo)

统计信息
———————————————————-
1 recursive calls
0 db block gets
2168766 consistent gets
0 physical reads
0 redo size
156639 bytes sent via SQL*Net to client
1933 bytes received via SQL*Net from client
140 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2085 rows processed

SQL> select /*+rule*/ * from t1,t2 where t1.object_id=29 and t2.data_id>8;

已选择2085行。

已用时间: 00: 00: 18.51

执行计划
———————————————————-
Plan hash value: 4016936828

Predicate Information (identified by operation id):
—————————————————

2 – filter(“T2″.”DATA_ID”>8)
3 – filter(“T1”.”OBJECT_ID”=29)

Note
—–
– rule based optimizer used (consider using cbo)

统计信息
———————————————————-
1 recursive calls
0 db block gets
2168764 consistent gets
2155 physical reads
0 redo size
156639 bytes sent via SQL*Net to client
1933 bytes received via SQL*Net from client
140 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2085 rows processed

看结果，RBO都做不出来网上所说的那样的结果啊！

再加上连接条件：

SQL> select /*+rule*/ * from t1,t2 where t1.object_id=t2.id and t1.object_id=29
and t2.data_id>8;

已用时间: 00: 00: 00.06

执行计划
———————————————————-
Plan hash value: 1792967693

————————————
| Id | Operation | Name |
————————————
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
|* 3 | TABLE ACCESS FULL| T2 |
|* 4 | SORT JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
————————————

Predicate Information (identified by operation id):
—————————————————

3 – filter(“T2″.”DATA_ID”>8)
4 – access(“T1″.”OBJECT_ID”=”T2″.”ID”)
filter(“T1″.”OBJECT_ID”=”T2″.”ID”)
5 – filter(“T1”.”OBJECT_ID”=29)

Note
—–
– rule based optimizer used (consider using cbo)

统计信息
———————————————————-
1 recursive calls
0 db block gets
1129 consistent gets
0 physical reads
0 redo size
1623 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+rule*/ * from t1,t2 where t1.object_id=t2.id and t2.data_id>8 and
t1.object_id=29 ;

已用时间: 00: 00: 00.03

执行计划
———————————————————-
Plan hash value: 1792967693

Predicate Information (identified by operation id):
—————————————————

3 – filter(“T2″.”DATA_ID”>8)
4 – access(“T1″.”OBJECT_ID”=”T2″.”ID”)
filter(“T1″.”OBJECT_ID”=”T2″.”ID”)
5 – filter(“T1”.”OBJECT_ID”=29)

Note
—–
– rule based optimizer used (consider using cbo)

同样，基本没什么影响啊！！

所以说，网上的一些的sql优化准则，有时候真的是有问题的，优化还是需要具体问题具体分析才行。

做人也是如此，那些人们认为天经地义的准则，有时候是有问题的，还是需要我们自己去检验，最后得出真理啊！！

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/7236.html

大表在前，下表在后的说法真的对吗详解程序员

相关推荐

发表回复