Re: Need Help With SQL Statement Please

"James A. Mumper" <jamumper_at_mercury.ptes.com> wrote:
>
> Charlie Peck wrote:
> >
> > In article <4m85du$t18_at_newshost.ptes.com>, jamumper_at_ptes.com wrote:
> >
> > > In Loney's DBA book and the Oracle DBA book is a script which allows you
 to use existing
> > > data in a table to size the table. The script looks something like this:
> > >
> > >
> > > select
> > > avg(nvl(vsize(column1),0))+
> > > avg(nvl(vsize(column2),0)) avg_row_length
> > > from table_name;
> > >
> > >
> > > What I would like to do is see if it is possible to make this script
 build itself. I have a database
> > > with umpteen tables in it, all with umpteen columns and I would rather
 not have to do a
> > > describe on all of them and then write this script.
> >
> > You can get the table and column name information from dba_tables and
> > dba_columns. In PL/SQL (or some other procedural wrapper) you could:
> >
> > write "select "
> > fetch a table name from dba_tables
> > fetch a column name for that table from dba_columns
> > write "avg(nvl(vsize(<column_name>),0) "
> > until no more columns
> > write "from table_name;"
> > until no more tables
> >
> > > The alternative to this is to ANALYZE the tables and then use the
 avg_row_len from
> > > dba_tables. Does anyone know whether these would be the same as the sql
 script would
> > > generate?
> >
> > I imagine it would give about the same information, and probably much faster
> > overall.
> >
> > charlie peck
>
> Thanks to everyone who helped me with this problem. I am not the
> strongest SQL writer in the world, but this gave me an opportunity to
> hone my skills a little. I ended up writing a PL/SQL program that uses
> the DBMS_DDL package ANALYZE_TABLE. Here's the script:
>
> DECLARE
>
> CURSOR c_getdata IS
> SELECT object_name, object_type, owner
> FROM dba_objects
> WHERE owner = 'DBO'
> AND object_type = 'TABLE';
>
> v_name varchar(30);
> v_type varchar(30);
> v_schema varchar(30);
> v_method VARCHAR2(20) := 'ESTIMATE';
> v_rows number;
> v_percent number := 100;
>
> begin
>
> OPEN c_getdata;
>
> LOOP
>
> FETCH c_getdata INTO v_name, v_type, v_schema;
>
> EXIT WHEN c_getdata%NOTFOUND;
>
> INSERT INTO jimm_test (name, type, owner) VALUES
> (v_name,v_type,v_schema);
>
> DBMS_DDL.ANALYZE_OBJECT
> (v_type,v_schema,v_name,v_method,v_rows,v_percent);
>
> END LOOP;
>
> END;
>
> The odd thing about this is that, according to the documentation, I
> should be able to set v_method to COMPUTE and leave v_rows and v_percent
> null. However, when I do this, it errors out. SO, I figured that an
> estimate of 100 percent was as good as COMPUTE. Works very nicely.
>
> --
> James A. Mumper
> Senior Oracle DBA
> Pacific Telesis Video Services

Beware of side effects!

The analyze command produces statistics that are used by the cost based optimizer. If the statistics are not present for any table involved in a statement the rule based optimizer is used. If the statistics are present for some of the tables the cost based optimizer is used. In this mixed situation the cost based optimizer does not have all of the information that it needs and is not likely to do the best possible job.

You can run ANALYZE ... DELETE to remove statisics after you get table size. Received on Tue May 07 1996 - 00:00:00 CEST