Home » RDBMS Server » Server Administration » Returning free space from index to the tbsp (Oracle, 9.2.0.6.0, Win 2003)
Returning free space from index to the tbsp [message #304473] Wed, 05 March 2008 06:11 Go to next message
irremediable
Messages: 38
Registered: December 2007
Member

Hi, All!
I have a table that contains about 15 mill of rows that
holds account saldo that is loaded into the table
each night. The table has four indeces, one unique (AK_ACCOUNT_SALTRAN),
and three normal.
This is how I created the table
 create table FCT_ACOUNT_SALTRAN
(
  ID_FINSTR  INTEGER,
  ID_ACCOUNT INTEGER,
  ID_SUBSYS  INTEGER,
  ID_CHAPTER INTEGER,
  SALDO      NUMBER(20,8),
  SALDOEKV   NUMBER(20,8),
  TR_DEB     NUMBER(20,8),
  TR_DEBEKV  NUMBER(20,8),
  TR_CR      NUMBER(20,8),
  TR_CREKV   NUMBER(20,8),
  IS_32      CHAR(1 CHAR),
  IS_TURN    INTEGER,
  DT_OPEN    DATE,
  WHMOMENT   DATE,
  SYSMOMENT  DATE,
  ID_FILE    NUMBER(16),
  DT_CLOSE   DATE
)


After
  ANALYZE INDEX AK_ACCOUNT_SALTRAN VALIDATE STRUCTURE
  SELECT * FROM INDEX_STATS
 


I noticed that the value of BTREE_SPACE column is
532141728 and USED_SPACE is 354425030 what means
that nearly 33% of the total space allocated for the index is
is not used.

What could be the possible reasons of existing so much
unused space in the index?
Thank you.








Re: Returning free space from index to the tbsp [message #304519 is a reply to message #304473] Wed, 05 March 2008 08:23 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
What was the STORAGE clause used for the table and index creation?

PCTUSED
PCTFREE
INITIAL
NEXT
etc.

What version of database?
Locally managed tablespace vs. Dictionary managed?
Re: Returning free space from index to the tbsp [message #304636 is a reply to message #304473] Wed, 05 March 2008 23:26 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member

Database version is 9.2.0.6.0
The tbsp both for the table and the index is locally managed.
And here are the storage parameters
for the table
  tablespace DWH_TEMP
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 80K
    minextents 1
    maxextents unlimited
  );
  

and the index
  alter table FCT_ACOUNT_SALTRAN
  add constraint AK_ACCOUNT_SALTRAN unique (ID_ACCOUNT, DT_OPEN)
  using index 
  tablespace INDX_TEMP
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
  
Re: Returning free space from index to the tbsp [message #305452 is a reply to message #304473] Mon, 10 March 2008 16:45 Go to previous messageGo to next message
shlomi_bt
Messages: 16
Registered: August 2005
Location: Canada
Junior Member

hi,
can you try the following:
sql> analyze index <IDX> validate structure;
sql> select * from index_stats;
one right after the other..
shlomi.
Re: Returning free space from index to the tbsp [message #305596 is a reply to message #304473] Tue, 11 March 2008 05:59 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member


Hi, here it is.
    HEIGHT	3
BLOCKS	68096
NAME	AK_ACCOUNT_SALTRAN
PARTITION_NAME	
LF_ROWS	14914576
LF_BLKS	66990
LF_ROWS_LEN	357798767
LF_BLK_LEN	8000
BR_ROWS	66989
BR_BLKS	182
BR_ROWS_LEN	1084825
BR_BLK_LEN	8032
DEL_LF_ROWS	0
DEL_LF_ROWS_LEN	0
DISTINCT_KEYS	14914576
MOST_REPEATED_KEY	1
BTREE_SPACE	537381824
USED_SPACE	358883592
PCT_USED	67
ROWS_PER_KEY	1
BLKS_GETS_PER_ACCESS	4
PRE_ROWS	0
PRE_ROWS_LEN	0
OPT_CMPR_COUNT	1
OPT_CMPR_PCTSAVE	255 
    
Re: Returning free space from index to the tbsp [message #305984 is a reply to message #304473] Wed, 12 March 2008 08:29 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member


Guys, anybody will suggest smth?
Re: Returning free space from index to the tbsp [message #309848 is a reply to message #304473] Sat, 29 March 2008 03:57 Go to previous message
irremediable
Messages: 38
Registered: December 2007
Member


I quess you guys having a holiday now....
Have a good rest then...........
Previous Topic: moving an user
Next Topic: 8i to 9i migration
Goto Forum:
  


Current Time: Thu Dec 05 17:20:58 CST 2024