8 Replies Latest reply: Mar 6, 2013 4:08 AM by Chewy RSS

    help with a select query

    Chewy
      Hi Guys,

      For example I have 2 tables:
      I wish to list out the owner, table_name, total count of chain row of the table, num rows by joining the below 2 tables.
      Can advise how can i do it?
      Currently i'm doing it by executing this statement but very slow:

      select a.owner_name, a.table_name, count(*), b.num_Rows
      from system.chained_rows a, dba_tables b
      where a.owner_name=b.owner
      and a.table_name=b.table_name
      and b.num_Rows>0
      group by a.owner_name, a.table_name, b.num_Rows
      having count(*)*100/b.num_Rows>10;




      TABLE INFORMATION:

      1. CHAINED_ROWS (one table name can have multiple chain row id records)

      column:
      1. owner
      2. table_name
      3. chain row id


      2. DBA_TABLES (one table name only one record)

      column:
      1. owner
      2. table_name
      3. num_rows


      thanks
        • 1. Re: help with a select query
          Turion
          Provide sample data of chained row table.
          • 2. Re: help with a select query
            jeneesh
            You have the CHAIN_CNT column in DBA_TABLES itself..

            Why are you not using it?
            select owner,table_name,num_rows,chain_cnt,
               chain_cnt/num_rows percent
            from dba_tables where
            owner not in ('SYS','SYSTEM',.......)
            and chain_cnt/num_rows > 10
            order by percent desc;
            Edited by: jeneesh on Mar 6, 2013 12:13 PM
            • 3. Re: help with a select query
              Chewy
              Hi,

              SQL> desc chained_rows;
              Name Null? Type
              ----------------------------------------- -------- ---------------------
              OWNER_NAME VARCHAR2(30)
              TABLE_NAME VARCHAR2(30)
              HEAD_ROWID ROWID


              I'm doing it as i'm using the analyze table list chained rows command.
              I do not wish to perform analyze table compute statistics.

              thanks
              • 4. Re: help with a select query
                jeneesh
                Chewy wrote:
                Hi,

                SQL> desc chained_rows;
                Name Null? Type
                ----------------------------------------- -------- ---------------------
                OWNER_NAME VARCHAR2(30)
                TABLE_NAME VARCHAR2(30)
                HEAD_ROWID ROWID


                I'm doing it as i'm using the analyze table list chained rows command.
                I do not wish to perform analyze table compute statistics.

                thanks
                Which version are you working on?

                Why are you not using DBMS_STATS.. ?

                And if you are not gathering stats on the TABLE, how do you expect NUM_ROWS will be proper?
                • 5. Re: help with a select query
                  Chewy
                  Hi,

                  Which version are you working on?
                  10.2.0.5

                  Why are you not using DBMS_STATS.. ?
                  Yes i'm using. DBMS_STATS will not populate chained cnt column in dba_Tables.

                  And if you are not gathering stats on the TABLE, how do you expect NUM_ROWS will be proper?
                  As above.

                  thanks
                  • 6. Re: help with a select query
                    Chewy
                    anyone can help?
                    many thanks!
                    • 7. Re: help with a select query
                      Cherif bh
                      Hi ,

                      http://www.akadia.com/services/ora_chained_rows.html

                      This is helpful for you .

                      Thanks,
                      • 8. Re: help with a select query
                        Chewy
                        Hi,

                        I think u mis-inteprete my question.
                        My question is more on how to formulate a sql to get the output i hope so.. and not how to get chain counts.


                        I wish to list out the owner, table_name, total count of chain row of the table, num rows by joining the below 2 tables.
                        Can advise how can i do it?
                        Currently i'm doing it by executing this statement but very slow:

                        select a.owner_name, a.table_name, count(*), b.num_Rows
                        from system.chained_rows a, dba_tables b
                        where a.owner_name=b.owner
                        and a.table_name=b.table_name
                        and b.num_Rows>0
                        group by a.owner_name, a.table_name, b.num_Rows
                        having count(*)*100/b.num_Rows>10;


                        TABLE INFORMATION:

                        1. CHAINED_ROWS (one table name can have multiple chain row id records)

                        column:
                        1. owner
                        2. table_name
                        3. chain row id


                        2. DBA_TABLES (one table name only one record)

                        column:
                        1. owner
                        2. table_name
                        3. num_rows

                        thanks