How come Index tablespace is so big?

[newbie]

Hello all, I have a database that was created a little while ago and by looking at the sizes of the tablespace I find that the Index_data1 tablespace is much bigger than data1 tablespace, I'm not sure what why this is the case, how can I fix this problem?? Another newbie question, what's TWO_TASK parameter in Oracle, what is it used for? Thank you all.

[SANJAY_G]

The size of the tablespace in itself should not be problem. Check what are the objects in the tablespace. If you are purging the base tables regularly you should rebuild the indexes. It will release some free space.

Sanjay

[Mr.Hanky]

In a word "fragmentation".

You may need to rebuild.

MH

[jgmagnus]

Using different tablespaces (on different disks)
for a table and its index
produces better performance
than storing the table and index
in the same tablespace, due to
reduced disk contention.

(change schema/index names to your satisfaction and rebuild all your indexes)

spool c:\temp\spools\index_rebuild.sql

select 'alter index '|| OWNER ||'.'|| INDEX_NAME ||'
rebuild
tablespace INDX01;'
FROM dba_indexes
where owner = 'INDEX_OWNER'
AND tablespace_name = 'TABLESPACE_NAME';

spool off;

@c:\temp\spools\index_rebuild.sql

[prakashs43]

Originally posted by newbie
what's TWO_TASK parameter in Oracle, what is it used for? Thank you all.

i have not tested this , let me know after testing

TWO_TASK is used to specifiy a SQLNET or NET8 connect alias. Not necessarily the Oracle SID. When you set TWO_TASK to any value, including NULL. You will not be able to connect internal, because at that point you are going through a sql*net and Oracle can't valid the user id as belonging to the dba group.

You should not set TWO_TASK anytime you want to connect internal if you are logged directly on the server.

ORACLE_SID should always be set in the users environment when they are working with Oracle.

[gandolf989]

You can find out about the following using the DBMS_SPACE package if you analyze your tables. I do an analyze compute, that would be best if you can do it.

I wrote a simple script to look at one table. You will obviously want to change it to produce a report of all of your indexes.

DECLARE
mysegment_owner varchar2(200);
mysegment_name varchar2(200);
mysegment_type varchar2(200);
mytotal_blocks number;
mytotal_bytes number;
myunused_blocks number;
myunused_bytes number;
mylast_used_extent_file_id number;
mylast_used_extent_block_id number;
mylast_used_block number;
BEGIN
mysegment_owner := 'CSCS';
mysegment_name := 'TB_CASE';
mysegment_type := 'TABLE';
DBMS_SPACE.unused_space(
mysegment_owner,
mysegment_name,
mysegment_type,
mytotal_blocks,
mytotal_bytes,
myunused_blocks,
myunused_bytes,
mylast_used_extent_file_id,
mylast_used_extent_block_id,
mylast_used_block);
dbms_output.put_line( 'mytotal_blocks '|| mytotal_blocks);
dbms_output.put_line( 'mytotal_bytes '|| mytotal_bytes);
dbms_output.put_line( 'myunused_blocks '|| myunused_blocks);
dbms_output.put_line( 'myunused_bytes '|| myunused_bytes);
dbms_output.put_line( 'mylast_used_extent_file_id '|| mylast_used_extent_file_id);
dbms_output.put_line( 'mylast_used_extent_block_id '|| mylast_used_extent_block_id);
dbms_output.put_line( 'mylast_used_block '|| mylast_used_block);
END;
/

[stmontgo]

does it really improve performamce though jgmagnus?

interesting discussion on this topic here.........

http://groups.google.com/groups?hl=e...om%26rnum%3D12

[jmodic]

Originally posted by stmontgo
does it really improve performamce though jgmagnus?

Yeah, another one of those old myths that float around in Oracle comunity and refuse to sink....

[JChiappa]

I was thinking in two situations : rebuilding an index (example : after data loads), and IOTs readings (or any SELECT what can be satisfied only by index reads). In this situations, isolating the required index tablespaces in another disks with another controllers can be a good thing for performance, or no ?

Regards,

Chiappa