当前位置：文江博客话题详情

如何编写一个查询，为某个用户拥有的每个索引生成一组 ANALYZE INDEX 命令？

发布于 2024-11-03 13:37:54 字数 119 浏览 1 评论 0原文

正如标题所示，在 Oracle 10g 数据库中，我想创建一个查询，该查询将为特定用户拥有的每个索引生成一组 ANALYZE INDEX 命令。当涉及到数据库主题时，我是一个完全的门外汉，所以请尽可能保证答案的虚拟性，谢谢！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

青巷忧颜 2024-11-10 13:37:54

当您使用 Oracle 10g 时，您确实应该使用 DBMS_STATS。这可以简单地实现...

begin
    for r in ( select index_name from user_indexes )
    loop
        dbms_stats.gather_index_stats(user, r.index_name);
    end loop;
end;

还有其他参数：c查看文档。

如果您想为其他用户的索引生成统计信息，请改用 ALL_INDEXES，并限制 OWNER。

如果您专注于分析，那么

spool anl_idx.sql

select 'analyze index '||&&owner||'.'||index_name||' compute statistics;'
from all_indexes
where owner = '&&owner';

spool off

As you're on Oracle 10g you really ought to be using DBMS_STATS instead. That could be implemented as simply as ...

begin
    for r in ( select index_name from user_indexes )
    loop
        dbms_stats.gather_index_stats(user, r.index_name);
    end loop;
end;

There are other parameters: check out the documentation.

If you want to generate stats for some other user's indexes use ALL_INDEXES instead, and restrict on OWNER.

If your heart is set on ANALYZE then

spool anl_idx.sql

select 'analyze index '||&&owner||'.'||index_name||' compute statistics;'
from all_indexes
where owner = '&&owner';

spool off

回复收藏 0 原文

北方的巷 2024-11-10 13:37:54

你可以。但是，如果您是数据库主题的外行，我会非常担心您正在做的事情会给自己带来各种问题。所以我想问你想解决什么问题。

在许多版本中，ANALYZE 通常已被弃用，取而代之的是 DBMS_STATS 包。
默认情况下，Oracle 10g 已经有一个后台作业，可以定期收集对象（包括索引）的统计信息。如果该功能被禁用，您的 DBA 几乎肯定会有自己的工作来收集您几乎肯定不想干涉的统计数据。
收集统计信息有可能改变查询计划，这可能会导致各种性能问题，特别是当索引统计信息与表统计信息不一致时。

你可以做类似的事情

DECLARE
  p_owner VARCHAR2(30) := 'BOB'; -- Replace BOB with the user name you are interested in
BEGIN
  FOR idx IN (SELECT * FROM dba_indexes WHERE owner = p_owner)
  LOOP
    EXECUTE IMMEDIATE 'ANALYZE INDEX ' || p_owner || '.' || idx.index_name ||
                      ' COMPUTE STATISTICS';
  END LOOP;
END;

，但在我建议你实际运行这个之前，我真的很想了解你来自哪里。

You can. However, if you are a layman when it comes to database topics, I'd be very concerned that you're doing something that is going to create all sorts of problems for yourself. So I'd ask what problem you're trying to solve.

ANALYZE has generally been deprecated for a number of releases in favor of the DBMS_STATS package.
Oracle 10g already has, by default, a background job that regularly gathers statistics on objects including indexes. If that has been disabled, your DBAs would almost certainly have their own job to gather statistics that you almost certainly don't want to interfere with.
Gathering statistics has the potential to change query plans which could have cause all sorts of performance issues particularly if index statistics are out of step with table statistics.

You can do something like

DECLARE
  p_owner VARCHAR2(30) := 'BOB'; -- Replace BOB with the user name you are interested in
BEGIN
  FOR idx IN (SELECT * FROM dba_indexes WHERE owner = p_owner)
  LOOP
    EXECUTE IMMEDIATE 'ANALYZE INDEX ' || p_owner || '.' || idx.index_name ||
                      ' COMPUTE STATISTICS';
  END LOOP;
END;

but I'd really want to understand where you're coming from before I'd suggest you'd actually run this.

回复收藏 0 原文

~没有更多了~