驱动表、为什么不走索引 小实验

两种连接方式，有驱动表的概念(其实应该说是驱动行源更为准确)

-- hash join

-- nested-loop join

?

对于Nested-loop join

适合于小表(几千条，几万条记录)与大表做联接在联接列上有索引。分内表和外表(驱动表)，靠近from子句的是内表。

从效率上讲，小表应该作外表，大表应该作内表，即大表查询时走索引。

?

COST= Access cost of A(驱动表) + (access cost of B * number of rows from A)

?

成本计算方法：

设小表100行，大表100000行。

?

两表均有索引：

如果小表在内，大表在外(驱动表)的话，则扫描次数为：

??100000+100000*2 (其中2表示IO次数，一次索引，一次数据)

如果大表在内，小表在外(驱动表)的话，则扫描次数为：

??100+100*2.

?

两表均无索引：

如果小表在内，大表在外的话，则扫描次数为：

??100000+100*100000

如果大表在内，小表在外的话，则扫描次数为：

??100+100000*100

?

注意：如果一个表有索引，一个表没有索引，ORACLE会将没有索引的表作驱动表。如果两个表都有索引

，则外表作驱动表。如果两个都没索引的话，则也是外表作驱动表。

?

基本的执行计划如下所示：

??NESTED LOOPS

?? ? ? ? ? TABLE ACCESS (BY ROWID) ?OF ?our_outer_table

?? ? ? ? ? ? ? ? ? INDEX (..SCAN) OF outer_table_index(….)

?? ? ? ? ? TABLE ACCESS (BY ROWID) ?OF ?our_inner_table

?? ? ? ? ? ? INDEX (..SCAN) OF inner_table_index(….)

?

从效率上讲

from B， ? ? ? ?--内表，大表

?? ? A ? ? ? ? ? ? ? ?--外表，小表，驱动表

COST = Access cost of A + (access cost of B * number of rows from A) ? ??

?

---------------------------实验(条件要求，小表很小，大表很大，效果更具有可见性)、结论（仅代表个人意见，有不同意见的可以和我讨论）

大表tt1:

CREATE table tt1

as select * from dba_objects;

小表tt2:

CREATE table tt2

as select * from user_objects;

分析表：

select num_rows,blocks,table_name

from user_tables

where table_name in('TT1','TT2');

?

?

1、两表都无索引，大表在内，小表在外

select tt1.object_name,tt2.object_name

from tt1,

?? ? tt2

where tt1.object_id = tt2.object_id; ?

?

2、两表都无索引，小表在内，大表在外

select tt1.object_name,tt2.object_name

from tt2,

?? ? tt1

where tt1.object_id = tt2.object_id; ?

?

3、两表无索引，使用hint /*+use_nl(tt2)*/

select /*+use_nl(tt2)*/tt1.object_name,tt2.object_name

from tt2,

?? ? tt1

where tt1.object_id = tt2.object_id;?

?

执行计划：

---------------------------------------

| Id ?| Operation ? ? ? ? ?| Name | Rows ?| Bytes | Cost (%CPU)| Time ? ? |

---------------------------------------

| ? 0 | SELECT STATEMENT ? | ? ? ?| ? ?84 | ?4032 | ? 162 ? (2)| 00:00:02 |

|* ?1 | ?HASH JOIN ? ? ? ? | ? ? ?| ? ?84 | ?4032 | ? 162 ? (2)| 00:00:02 |

| ? 2 | ? TABLE ACCESS FULL| TT2 ?| ? ?86 | ?1548 | ? ? 3 ? (0)| 00:00:01 |

| ? 3 | ? TABLE ACCESS FULL| TT1 ?| 49863 | ?1460K| ? 158 ? (2)| 00:00:02 |

---------------------------------------

?

结论：

?? ? ? ?在没有索引的情况下：

?? ? ? ?-- hash join的cost更小，优化器自动选择hash join

?? ? ? ?-- 优化器自动选择小表做驱动表

?? ? ? ?-- hints也不能改变连接方式

?

?

create index tt1_ind on tt1(object_id);

4、大表有索引，小表无索引，大表在内，小表在外

select tt1.object_name,tt2.object_name

from tt2,

?? ? tt1

where tt1.object_id = tt2.object_id;?

5、大表有索引，小表无索引，小表在内，大表在外

select tt1.object_name,tt2.object_name

from tt1,

?? ? tt2

where tt1.object_id = tt2.object_id;?

?

---------------------------------------------------

| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |

---------------------------------------------------

| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ? | ? 673 | 32304 | ? 950 ? (1)| 00:00:12 |

| ? 1 | ?TABLE ACCESS BY INDEX ROWID| TT1 ? ? | ? ? 8 | ? 240 | ? ?11 ? (0)| 00:00:01 |

| ? 2 | ? NESTED LOOPS ? ? ? ? ? ? ?| ? ? ? ? | ? 673 | 32304 | ? 950 ? (1)| 00:00:12 |

| ? 3 | ? ?TABLE ACCESS FULL ? ? ? ?| TT2 ? ? | ? ?86 | ?1548 | ? ? 3 ? (0)| 00:00:01 |

|* ?4 | ? ?INDEX RANGE SCAN ? ? ? ? | TT1_IND | ? ? 8 | ? ? ? | ? ? 2 ? (0)| 00:00:01 |

---------------------------------