===============================================================================================================================================
构建测试环境
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24890594/viewspace-694545/
1417
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
