Oracle-L: Please Help: estimating size for materialized view Question

From: Srinivas <zirraffeee_at_YAHOO.COM>
Date: Wed, 24 Jul 2002 07:08:23 -0800
Message-ID: <F001.004A0D7B.20020724070823@fatcity.com>

Hi
I am trying to find estimated size for a view using DBMS_OLAP.ESTIMATE_SUMMARY_SIZE package. Using the following sql (logged in as SYS). I am new to materialized views
and DBMS_OLAP.ESTIMATE_SUMMARY_SIZE usage.

I am doing this through SQL*Plus logged in as SYS. I also set SERVEROUTPUT ON
and VAR num_bytes NUMBER

VAR num_rows NUMBER at SQL prompt in SQL*Plus.

Can someone help me how to find the estimated size before creating a materialized view for the following syntax for
the following.

thanks,
Srinivas

exec DBMS_OLAP.estimate_summary_size('test_est_size', 'SELECT empno, ename, dname, loc FROM scott.emp, scott.dept where emp.deptno=dept.deptno',

                      :num_rows, :num_bytes)

This is the output I got :

SQL> set SERVEROUTPUT ON
SQL> show serveroutput
serveroutput ON size 2000 format WORD_WRAPPED

SQL> VAR num_rows number
SQL> var num_bytes number
SQL> var

variable mv_size
datatype NUMBER

variable num_bytes
datatype NUMBER

variable num_rows
datatype NUMBER

SQL> exec
DBMS_OLAP.estimate_summary_size('test_est_size',

'SELECT empno, ename, dname, loc FROM scott.emp, scott.dept where emp.deptno=dept.deptno',

                               :num_rows, :num_bytes)

PL/SQL procedure successfully completed.

SQL> print :num_rows

NUM_ROWS

SQL> print :num_bytes

NUM_BYTES

Do You Yahoo!?
Yahoo! Health - Feel better, live better http://health.yahoo.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Srinivas
INET: zirraffeee_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Jul 24 2002 - 10:08:23 CDT