How can I write a query that generates a set of ANALYZE INDEX commands for every index owned by a certain user?

As the title says, in an Oracle 10g database, I'd like to create a query that will generate a set of ANALYZE INDEX commands fo开发者_如何学Cr every index owned by a certain user. I'm a complete layman when it comes to database topics, so please keep answers as dummy-proof as possible, thanks!

---

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

---

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.