关于索引degree设置的问题

--并行查询
可以使用并行查询的情况
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. 

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3797915.html