01:01:03 SQL> alter session set workarea_size_policy=manual
Session altered.
01:13:44 SQL> alter session set hash_area_size=1073741824
Session altered.
01:13:44 SQL> alter session set sort_area_size=1073741824
Session altered.
01:13:44 SQL> select force_logging from v$database
FOR
---
YES
01:13:44 SQL> alter database no force logging
Database altered.
01:13:46 SQL>create index imei_prod.usage_profile_idx1 on imei_prod.usage_profile(msisdn)
01:13:46 2 nologging
01:13:46 3 compress
01:13:46 4 parallel 16
01:13:46 5 local
01:13:46 6 (
01:13:46 7 partition USAGE_199401 tablespace indx nologging compress,
01:13:46 8 partition USAGE_200106 tablespace indx nologging compress,
01:13:46 9 partition USAGE_200202 tablespace indx nologging compress,
01:13:46 10 partition USAGE_200207 tablespace indx nologging compress,
...
01:13:46 56 partition USAGE_200809 tablespace indx nologging compress,
01:13:46 57 partition USAGE_200810 tablespace indx nologging compress,
01:13:46 58 partition USAGE_200811 tablespace indx nologging compress
01:13:46 59 )
01:13:46 60 /
Index created
Elapsed: 01:58:57.99
03:12:44 SQL> SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi')
03:12:44 2 logtime, se.program, (value/1048576) VALUE
03:12:44 3 FROM gv$sesstat st, gv$statname sn, gv$session se
03:12:44 4 WHERE username = 'MEYERM'
03:12:44 5 and sn.name = 'redo size'
03:12:44 6 AND sn.statistic# = st.statistic#
03:12:44 7 AND st.sid = se.sid
03:12:44 8 /
Redo
Generated
SID USERNAME Logon Time PROGRAM in MB
--------------------------------------------------------------
146 MEYERM 26-nov-08 01:13 sqlplus@pxxxx1 (TNS V1-V3) 116.583
03:12:46 SQL> alter database force logging
Database altered.
03:12:46 SQL> alter index imei_prod.usage_profile_idx1 logging;
Index altered.
Friday, December 5, 2008
Fast Index Creation
I recently had to create a new index on a fairly large date ranged partitioned table. I tried a few permutations on test, but found the following steps to be the fastest;
Labels:
Administration,
Performance
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment