Creating index/pk in a huge table is taking too long. I am using Oracle. How do I know if it is going well?

oracle indexing primary-key composite-primary-key
Michael picture Michael · Oct 7, 2010 · Viewed 9.1k times · Source

I have a really huge table, with ~200 million rows. It had no index/pk at all. Selects in this table were (obviously) running slow. I decided to create a PK using 3 columns. I did it in a test environment that has a smaller version of this table and it worked like a charm.

So, before going home i did a ALTER TABLE HUGETABLE ADD CONSTRAINT PK_HUGETABLE PRIMARY KEY (ID1, ID2, ID3);

I expected it to run over the night, but it has been already over 24 hours and it still running.

I know if i had kept the Session Id before starting my query i would be able to track it at V$SESSION_LONGOPS. But i didn't.

Is there any way to check how is my query going or how long will it still take?

Answer

Justin Cave picture Justin Cave · Oct 7, 2010

You should still be able to query V$SESSION_LONGOPS. If you run something like

SELECT sid, serial#, start_time, sofar, totalwork, time_remaining, message
  FROM v$session_longops
 WHERE time_remaining > 0

you'll probably see only one session that started yesterday and the other columns should corroborate that with indications that the session has done a lot of work. The MESSAGE should also indicate something like a full scan on HUGETABLE.

Related questions

Does making a primary key in multiple columns generate indexes for all of them?

If I set a primary key in multiple columns in Oracle, do I also need to create the indexes if I need them? I believe that when you set a primary key on one column, you have it indexed by …

Read More
Force index use in Oracle

I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: select * from table_name where column_having_index="some value"; The query …

Read More
Oracle - Select where field has lowercase characters

I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name. When rows are inserted into this table, the first name and last name fields are supposed to be in …

Read More