How to speed up index creation

 

My friend built an index on a million table and it didn't end in half an hour. So we will discuss how to speed up.

 

I. First, let's take a look at the operations to create an index:

1. Read the data of the index key to the memory.

==> If data is not in db_cache, it is easy to have a large number of db file scatter read wait

 

2. Sort the data of the index key

==> If sort_area_size or pga_aggregate_target is not large enough, disk sort is required, and a large number of driect path read/write operations are required. In addition, a large amount of CPU Time is consumed.

 

3. Create a new index segment and write the sorted index data to the created index segment.

==> If the index is large, sometimes there will be redo log wait, such:

Log buffer space, log file sync, log file parallel write, etc.

Therefore, when creating a large table index, you can increase the pga, temp tablepace, and use nologging or parallel options.

For example:

Create index idx_logs on logs (time) nologging parallel 4;

Concurrency generally depends on the number of CPUs. Of course, when the CPU is relatively idle, You can parallel several times. Parallel processing is not recommended for a single CPU, which increases the creation time. You can also perform targeted tuning Based on the data of v $ session_wait to reduce the time.

Additional knowledge:

View cpu information: more/proc/cpuinfo

View memory information: more/proc/meminfo

View the operating system information: more/etc/issue

For more information about index concepts, see my Blog:

Oracle index explanation: http://www.bkjia.com/database/201110/107271.html

Ii. Test

 

I also tested it myself. Test environment: Oracle 11g R2, win7 64bit, CPU T6670 2.2G dual-core, memory: 4G.

1. view the table data volume:

SQL> select count (*) from custaddr;

COUNT (*)

----------

7230464

2. view existing indexes:

SQL> select index_name, index_type from user_indexes where table_name = 'custaddr ';

INDEX_NAME INDEX_TYPE

---------------------------------------------------------

PK_CUSTADDR_TP_723 NORMAL

IX_CUSTADDR_ADDRABB_TP NORMAL

IX_CUSTADDR_TEAMID_TP NORMAL

IX_CUSTADDR_CUSTID_TP NORMAL

IX_CUSTADDR_COMPABB_TP NORMAL

IX_CUSTADDR_AREACODE NORMAL

IX_CUSTADDR_ADDR_TP NORMAL

You have selected 7 rows.

3. Delete the index: IX_CUSTADDR_CUSTID_TP

 

SQL> drop index IX_CUSTADDR_CUSTID_TP;

The index has been deleted.

4. index creation by default:

 

SQL> SET timing on;

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID );

The index has been created.

Used time: 00: 00: 48.37

Unit: s

5. Use the nologging mode:

SQL> drop index IX_CUSTADDR_CUSTID_TP;

The index has been deleted.

Used time: 00: 00: 00.09

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID) NOLOGGING;

The index has been created.

Used time: 00: 00: 34.46

6. Nologging + parallel Mode

SQL> drop index IX_CUSTADDR_CUSTID_TP;

The index has been deleted.

Used time: 00: 00: 00.17

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID) NOLOGGING PARALLEL 2;

The index has been created.

Used time: 00: 00: 52.56

SQL> drop index IX_CUSTADDR_CUSTID_TP;

The index has been deleted.

Used time: 00: 00: 00.07

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID) NOLOGGING PARALLEL 4;

The index has been created.

Used time: 00: 00: 53.44

It seems that the parallel performance is not good on a single CPU.

7. Parallel Mode

SQL> drop index IX_CUSTADDR_CUSTID_TP;

The index has been deleted.

Used time: 00: 00: 00.02

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID) PARALLEL 2;

The index has been created.

Used time: 00: 00: 49.97

SQL> drop index IX_CUSTADDR_CUSTID_TP;

The index has been deleted.

Used time: 00: 00: 00.02

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID) PARALLEL 4;

The index has been created.

Used time: 00: 00: 50.25

 

From the test data above, we can see that 7 million of the data, index creation, is also within 1 minute. However, the performance of Parallel Processing on a single CPU is not obvious, and it is slower than NOLOGGING. Because of resource contention, it may be CPU contention or I/O contention.

 

------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware