CREATE INDEX ..... PARALLEL

We generally use the PARALLEL keyword to speed up index creation thus : CREATE INDEX ..... PARALLEL n. But also results in the Parallel attribute being set for the index, which means that Oracle may choose to read the index using Parallel Query operators.
If the same index were read by Index Fast Full Scan by many sessions concurrently, the database instance could end up having many PQ slaves reading the index blocks and not buffering it in the SGA (database cache). Every user makes a fresh read call for all the index blocks, and they are not cached and reused.

I generally prefer that every Index be set to NOPARALLEL immediately after creation --- unless it can be proven that Parallel reads of the index would be beneficial.


Here is a table with 1.6million rows and 50 distinct CUST_IDs :

SQL> select partition_name, num_rows from user_tab_partitions

  2  where table_name = 'SALES_FACT';



PARTITION_NAME                   NUM_ROWS

------------------------------ ----------

SF_P_2007                          249307

SF_P_2008                          350691

SF_P_2009                          398842

SF_P_2010                          499781

SF_P_2011                          101374



SQL> 

SQL> drop index sf_cust_b_ndx;



Index dropped.



SQL> create bitmap index sf_cust_b_ndx on SALES_FACT(CUST_ID) local parallel 4 nologging;



Index created.



SQL>

SQL> explain plan for select distinct cust_id from sales_fact;



Explained.



SQL> select * from table(dbms_xplan.display);



PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1638564360



------------------------------------------------------------------------------------------------------------------------------------

------------



| Id  | Operation                         | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT|

PQ Distrib |



------------------------------------------------------------------------------------------------------------------------------------

------------



|   0 | SELECT STATEMENT                  |               |    50 |   150 |   356  (14)| 00:00:05 |     |        |        |      |

           |



|   1 |  PX COORDINATOR                   |               |       |       |            |          |     |        |        |      |

           |



|   2 |   PX SEND QC (RANDOM)             | :TQ10001      |    50 |   150 |   356  (14)| 00:00:05 |     |        |  Q1,01 | P->S |

QC (RAND)  |



|   3 |    HASH UNIQUE                    |               |    50 |   150 |   356  (14)| 00:00:05 |     |        |  Q1,01 | PCWP |

           |



|   4 |     PX RECEIVE                    |               |  1599K|  4687K|   309   (0)| 00:00:04 |     |        |  Q1,01 | PCWP |

           |



|   5 |      PX SEND HASH                 | :TQ10000      |  1599K|  4687K|   309   (0)| 00:00:04 |     |        |  Q1,00 | P->P |

HASH       |



|   6 |       PX BLOCK ITERATOR           |               |  1599K|  4687K|   309   (0)| 00:00:04 |   1 |      5 |  Q1,00 | PCWC |

           |



|   7 |        BITMAP INDEX FAST FULL SCAN| SF_CUST_B_NDX |  1599K|  4687K|   309   (0)| 00:00:04 |   1 |      5 |  Q1,00 | PCWP |

           |



------------------------------------------------------------------------------------------------------------------------------------

------------





14 rows selected.



SQL> 

SQL> alter index sf_cust_b_ndx noparallel;



Index altered.



SQL> 

SQL> explain plan for select distinct cust_id from sales_fact;



Explained.



SQL>  select * from table(dbms_xplan.display);



PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1637766894



---------------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |               |    50 |   150 |   356  (14)| 00:00:05 |       |       |

|   1 |  HASH UNIQUE                  |               |    50 |   150 |   356  (14)| 00:00:05 |       |       |

|   2 |   PARTITION RANGE ALL         |               |  1599K|  4687K|   309   (0)| 00:00:04 |     1 |     5 |

|   3 |    BITMAP INDEX FAST FULL SCAN| SF_CUST_B_NDX |  1599K|  4687K|   309   (0)| 00:00:04 |     1 |     5 |

---------------------------------------------------------------------------------------------------------------



10 rows selected.



SQL> 

Thus, after the Index is created with PARALLEL, the default behaviour for an Index Fast Full Scan is to use Parallel Query.

What other sorts of queries would use PQ (without having specified a Hint) ? When would it be beneficial to allow PQ ?

.
.
.