Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Help: Using DBMS_OLAP.ESTIMATE_SUMMARY_SIZE procedure

Help: Using DBMS_OLAP.ESTIMATE_SUMMARY_SIZE procedure

From: Srinivasa Medam <msrhome_at_yahoo.com>
Date: 24 Jul 2002 09:16:35 -0700
Message-ID: <a682873e.0207240816.7a3cda42@posting.google.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. 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 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


Received on Wed Jul 24 2002 - 11:16:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US