Home » SQL & PL/SQL » SQL & PL/SQL » index on a huge table
index on a huge table [message #410691] Mon, 29 June 2009 14:55 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: calculate first date of any month
Next Topic: partitioned table
Goto Forum:
  


Current Time: Mon Dec 05 05:10:12 CST 2016

Total time taken to generate the page: 0.10812 seconds