--并行查询
可以使用并行查询的情况
1. Full table scans, full partition scans, and fast full index scans
2. Index full and range scans, but only if the index is partitioned (at a given time, a partition
can be accessed by a single slave process only; as a side effect, the degree of parallelism
is limited by the number of accessed partitions)
3. Joins (Chapter 10 provides some examples)
4. Set operators
5. Sorts
6. Aggregations create index T46_IDX1 on T46_HX_ACCJ_ATOM_LIST_HIS_Q(set_date)
nologging parallel 4; SQL> select index_name,degree from dba_indexes
2 where index_name='T46_IDX1'; INDEX_NAME DEGREE
------------------------------ ----------------------------------------
T46_IDX1 4 SQL_ID c9ffc6rh2du8p, child number 0
-------------------------------------
select * from T46_HX_ACCJ_ATOM_LIST_HIS_Q where set_date='2013-03-03' Plan hash value: 3257071247 -----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9583 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T46_HX_ACCJ_ATOM_LIST_HIS_Q | 69401 | 18M| 9583 (1)| 00:01:55 |
|* 2 | INDEX RANGE SCAN | T46_IDX1 | 69401 | | 217 (1)| 00:00:03 |
----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("SET_DATE"='2013-03-03') SQL> select count(set_date) from T46_HX_ACCJ_ATOM_LIST_HIS_Q; COUNT(SET_DATE)
---------------
74467523 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0ywk03qx547du, child number 1
-------------------------------------
select count(set_date) from T46_HX_ACCJ_ATOM_LIST_HIS_Q Plan hash value: 1676932122 ------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:12.22 | 4 |
| 2 | PX COORDINATOR | | 1 | | 4 |00:00:12.22 | 4 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 4 | SORT AGGREGATE | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 5 | PX BLOCK ITERATOR | | 0 | 74M| 0 |00:00:00.01 | 0 |
|* 6 | INDEX FAST FULL SCAN| T46_IDX1 | 0 | 74M| 0 |00:00:00.01 | 0 |
------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):
--------------------------------------------------- 6 - access(:Z>=:Z AND :Z<=:Z) 22 rows selected.