Speed up index creation

To speed up CREATE INDEX for large table or to adjust PGA per session.
Environment
Solaris 10, Oracle database 10.2.0.4

Solution
The most obvious way is to use NOLOGGING and PARALLEL, e.g.

CREATE INDEX index_name ON table_name
(column1, column2)
TABLESPACE ts_name
PARALLEL (DEGREE 6) NOLOGGING;

If it is not fast enough, you need to increase sort area size. Using WORKAREA_SIZE_POLICY = MANUAL and increasing SORT_AREA_SIZE didn't give a good result in my case. PGA was still quite small.

Another option is to play with hidden parameters
_pga_max_size Maximum size of the PGA memory for one process
_smm_max_size maximum work area size in auto mode (serial)
_smm_px_max_size maximum work area size in auto mode (global)

To see your current values log in as SYS and run following query:
(_smm* parameters are already in KiB)

select substr(ksppinm,1,20) param, ksppstvl/1024 "size_KiB", substr(ksppdesc,1,70) description
from   x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and KSPPINM in ('_pga_max_size', 'pga_aggregate_target')
union all
select substr(ksppinm,1,20), to_number(ksppstvl), substr(ksppdesc,1,70)
from   x$ksppi x, x$ksppcv y
where (x.indx = y.indx) and KSPPINM in ('_smm_max_size', '_smm_px_max_size');

for 10.2.0.4 it looks like Oracle automatically sets values as
_pga_max_size = 0.2 * pga_aggregate_target
_smm_max_size = 0.5 * _pga_max_size (it automatically changes when you change _pga_max_size)
_smm_px_max_size = 0.5 * pga_aggregate_target

So by changing pga_aggregate_target you can change PGA size limit for session. You also can change these hidden parameters dynamically and monitor PGA usage, e.g.

alter system set pga_aggregate_target=6G;
alter system set "_pga_max_size"=2G;

These changes affect all sessions.