4 Replies Latest reply: Dec 30, 2012 12:04 AM by jeneesh RSS

    why this difference in query count

    kumar73
      Hello friends,

      am running the following query say
      1) SELECT table_name, num_rows , OWNER FROM ALL_TABLES where table_name !='KEY_IDS' AND TABLE_NAME NOT LIKE '%BKP%' AND TABLE_NAME NOT LIKE '%$%' AND OWNER ='KUMAR '

      output:
      TABLE_NAME NUM_ROWS OWNER
      ADDL_INFO_VALUE_LANG 4457845     KUMAR


      But when I run the query like

      2) select count(*) from KUMAR.ADDL_INFO_VALUE_LANG ;
      -- 4524897

      Why their is a count of difference in the above mentioned queries (1) and (2) .


      Pls clarify ..

      Thanks/
        • 1. Re: why this difference in query count
          sb92075
          kumar73 wrote:
          Hello friends,

          am running the following query say
          1) SELECT table_name, num_rows , OWNER FROM ALL_TABLES where table_name !='KEY_IDS' AND TABLE_NAME NOT LIKE '%BKP%' AND TABLE_NAME NOT LIKE '%$%' AND OWNER ='KUMAR '

          output:
          TABLE_NAME NUM_ROWS OWNER
          ADDL_INFO_VALUE_LANG 4457845     KUMAR


          But when I run the query like

          2) select count(*) from KUMAR.ADDL_INFO_VALUE_LANG ;
          -- 4524897

          Why their is a count of difference in the above mentioned queries (1) and (2) .


          Pls clarify ..

          Thanks/
          the number of rows changed since statistics were collected.

          Handle:     kumar73
          Status Level:     Newbie
          Registered:     Mar 16, 2010
          Total Posts:     335
          Total Questions:     147 (97 unresolved)

          why so MANY questions remain unanswered?
          • 2. Re: why this difference in query count
            rp0428
            >
            am running the following query say
            1) SELECT table_name, num_rows , OWNER FROM ALL_TABLES where table_name !='KEY_IDS' AND TABLE_NAME NOT LIKE '%BKP%' AND TABLE_NAME NOT LIKE '%$%' AND OWNER ='KUMAR '

            output:
            TABLE_NAME NUM_ROWS OWNER
            ADDL_INFO_VALUE_LANG 4457845 KUMAR

            But when I run the query like

            2) select count(*) from KUMAR.ADDL_INFO_VALUE_LANG ;
            -- 4524897

            Why their is a count of difference in the above mentioned queries (1) and (2) .
            >
            Delete all of the rows from the table and the second query will now have a count of zero and the first query count will still be 4457845.

            Why the difference?

            Because the table count represents the number of rows in the table NOW.

            But the first query is a system view that only gets updated when you generate statistics on the table. It might have been 3 years ago when you last generated statistics so that is the number of rows the table had then.

            Generate fresh statistics on the table and then the numbers will match.
            • 3. Re: why this difference in query count
              ranit B
              Hi,

              I'm quite sure that you are just going by the name +'NUM_ROWS'+ and thinking it to contain the total row count of the table.
              But that is not the exact case.

              As RP and SB have very rightly said, it only gets updated to latest count whenever their Statistics are gathered.

              Please do the workout that RP mentioned and things will get very easy to digest.
              • 4. Re: why this difference in query count
                jeneesh
                And it is very clearly written in the Doc

                >
                Note:
                Columns marked with an asterisk (*) are populated only if you collect statistics on the table with the ANALYZE statement or the DBMS_STATS package.
                >

                See the "proof"
                create table test_stats as select rownum as c1 from dual connect by level <= 100;
                
                select count(*) from scott.test_stats;
                
                COUNT(*)
                --------
                     100 
                
                
                --"See below, NOM_ROWS is NULL coz the stats are not yet gathered for the table
                
                select table_name, num_rows , owner,
                       last_analyzed
                from all_tables 
                where table_name = 'TEST_STATS' 
                AND OWNER ='SCOTT' ;
                
                TABLE_NAME                     NUM_ROWS OWNER                          LAST_ANALYZED
                ------------------------------ -------- ------------------------------ -------------
                TEST_STATS                              SCOTT                                        
                
                --"Gather the stats
                
                exec dbms_stats.gather_table_stats('SCOTT','TEST_STATS');
                
                anonymous block completed
                
                --"See below, NOM_ROWS is updates properly
                
                
                select table_name, num_rows , owner,
                       last_analyzed
                from all_tables 
                where table_name = 'TEST_STATS' 
                AND OWNER ='SCOTT' ;
                
                TABLE_NAME                     NUM_ROWS OWNER                          LAST_ANALYZED
                ------------------------------ -------- ------------------------------ -------------
                TEST_STATS                          100 SCOTT                          30-DEC-12     
                
                --"truncate the table
                
                truncate table test_stats;
                
                table TEST_STATS truncated.
                
                --"NUM_ROWS are wrong..
                
                select table_name, num_rows , owner,
                       last_analyzed
                from all_tables 
                where table_name = 'TEST_STATS' 
                AND OWNER ='SCOTT' ;
                
                TABLE_NAME                     NUM_ROWS OWNER                          LAST_ANALYZED
                ------------------------------ -------- ------------------------------ -------------
                TEST_STATS                          100 SCOTT                          30-DEC-12     
                
                --"After stats gather, it is fine..
                
                exec dbms_stats.gather_table_stats('SCOTT','TEST_STATS');
                
                anonymous block completed
                
                
                select table_name, num_rows , owner,
                       last_analyzed
                from all_tables 
                where table_name = 'TEST_STATS' 
                AND OWNER ='SCOTT' ;
                
                
                TABLE_NAME                     NUM_ROWS OWNER                          LAST_ANALYZED
                ------------------------------ -------- ------------------------------ -------------
                TEST_STATS                            0 SCOTT                          30-DEC-12    
                Edited by: jeneesh on Dec 30, 2012 11:31 AM
                Above all NUM_ROWS is an approximate count

                And you can set the value of NUM_ROWS using DBMS_STATS package (set_table_stats)