Skip to content

how to calculate avg row size including blobs

how to calculate avg row size including blobs published on Комментариев к записи how to calculate avg row size including blobs нет

NOTE: RUN ON STANDBY IF POSSIBLE =)

alter session force parallel query parallel 4;
set timing on
set SERVEROUTPUT ON
DECLARE
 l_vc2_table_owner VARCHAR2(30) := '&TABLE_OWNER';
 l_vc2_table_name VARCHAR2(30) := '&TABLE_NAME';
 /* sample number of rows */
 l_nu_sample_rows NUMBER := 1000000;

 /* loop through columns in the table */
 CURSOR l_cur_columns IS
 SELECT column_name, data_type FROM dba_tab_columns
 WHERE owner = l_vc2_table_owner
 AND table_name = l_vc2_table_name;
 l_rec_columns l_cur_columns%ROWTYPE;
 l_vc2_sql VARCHAR2(10000);
 l_avg_row_size NUMBER(10,2);
 BEGIN
 l_vc2_sql := '';
 OPEN l_cur_columns;
 FETCH l_cur_columns INTO l_rec_columns;
 /* loop through columns */
 WHILE l_cur_columns%FOUND
 LOOP
 /* if LOB datatype use dbms_log.get_length to find length */
 IF l_rec_columns.data_type = 'CLOB' OR l_rec_columns.data_type = 'BLOB' THEN
 l_vc2_sql := l_vc2_sql || 'NVL(dbms_lob.getlength(' || l_rec_columns.column_name || '), 0) + 1';
 ELSE
 l_vc2_sql := l_vc2_sql || 'NVL(VSIZE(' || l_rec_columns.column_name || '), 0) + 1';
 END IF;
 FETCH l_cur_columns INTO l_rec_columns;
 IF l_cur_columns%FOUND THEN
 l_vc2_sql := l_vc2_sql || ' + ';
 END IF;
 END LOOP;
 IF l_vc2_sql IS NOT NULL THEN
 l_vc2_sql := 'SELECT 3 + AVG(' || l_vc2_sql || ') FROM ' || l_vc2_table_owner || '.' || l_vc2_table_name
 || ' WHERE rownum < ' || l_nu_sample_rows;
 EXECUTE IMMEDIATE l_vc2_sql INTO l_avg_row_size;
 dbms_output.put_line(l_vc2_table_owner || '.' || l_vc2_table_name || ' average row length: ' || l_avg_row_size);
 ELSE
 dbms_output.put_line('Table ' || l_vc2_table_owner || '.' || l_vc2_table_name || ' not found');
 END IF;
 END;
 /

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика