one First take a look at what operations: create indexes to do
1. The index key data read into memory
==& gt; If the data in db_cache, this time it's easy to have a lot of the db file scatter read wait
2. The data of the index key sorting
==& gt; Sort_area_size or pga_aggregate_target is not big enough, you need to do to disk sort, there will be a lot of driect path the read/write, in addition, consume a large amount of CPU Time
3. Create a new index segment, the sequence of the index data written to the index created segment inside
==& gt; If the index is very big, so, sometimes also can have redo log related to wait for, such as:
The log buffer space, the log file sync, the log file parallel write etc.
So, big table indexes under construction, can increase the pga, increase the temp tablepace, and use nologging options or parallel,
Such as:
The create index idx_logs on logs (time) nologging parallel 4;
Parallelism generally see CPU number, of course, in the case of the CPU is idle can parallel several more, for a single CPU is not recommended for use in parallel, this would increase the creation time, can also according to the data of v $session_wait, do specific tuning, so can reduce a little time,
Added knowledge:
Check the CPU information: more/proc/cpuinfo
: memory information more/proc/meminfo
Check the operating system information: more/etc/issue
Relevant index concept, please refer to my Blog:
A: Oracle index/database/201110/107271. HTML
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- scenario 1:
SQL> The CREATE index IX_RC_MATCH ON CONTROL_MATCH (ITS_ORDER_NO, TEL) initrans 16 online;
The Index created
Executed in 306.588 seconds
- scene 2:
SQL> The SET TIMING ON;
SQL> The CREATE index IX_RC_MATCH ON CONTROL_MATCH (ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 4 online;
The Index created
Executed in 137.983 seconds
- scene 3:
SQL> The set timing on;
SQL> The CREATE index IX_RC_MATCH ON CONTROL_MATCH (ITS_ORDER_NO, TEL) initrans 16 NOLOGGING online;
The Index created
Executed in 302.689 seconds
- scenario 4:
SQL> The CREATE index IX_RC_MATCH ON CONTROL_MATCH (ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 6 online;
The Index created
Executed in 87.142 seconds
- scene 5:
SQL> The set timing on;
SQL> The CREATE index IX_RC_MATCH ON CONTROL_MATCH (ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL online;
The Index created
Executed in 102.274 seconds
Six - scene:
SQL> The CREATE index IX_RC_MATCH ON CONTROL_MATCH (ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 5 online;
The Index created
Executed in 167.981 seconds
Seven - scene:
SQL> The set timing on;
SQL> The CREATE index IX_RC_MATCH ON CONTROL_MATCH (ITS_ORDER_NO, TEL) initrans 16 NOLOGGING PARALLEL 7 online;
The Index created
Executed in 100.668 seconds
Eight - scene:
SQL> The set timing on;
SQL> The CREATE index IX_RC_MATCH ON CONTROL_MATCH (ITS_ORDER_NO, TEL) initrans 16 PARALLEL 7 online;
The Index created
Executed in 197.809 seconds
CodePudding user response:
Data in the table can be transferred to the temporary table, then on the empty table index, completes the data again to insert to the index tableCodePudding user response:
Business, less as far as possible in parallel;CodePudding user response:
If this production line is not to hang, it means that, not production system bai, may suspend, such as the 2nd floor, move the data, built the index moved backCodePudding user response: