Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Help: Using DBMS_OLAP.ESTIMATE_SUMMARY_SIZE procedure
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. PLAN_TABLE is already
created.
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. I am not getting values for num_bytes and num_rows variables. Can someone please help me.
thanks,
Srinivasa
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 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