How can I improve index performance for creating local partitioned index?
When you have multiple CPUs, you can improve indexing performance by creating a local index in parallel. There are two ways to index in parallel: You can create a local partitioned index in parallel in two ways: Use the PARALLEL clause with the LOCAL clause in CREATE INDEX.In this case, the maximum parallel degree is limited to the number of partitions you have. Create an unusable index first, then run the DBMS_PCLXUTIL.BUILD_PART_INDEX utility. This method can result in a higher degree of parallelism, especially if you have more CPUs than partitions. The following is an example for the second method. In this example, the base table has three partitions. We create a local partitioned unusable index first, the run the DBMS_PCLUTIL.BUILD_PART_INDEX, which builds the 3 partitions in parallel (inter-partition parallelism). Also inside each partition, index creation is done in parallel (intra-partition parallelism) with a parallel degree of 2.
