31 August, 2011

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 ?

.
.
.



2 comments:

Anonymous said...

By the way the altering the session with PARALLEL DDL would be a good helper:

ALTER SESSION ENABLE PARALLEL DDL;

before the process of index creation.

Anonymous said...

Hi,
Best way to create index for nonpartioned table having 100millions of record. Thanks!