Hi There,
have found the code below on the net, but when i use sp_placeobject 'default', object, the index is automatically moved to the default segment.
I can drop the other (old) segment without any problems.
Is this correct? Is the code and comments below, obsolete?
<code>
/* move a table, in its entirety, to the new segment */
drop index 'employee.idx_employee'
go
create clustered index on employee (emp_id) on new_seg
go
/* leave table where it is, but future allocations go to the new segment */
sp_placeobject new_seg , 'employee'
go
/* leave table where it is, but future allocations for the
text column (employee_notes) go to the new segment */
sp_placeobject new_seg , 'employee.temployee'
go
/* leave table where it is, but future allocations for the
text column (resume) go to the new segment */
sp_placeobject new_seg , 'student.tstudent'
go
</code>
Regards,
Harry
What is your ASE version, the locking scheme in use by your table, and is the index in question clustered or nonclustered?
While there's a small blurb in the usage notes for sp_placeobject that mentions the table moves with the clustered index (obvious for APL, not so obvious for DOL), a quick review of the sp_placeobject code appears to show the table and placement index (ie, DOL clustered index) also moving together. Net result is that sp_placeobject appears to be hard coded to keep the table and clustered index together.
The good news is that sp_placeobject has a 3rd parameter for a partition name (@pttname). [Undocumented in ASE 15.7; documented in ASE 16.]
On my ASE 15.7 SP136 dataserver I've verified that supplying the table's partition name as the 3rd argument to sp_placeobject allows me to move the table to a new segment without moving the DOL clustered index. (Obviously you can't separate a APL table from its clustered index.)
Add a comment