1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Count rows from multiple views

Discussion in 'SQL PL/SQL' started by Sparamanga, Nov 22, 2016.

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi,

    I am trying to count the number of rows each view has in an oracle database.

    There are over 100 views.

    I want it to display the view name and the number of rows.

    I tried

    SELECT VIEW_NAME, COUNT(*) FROM ALL_VIEWS

    But this doesn't work.

    I tried a similar query using a group by of view_name and max(rownum) but this too didn't work.

    The SQL I'm writing is generating the count of view_names or max(rownum)'s assigned to view_names. I need the count of rows for each individual view_name.

    Is there a simple way to do this?

    Thanks,

    Rob.
     
  2. njuro

    njuro Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Poland
    Hi,

    You could try generate selects for each view, something like this:
    Code (SQL):
    SELECT 'SELECT '||VIEW_NAME||' as VIEW_NAME,
    (SELECT COUNT(*) FROM '
    ||VIEW_NAME||') AS COUNT FROM DUAL UNION ALL'
    FROM ALL_VIEWS
     
    and run result as single sql statement.

    But if you have views over large tables, this selects might take long time.
     
  3. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi thanks, but I dont think this is providing the correct results. I need each view_name and the number of rows it has..

    I think I need to do a cursor for loop which grabs all the view_names, then loops through each one doing a count(*) for each.. something along those lines, just not sure of the correct syntax yet. If anyone knows please let me know as been looking at this for a while!

    Thanks.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try this instead:

    Code (SQL):
    SET pagesize 0 linesize 200 trimspool ON termout off feedback off

    SELECT 'SELECT '''||VIEW_NAME||''', COUNT(*) FROM '||owner||'."'||VIEW_NAME||'";'
    FROM ALL_VIEWS

    spool get_all_view_counts.SQL
    /
    spool off

    SET termout ON trimspool off
    @get_all_view_counts
    SET pagesize 14 linesize 80 feedback ON
     
     
    Last edited: Nov 23, 2016
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Obviously this code was not tested; attempting to execute one of the generated statements produces:

    Code (SQL):
    SQL> SELECT FILE_LOCK AS VIEW_NAME,
      2  (SELECT COUNT(*) FROM FILE_LOCK) AS COUNT FROM DUAL;
    SELECT FILE_LOCK AS VIEW_NAME,
           *
    ERROR at line 1:
    ORA-00904: "FILE_LOCK": invalid identifier


    SQL>
    Posting untested code is wrong as it can lead those asking questions to think that THEY are at fault when the posted code doesn't execute. This is a grave disservice to the person posting the question and apparent laziness on the part of the person posting such code.