OraFAQ Forum: Server Administration » Returning free space from index to the tbsp

Home » RDBMS Server » Server Administration » Returning free space from index to the tbsp (Oracle, 9.2.0.6.0, Win 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Returning free space from index to the tbsp [message #304473]

Wed, 05 March 2008 06:11

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.

Report message to a moderator

Re: Returning free space from index to the tbsp [message #304519 is a reply to message #304473]

Wed, 05 March 2008 08:23

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?

Report message to a moderator

Re: Returning free space from index to the tbsp [message #304636 is a reply to message #304473]

Wed, 05 March 2008 23:26

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
  );

Report message to a moderator

Re: Returning free space from index to the tbsp [message #305452 is a reply to message #304473]

Mon, 10 March 2008 16:45

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.

Report message to a moderator

Re: Returning free space from index to the tbsp [message #305596 is a reply to message #304473]

Tue, 11 March 2008 05:59

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

Report message to a moderator