-
But index coalesce does not reduce the height of the index.If the said indexes have 5-6 branches index rebuild would be a better idea.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Rebuild or coalesced?
Then, I found following information for one of the tablespace that has been growing very fast:
select block_id, sum(bytes) from dba_free_space
where tablespace_name like 'XYZ'
group by block_id;
block_id sum(bytes)
562209 419430400
and
select total_bytes, bytes_coalesced, total_blocks, blocks_coalesced from dba_free_space_coalesced
where tablespace_name like 'XYZ';
total_bytes bytes_coalesced total_blocks blocks_coalesced
419430400 419430400 51200 51200
It seems to me system automatically coalesced the free space.
The branch level is only up to 3 from dba_ind_subpartitions.
-
The tablespaces are all locally managed and the initial=next, pctincrease=0. So it's not necessary to coalesce tablespaces.
The table is a composite partitioned table (rang_hash). If I rebuild the index, I have to rebuild at subpartition level, alter index rebuild subpartition xyz ...
Is there a command to coalesce index for this kind of table?
-
Originally posted by lgaorcl
Is there a command to coalesce index for this kind of table?
Yes.
Alter Index INDEX_NAME Coalesce;
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
What I really like to know is: is there any command down to subpartition level like alter index ... rebuild subpartition ....? Because this is a composite partitioned table.
-
A 320 column table has 85 indexes and the table has 40 M rows and it's size 40 GB
PeopleSoft perchance
-
===
PeopleSoft perchance
===
No, it is Siebel.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|