Solved

How to create a Oracle Index without locking the column.

Posted on 2011-02-23
5
677 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 63 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 62 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now