#################################################################################
----------例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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10678398/viewspace-715056/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10678398/viewspace-715056/