Re: Activating / Rebuilding Statistics

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 26 Aug 2003 06:39:13 -0700
Message-ID: <2687bb95.0308260539.524abc8@posting.google.com>

Marc Eggenberger <marc.eggenberger_at_itc.alstom.com> wrote in message news:<MPG.19b52d39a0b26d819896d0_at_iww.cacti.ch.alstom.com>...
> In article <3F4AA9AF.5290B99C_at_exxesolutions.com>,
> damorgan_at_exxesolutions.com says...
>
> [...]
>
> > SELECT max(last_ddl_date)
> > FROM user_objects;
>
> Isn't it
> SELECT max(last_ddl_time) from user_objects;
> ?

Daniel, Mark, what does last_ddl_time have to do with statistics? It is the time that the object was last affected by a DDL operation such as create and alter. For Oracle versions 7.0 - 8.1 and 9.2 an analyze does not affect the column value. I think that it might have under version 9.0, but that was a screw-up by Oracle development.

On 8.1.7.4 and 9.2.0.1 on AIX
UT1> l
  1 select last_ddl_time from dba_objects where object_name = 'MARKTEST'
  2* and object_type = 'TABLE'
UT1> /

LAST_DDL_

---

21-AUG-03 UT1> analyze table marktest compute statistics;

Table analyzed.

UT1> select last_ddl_time from dba_objects where object_name = 'MARKTEST'
  2 and object_type = 'TABLE'
  3 /

LAST_DDL_

---

21-AUG-03 UT1> select sysdate from dual;

SYSDATE

---

26-AUG-03 A test with dbms_stats.gather_table_stats also showed no effect on the last_ddl_time column under 8.1.7.4

HTH -- Mark D Powell -- Received on Tue Aug 26 2003 - 08:39:13 CDT