| Creating index on a table having 495 million record. [message #317247] |
Wed, 30 April 2008 04:25  |
gauravsunil
Messages: 46
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  |
Michel Cadot
Messages: 15238
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
rleishman
Messages: 2563
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 |
coleing
Messages: 92
Registered: February 2008 |
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 |
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 |
joy_division
Messages: 1978
Registered: February 2005
Location: NY |
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 |
michael_bialik
Messages: 419
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 |
smartin
Messages: 1758
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 |
Kevin Meade
Messages: 330
Registered: November 2001 |
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 |
|
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 |
JRowbottom
Messages: 2663
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 |
rleishman
Messages: 2563
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 |
JRowbottom
Messages: 2663
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.
|
|
|
]
Members
Search
Help
Register
Login
Home
