|
| 3 |PX SEND QC (RANDOM) | :TQ10000
| 463K|28M|4940 (1)| 00:01:00 | | |Q1,00 | P->S | QC (RAND)|
| 4 | INLIST ITERATOR
|
| | |
|
| | |Q1,00 | PCWC |
|
| 5 |PX PARTITION HASH ITERATOR|
| 463K|28M|4940 (1)| 00:01:00 |KEY(I) |KEY(I) |Q1,00 | PCWC |
|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|*6 | INDEX RANGE SCAN | INX_TRANS_ID_DATE_TYPE | 463K|28M|4940 (1)| 00:01:00 |KEY(I) |KEY(I) |Q1,00 | PCWP |
|
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(("T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT002' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT017' OR
"T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT023' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT025' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT034' OR
"T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT057' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT079' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT098' OR
"T"."COUNTER_ID"=U'A-GD-SZ-0755-CT270' OR "T"."COUNTER_ID"=U'A-GD-SZ-0755-CT273') AND "T"."TRANSACTION_DATE">=U'2010-01-01' AND
"T"."TRANSACTION_DATE" explain plan for updateview_tra_transaction t
2 set t.transaction_ismodify = 1
3 where t.transaction_date >= '2010-01-01'
4 and t.transaction_dateselect * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1295887258
---------------------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT |
| 577K|35M| 259K(1)| 00:51:54 | | |
| 1 |UPDATE
| VIEW_TRA_TRANSACTION | | |
|
| | |
| 2 | PARTITION HASH INLIST|
| 577K|35M| 259K(1)| 00:51:54 |KEY(I) |KEY(I) |
|*3 |TABLE ACCESS FULL | VIEW_TRA_TRANSACTION | 577K|35M| 259K(1)| 00:51:54 |KEY(I) |KEY(I) |
---------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."TRANSACTION_DATE">=U'2010-01-01' AND ("T"."TRANSACTION_TYPE"=0 OR
"T"."TRANSACTION_TYPE"=1 OR "T"."TRANSACTION_TYPE"=6 OR "T"."TRANSACTION_TYPE"=7) AND
("T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT002' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT017' OR
"T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT023' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT025' OR
"T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT034' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT057' OR
"T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT071' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT079' OR
"T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT098' OR "T"."COUNTER_ID"=U'A-GD-SHZH-0755-CT106' OR
"T"."COUNTER_ID"=U'A-GD-SZ-0755-CT270' OR "T"."COUNTER_ID"=U'A-GD-SZ-0755-CT273') AND
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
"T"."TRANSACTION_DATE" select count(*) from view_tra_transaction t where
2 t.counter_id in ('A-GD-SHZH-0755-CT034','A-GD-SZ-0755-CT270','A-GD-SHZH-0755-CT025','A-GD-SHZH-0755-CT023',
3
'A-GD-SHZH-0755-CT057','A-GD-SHZH-0755-CT079','A-GD-SZ-0755-CT273','A-GD-SHZH-0755-CT002',
4
'A-GD-SHZH-0755-CT017','A-GD-SHZH-0755-CT071','A-GD-SHZH-0755-CT106','A-GD-SHZH-0755-CT098');
COUNT(*)
----------
85714
SQL> select count(*) from view_tra_transaction t where
2 t.counter_id in ('A-GD-SHZH-0755-CT034','A-GD-SZ-0755-CT270','A-GD-SHZH-0755-CT025','A-GD-SHZH-0755-CT023',
3
'A-GD-SHZH-0755-CT057','A-GD-SHZH-0755-CT079','A-GD-SZ-0755-CT273','A-GD-SHZH-0755-CT002',
4
'A-GD-SHZH-0755-CT017','A-GD-SHZH-0755-CT098');
COUNT(*)
----------
67903
表一共1千多万数据,加索引hint就会跟快
[ 本帖最后由 guoq.lee 于 2010-2-5 15:40 编辑 ]
|