This discussion is archived
8 Replies Latest reply: Mar 6, 2013 2:08 AM by Chewy RSS

help with a select query

Chewy Newbie
Currently Being Moderated
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
    Prashant Dabral Explorer
    Currently Being Moderated
    Provide sample data of chained row table.
  • 2. Re: help with a select query
    jeneesh Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    anyone can help?
    many thanks!
  • 7. Re: help with a select query
    Cherif bh Explorer
    Currently Being Moderated
    Hi ,

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

    This is helpful for you .

    Thanks,
  • 8. Re: help with a select query
    Chewy Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points