Maximum Row Length? 2

[Ed2020]

Hi,

I have been asked to perform some analysis on some tables within a large Oracle database to determine the maximum row length.

I thought I could get the information from ALL_TAB_COLUMNS, with something along the lines of:

SELECT
    SUM(?????)
FROM
    ALL_TAB_COLUMNS
WHERE
    TABLE_NAME='NAME_OF_TABLE'

However this doesn't seem to do what I need when the table includes number or date fields.

If somebody could point me in the right direction I'd appreciate it!

Ed Metcalfe.

Please do not feed the trolls.....

 

[SantaMufasa]

Ed,

Are you looking for the Maximum theoretical row length (e.g. col_1 varchar2(100), col_2 varchar(10) = 110), or are you looking for the highest actual storage consumption for a row?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Ed2020]

Hi,

Maximum theoretical row length initially. My guess is I will also be asked to find average row length next, but I'll cross that bridge when I come to it!

Ed Metcalfe.

Please do not feed the trolls.....

 

[SantaMufasa]

Ed,

I can understand the business interest in "average row length" (which you can query from DBA_TABLES.AVG_ROW_LEN, if you have gathered statistics for your tables), but I cannot fathom a business need/interest in the calculated maximum length of the row definitions for a table (since there will never be a real-life case that a row will occupy the maximum length of a row's columns).

Regardless, you have asked for a way to calculate the theoretical max. length, so I suggest you write a query that sums these values from DBA_TABLES:

      Column Length Data Type
=================== =======================================
      2,147,483,647 CLOBs, BLOBs, LONGs
                  7 DATE
        DATA_LENGTH VARCHAR, VARCHAR2, CHAR
DATA_PRECISION * .6 Any NUMBER

All Oracle DATE datatypes occupy 7 bytes, regardles of date value. the ".6" multiplier for NUMBER datatypes is an approximation. The actual length multipliers of numeric maximums vary from 2.0 for a 1-digit numeric data item to .525 for a 40-digit numeric data item. For example, a number with 1 digit of significance occupies 2 bytes of storage; a number with 40 digits of significance occupies 21 bytes of storage.

I hope this helps...Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Ed2020]

Hi,

I agree the answer they get will be a fairly meaningless one. I have questioned what has been requested and they're still saying this is what is required. <shrug>

Thanks for the pointers. Much appreciated.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Dagon]

You may find the vsize function to be of some use. It gives you the size in bytes of a field e.g.

select vsize(sysdate) from dual
7

So you could use something like:

select max(row_length)
from
(select vsize(col1) + vsize(col2) + ... as row_length
from table)

 

[Ed2020]

Thanks Dagon, I'll take a look at that.

Purple stars all round!

Ed Metcalfe.

Please do not feed the trolls.....