Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to create a Oracle Index without locking the column.

Posted on 2011-02-23
5
Medium Priority
?
695 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:titanium0203
  • 3
5 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 34966617
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
 
LVL 7

Expert Comment

by:MrNed
ID: 34966640
0
 

Author Comment

by:titanium0203
ID: 34966812
Hi MrNed,

FYI, Our Oracle is in 10G.
0
 
LVL 7

Accepted Solution

by:
MrNed earned 252 total points
ID: 34966846
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
 
LVL 5

Assisted Solution

by:jaiminpsoni
jaiminpsoni earned 248 total points
ID: 34968660
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question