Re: Is there a way to create, by default, an index in UNUSABLE state?
Date: Sat, 17 Aug 2019 20:46:25 +0100
Message-ID: <787C5B19-C650-436B-972A-6DD738D768AC_at_vishalgupta.com>
DBMS_INDEX_UTL is documented slightly in following MOS note.
How Parallel Execution Differs Between CREATE INDEX and DBMS_INDEX_UTL (Doc ID 959905.1)
Regards,
Vishal Gupta
From: <oracle-l-bounce_at_freelists.org> on behalf of Luis Santos <lsantos_at_pobox.com>
Reply-To: <lsantos_at_pobox.com>
Date: Friday, 16 August 2019 at 18:11
To: ORACLE-L <oracle-l_at_freelists.org>
Subject: Is there a way to create, by default, an index in UNUSABLE state?
Hi ORACLE-L!
I'm planning to perform a big schema transfer, using impdp by network_link. The schema has few big tables, with several partitions, subpartitions and indexes. So we have just eight tables but 3760 distinct index segments.
When using network_link feature the impdp doesn't create the indexes concurrently.
I measured the time impdp took to create the indexes, and it last 19 hours. We are performing several tests before the real production move.
I have done a test, putting all indexes from these 8 tables unusable, and rebuilded them using (undocomented, I know...) package dbms_index_utl, procedure build_schema_indexes. The concurrent rebuild tooks 45 minutes, indeed using all machine (CPU, IO) resources.
I was wondering, for next test, a way to make impdp create the indexes already unusable. We are using TABLE_EXISTS_ACTION=REPLACE on IMPDP, as each day the partitions changes (day rolling window) in the source database.
If this were possible the process would be too much easily to design. I don't want to generate indexes script and manually create them, appending the UNUSABLE clause...
--
Att
Luis Santos
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Aug 17 2019 - 21:46:25 CEST