Geek Answers Handbook

Creating an index / pk in a huge table takes too much time. I am using Oracle. How do you know if this is good?

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

So, before heading home, I did ALTER TABLE HUGETABLE ADD CONSTRAINT PK_HUGETABLE PRIMARY KEY (ID1, ID2, ID3);

I expected it to work all night, but more than 24 hours have passed and it still works.

I know that if I saved the session identifier before starting my request, I could track it on V $ SESSION_LONGOPS. But I didn’t.

Is there any way to check how my request goes or how long it will last?

+7
oracle indexing primary-key composite-primary-key
source share
2 answers

You can still request 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 rest of the columns should confirm this, indicating that the session has done a great job. The MESSAGE should also indicate something like a full scan on HUGETABLE.

+7
source share

You do not need to remember the session identifier in order to monitor the status of the operator. As Justin suggested, you shouldn't have too many long requests from the previous day. You can also specify a start time to narrow the results.

 select * from V$SESSION_LONGOPS where time_remaining > 0 and start_time > <'your run date/time here'>;

In addition to the "PARALLEL n" option, you should also use the "ONLINE" option if you are creating (or restoring) an index while reading / writing at the same time takes place in a HUGE TABLE.

0
source share

More articles:


All Articles