Improving Index creation speed in Oracle
Improving Index creation speed in Oracle
It is sometimes a time consuming task if you like to create index with much number of rows. For example you are asked to created an index over 1 billion of data. It may take over 6 hours on your computer and you want to make it faster.
With providing several options while creating index you can improve index creation speed dramatically.
1)PARALLEL Option: While creating index oracle must first collect the symbolic key/ROWID pairs with a full-table scan. With Parallel option supplied to the create index script oracle will scan full table parallel based on the number of CPUs, table partitioning and disk configuration and thus do the tasks faster.
On a server that have 6 CPUs you may give parallel 5 as below.
create index table_1_I on table_1(id,code) parallel 5;
2)NOLOGGING Option: With the NOLOGGING option provided while creating index you can restrict database to generate a large redo log. NOLOGGING option generates minimal redo. So you will achieve higher performance.
create index table_1_I on table_1(id,code) parallel 5 nologging;
3)COMPRESS Option: With the COMPRESS option you will enable key compression, which eliminates repeated occurrence of key column values and may substantially reduce storage. Use integer to specify the prefix length (number of prefix columns to compress).
For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.
For nonunique indexes, the valid range of prefix length values is from 1 to the number of key columns. The default prefix length is the number of key columns.
You can use as,
create index table_1_I on table_1(id,code) parallel 5 nologging compress;
4)Index in a bigger block: You can create an index in a tablespace that uses bigger block size. If you have DSS environment then you can do it. This will improve performance while creating index.
You can do it by first creating a tablespace with 32k blocksize and then create index under it,
create tablespace 32k_ts
datafile ‘/u01/32k_file.dbf’
blocksize 32k;
create index table_1_I on table_1(id,code) parallel 5 nologging compress tablespace 32K;
No comments yet.
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Leave a Reply