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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).