oracle 在大表中创建索引/pk 花费的时间太长。我正在使用甲骨文。我怎么知道它是否进展顺利？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3882322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating index/pk in a huge table is taking too long. I am using Oracle. How do I know if it is going well?
提问by Michael
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.
我有一个非常大的表，大约有 2 亿行。它根本没有索引/pk。此表中的选择（显然）运行缓慢。我决定使用 3 列创建一个 PK。我在这个表的较小版本的测试环境中完成了它，并且它的工作原理非常棒。
So, before going home i did a ALTER TABLE HUGETABLE ADD CONSTRAINT PK_HUGETABLE PRIMARY KEY (ID1, ID2, ID3);
所以，在回家之前，我做了一个 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.
我原以为它会运行一夜，但它已经超过 24 小时了，它仍在运行。
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.
我知道如果我在开始查询之前保留了会话 ID，我将能够在 V$SESSION_LONGOPS 上跟踪它。但我没有。
Is there any way to check how is my query going or how long will it still take?
有什么方法可以检查我的查询进展如何或还需要多长时间？
采纳答案by Justin Cave
You should still be able to query V$SESSION_LONGOPS. If you run something like
您应该仍然能够查询 V$SESSION_LONGOPS。如果你运行类似
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.
您可能只会看到昨天开始的一个会话，其他列应该证实该会话已经完成了大量工作的迹象。MESSAGE 还应指示对 HUGETABLE 进行全面扫描之类的内容。
回答by pahariayogi
You don't need to remember your session id to monitor a statement's status. As Justin suggested, you shouldn't have too many long running queries since previous day. You can specify start time as well to narrow down the results.
您无需记住会话 ID 即可监控语句的状态。正如贾斯汀所建议的那样，自前一天以来，您不应该有太多长时间运行的查询。您也可以指定开始时间以缩小结果范围。
select * from V$SESSION_LONGOPS where time_remaining > 0 and start_time > <'your run date/time here'>;
Besides 'PARALLEL n' option, you should consider using 'ONLINE' option as well if you are creating (or rebuilding) an index while concurrent read/write are taking place on HUGE TABLE.
除了 'PARALLEL n' 选项，如果您在 HUGE TABLE 上进行并发读/写时创建（或重建）索引，您也应该考虑使用 'ONLINE' 选项。
