Oracle: How to estimate the size of a view?

Question

In Oracle I can get the size of a table. I would like to estimate the size of a view (non materialized). Is it possible?

I know that views don't have any data per se, but we are moving the data to our data lake and would like to estimate it. Knowing the size we will be able to optimize our resources and speed up the process

Answer 1

You can use EXPLAIN PLAN to estimate the number of bytes and rows that will be returned by reading the entire view. But keep in mind that these numbers are only estimates, they depend on having current statistics, and they will be less accurate for more complicated queries.

For example, on my system, EXPLAIN PLAN estimates that a somewhat complicated metadata view will return 34 MB and 75,590 rows. Whereas the actual values are roughly 14 MB and 85,402 rows.

Commands:

explain plan for select * from dba_objects;

select * from table(dbms_xplan.display);

Results:

Plan hash value: 3423780594
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                   | 75590 |    34M|   134K  (1)| 00:00:06 |
|   1 |  VIEW                                  | DBA_OBJECTS       | 75590 |    34M|   134K  (1)| 00:00:06 |
|   2 |   UNION-ALL                            |                   |       |       |            |          |
...

Estimate multiple views in a single query

With a few tricks you can create estimates for multiple views all within a single query. This solution requires Oracle 12.1 or higher. The WITH FUNCTION syntax is a bit odd, and some IDEs struggle with it, so you might have to play around with the semicolon and slash at the end.

--Create sample views from data dictionary views. 
create or replace view view1 as select * from all_tables;
create or replace view view2 as select * from all_tab_privs;
create or replace view view3 as select * from all_objects;

--Get the estimated size of each query. The actual values will differ for your database.
with function get_bytes(p_view_name varchar2) return number is
    v_bytes number;
    --(Needed because "explain plan" is technically DML, which normally shouldn't be executed inside a query.)
    pragma autonomous_transaction;
begin
    --Create an explain plan for reading everything from the view.
    execute immediate replace(q'[explain plan set statement_id = '#VIEW_NAME#' for select * from #VIEW_NAME#]', '#VIEW_NAME#', p_view_name);

    --Get the size in bytes.
    --Latest plan information. (In case the explain plan was generated multiple times.)
    select max(bytes)
    into v_bytes
    from
    (
        --Plan information.
        select bytes, timestamp, max(timestamp) over () latest_timestamp
        from plan_table
        where statement_id = p_view_name and id = 0
    )
    where latest_timestamp = timestamp;

    --As part of the AUTONOMOUS_TRANSACTION feature, the function must either commit or rollback.
    rollback;

    return v_bytes;
end;
select view_name, round(get_bytes(view_name) / 1024 / 1024, 1) mb
from user_views
order by mb desc, view_name;
/

Results:

VIEW_NAME            MB
------------ ----------
VIEW3               2.4
VIEW1                .8
VIEW2                .7