AW: impdp and indexes

Hi Orlando,  

I think there are many ways to achieve this. The following trick should work:  

After creating (loading) the indexes in unusable state, you can create a function that will rebuild an index:  

FUNCTION fu_rebuild_index(p_index_name VARCHAR2, p_dop NUMBER) RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION; /* this is important, because the function will be called from pipelined function*/ BEGIN
EXECUTE IMMEDIATE 'alter index ' || p_index_name || ' rebuild parallel ' || p_dop; /* check the syntax */
RETURN 1;
END fu_rebuild_index;  

Then you create a pipelined a function, the function should enable parallel processing (using parallel_enable)
and accept strongly defined cursor.  

FUNCTION dummy(p_cursor t_parallel_test_ref_cursor) RETURN t2_list PIPELINED PARALLEL_ENABLE(PARTITION p_cursor BY HASH(column_value)) IS ret t2;
x VARCHAR2(33);
BEGIN
LOOP
FETCH p_cursor INTO x;
EXIT WHEN p_cursor%NOTFOUND;
ret.id := fu_rebuild_index(x);
PIPE ROW(ret);
END LOOP;
END dummy;  

Then you can call this SQL:  

SELECT count(*) FROM mypackage.dummy(CURSOR(SELECT /*+ parallel(t 16) */ index_name COLUMN_VALUE FROM table_hold_idx_name t),16)  

You have to define the two functions in a package e.g. mypackage. In the package you have also to define the cursor type:  

TYPE xxx IS RECORD(column_value VARCHAR2(200)); TYPE t_parallel_test_ref_cursor IS REF CURSOR RETURN xxx;  

I have often used this trick (for other tasks) and has proven itself, it should also work for your case.  

Regards
Ahmed Fikri          

-----Original-Nachricht-----
Betreff: Re: impdp and indexes
Datum: 2020-06-19T22:49:37+0200
Von: "Orlando L" <oralrnr_at_gmail.com>
An: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>      

To clarify, index creation is running one index at a time, although it uses parallelism for that index. I would like to create multiple indexes in parallel at the same time, all of them in turn creating indexes in parallel.

On Fri, Jun 19, 2020 at 3:17 PM Orlando L <oralrnr_at_gmail.com <mailto:oralrnr_at_gmail.com> > wrote:

  I am importing a 9TB warehouse into another server. the index recreation   part seems to take forever. I have used EXCLUDE=INDEX option before   elsewhere, but not in this run. But creating index creation scripts can   be somewhat tedious, with indexes and partitions. etc if I go with the   exclude index option. How do you guys speed up index creation with impdp?   I also ran into this link
  <https://mikedietrichde.com/2015/04/10/parallel-index-creation-with-data-pump-import/>   , which says that the patch may or may not improve the situation :(    

  Plus, I also see that the index creation is running in parallel with the   degree I chose for the IMPDP task. I am guessing I can bump up the degree   from current 12 to say 24 since most resources are idle. We have 24   cores, plenty of memory and nothing else is running in this server till I   complete this setup.    

  PS. What is the deal with session long ops? It shows that that import   would be over in 2 seconds for about 20 hours.    

  Orlando.


--
http://www.freelists.org/webpage/oracle-l