My current sort_area_size was 64K. Now I will try building index using 2MB sort_area_size.

Tom,

If we need to build an index on a very large table (index size is estimated to be 65 GB... Partitioned global index),
how do we estimate sort area size/temp space for index creation step.

Also, how do we estimate rollback requirement. We are planning to build the index in parallel with nologging.

Thanks.

Hi Tom

Reading about dbms_pclxutil package.if i rebuild the unusable local index using this package - will it be an online operation ( the index which i want to create is a b*tree index - local on a composite partitioned table).

Thanks
Ajeet

no, if you do this and query v$sql, we can see exactly what is done:

ops$tkyte@ORA10GR2> select sql_text from v$sql where upper(sql_text) like '%T_IDX%';

SQL_TEXT
-------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSD
ATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "OPS$TKYTE"."T_IDX" r
ebuild partition "PART2" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSD
ATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "OPS$TKYTE"."T_IDX" r
ebuild partition "PART1" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN
:= FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "OPS$TKYTE"."T_IDX
" rebuild partition "JUNK" parallel (degree 4)'); :mydate := next_date; IF brok
en THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSD
ATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "OPS$TKYTE"."T_IDX" r
ebuild partition "JUNK" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN
:= FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "OPS$TKYTE"."T_IDX
" rebuild partition "PART2" parallel (degree 4)'); :mydate := next_date; IF bro
ken THEN :b := 1; ELSE :b := 0; END IF; END;

select sql_text from v$sql where upper(sql_text) like '%T_IDX%'
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN
:= FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "OPS$TKYTE"."T_IDX
" rebuild partition "PART1" parallel (degree 4)'); :mydate := next_date; IF bro
ken THEN :b := 1; ELSE :b := 0; END IF; END;

BEGIN dbms_pclxutil.build_part_index(4,4,'T', 'T_IDX',TRUE); END;



pclxutil just automates the rebuilding of the index using the job queues (so in theory, you can queue your OWN jobs that do the same with any options on the rebuild you wanted!) 

Team,

Help me to understand the number of parallel session spanned on dbms_pclxutil package.

drop table t purge;
create table t 
partition by hash(object_id)
( partition p1 ,
  partition p2,
  partition p3,
  partition p4 ) 
parallel 4  
as
select * from big_table;
alter table t noparallel;
create index t_idx on t(owner,object_type,object_name) local unusable;
column partition_name format a10
select partition_name,partition_position,status
from user_ind_partitions
where index_name  ='T_IDX';

While this command is running from session#1

rajesh@ORA10G> exec dbms_pclxutil.build_part_index(3,4,'T','T_IDX');
INFO: Job #1802 created for partition P1 with 4 slaves
INFO: Job #1803 created for partition P2 with 4 slaves
INFO: Job #1804 created for partition P3 with 4 slaves
INFO: Job #1805 created for partition P4 with 4 slaves

PL/SQL procedure successfully completed.

rajesh@ORA10G>

Monitoring this from session#2

rajesh@ORA10G> select * from dba_jobs_running order by sid;

       SID        JOB   FAILURES LAST_DATE   LAST_SEC THIS_DATE   THIS_SEC   INSTANCE
---------- ---------- ---------- ----------- -------- ----------- -------- ----------
       108       1803                                 11-JAN-2016 19:45:48          0
       138       1804                                 11-JAN-2016 19:45:48          0
       148       1802                                 11-JAN-2016 19:45:48          0

3 rows selected.

rajesh@ORA10G>
rajesh@ORA10G> select qcsid,count(*) as cnt0,
  2             count(distinct sid) cnt1,
  3             count(distinct server_set) cnt2,
  4             count(distinct server#) cnt3
  5  from v$px_sesstat
  6  where qcsid in (select sid from dba_jobs_running)
  7  group by grouping sets( (qcsid),() )
  8  order by qcsid;

     QCSID       CNT0       CNT1       CNT2       CNT3
---------- ---------- ---------- ---------- ----------
       108       3123          9          2          4
       138       3123          9          2          4
       148       3123          9          2          4
                 9369         27          2          4

4 rows selected.

rajesh@ORA10G>

a) requested explicitly to go with 3 jobs per batch so job_id are 108,138,148.
b) Each job has 2 parallel server sets(operating in producer and consumer model) and each server set has 4 px_servers, so in total each job has 2 PX_server_sets * 4 Px_servers_per_server_set + 1 QC = 9 sid's in total.

I am able to understand this, but why does the count(*) shows 3123 for each job? i am unable to get that, please help me to understand.

PS: I am the only user connected this database, no other active user/processing running though.

Thanks Connor.

If you are looking for a session count, wouldnt you be using v$px_session ?

often make these kind of mistakes, thanks for pointing out to v$px_session, completely missed that point. thanks.

Team,

Any option available to use dbms_pclxutil API across the schema objects?

dont see an argument for schema_name in the "build_part_index" method. Please advice.

demo@ORA11G> create table t
  2  partition by hash(object_id)
  3  ( partition p1, partition p2)
  4  as
  5  select * from all_objects;

Table created.

demo@ORA11G> create index t_idx on t(object_name) local unusable;

Index created.

demo@ORA11G> select index_name,partition_name,status
  2  from all_ind_partitions
  3  where index_owner ='DEMO'
  4  and index_name like 'T%';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_IDX      P1         UNUSABLE
T_IDX      P2         UNUSABLE

demo@ORA11G> conn rajesh@ora11g
Enter password:
Connected.
rajesh@ORA11G> alter session set current_schema=DEMO;

Session altered.

rajesh@ORA11G> exec dbms_pclxutil.build_part_index(2,4,'T','T_IDX');
BEGIN dbms_pclxutil.build_part_index(2,4,'T','T_IDX'); END;

*
ERROR at line 1:
ORA-20001: Table name 'T' does not exist or is not partitioned
ORA-06512: at "SYS.DBMS_PCLXUTIL", line 294
ORA-06512: at line 1


rajesh@ORA11G> exec dbms_pclxutil.build_part_index(2,4,'DEMO.T','DEMO.T_IDX');
BEGIN dbms_pclxutil.build_part_index(2,4,'DEMO.T','DEMO.T_IDX'); END;

*
ERROR at line 1:
ORA-20001: Table name 'DEMO.T' does not exist or is not partitioned
ORA-06512: at "SYS.DBMS_PCLXUTIL", line 294
ORA-06512: at line 1


rajesh@ORA11G>

SQL> create table scott.t
  2      partition by hash(object_id)
  3      ( partition p1, partition p2)
  4      as
  5      select * from all_objects;

Table created.

SQL> create index scott.t_idx on scott.t(object_name) local unusable;

Index created.

SQL>
SQL> select index_name,partition_name,status
  2      from all_ind_partitions
  3      where index_owner ='SCOTT'
  4      and index_name like 'T%';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
T_IDX                          P1                             UNUSABLE
T_IDX                          P2                             UNUSABLE

2 rows selected.

SQL>
SQL> conn demo/demo
Connected.

SQL>
SQL> create or replace procedure scott.tmp is
  2  begin
  3     dbms_pclxutil.build_part_index(2,4,'T','T_IDX');
  4  end;
  5  /

Procedure created.

SQL>
SQL> exec scott.tmp

PL/SQL procedure successfully completed.

SQL>     select index_name,partition_name,status
  2      from all_ind_partitions
  3      where index_owner ='SCOTT'
  4      and index_name like 'T%';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
T_IDX                          P1                             USABLE
T_IDX                          P2                             USABLE

2 rows selected.

SQL>

Team,

the above workaround works in 12.2 but not in 11.2.0.4.

any patches/fixes available for 11g database?

demo@ORA11G> create table t
  2  partition by hash(object_id)
  3  ( partition p1, partition p2 )
  4  as
  5  select * from all_objects;

Table created.

demo@ORA11G>
demo@ORA11G> create index t_idx on t(object_name) LOCAL UNUSABLE;

Index created.

demo@ORA11G> select index_name,partition_name,status
  2  from all_ind_partitions
  3  where index_name ='T_IDX'
  4  and index_owner ='DEMO';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_IDX      P1         UNUSABLE
T_IDX      P2         UNUSABLE

demo@ORA11G> conn rajesh@ora11g
Enter password:
Connected.
rajesh@ORA11G> select index_name,partition_name,status
  2  from all_ind_partitions
  3  where index_name ='T_IDX'
  4  and index_owner ='DEMO';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_IDX      P1         UNUSABLE
T_IDX      P2         UNUSABLE

rajesh@ORA11G> create or replace procedure demo.rebuild_local_idx
  2  (p_tabname in varchar2,p_idxname in varchar2)
  3  as
  4  begin
  5     dbms_pclxutil.build_part_index(2,4,p_tabname,p_idxname);
  6  end;
  7  /

Procedure created.

rajesh@ORA11G> exec demo.rebuild_local_idx('T','T_IDX');
BEGIN demo.rebuild_local_idx('T','T_IDX'); END;

*
ERROR at line 1:
ORA-20001: Table name 'T' does not exist or is not partitioned
ORA-06512: at "SYS.DBMS_PCLXUTIL", line 294
ORA-06512: at "DEMO.REBUILD_LOCAL_IDX", line 5
ORA-06512: at line 1


rajesh@ORA11G> create or replace procedure demo.rebuild_local_idx
  2  as
  3  begin
  4     dbms_pclxutil.build_part_index(2,4,'T','T_IDX');
  5  end;
  6  /

Procedure created.

rajesh@ORA11G> exec demo.rebuild_local_idx;
BEGIN demo.rebuild_local_idx; END;

*
ERROR at line 1:
ORA-20001: Table name 'T' does not exist or is not partitioned
ORA-06512: at "SYS.DBMS_PCLXUTIL", line 294
ORA-06512: at "DEMO.REBUILD_LOCAL_IDX", line 4
ORA-06512: at line 1


rajesh@ORA11G> conn demo/demo@ora11g
Connected.
demo@ORA11G> exec dbms_pclxutil.build_part_index(2,4,'T','T_IDX');
INFO: Job #45 created for partition P1 with 4 slaves
INFO: Job #46 created for partition P2 with 4 slaves

PL/SQL procedure successfully completed.

demo@ORA11G> select index_name,partition_name,status
  2  from all_ind_partitions
  3  where index_name ='T_IDX'
  4  and index_owner ='DEMO';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_IDX      P1         USABLE
T_IDX      P2         USABLE

demo@ORA11G>