Skip to Content
author's profile photo H. Splinter
0
H. Splinter

move objects back to the 'default' segment

Posted on Aug 15, 2016 at 09:17 AM 61 Views

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Mark A Parsons
    Mark A Parsons
    Posted on Aug 15, 2016 at 02:48 PM
    4

    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
    10|10000 characters needed characters exceeded

    • H. Splinter
      Aug 16, 2016 at 05:17 AM

      Hi Mark,

      thanks for having a look into my question. My ASE version is: Adaptive Server Enterprise/15.7/EBF 24641 SMP SP134 /P/X64/Windows Server/ase157sp133x/3925/64-bit/OPT/Sat May 02 00:22:51 2015. The table is APL with clustered index. I will have some further experiments with DOL because almost all tables within this environment are DOL. It is b.t.w. good news that it moves with the clustered index. That will make removing all my user defined segments easier! The documentation had put me off.

      Regards

      Harry

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.