|
|
|
|
|
|
Table and Index Resizing
|
Table and Index Resizing (1 of 6), Read 107 times |
|
|
Conf: |
|
|
From: |
|
|
Date: |
Wednesday, August 15, 2001 04:43 PM |
I am attempting to resize all existing tables and indexes in the database. I
currently have approximately 150 tables and 500 indexes that need to be resized.
I have the formula to estimate prospective table and index sizes. Both formula
requires "Average Row Length" and "Number Of Rows". I am
using the table estimate formula but deriving all data from existing DBA_VIEWS
(DBA_SEGMENTS, DBA_TABLES) and using "AVG_ROW_LEN and NUM_ROWS" from
DBA_TABLES (see query below). I use the query below to obtain table information
and use that information to calculate the new table sizes without having to
manually go back and analyze each and every existing table on the database.
However, this is not such a simple task when it comes to indexes. How can I
automatically obtain the current "number of rows" and "average
row length" of these indexes without having to manually go back and analyze
each and every existing index on the database.
i.e. I want to be able to pick up a database column that already has this
information - just like the one for tables.
P.S.
The NUM_ROWS column in the DBA_INDEXES is misleading because it doesn't give the
correct number of rows.
QUERIES
--FOR TABLES
select a.tablespace_name, segment_name SEGMENT, a.bytes/1024 KB, a.BLOCKS,
a.initial_extent, a.next_extent, a.extents TOTAL_EXTENTS, PCT_FREE, PCT_USED,
AVG_ROW_LEN, NUM_ROWS
from dba_segments a, dba_tables b
where a.tablespace_name = b.tablespace_name
and a.segment_name = b.table_name
and a.owner = 'SCOTT'
--GROUP BY segment_name
ORDER BY Tablespace_name
/
--FOR INDEXES
select a.tablespace_name, segment_name, a.bytes/1024 KB, a.blocks,
a.initial_extent, a.next_extent, a.extents TOTAL_EXTENTS, PCT_FREE, '',
sum(column_length) col_len, distinct_keys
from dba_segments a, dba_INDEXES b, dba_ind_columns c
where a.tablespace_name = b.tablespace_name
and a.segment_name = b.index_name
and b.table_name = c.table_name
and b.index_name = c.index_name
and a.owner = 'SCOTT'
--and rownum < 10
GROUP BY a.tablespace_name, segment_name, a.bytes/1024 KB, a.blocks,
a.initial_extent, a.next_extent, a.extents TOTAL_EXTENTS, PCT_FREE, '',
distinct_keys
ORDER BY Tablespace_name
/
|
Topic: |
Table and Index Resizing (2 of 6), Read 65 times |
|
Conf: |
|
|
From: |
|
|
Date: |
Wednesday, August 15, 2001 08:40 PM |
If I needed to know how much space was required by my index, I'd use the
DBMS_SPACE package to determine how much space in your index is unused. Subtract
that from how much is allocated and you'll know how much you need. BTW, it works
for tables too!
HTH,
Brian
Brian Peasland
Database Administrator
OCP DBA (7,8,8i)
|
Topic: |
Table and Index Resizing (3 of 6), Read 66 times, 2 File Attachments |
|
Conf: |
|
|
From: |
|
|
Date: |
Thursday, August 16, 2001 10:57 AM |
You can use a dynamic SQL to create a set of analyze index validate structure
commands, between each one suck the contents of the index_stats into a temp
table, then report on the contents of the temp table, for example:
Michael R. Ault
Senior Technical Management Consultant
TUSC - The Ultimate Software Consultants
Training, Consulting, Monitoring, Development
ACT_SIZE8.SQL
(3KB)
BROWN(4).SQL
(2KB)
|
Topic: |
Table and Index Resizing (4 of 6), Read 60 times |
|
Conf: |
|
|
From: |
|
|
Date: |
Friday, August 17, 2001 12:20 PM |
Thanks Mike (and Brian).
|
Topic: |
Table and Index Resizing (5 of 6), Read 50 times |
|
Conf: |
|
|
From: |
|
|
Date: |
Friday, August 24, 2001 02:29 PM |
Does Percent Browned means it has not been used, or used less often?
please explain
thanks
|
Topic: |
Table and Index Resizing (6 of 6), Read 49 times |
|
Conf: |
|
|
From: |
|
|
Date: |
Friday, August 24, 2001 03:11 PM |
Percent browned has to do with the number of deleted leaf blocks in the
index. If the ratio of deleted leaf blocks to total blocks is 30% or more, then
you should rebuild the index.
Andrew Simkovsky
Pipeline SYSOP
Senior Oracle DBA
