Oracle分区,索引,测试(2)(四)
->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 23 | 437 | 1 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| CITY | 23 | 437 | 1 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | PX BLOCK ITERATOR | | 1820K| 27M| 14604 (3)| 00:02:56 |KEY(I) |KEY(I) | Q1,01 | PCWC | |
|* 13 | TABLE ACCESS FULL | SALES_DATA | 1820K| 27M| 14604 (3)| 00:02:56 |KEY(I) |KEY(I) | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("SALES_DATA"."CITY_ID"="CITY"."CITY_ID")
13 - filter("SALES_DATA"."SALES_DATE"=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"SALES_DATA"."SALES_DATE"=TO_DATE('2009-03-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Statistics
-----------------------------------------------------------
40 recursive calls
234602 consistent gets direct
234602 physical reads direct
0 recovery blocks read
0 redo buffer allocation retries
用了22秒 没有走分区索引
强制使用索引
Sql代码
select /*+ index(sales_data INDEX_SALES_DATA_PARTITION) */ city,sum(sales_amount) from sales_data
inner join city using (city_id)
where sales_date = to_date ('2009-01-1' ,'yyyy-mm-dd') or sales_date = to_date ('2009-03-5' ,'yyyy-mm-dd')
group by city;
50,296ms elapsed
Plan hash value: 1538767871
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 805 | 240K (1)| 00:48:12 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 23 | 805 | 240K (1)| 00:48:12 | | | Q1,03 | P->S | QC (RAND) |
| 3 | SORT GROUP BY | | 23 | 805 | 240K (1)| 00:48:12 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 23 | 805 | 240K (1)| 00:48:12 | | | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | 23 | 805 | 240K (1)| 00:48:12 | | | Q1,02 | P->
P | HASH |
| 6 | SORT GROUP BY | | 23 | 805 | 240K (1)| 00:48:12 | | | Q1,02 | PCWP | |
|* 7 | HASH JOIN | | 1820K| 60M| 240K (1)| 00:48:11 | | | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | 23 | 437 | 1 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 23 | 437 | 1 (0)| 00:00:01 | | | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 23 | 437 | 1 (0)| 00:00:01 | | | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL | CITY | 23 | 437 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 12 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 13 | PX RECEIVE | | 1820K| 27M| 240K (1)| 00:48:11 | | | Q1,02 | PCWP | |
| 14 | PX SEND HASH | :TQ10000 | 1820K| 27M| 240K (1)| 00:48:11 | | | | S->P | HASH |
| 15 | INLIST ITERATOR | | | | | | | | | | |
| 16 | PARTITION RANGE ITERATOR | | 1820K| 27M| 240K (1)| 00:48:11 |KEY(I) |KEY(I) | | | |
| 17 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES_DATA | 1820K| 27M| 240K (1)| 00:48:11 |KEY(I) |KEY(I) | | | |
|* 18 | INDEX RANGE SCAN | INDEX_SALES_DATA_PARTITION | 1831K| | 4884 (1)| 00:00:59 |KEY(I) |KEY(I) | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------