CODEWITHSUNDEEP
sqloracle-database
Scouse_Bob
Scouse_Bob

Reputation: 546

How can I get this query to output for multiple values in a table

I have a query which will give me the average size in bytes for a given table name.

I intend to use it as an estimate for a sql result set, for example, a 100 byte table has 100 rows, it'll work out each row may be 1 byte. Therefore a select on the table with a where clause that returns 25 rows may be estimated to be 25 bytes.

It's a rudimentary capacity planner. Which having looked at other possible methods, i'd prefer compared to anything that is reliant on stats.

I have tried the code below, which works fine for 1 table. 

SELECT a.total_bytes / b.num_rows avg_row_size
FROM (SELECT SUM(bytes) total_bytes
FROM dba_extents
WHERE owner = 'KEVIN'
AND segment_name = 'KEVS_TABLE' ) a,
(SELECT COUNT(*) num_rows
FROM KEVS_TABLE) b

However, i'd like this to loop through and give results for a whole load of tables. I've tried: 

SELECT a.total_bytes / b.num_rows avg_row_size
FROM (SELECT SUM(bytes) total_bytes
FROM dba_extents
WHERE 
segment_name in (select table_name from table_list) ) a,
(SELECT COUNT(*) num_rows
FROM table_list) b

But it just returns nothing.

Any ideas how I can get it to return a value for each row in the table_list?

Upvotes: 2

Views: 405

Answers (1)

GMB
GMB

Reputation: 222682

I think that you are overcomplicating this. As far as concerned, Oracle system view ALL_TABLES gives you the information you are looking for, in column AVG_ROW_LEN.

The documentation says:

AVG_ROW_LEN - NUMBER : Average length of a row in the table (in bytes)

NB: AVG_ROW_LENGTH is populated only if statistics have been collected on the corresponding table.

So you should be able to use a query like:

SELECT table_name, avg_row_length FROM all_tables WHERE owner = 'KEVIN';

Or, if you want to check all tables in your current schema, just use USER_TABLES:

SELECT table_name, avg_row_length FROM user_tables;

NB: please note that ALL_TABLES (and USER_TABLES) contains many other information that can be useful for your use case, provided the related tables are analyzed recently, i.e. magnitude of data close to real-time, for example:

  • NUM_ROWS: Number of rows in the table
  • BLOCKS: Number of used data blocks in the table (you can multiply that with the block size of the tablespace that hosts the table to compute its total size in bytes)
  • LAST_ANALYZED: Date on which this table was most recently analyzed

Upvotes: 2

Related Questions