Home » SQL & PL/SQL » SQL & PL/SQL » Creating index on a table having 495 million record.
Creating index on a table having 495 million record. [message #317247] Wed, 30 April 2008 04:25 Go to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Hi Experts,

I want to create an index on an existing table having no constraint.
It has 495 million records.
Please let me know what is the optimized way to create index in
this scenario.

Thanks in advance for westing your precious time for me.

Regards,
Gaurav
Re: Creating index on a table having 495 million record. [message #317249 is a reply to message #317247] Wed, 30 April 2008 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use parallel (if your IO subsystem allows it) and nologging clauses.

Regards
Michel
Re: Creating index on a table having 495 million record. [message #317314 is a reply to message #317249] Wed, 30 April 2008 07:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's going to use up a lot of temp space. If you don't have enough, you may need to allocate more or - and I think this works but not 100% sure - partition the index.

Logically, that should break the big SORT job required for the index into manageable chunks. Perhaps someone else here can verify whether it works or not.

Ross Leishman
Re: Creating index on a table having 495 million record. [message #317339 is a reply to message #317247] Wed, 30 April 2008 12:54 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Make sure you have your extent management set up correctly.

If you are using Locally Managed Tablespaces, you wont need to, but if you have initial and next extents defaulting to 16k each, it will take forever.
Re: Creating index on a table having 495 million record. [message #317667 is a reply to message #317247] Fri, 02 May 2008 07:21 Go to previous messageGo to next message
asharch1982
Messages: 1
Registered: May 2008
Junior Member
you have to use index partitioning concept
Re: Creating index on a table having 495 million record. [message #317673 is a reply to message #317667] Fri, 02 May 2008 07:55 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
incorrect.
Re: Creating index on a table having 495 million record. [message #318285 is a reply to message #317247] Tue, 06 May 2008 06:29 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Is the table partitioned?
Re: Creating index on a table having 495 million record. [message #318291 is a reply to message #317247] Tue, 06 May 2008 06:47 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
With that much data, it almost certainly would be worth it to start using partitioned tables and indexes.

On a side note, I hadn't considered using a partitioned index on a non partitioned table before. Don't know why I never thought about it, but I guess usually if you have a lot of data, and pay for the partitioning license, you end up partitioning both. A technique to keep in mind though.
Re: Creating index on a table having 495 million record. [message #319020 is a reply to message #317247] Thu, 08 May 2008 14:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
That is Rosco for you, a man with a practical wisdom.

Kevin
Re: Creating index on a table having 495 million record. [message #319070 is a reply to message #319020] Thu, 08 May 2008 22:41 Go to previous messageGo to next message
psingh7777
Messages: 22
Registered: August 2007
Location: New Delhi
Junior Member

What I suggest is

A) Take export of that table,(verify the dump file using show=Y command)

B) truncate table
C) create index
d) do import of table with ignore = 'Y'

please check constraints and other things before doing this

Re: Creating index on a table having 495 million record. [message #319136 is a reply to message #319070] Fri, 09 May 2008 02:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And in your opinion will this entire process be:
a) Quicker
b) About the same
c) Slower
d) So much slower that it beggars belief

Than simply doing a 'CREATE INDEX'

(hint: the answer isn't one of the first 3)

Why would you think that creating the index AND inserting all of the data into the table would be quicker than just creating the index?
Re: Creating index on a table having 495 million record. [message #319493 is a reply to message #319136] Sun, 11 May 2008 22:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
At the risk of invoking JR's wrath, there is a kernal of wisdom in @psingh7777's approach.

If the problem is insufficient sort-space to create the index, then a conventional-path insert with interim commits (to avoid blowing UNDO as well) would do the trick. Slow, I grant you, but it would work.

Another equally slow method but equally viable would be the SQL*Plus COPY command.

A faster method using the rebuild-the-table approach would be Rowid Range (search if you are interested).

But the point remains that rebuilding an entire table is not a practical approach to index maintenance.

Ross Leishman
Re: Creating index on a table having 495 million record. [message #319618 is a reply to message #319493] Mon, 12 May 2008 06:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I stand (or rather sit) corrected. If that's the problem that you're having, then a slow approach is still faster than one that errors.
Previous Topic: Problem with Utl_file.fopen
Next Topic: Need to know about BULK Collect with simple example..help!
Goto Forum:
  


Current Time: Fri Dec 02 12:14:55 CST 2016

Total time taken to generate the page: 0.23907 seconds