Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to create a Oracle Index without locking the column.

Posted on 2011-02-23
5
Medium Priority
?
685 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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 …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

604 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