Coalescing? Anyone?

Coalescing? Anyone?

Post by sandeep.si.. » Sun, 31 Dec 1899 09:00:00



Hello all,

I am trying to coalesce a tablespace that has
a fragmentation index of 21 (according to DB
Artisan). The following command:

ALTER TABLESPACE ${TBLSPACE_NAME} COALESCE;

returns without errors but the fragmentation
index remains unchanged. There are no objects
in this tablespace -the space is 100% available
so, why does it not coalesce?

There are 6 raw devices (Veritas) in this
particular tablespace and even proceeded to
drop these raw devices and recreated them -
but lo and behold -when I created the table
space again, the fragmentation index was back
to 21!

How does one get a contiguous space out of this
tablespace? Thanks in advance for taking the
time to respond...

Sandeep

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Coalescing? Anyone?

Post by markp7.. » Sun, 31 Dec 1899 09:00:00




> Hello all,

> I am trying to coalesce a tablespace that has
> a fragmentation index of 21 (according to DB
> Artisan). The following command:

> ALTER TABLESPACE ${TBLSPACE_NAME} COALESCE;

> returns without errors but the fragmentation
> index remains unchanged. There are no objects
> in this tablespace -the space is 100% available
> so, why does it not coalesce?

> There are 6 raw devices (Veritas) in this
> particular tablespace and even proceeded to
> drop these raw devices and recreated them -
> but lo and behold -when I created the table
> space again, the fragmentation index was back
> to 21!

> How does one get a contiguous space out of this
> tablespace? Thanks in advance for taking the
> time to respond...

> Sandeep

If there are no objects in the tablespace then the alter tablespace x
coalesce command should result in all free space being in one
contiguous extent per data file in the tablespace.  Is the tablespace
made up of more than one file?  If so then the size of the largest file
minus the header block will be the size of your largest free extent in
the tablespace and would explain what you are seeing.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

1. COALESCE in MSSQL7.0

I am trying to port my stored procs from 6.5 to 7.0 Everything works as is
except for COALESCE.

The 7.0 Query Anaylzer chokes on the coalesce statement.
This used to work in 6.5

Is there any way to accomplish this in 7.0?
I am surprised that MS would remove a good function without replacing it.

COALESCE functions by selecting the first non-null result in a list
for example

select coalesce(max(inxnum)+1,1) ...

would return the maximum value of the column inxnum, or 1 if there are none.

2. Security manager problem with OpenLink request broker

3. Bug in Coalesce?

4. query dilemma

5. Update FROM and COALESCE

6. How do I return integer values from an sql query??

7. COALESCE + variables problem

8. Free ODBC tool

9. cant use IN with COALESCE?

10. Using coalesce with stored procedures

11. Dynamic SQL, Coalesce, Stored Procs execution plans and cost

12. Bug? Coalesce and group by in SQL 2k

13. Coalesce: building dynamic query in stored procedure