#################################################################################
----------例1
SQL> explain plan for select order_id from t_to_order_info,t_s_employee
2 where t_to_order_info.employee_id=t_s_employee.employee_id
3 and employee_name='劉玲';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 4053182657
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2495 | 59880 | 4
| 1 | TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_INFO | 2133 | 21330 | 4
| 2 | NESTED LOOPS | | 2495 | 59880 | 4
|* 3 | TABLE ACCESS FULL | T_S_EMPLOYEE | 1 | 14 |
|* 4 | INDEX RANGE SCAN | TICKETORDER_BELONG_FK | 7325 | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMPLOYEE_NAME"=U'\5218\7F8E\73B2')
4 - access("T_TO_ORDER_INFO"."EMPLOYEE_ID"="T_S_EMPLOYEE"."EMPLOYEE_ID")
17 rows selected
索引TICKETORDER_BELONG_FK是表t_to_order_info上employee_id的索引，
表t_s_employee由'劉玲'條件過濾出來的資料在去一行一行匹配
t_to_order_info表上的內容，因為表連線用的是employee_id，所以他
可以走索引，換種想法，符合'劉玲'的每行資料中都有employee_id，
通過這一部分employee_id自然就能通過索引的方式訪問t_to_order_info
這張表。
#################################################################################
----------例2
SQL> explain plan for Select * From t_to_order_info,t_to_order_change
2 Where t_to_order_info.order_id=t_to_order_change.order_id
3 And t_to_order_change.employee_id=1014;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 444496446
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byte
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 353 | 51
| 1 | NESTED LOOPS | | 353 | 51
| 2 | TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_CHANGE | 346 | 40
|* 3 | INDEX RANGE SCAN | TO_CHANGEORDER_OPERATOR_FK | 346 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_INFO | 1 | 29
|* 5 | INDEX UNIQUE SCAN | PK_T_TO_ORDER_INFO | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T_TO_ORDER_CHANGE"."EMPLOYEE_ID"=1014)
5 - access("T_TO_ORDER_INFO"."ORDER_ID"="T_TO_ORDER_CHANGE"."ORDER_ID")
18 rows selected
TO_CHANGEORDER_OPERATOR_FK為表T_TO_ORDER_CHANGE上employee_id的索引
PK_T_TO_ORDER_INFO為表T_TO_ORDER_INFO上order_id的索引
上面兩個例子都說明了訪問大表的時候，大表本身沒有條件，但是連線的字
段上有索引仍然也可以通過這個連線的欄位的方式來進行索引訪問
#################################################################################
----------例3
SQL> explain plan for select order_id from t_HO_Order_Info o
2 where
3 o.reserv_time >=to_date('2011-03-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
4 and o.reserv_time <=to_date('2011-04-01 00:00:00','yyyy-mm-dd HH24:MI:SS');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 664508847
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23793 | 325K| 6148 (5
|* 1 | VIEW | index$_join$_001 | 23793 | 325K| 6148 (5
|* 2 | HASH JOIN | | | |
|* 3 | INDEX RANGE SCAN | IDX_RESERV_TIME | 23793 | 325K| 145 (7
| 4 | INDEX FAST FULL SCAN| PK_T_HO_ORDER_INFO | 23793 | 325K| 5965 (3
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("O"."RESERV_TIME">=TO_DATE('2011-03-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "O"."RESERV_TIME"<=TO_DATE('2011-04-01 00:00:00',
hh24:mi:ss'))
2 - access(ROWID=ROWID)
3 - access("O"."RESERV_TIME">=TO_DATE('2011-03-01 00:00:00', 'yyyy-mm-dd
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
hh24:mi:ss') AND "O"."RESERV_TIME"<=TO_DATE('2011-04-01 00:00:00',
hh24:mi:ss'))
22 rows selected
注意這裡走了索引IDX_RESERV_TIME，但是再多新增一個欄位進來，下面這種情況
SQL> explain plan for select order_id,use_rule from t_HO_Order_Info o
2 where
3 o.reserv_time >=to_date('2011-03-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
4 and o.reserv_time <=to_date('2011-04-01 00:00:00','yyyy-mm-dd HH24:MI:SS');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3126163901
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23793 | 371K| 16957 (4)| 00:03
|* 1 | TABLE ACCESS FULL| T_HO_ORDER_INFO | 23793 | 371K| 16957 (4)| 00:03
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("O"."RESERV_TIME">=TO_DATE('2011-03-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "O"."RESERV_TIME"<=TO_DATE('2011-04-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
15 rows selected
這裡就是走的全表掃描了，這是為什麼呢？走索引與否與搜尋的欄位的數量還有關？
再做如下的SQL
時間跨度小下，可以走索引
--
select * from t_HO_Order_Info o
where o.reserv_time >=to_date('2011-03-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
and o.reserv_time <=to_date('2011-03-03 00:00:00','yyyy-mm-dd HH24:MI:SS');
將時間跨度拉長到一個月的時候
--
select * from t_HO_Order_Info o
where o.reserv_time >=to_date('2011-03-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
and o.reserv_time <=to_date('2011-03-03 00:00:00','yyyy-mm-dd HH24:MI:SS');
這種情況就沒走索引了，這個是可以理解的，那麼問題就集中在了，為什麼單單查詢
order_id這個欄位的時候，跨度即使是一個月還是走的索引，那是什麼索引?
而下面這種情況也是不走索引的
select use_rule from t_HO_Order_Info o
where
o.reserv_time >=to_date('2011-03-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
and o.reserv_time <=to_date('2011-04-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
將問題定位到是因為查詢的欄位order_id上面所以才走的索引，因為order_id上面本身
也有索引，然後我再次試驗了一吧，換成單查兩個有索引的欄位，然後不行，那麼說明
原因應該是跟order_id是主鍵有關（這裡都不走索引的情況是因為時間跨度大了，整個
部分是研究在時間跨度大不應該走索引確走了索引的情況）
#################################################################################
----------例4
回到實際中的例子來，對於一條SQL分別用兩個時間跨度來進行比較，看一下查一個月和
兩天的情況，分析一下t_ho_order_info走索引與否對整個其他連線表走索引與否的影響
--跨度為一個月的
SQL> explain plan for select p.Province_Name as 省市,sum(1) as 訂單量
2 from t_HO_Order_Info o
3 inner join t_HO_Order_Statistics s ON s.Order_ID=o.Order_ID
4 inner join t_HP_Hotel h ON h.Hotel_ID=o.Hotel_ID
5 inner join t_SD_Province p ON p.Province_ID=h.Province_ID
6 where o.reserv_time >=to_date('2011-03-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
7 and o.reserv_time <=to_date('2011-04-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
8 and (o.use_rule not in(1,2) or o.use_rule is null)
9 group by p.Province_Name;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 492412594
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41 | 1927 | 18
| 1 | HASH GROUP BY | | 41 | 1927 | 18
|* 2 | HASH JOIN | | 22462 | 1030K| 18
| 3 | TABLE ACCESS FULL | T_SD_PROVINCE | 41 | 492 |
|* 4 | HASH JOIN | | 22462 | 767K| 18
|* 5 | HASH JOIN | | 22462 | 636K| 17
| 6 | VIEW | index$_join$_004 | 12608 | 110K|
|* 7 | HASH JOIN | | | |
| 8 | INDEX FAST FULL SCAN| PK_T_HP_HOTEL | 12608 | 110K|
| 9 | INDEX FAST FULL SCAN| HBASEINFO_PROVINCE_FK | 12608 | 110K|
|* 10 | TABLE ACCESS FULL | T_HO_ORDER_INFO | 22462 | 438K| 17
| 11 | INDEX FAST FULL SCAN | HOBASEINFO_STATINFO_FK | 1742K| 9M| 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - access("P"."PROVINCE_ID"="H"."PROVINCE_ID")
4 - access("S"."ORDER_ID"="O"."ORDER_ID")
5 - access("H"."HOTEL_ID"="O"."HOTEL_ID")
7 - access(ROWID=ROWID)
10 - filter("O"."RESERV_TIME">=TO_DATE('2011-03-01 00:00:00', 'yyyy-mm-dd hh24
"O"."RESERV_TIME"<=TO_DATE('2011-04-01 00:00:00', 'yyyy-mm-dd hh24
("O"."USE_RULE" IS NULL OR "O"."USE_RULE"<>1 AND "O"."USE_RULE"<>2
29 rows selected
--跨度為兩天的
SQL> explain plan for select p.Province_Name as 省市,sum(1) as 訂單量
2 from t_HO_Order_Info o
3 inner join t_HO_Order_Statistics s ON s.Order_ID=o.Order_ID
4 inner join t_HP_Hotel h ON h.Hotel_ID=o.Hotel_ID
5 inner join t_SD_Province p ON p.Province_ID=h.Province_ID
6 where o.reserv_time >=to_date('2011-03-01 00:00:00','yyyy-mm-dd HH24:MI:SS')
7 and o.reserv_time <=to_date('2011-03-03 00:00:00','yyyy-mm-dd HH24:MI:SS')
8 and (o.use_rule not in(1,2) or o.use_rule is null)
9 group by p.Province_Name;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2502358448
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41 | 1927
| 1 | HASH GROUP BY | | 41 | 1927
|* 2 | HASH JOIN | | 1451 | 68197
|* 3 | HASH JOIN | | 1451 | 59491
| 4 | TABLE ACCESS FULL | T_SD_PROVINCE | 41 | 492
|* 5 | HASH JOIN | | 1451 | 42079
|* 6 | TABLE ACCESS BY INDEX ROWID| T_HO_ORDER_INFO | 1451 | 29020
|* 7 | INDEX RANGE SCAN | IDX_RESERV_TIME | 1537 |
| 8 | VIEW | index$_join$_004 | 12608 | 110
|* 9 | HASH JOIN | | |
| 10 | INDEX FAST FULL SCAN | PK_T_HP_HOTEL | 12608 | 110
| 11 | INDEX FAST FULL SCAN | HBASEINFO_PROVINCE_FK | 12608 | 110
| 12 | INDEX FAST FULL SCAN | HOBASEINFO_STATINFO_FK | 1742K| 9
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."ORDER_ID"="O"."ORDER_ID")
3 - access("P"."PROVINCE_ID"="H"."PROVINCE_ID")
5 - access("H"."HOTEL_ID"="O"."HOTEL_ID")
6 - filter("O"."USE_RULE" IS NULL OR "O"."USE_RULE"<>1 AND "O"."USE_RULE"<>2)
7 - access("O"."RESERV_TIME">=TO_DATE('2011-03-01 00:00:00', 'yyyy-mm-dd hh24
"O"."RESERV_TIME"<=TO_DATE('2011-03-03 00:00:00', 'yyyy-mm-dd hh24
9 - access(ROWID=ROWID)
30 rows selected
分析：
T_SD_PROVINCE沒有走索引是因為Province_ID欄位在T_HO_ORDER_INFO上沒有索引，仍然是
有關於連線欄位的問題，這裡同樣也只能證明上面的問題，這裡T_HO_ORDER_INFO是否走索
引好像沒有影響到其他的表，T_HO_ORDER_INFO是否走索引會影響到其他表走索引的情況嗎
再用下面的SQL做個簡單的實驗，依然是以時間跨度來區分
Select * From t_to_order_info aa,t_to_order_change cc
Where aa.order_id=cc.order_id
And aa.create_time Between to_date('2011-01-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS')
and to_date('2011-01-03 00:00:00','yyyy/MM/DD/ HH24:MI:SS')
Select * From t_to_order_info aa,t_to_order_change cc
Where aa.order_id=cc.order_id
And aa.create_time Between to_date('2011-01-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS')
and to_date('2011-03-03 00:00:00','yyyy/MM/DD/ HH24:MI:SS')
跨度為2個月的那條SQL兩張表皆是全表掃描
跨度為2天的那條SQL執行計劃如下
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1971526149
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5039 | 7341K| 1
|* 1 | FILTER | | | |
| 2 | NESTED LOOPS | | 5039 | 7341K| 1
| 3 | TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_INFO | 4851 | 1392K|
|* 4 | INDEX RANGE SCAN | IDX_T_TO_ORDER_1 | 4851 | |
| 5 | TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_CHANGE | 1 | 1198 |
|* 6 | INDEX RANGE SCAN | TO_RELATIONCHANGE_FK | 1 | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE('2011-01-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS')<=TO_DATE('
00:00:00','yyyy/MM/DD/ HH24:MI:SS'))
4 - access("AA"."CREATE_TIME">=TO_DATE('2011-01-01 00:00:00','yyyy/MM/DD/ HH2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"AA"."CREATE_TIME"<=TO_DATE('2011-01-03 00:00:00','yyyy/MM/DD/ HH2
6 - access("AA"."ORDER_ID"="CC"."ORDER_ID")
22 rows selected
這裡也很好的說明了網上一直說的外來鍵上要建索引的原因就在這個地方，外來鍵的欄位一般會
用來做表連線的字，連線欄位上有索引使得訪問這張表可以以索引的形式
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
最後將問題集中在這條SQL身上來，開發平時說這條SQL查詢的時候不會很慢，也都會走索引
為什麼突然在那一天就非常慢，而且全部都是全表掃描了，道理其實非常簡單了，因為前端
工作人員查詢的時間改變了，時間跨度拉拉長了之後，造成整個SQL裡面最重要的過濾條件那
裡不能再走索引，而根據上面實驗顯示，這樣連線到上面另外兩張大表自然也不會走索引（畢
竟資料量在那裡放起的），Check_Month後面直接改成等於符號後再檢視執行計劃會發現整個
SQL中沒有一個全表掃描，這就是問題的所在，看來SQL效能和前端業務需求的合理性也有非常
緊密的關係
SQL如下：
select P.Province_Name,
H.Hotel_ID,
H.Hotel_Name,
G.Hotel_Grade_Type_Desc,
sum(OP.RmDy_Stay) as RmDy_All,
sum(OP.Comm_Total_Confirm) as Comm_Total_All,
sum(case
when O.Order_Flow_ID between 700 and 720 then OP.RmDy_Confirm
else 0
end) as RmDy,
sum(case
when O.Order_Flow_ID between 700 and 720 then OP.Comm_Total_Confirm
else 0
end) as Comm_Total
from t_HO_Room_Price OP
inner join t_HO_Order_Info O on O.Order_ID = OP.Order_ID
inner join t_hp_hotel_belong HB on HB.hotel_belong = O.hotel_belong
inner join t_HP_Hotel H on H.Hotel_ID = O.Hotel_ID
inner join t_SD_Province P on P.Province_ID = H.Province_ID
inner join t_HD_Hotel_Grade_Type G on G.Hotel_Grade_Type_ID =H.Hotel_Grade_Type_ID
inner join t_HP_ChkPymt_Rule R on R.Hotel_ID = H.Hotel_ID and R.Rmdy_Sep_Date = 0
inner join t_HO_Order_Statistics S on S.Order_ID = OP.Order_ID and S.Check_Month <= '2011-12' and S.Is_Issue_HotelBill = 0
where O.Order_Status_ID <= 700
and HB.FINANCE_TYPE = 1
and H.County_ID = 110000
group by P.Province_Name,
H.Hotel_ID,
H.Hotel_Name,
G.Hotel_Grade_Type_Desc