| Indexes grows extremely when rebuilded [message #69860] |
Thu, 14 March 2002 03:52  |
Sven
Messages: 10
Registered: July 2001
|
Junior Member |
|
|
He again, yet another index quizz...
I've made an export from our production environment where the index tablespace is two files at 150MB, not yet filled.
But when I've imported the data to the development db (without indexes) and then use a script to create them the index tablespace is filled up - even if it's 2x800MB!
So, is there a possibility that the indexes at the production db is not updated or can I assume that they will always grow like hell when they are re-created?
Best regards,
Sven
|
|
|
|
| Re: Indexes grows extremely when rebuilded [message #69863 is a reply to message #69860] |
Thu, 14 March 2002 07:59  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
How are you creating the index? If you do not give a storage clause it will use the tablespace storage clause when you create it. Try this to see what you have for your clauses and usage:
-- This script creates a report on index usage.
-- Created 2/25/00 Grant Howell
set linesize 100
set pagesize 24
column owner format a12
column table_name format a24
column index_name format a30
column tablespace_name format a20
create or replace view v_idx as
select
dba_indexes.owner,
dba_indexes.index_name,
dba_indexes.table_name,
dba_indexes.tablespace_name,
dba_indexes.num_rows * (sum(dba_ind_columns.column_length) + 20) TOTAL_BYTES,
dba_indexes.initial_extent,
dba_indexes.next_extent,
dba_indexes.pct_increase
from
dba_indexes,dba_ind_columns
where
dba_indexes.index_name=dba_ind_columns.index_name
and
owner not in ('SYS','SYSTEM')
group by
dba_indexes.owner,
dba_indexes.index_name,
dba_indexes.table_name,
dba_indexes.tablespace_name,
dba_indexes.num_rows,
dba_indexes.initial_extent,
dba_indexes.next_extent,
dba_indexes.pct_increase
/
select
owner,index_name,total_bytes,initial_extent,next_extent,pct_increase
from
v_idx
order by
owner,index_name
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
