index on a huge table [message #410691] |
Mon, 29 June 2009 14:55  |
ora1980
Messages: 251 Registered: May 2008
|
Senior Member |
|
|
there is a table which has 6844153 rows, i am trying to create an index on 3 columns on that table, it is taking forever..please advice
|
|
|
Re: index on a huge table [message #410692 is a reply to message #410691] |
Mon, 29 June 2009 15:01   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
I would estimate it to take around 10 minutes on my hardware.
Query V$SESSION_LONGOPS to see if it's in there, and how far it is.
|
|
|
Re: index on a huge table [message #410897 is a reply to message #410691] |
Tue, 30 June 2009 23:51   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
ora1980 wrote on Mon, 29 June 2009 21:55 | there is a table which has 6844153 rows, i am trying to create an index on 3 columns on that table, it is taking forever..please advice
|
Even though it looks like you have been quite precise in your post (even noted the exact number of rows), it is as vague as it can be considering what it is you ask.
For example: define "forever". Have you been waiting for 2 days now or did you get impatient after 20 seconds of no response?
You say you have a huge table, containing somewhere around 7 million rows. On what server does your database run? Is it an old 80286 intel machine with 512 MB memory or is it a server that would make Google jealous?
How much resources are given to the Oracle processes?
See? When it comes to performance, everything should be related to the environment, which is why it is important to mention that. Quantify the environment and quantify the waiting time.
|
|
|
Re: index on a huge table [message #410912 is a reply to message #410691] |
Wed, 01 July 2009 01:01  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> set timing on
SQL> create table t as
2 select a.* from dba_objects a, dba_objects b
3 where rownum <= 6844153
4 /
Table created.
Elapsed: 00:00:47.08
SQL> create index t_i on t (object_id, owner, object_name)
2 /
Index created.
Elapsed: 00:02:05.25
SQL> @v
Version Oracle : 10.2.0.4.0
OS: Windows XP Pro (Version 5.01.2600) Service Pack 2
Processor: Intel(R) Pentium(R) 4, CPU 3.00GHz
2 Processor(s) 2992MHz
Cache: L2=2048K
1 core per die - 2 threads per cache
Memory (Available Ko) :
Physical: 680.548 / 2.096.488 (32%)
Paging: 2.763.924 / 4.035.020 (68%)
Regards
Michel
|
|
|