Results 1 to 2 of 2

Thread: Sizing views

  1. #1
    Join Date
    Sep 2003
    Greater London

    Unanswered: Sizing views


    What would be the best sql to run that will give the size of a view in 7.3.4? I joined dba_objects and dba_data_files, however I am not sure if that gave a true size as there were several values returned.



  2. #2
    Join Date
    Sep 2003
    Virginia, USA
    A view is a stored SQL statement, and has no data or storage. Even if you select * from viewname, there is no storage. Oracle fetches some data, gives it to you, fetches more data, etc. The whole dataset is not stored in RAM or temp files, so you're asking a question that can't be answered.

    More imporant is determining the maximum storage requirements for a view's underlying table. Repeat this query for each table in the view, then figure out what percentage of rows from each table might be queried by the view.

    select a.blocks*b.value/1048576) "MB"
    from dba_tables a, v$parameter b
    where a.table_name = 'your table name'
    and = 'db_block_size';
    "Blocks" is the number of blocks used by the object's data.

    You might also want to look at columns num_rows and avg_row_len. Multiplying these two columns should tell you how much data you have. Now, multiply this value times the percentage of rows fetched by your view.
    Author, Oracle Database 10g: From Nuts to Soup

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts