ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 嵌套循环连接的使用原则和分析(use_nl)

嵌套循环连接的使用原则和分析(use_nl)

原创 Linux操作系统 作者:xpj0515 时间:2011-05-05 18:55:24 0 删除 编辑

                      
===============================================================================================================================================
                                                                                                                     构建测试环境



SYS@standby1/2002-01-01 00:58:27>create table  test_tab as select rownum id,trunc(dbms_random.value(100,999)) t1,trunc(dbms_random.value(10000,99999)) t2,dbms_random.string('U',3) str1,dbms_random.string('U',5) str2 from dual connect by level <=1000000;

Table created.

Elapsed: 00:01:06.42


SYS@standby1/2002-01-01 01:02:35>col id for 9999999
SYS@standby1/2002-01-01 01:02:47>col t1 for 999999
SYS@standby1/2002-01-01 01:02:56>col t2 for 999999
SYS@standby1/2002-01-01 01:03:01>col str1 for a7
SYS@standby1/2002-01-01 01:03:09>col str2 for a7
SYS@standby1/2002-01-01 01:03:13>select * from test_tab where rownum<=10;

      ID      T1      T2 STR1    STR2
-------- ------- ------- ------- -------
       1     108   80943 JXB     AIRJK
       2     773   59153 KEM     MOMLT
       3     796   89751 HXU     QUULO
       4     703   17214 QFH     ZBQPZ
       5     603   60430 PJJ     LCMJP
       6     104   62811 RNN     ULDSM
       7     619   99953 UAP     BNMUC
       8     509   20605 AAD     DIKJD
       9     330   81171 KDC     EWBIA
      10     518   67222 QJZ     EPDVD

10 rows selected.


Elapsed: 00:00:00.01

SYS@standby1/2002-01-01 01:04:24>analyze table  test_tab compute statistics;

Table analyzed.

Elapsed: 00:00:17.56
SYS@standby1/2002-01-01 01:05:15>@table_detail.sql
Enter value for table_name: test_tab

TABLE_NAME PCT_FREE PCT_USED INI_TRANS INIT_EXT NEXT_EXT  FREELISTS CHAIN_CNT     ROWS  BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- -------- -------- --------- -------- -------- ---------- --------- -------- ------- ------------ -----------
TEST_TAB         10       40         1       64     1024          1         0  1000000    4005           90        27

1 row selected.

Elapsed: 00:00:00.01


SYS@standby1/2011-05-04 17:40:50>create table test_tab2 tablespace assm  as select * from test_tab where rownum<10000;

Table created.

Elapsed: 00:00:00.51


SYS@standby1/2011-05-04 17:44:42>@column_detail.sql
Enter value for table_name: test_tab

TABLE      COLUMN     DATA_TYP LENGTH AVG_LEN NUM_DISTINCT NUM_NULLS LOW_VALUE  HIGH_VALUE
---------- ---------- -------- ------ ------- ------------ --------- ---------- ----------
TEST_TAB   ID         NUMBER       22       4      1000000         0 C102       C402
TEST_TAB   T1         NUMBER       22       3          899         0 C202       C20A63
TEST_TAB   T2         NUMBER       22       4        89998         0 C302       C30A6463
TEST_TAB   STR1       VARCHAR2   4000       3        17576         0 414141     5A5A5A
TEST_TAB   STR2       VARCHAR2   4000       5       959277         0 4141414145 5A5A5A5A5A

5 rows selected.

Elapsed: 00:00:00.19


SYS@standby1/2011-05-04 17:42:43>@column_detail.sql
Enter value for table_name: test_tab2

TABLE      COLUMN     DATA_TYP LENGTH AVG_LEN NUM_DISTINCT NUM_NULLS LOW_VALUE  HIGH_VALUE
---------- ---------- -------- ------ ------- ------------ --------- ---------- ----------
TEST_TAB2  ID         NUMBER       22       3         9999         0 C102       C26464
TEST_TAB2  T1         NUMBER       22       3          899         0 C202       C20A63
TEST_TAB2  T2         NUMBER       22       4         9481         0 C3020111   C30A645D
TEST_TAB2  STR1       VARCHAR2   4000       3         7618         0 414142     5A5A56
TEST_TAB2  STR2       VARCHAR2   4000       5         9997         0 4141435754 5A5A585146

5 rows selected.

Elapsed: 00:00:00.31


=============================================================================================================================================
                                                                                                                    嵌套循环连接(示例)

1。连接两个集合连接列都没索引(都执行全表扫描  但驱动表不同)

SYS@standby1/2011-05-04 18:41:19>select count(*) from test_tab where t1 between 100 and 105;

  COUNT(*)
----------
      6801

1 row selected.


SYS@standby1/2011-05-04 18:41:10>select count(*) from test_tab2 where t2 between 10000 and 40000;

  COUNT(*)
----------
      3366

1 row selected.


SYS@standby1/2011-05-04 18:42:49>explain plan for select /*+ leading(a) use_nl(b a) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-04 18:43:20>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4003557917

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1110 | 22200 | 81949   (2)| 00:16:24 |
|   1 |  NESTED LOOPS      |           |  1110 | 22200 | 81949   (2)| 00:16:24 |
|*  2 |   TABLE ACCESS FULL| TEST_TAB  |  6680 | 66800 |  1123   (2)| 00:00:14 |
|*  3 |   TABLE ACCESS FULL| TEST_TAB2 |     1 |    10 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("A"."T1"<=105 AND "A"."T1">=100)
   3 - filter("B"."T2"<=20000 AND "B"."T2">=10000 AND "A"."ID"="B"."ID")
分析:大表作为驱动表,但返回行数为6680

SYS@standby1/2011-05-04 18:43:22>explain plan for select /*+ leading(b) use_nl(a b) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2011-05-04 18:44:56>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1355440866

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1110 | 22200 |  1248K  (2)| 04:09:43 |
|   1 |  NESTED LOOPS      |           |  1110 | 22200 |  1248K  (2)| 04:09:43 |
|*  2 |   TABLE ACCESS FULL| TEST_TAB2 |  1111 | 11110 |    14   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_TAB  |     1 |    10 |  1124   (2)| 00:00:14 |
--------------------------------------------------------------------------------

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

   2 - filter("B"."T2"<=20000 AND "B"."T2">=10000)
   3 - filter("A"."T1"<=105 AND "A"."T1">=100 AND "A"."ID"="B"."ID")

分析:小表作为驱动表,但返回行数较少为1111行

总结:嵌套循环连接的成本主要包括两个方面,外循环表返回的行数决定循环的次数,内循环表的每次循环扫描的成本,应该综合考量,
上例,test_tab返回6680行 test_tab2 返回1111行,但是由于test_tab为大表拥有1000000万行,若是作为内循环表将要在没有索引的情况下,
全表扫描100次  ,所以将小表作为内循环表相对成本更低。若是两表大小差不多,应该使循环次数为重点
假设测试如下
(创建与小表大小差不多的小表 记录数为10000行)

SYS@standby1/2011-05-04 18:08:03>create table test_tab3 tablespace assm  as select * from test_tab where rownum<10000;

Table created.

Elapsed: 00:00:00.09

SYS@standby1/2011-05-04 18:44:58>explain plan for select /*+ leading(b) use_nl(a b) */  a.id,b.id,a.str1,b.str1 from test_tab2 a,test_tab3 b where a.id=b.id and a.t2 between 10000 and 30000 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-04 18:49:03>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3176029732

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1111 | 22220 | 13460   (2)| 00:02:42 |
|   1 |  NESTED LOOPS      |           |  1111 | 22220 | 13460   (2)| 00:02:42 |
|*  2 |   TABLE ACCESS FULL| TEST_TAB3 |  1111 | 11110 |    14   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_TAB2 |     1 |    10 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("B"."T2"<=20000 AND "B"."T2">=10000)
   3 - filter("A"."T2"<=30000 AND "A"."T2">=10000 AND "A"."ID"="B"."ID")

总结使用返回行数较少的表为驱动表,消耗时间少于行数多的为驱动表的查询

SYS@standby1/2011-05-04 18:49:05>explain plan for select /*+ leading(a) use_nl(b a) */  a.id,b.id,a.str1,b.str1 from test_tab2 a,test_tab3 b where a.id=b.id and a.t2 between 10000 and 30000 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2011-05-04 18:51:05>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3130340337

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1111 | 22220 | 26900   (2)| 00:05:23 |
|   1 |  NESTED LOOPS      |           |  1111 | 22220 | 26900   (2)| 00:05:23 |
|*  2 |   TABLE ACCESS FULL| TEST_TAB2 |  2222 | 22220 |    14   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_TAB3 |     1 |    10 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("A"."T2"<=30000 AND "A"."T2">=10000)
   3 - filter("B"."T2"<=20000 AND "B"."T2">=10000 AND "A"."ID"="B"."ID")

=============================================================================================================================================


2。连接两个集合一边连接列创建索引(驱动表不同)

SYS@standby1/2011-05-04 17:58:22>create index pk_test2_id on test_tab2(id);

Index created.

Elapsed: 00:00:00.11
SYS@standby1/2011-05-04 17:58:51>@index_statistics.sql
Enter value for tablename: test_tab2

TYPE     INDEX          TABLE         BLOCKS  NUM_ROWS    DIS_KEY   KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL   PK_TEST2_ID    TEST_TAB2         44      9999       9999          1      21         39

1 row selected.

Elapsed: 00:00:00.00

SYS@standby1/2011-05-04 18:52:02>explain plan for select /*+ leading(b) use_nl(a b) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.01
SYS@standby1/2011-05-04 18:53:05>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1355440866

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1110 | 22200 |  1248K  (2)| 04:09:43 |
|   1 |  NESTED LOOPS      |           |  1110 | 22200 |  1248K  (2)| 04:09:43 |
|*  2 |   TABLE ACCESS FULL| TEST_TAB2 |  1111 | 11110 |    14   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_TAB  |     1 |    10 |  1124   (2)| 00:00:14 |
--------------------------------------------------------------------------------

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

   2 - filter("B"."T2"<=20000 AND "B"."T2">=10000)
   3 - filter("A"."T1"<=105 AND "A"."T1">=100 AND "A"."ID"="B"."ID")

SYS@standby1/2011-05-04 18:53:08>explain plan for select /*+ leading(a) use_nl(b a) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-04 18:54:11>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2205613710

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  1110 | 22200 |  7873   (1)| 00:01:35 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |  1110 | 22200 |  7873   (1)| 00:01:35 |
|*  3 |    TABLE ACCESS FULL         | TEST_TAB    |  6680 | 66800 |  1123   (2)| 00:00:14 |
|*  4 |    INDEX RANGE SCAN          | PK_TEST2_ID |     1 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB2   |     1 |    10 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - filter("A"."T1"<=105 AND "A"."T1">=100)
   4 - access("A"."ID"="B"."ID")
   5 - filter("B"."T2"<=20000 AND "B"."T2">=10000)

SYS@standby1/2011-05-04 18:54:13>create index pk_test_id on test_tab(id);

Index created.

Elapsed: 00:00:06.27
SYS@standby1/2011-05-04 18:55:49>explain plan for select /*+ leading(b) use_nl(a b) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-04 18:55:54>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2129721392

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  1110 | 22200 |  3349   (1)| 00:00:41 |
|   1 |  NESTED LOOPS                |            |       |       |            |          |
|   2 |   NESTED LOOPS               |            |  1110 | 22200 |  3349   (1)| 00:00:41 |
|*  3 |    TABLE ACCESS FULL         | TEST_TAB2  |  1111 | 11110 |    14   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | PK_TEST_ID |     1 |       |     2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   3 - filter("B"."T2"<=20000 AND "B"."T2">=10000)
   4 - access("A"."ID"="B"."ID")
   5 - filter("A"."T1"<=105 AND "A"."T1">=100)

上述执行计划对比总结:
查询一:内循环表连接列没有使用索引,导致驱动表每处理一条数据就执行一次全表扫描(小表为驱动表 对大表执行1111次全表扫描  悲剧的执行计划)
查询二:内循环表连接列使用索引,大表返回6680行数据,对小表(内循环表)执行6680次循环,但由于连接列有索引,执行时间大减04:09:43=》00:01:35
查询三:内循环表连接列使用索引,小表作为驱动表,大表也创建了索引,但是只对大表执行1111次循环扫描,扫描由于有索引采用的是随即读时间进一步降低00:01:35=》00:00:41
总结:要点为循环次数和每次随机读(或者全表扫描的成本),根据表的大小决定那个更为重要 (都是小表的话循环次数为先,差距很大的话每次读为先)
,再有就是索引的优先级最高。

=============================================================================================================================================


3。连接两个集合连接列都创建索引(驱动表不同)

SYS@standby1/2011-05-04 19:20:55>explain plan for select /*+ leading(b) use_nl(a b) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-04 19:22:57>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2129721392

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  1110 | 22200 |  3349   (1)| 00:00:41 |
|   1 |  NESTED LOOPS                |            |       |       |            |          |
|   2 |   NESTED LOOPS               |            |  1110 | 22200 |  3349   (1)| 00:00:41 |
|*  3 |    TABLE ACCESS FULL         | TEST_TAB2  |  1111 | 11110 |    14   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | PK_TEST_ID |     1 |       |     2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   3 - filter("B"."T2"<=20000 AND "B"."T2">=10000)
   4 - access("A"."ID"="B"."ID")
   5 - filter("A"."T1"<=105 AND "A"."T1">=100)

19 rows selected.

Elapsed: 00:00:00.03
SYS@standby1/2011-05-04 19:23:00>explain plan for select /*+ leading(a) use_nl(b a) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

Explained.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-04 19:23:15>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2205613710

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  1110 | 22200 |  7873   (1)| 00:01:35 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |  1110 | 22200 |  7873   (1)| 00:01:35 |
|*  3 |    TABLE ACCESS FULL         | TEST_TAB    |  6680 | 66800 |  1123   (2)| 00:00:14 |
|*  4 |    INDEX RANGE SCAN          | PK_TEST2_ID |     1 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB2   |     1 |    10 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - filter("A"."T1"<=105 AND "A"."T1">=100)
   4 - access("A"."ID"="B"."ID")
   5 - filter("B"."T2"<=20000 AND "B"."T2">=10000)

总结:当连接列内循环和外循环都创建有索引时,都是采用的随机读的方式根据索引获取数据,且返回的最终结果集相同都是1110行数据,所以主要差异就在循环的次数上,和每次随即读的成本
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB2  |     1 |    10 |     2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     1 |    10 |     3   (0)| 00:00:01 |
一个循环读次数为6680 一个为1110  但每次扫描索引的成本也不同 2 和3 因为索引所占空间的大小不同

SYS@standby1/2011-05-04 19:19:35>@index_statistics
Enter value for tablename: test_tab2

TYPE     INDEX          TABLE         BLOCKS  NUM_ROWS    DIS_KEY   KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL   PK_TEST2_ID    TEST_TAB2         44      9999       9999          1      21         39

SYS@standby1/2011-05-04 19:23:17>@index_statistics
Enter value for tablename: test_Tab

TYPE     INDEX          TABLE         BLOCKS  NUM_ROWS    DIS_KEY   KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL   PK_TEST_ID     TEST_TAB        4087   1000000    1000000          1    2226       4015

1 row selected.



总结:嵌套循环连接方法的特征
1。首先处理驱动表,驱动表的查询范围决定所要处理的数据量的大小
2。只有优先处理了驱动的数据,才能获得连接列的常量值,然后扫描连接表中符合连接条件的列(全表扫描或者根据索引)
3。连接列的索引状态对连接执行特别重要,连接方向影响执行速度
4。即使为where中所有列创建索引,也不保证这些索引都会使用
5。允许处理结果没有全部完成时 先返回部分数据(对于大数据量非常有效)


=============================================================================================================================================

4。where中非连接条件(查询条件)上创建索引对执行计划的影响



******************************创建测试环境****************************


SYS@standby1/2011-05-04 19:32:41>create index ind_test_t1 on test_tab(t1);

Index created.

Elapsed: 00:00:05.46
SYS@standby1/2011-05-04 19:36:53>create index ind_test2_t2 on test_tab2(t2);

Index created.

Elapsed: 00:00:00.11
SYS@standby1/2011-05-04 19:37:17>@index_statistics.sql
Enter value for tablename: test_tab

TYPE     INDEX          TABLE         BLOCKS  NUM_ROWS    DIS_KEY   KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL   IND_TEST_T1    TEST_TAB        4087   1000000        899       1112    2091     874010
NORMAL   PK_TEST_ID     TEST_TAB        4087   1000000    1000000          1    2226       4015

2 rows selected.

Elapsed: 00:00:00.00
SYS@standby1/2011-05-04 19:37:29>@index_statistics.sql
Enter value for tablename: test_tab2

TYPE     INDEX          TABLE         BLOCKS  NUM_ROWS    DIS_KEY   KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL   IND_TEST2_T2   TEST_TAB2         44      9999       9481          1      23       9746
NORMAL   PK_TEST2_ID    TEST_TAB2         44      9999       9999          1      21         39

2 rows selected



******************************测试过程****************************

SYS@standby1/2011-05-04 20:06:54>select /*+ leading(a) use_nl(b a) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

7 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 2205613710

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  1110 | 22200 |  7873   (1)| 00:01:35 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |  1110 | 22200 |  7873   (1)| 00:01:35 |
|*  3 |    TABLE ACCESS FULL         | TEST_TAB    |  6680 | 66800 |  1123   (2)| 00:00:14 |
|*  4 |    INDEX RANGE SCAN          | PK_TEST2_ID |     1 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB2   |     1 |    10 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - filter("A"."T1"<=105 AND "A"."T1">=100)
   4 - access("A"."ID"="B"."ID")
   5 - filter("B"."T2"<=20000 AND "B"."T2">=10000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4128  consistent gets
        740  physical reads
          0  redo size
        757  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)
          7  rows processed


SYS@standby1/2011-05-04 19:58:02>select /*+ leading(a) use_nl(b a) index(a ind_test_t1)*/  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

7 rows selected.

Elapsed: 00:00:00.14

Execution Plan
----------------------------------------------------------
Plan hash value: 1605046911

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |  1110 | 22200 | 12607   (1)| 00:02:32 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |  1110 | 22200 | 12607   (1)| 00:02:32 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_TAB    |  6680 | 66800 |  5858   (1)| 00:01:11 |
|*  4 |     INDEX RANGE SCAN          | IND_TEST_T1 |  6680 |       |    16   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | PK_TEST2_ID |     1 |       |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | TEST_TAB2   |     1 |    10 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   4 - access("A"."T1">=100 AND "A"."T1"<=105)
   5 - access("A"."ID"="B"."ID")
   6 - filter("B"."T2"<=20000 AND "B"."T2">=10000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6088  consistent gets
       3252  physical reads
          0  redo size
        757  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)
          7  rows processed




SYS@standby1/2011-05-04 19:57:26>select /*+ leading(b) use_nl(a b) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

7 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2129721392

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  1110 | 22200 |  3349   (1)| 00:00:41 |
|   1 |  NESTED LOOPS                |            |       |       |            |          |
|   2 |   NESTED LOOPS               |            |  1110 | 22200 |  3349   (1)| 00:00:41 |
|*  3 |    TABLE ACCESS FULL         | TEST_TAB2  |  1111 | 11110 |    14   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | PK_TEST_ID |     1 |       |     2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB   |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   3 - filter("B"."T2"<=20000 AND "B"."T2">=10000)
   4 - access("A"."ID"="B"."ID")
   5 - filter("A"."T1"<=105 AND "A"."T1">=100)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        323  consistent gets
          0  physical reads
          0  redo size
        757  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)
          7  rows processed



SYS@standby1/2011-05-04 19:57:24>select /*+ leading(b) use_nl(a b) index(b ind_test2_t2)*/  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

7 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2487637775

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |  1110 | 22200 |  4422   (1)| 00:00:54 |
|   1 |  NESTED LOOPS                 |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |  1110 | 22200 |  4422   (1)| 00:00:54 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_TAB2    |  1111 | 11110 |  1087   (0)| 00:00:14 |
|*  4 |     INDEX RANGE SCAN          | IND_TEST2_T2 |  1111 |       |     4   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | PK_TEST_ID   |     1 |       |     2   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | TEST_TAB     |     1 |    10 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   4 - access("B"."T2">=10000 AND "B"."T2"<=20000)
   5 - access("A"."ID"="B"."ID")
   6 - filter("A"."T1"<=105 AND "A"."T1">=100)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4554  consistent gets
         64  physical reads
          0  redo size
        757  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)
          7  rows processed




总结:通过执行计划1和2 以及  3和4的对比发现,当对驱动表先通过索引过滤点不符合驱动查询条件的行时,对应的物理读和逻辑读的数量却比使用
通过全表扫描的逻辑读和物理读的数量都多,我猜想的原因是因为对应的索引的clustor factor都接近于记录的行数而不是数据块数,也就是说索引
数据排序也物理存储时的相似性太低,俩给表都是按照主键的顺序存储的

下面测试通过id列 (clustor factor接近于数据块数)的索引
 

SSYS@standby1/2011-05-04 20:19:12>select /*+ leading(a) use_nl(b a) index(a pk_test_id)*/  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.id between 1000 and 8000  and b.t2 between 10000 and 20000;

836 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1359229329

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   778 | 13226 |  7122   (1)| 00:01:26 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |   778 | 13226 |  7122   (1)| 00:01:26 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_TAB    |  7002 | 49014 |    47   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | PK_TEST_ID  |  7002 |       |    18   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | PK_TEST2_ID |     1 |       |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | TEST_TAB2   |     1 |    10 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   4 - access("A"."ID">=1000 AND "A"."ID"<=8000)
   5 - access("A"."ID"="B"."ID")
       filter("B"."ID"<=8000 AND "B"."ID">=1000)
   6 - filter("B"."T2"<=20000 AND "B"."T2">=10000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        473  consistent gets
          1  physical reads
          0  redo size
      22018  bytes sent via SQL*Net to client
       1020  bytes received via SQL*Net from client
         57  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        836  rows processed

SYS@standby1/2011-05-04 20:23:01>select /*+ leading(b) use_nl(a b) index(b pk_test2_id)*/  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and b.id between 1000 and 8000  and b.t2 between 10000 and 20000;

836 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 52871198

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   778 | 13226 |  1604   (1)| 00:00:20 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |   778 | 13226 |  1604   (1)| 00:00:20 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TEST_TAB2   |   778 |  7780 |    44   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | PK_TEST2_ID |  7003 |       |    16   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | PK_TEST_ID  |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | TEST_TAB    |     1 |     7 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - filter("B"."T2"<=20000 AND "B"."T2">=10000)
   4 - access("B"."ID">=1000 AND "B"."ID"<=8000)
   5 - access("A"."ID"="B"."ID")
       filter("A"."ID"<=8000 AND "A"."ID">=1000)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        533  consistent gets
          0  physical reads
          0  redo size
      22018  bytes sent via SQL*Net to client
       1020  bytes received via SQL*Net from client
         57  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        836  rows processed

总结同样是差不多返回七千行,可是使用PK_TEST_ID索引产生的逻辑读却只有473

查询一
SYS@standby1/2011-05-04 20:06:54>select /*+ leading(a) use_nl(b a) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

       4128  consistent gets
        740  physical reads
查询二
SYS@standby1/2011-05-04 19:58:02>select /*+ leading(a) use_nl(b a) index(a ind_test_t1)*/  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

      6088  consistent gets
       3252  physical reads
查询五
SSYS@standby1/2011-05-04 20:19:12>select /*+ leading(a) use_nl(b a) index(a pk_test_id)*/  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.id between 1000 and 8000  and b.t2 between 10000 and 20000;
        473  consistent gets
          1  physical reads

查询一二返回7行数据 查询五返回836行数据

分析:查询一使用全表扫描扫描外循环表,查询二使用t1列的索引 clustor factor 接近行数 ,查询五使用id列的索引 clustor factor 接近块数
由此可见,当对驱动表查询时可以通过 clustor factor 接近块数的索引  通过将过滤查询条件转换为驱动查询条件(filter =》access)减少逻辑读的数量(计划五),当使用的索引 clustor factor 接近行数 时由于读取每行差不多就要读取一个数据块,还有额外的索引块的扫描的成本,导致物理读和逻辑读数量更大(计划二)


查询三
SYS@standby1/2011-05-04 19:57:26>select /*+ leading(b) use_nl(a b) */  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

        323  consistent gets
          0  physical reads
查询四
SYS@standby1/2011-05-04 19:57:24>select /*+ leading(b) use_nl(a b) index(b ind_test2_t2)*/  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and a.t1 between 100 and 105 and b.t2 between 10000 and 20000;

       4554  consistent gets
         64  physical reads
查询六
SYS@standby1/2011-05-04 20:23:01>select /*+ leading(b) use_nl(a b) index(b pk_test2_id)*/  a.id,b.id,a.str1,b.str1 from test_tab a,test_tab2 b where a.id=b.id and b.id between 1000 and 8000  and b.t2 between 10000 and 20000;

        533  consistent gets
          0  physical reads

查询三四返回7行数据 查询六返回836行数据
分析:结论和上面类似 只是想提供佐证而已,不过有点区别就是,查询六的代价却比查询三要大,也许跟返回的数据行数有关系的吧,以后学习再分析??????


=============================================================================================================================================

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24890594/viewspace-694545/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-08

  • 博文量
    32
  • 访问量
    52731