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

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

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

-- 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;?

执行计划：

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

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

| ? 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;?

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

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

| ? 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 |

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

结论：

?? ? ? ?大表有索引：

?? ? ? ?-- 在大表够大的情况下，优化器采用了NL的连接方式

?? ? ? ?-- 驱动表的选择与内外没有关系，自动选择小的行源做为驱动表

drop index tt1_ind

create index tt2_ind on tt2(object_id);

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

select tt1.object_name,tt2.object_name

from tt1,

?? ? tt2

where tt1.object_id = tt2.object_id;?

7、小表有索引，大表无索引，小表在内，大表在外,加hints

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

from tt1,

?? ? tt2

where tt1.object_id = tt2.object_id;?

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

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

| ? 0 | SELECT STATEMENT ? | ? ? ?| ? 673 | 32304 | ?1230 ? (2)| 00:00:15 |

|* ?1 | ?HASH JOIN ? ? ? ? | ? ? ?| ? 673 | 32304 | ?1230 ? (2)| 00:00:15 |

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

| ? 3 | ? TABLE ACCESS FULL| TT1 ?| ? 398K| ? ?11M| ?1220 ? (2)| 00:00:15 |

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

结论：

?? ? ? ?小表有索引：

?? ? ? ?-- 始终不能用大表做驱动表

create index tt1_ind on tt1(object_id);

8、大表小表都有索引

select tt1.object_name,tt2.object_name

from tt1,

?? ? tt2

where tt1.object_id = tt2.object_id;?

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

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

| ? 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 |

--------------------------------------------------- ? ? ? ?

结论：

?? ? ? ?小表、大表都有索引：

?? ? ? ?-- 符合原则，小且做驱动，大表走索引

9、两个表一样大，且都有索引

select tt1.object_name,tt2.object_name

from tt1,

?? ? tt2

where tt1.object_id = tt2.object_id; ? ? ? ? ?

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

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

| ? 0 | SELECT STATEMENT ? | ? ? ?| 47358 | ?7307K| ? ? ? | ? 816 ? (1)| 00:00:10 |

|* ?1 | ?HASH JOIN ? ? ? ? | ? ? ?| 47358 | ?7307K| ?4216K| ? 816 ? (1)| 00:00:10 |

| ? 2 | ? TABLE ACCESS FULL| TT2 ?| 47358 | ?3653K| ? ? ? | ? 158 ? (2)| 00:00:02 |

| ? 3 | ? TABLE ACCESS FULL| TT1 ?| 52860 | ?4078K| ? ? ? | ? 158 ? (2)| 00:00:02 |

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

select /*+index(tt2 tt2_ind)*/tt1.object_name,tt2.object_name

from tt1,

?? ? tt2

where tt1.object_id = tt2.object_id; ? ? ? ? ??

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

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

| ? 0 | SELECT STATEMENT ? ? ? ? ? ? | ? ? ? ? | 47358 | ?7307K| ? ? ? | ?1898 ? (1)| 00:00:23 |

|* ?1 | ?HASH JOIN ? ? ? ? ? ? ? ? ? | ? ? ? ? | 47358 | ?7307K| ?4216K| ?1898 ? (1)| 00:00:23 |

| ? 2 | ? TABLE ACCESS BY INDEX ROWID| TT2 ? ? | 47358 | ?3653K| ? ? ? | ?1240 ? (1)| 00:00:15 |

| ? 3 | ? ?INDEX FULL SCAN ? ? ? ? ? | TT2_IND | 47358 | ? ? ? | ? ? ? | ? 124 ? (2)| 00:00:02 |

| ? 4 | ? TABLE ACCESS FULL ? ? ? ? ?| TT1 ? ? | 52860 | ?4078K| ? ? ? | ? 158 ? (2)| 00:00:02 |

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

结论：

?? ? ? ?--两个表一样大，无论用哪个来做驱动表，cost都会很高。

?? ? ? ?--优化器自动选择hash join

?? ? ? ?--如果要走索引，显然会让cost值增大(因为每一条记录，都要先用索引获得rowid,再回表。)

************************************************************************************************************************

set linesize 200 pagesize 1000

explain plan for

select /*+index(tt2 tt2_ind)*/tt1.object_name,tt2.object_name

from tt1,

?? ? tt2

where tt1.object_id = tt2.object_id; ? ?

select * from table(dbms_xplan.display());

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

热点推荐

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