删除所有表时的 Oracle 性能
我有以下 Oracle SQL：
Begin
-- tables
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints');
end loop;
-- sequences
for c in (select sequence_name from user_sequences) loop
execute immediate ('drop sequence '||c.sequence_name);
end loop;
End;
它是由另一个开发人员提供给我的，我不知道它是如何工作的，但它会删除我们数据库中的所有表。
它有效，但需要永远！
我认为删除所有表格不需要那么长时间。这是怎么回事？还有，这个脚本可以改进吗？
注意：大约有 100 张桌子。
I have the following Oracle SQL:
Begin
-- tables
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints');
end loop;
-- sequences
for c in (select sequence_name from user_sequences) loop
execute immediate ('drop sequence '||c.sequence_name);
end loop;
End;
It was given to me by another dev, and I have no idea how it works, but it drops all tables in our database.
It works, but it takes forever!
I don't think dropping all of my tables should take that long. What's the deal? And, can this script be improved?
Note: There are somewhere around 100 tables.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在这种情况下，永远意味着一个表不到三秒:)
删除表不仅仅是删除表。还有一些依赖对象需要删除 - 约束、索引、触发器、lob 或嵌套表存储等。还有一些视图、同义词存储过程需要失效。有补助金可以撤销。表的空间（及其索引的空间等）必须被取消分配。
所有这些活动都会生成递归 SQL、从数据字典中进行选择或更新的查询，这些查询的性能可能很差。即使我们不使用触发器、视图、存储过程，数据库仍然必须运行查询来确定它们是否存在。
与普通 SQL 不同，我们无法调整递归 SQL，但我们可以塑造环境以使其运行得更快。
我假设这是一个开发数据库，其中定期构建和拆除对象，并且您使用的是 10g 或更高版本。
清理回收站。
<代码>SQL> purge recyclebin;
收集数据字典的统计信息（需要 DBA 权限）。这些可能已经被收集，因为这是 10g 和 11g 中的默认行为。 了解更多信息。
获得字典统计信息后，请确保您正在使用基于成本的优化器。理想情况下，这应该在数据库级别设置，但我们可以在会话级别修复它：
<代码>SQL> alter session set optimizer_mode=choose;
Forever in this case meaning less than three seconds a table :)
There is more to dropping a table than just dropping the table. There are dependent objects to drop as well - constraints, indexes, triggers, lob or nested table storage, etc. There are views, synonyms stored procedures to invalidate. There are grants to be revoked. The table's space (and that of its indexes, etc) has to be de-allocated.
All of this activity generates recursive SQL, queries which select from or update the data dictionary, and which can perform badly. Even if we don't use triggers, views, stored procs, the database still has to run the queries to establish their absence.
Unlike normal SQL we cannot tune recursive SQL but we can shape the environment to make it run quicker.
I'm presuming that this is a development database, in which objects get built and torn down on a regular basis, and that you're using 10g or higher.
Clear out the recycle bin.
SQL> purge recyclebin;Gather statistics for the data dictionary (will require DBA privileges). These may already be gathered, as that is the default behaviour in 10g and 11g. Find out more.
Once you have dictionary stats ensure you're using the cost-based optimizer. Ideally this should be set at the database level, but we can fix it at the session level:
SQL> alter session set optimizer_mode=choose;我会尝试更改 DROP TABLE 语句以使用
Purge关键字。由于您要删除所有表，因此实际上不需要同时级联约束。此操作可能是导致其缓慢的原因。我没有 Oracle 实例来测试它，所以它可能会抛出错误。如果它确实抛出错误，或者速度没有加快，我会删除序列删除命令来找出哪个命令花费了这么多时间。
Oracle 有关 DROP TABLE 命令的文档位于此处 。
I would try changing the DROP TABLE statement to use the
Purgekeyword. Since you are dropping all tables, you don't really need to cascade the constraints at the same time. This action is probably what is causing it to be slow. I don't have an instance of Oracle to test this with though, so it may throw an error.If it does throw an error, or not go faster, I would remove the Sequence drop commands to figure out which command is taking so much time.
Oracle's documentation on the DROP TABLE command is here.
一种替代方法是删除用户而不是单个表等，并在需要时重新创建它们。它通常更健壮，因为它删除了所有表、视图、过程、序列等，并且可能会更快。
One alternative is to drop the user instead of the individual tables etc., and recreate them if needed. It's generally more robust as is drops all of the tables, view, procedures, sequences etc., and would probably be faster.