How to create a Oracle Index without locking the column.

Hi ,

How can I create Oracle index without locking the column?
Currently total rows on the targeted table is 50 million of rows and data being updated
every hour.

regards,
titanium0203
titanium0203Asked:
Who is Participating?
 
MrNedCommented:
I believe you can do it in 10g as long as there are no uncomitted transactions on the table. Besides, if it is only updated hourly you should be able to get it created in that time unless you're on old hardware. You could use parallel index create with no logging to really make it fly.
0
 
MrNedCommented:
If you use the ONLINE keyword it will only take a very brief lock (10g and 11g vary slightly).

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm#insertedID6

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
0
 
MrNedCommented:
0
 
titanium0203Author Commented:
Hi MrNed,

FYI, Our Oracle is in 10G.
0
 
jaiminpsoniCommented:
alternatively, You should parallel DML and create online index.

This will allow you to create index without locking the table...

http://www.orafaq.com/wiki/Parallel_Query_FAQ

ALTER TABLE table_name NOPARALLEL;

Check this as well...

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm

Hope this helps....





0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.