Forum

General discussion on using the Kognitio Analytical Platform.
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

How can I find out number of rows on disk for lots of tables

by markc » Tue Mar 04, 2014 11:33 am

I want to find out how many rows there are on disk for a lot of tables, but do not want to to a table scan for each one. I also want the results to be neatly formatted in one query. Is there a way to do this?
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: How can I find out number of rows on disk for lots of ta

by markc » Tue Mar 04, 2014 11:35 am

What you want to do is use SYS.IPE_FTABLE which contains metadata for each (disk resource, slab, table) combination, and join that to other dictionary tables to get a well-formatted result.

The following should work:

SELECT S.NAME, T.NAME, SUM(F.NROWS)
FROM SYS.IPE_SCHEMA S, SYS.IPE_TABLE T, SYS.IPE_FTABLE F
WHERE S.ID = T.SCHEMA_ID AND T.ID = F.TABLE_ID
GROUP BY 1,2
ORDER BY 1,2
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: How can I find out number of rows on disk for lots of ta

by markc » Tue Mar 04, 2014 11:53 am

Note that if you want to see entries for table with no rows on disk you will need to LEFT JOIN in SYS.IPE_FTABLE. Likewise, if you want to see entries for tables which have been dropped, you will need to use a FULL OUTER JOIN to SYS.IPE_FTABLE.
Reply with quote Top
Contributor
Offline
Posts: 185
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: How can I find out number of rows on disk for lots of ta

by MikeAtkinson » Thu Mar 06, 2014 3:45 pm

The Console Ctrl-F6 built in query (and wxsubmit $F6 query) will show the number of rows, and disk usage for tables (including dropped tables) within the current schema.

Modifying it slightly gives data for all tables in the system.

Code: Select all

--
-- Disk Use by table for all schemas
--
select
  cast(case when g.tid=-1 then '_Total' 
            when g.sname is null and g.tname is null then '<dropped #' || to_char(g.tid) || '>'
            else substring(trim(g.sname) || '.' || trim(g.tname) from 1 for 40) end as varchar(40)) as "Table Name",
  coalesce(cast(to_char(case when g.tid=-1 then null else g.nrows end,'999,999,999,999') as varchar(15)),'') as "Num Rows",
  cast(to_char(g.bactive/power(1024,3),'9,990.9') as varchar(7)) as "Act GB",
  cast(to_char(g.bdel/power(1024,3),'9,990.9') as varchar(7)) as "Del GB",
  cast(to_char((g.btrunc+g.bdrop)/power(1024,3),'9,990.9') as varchar(7)) as "TrDr GB"
from
  (select
    decode(grouping(s.tid),0,s.tid,1,-1,null) as tid,
     max(st.sname) as sname,
     max(st.tname) as tname,
     sum(s.nrows) as nrows,
     sum(s.bactive) as bactive,
     sum(s.bdel) as bdel,
     sum(s.btrunc) as btrunc,
      sum(s.bdrop) as bdrop
  from
     (select  
        f.table_id as tid,
        f.nrows as nrows,
        decode(f.drop_tno,2147483647,decode(f.words,-1,null,f.words),0)*4 as bactive,
        decode(f.del_words,-1,null,f.del_words)*4 as bdel,
        decode(f.trunc_words,-1,null,f.trunc_words)*4 as btrunc,
        decode(f.drop_tno,2147483647,0,decode(f.words,-1,null,f.words))*4 as bdrop
      from sys.ipe_ftable f) s
        left outer join
     (select t.id as tid,s.id as sid,s.name as sname,t.name as tname 
      from sys.ipe_table t inner join sys.ipe_schema s on t.schema_id=s.id) st on s.tid=st.tid
   where coalesce(trim(st.sname),'<drop>') like '%' 
     and coalesce(trim(st.tname),'<drop>') not like 'IPE_%'
     --and ((current_user_id<>0 and st.sid=current_schema_id)
     --  or current_user_id=0)
   group by grouping sets((s.tid),()) ) g
order by 1
at now;
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron