2887302 - How to place database indexes from one segment to another segment ? - SAP ASE

• Add a new range partition using "alter table" command, the non-clustered index is placed into 'default' segment.
• An index was created without the "on" clause.

SAP ASE 15.7 or above

1. Create a new device and add this device into the database

disk init
name='test_part',physname='$SYBASE/data/test_part.dat',size='10M'
go

alter database test on test_part=10
go

2. Define a new segment named with 'test_part'

sp_addsegment 'test_part',test,'test_part'
go

3. Create a range partition table and a non-clustered local index

CREATE TABLE audit_event (
id                numeric(19,0),
dummy_timestamp   dattime not null,
dummy_timestamp1  datetime
)
lock datarows
PARTITION BY RANGE (dummy_timestamp) (
        dummy_M12018 values <=  ("Jan 31 2018 11:59:59:999PM") on test_part,
        dummy_M22018 values <=  ("Feb 28 2018 11:59:59:999PM") on test_part,
        dummy_M32018 values <=  ("Mar 31 2018 11:59:59:999PM") on test_part,
        dummy_M42018 values <=  ("Apr 30 2018 11:59:59:999PM") on test_part,
        dummy_M52018 values <=  ("May 31 2018 11:59:59:999PM") on test_part,
        dummy_M62018 values <=  ("Jun 30 2018 11:59:59:999PM") on test_part,
        dummy_M72018 values <=  ("Jul 31 2018 11:59:59:999PM") on test_part,
        dummy_M82018 values <=  ("Aug 31 2018 11:59:59:999PM") on test_part,
        dummy_M92018 values <=  ("Sep 30 2018 11:59:59:999PM") on test_part,
        dummy_M102018 values <= ("Oct 31 2018 11:59:59:999PM") on test_part,
        dummy_M112018 values <= ("Nov 30 2018 11:59:59:999PM") on test_part,
        dummy_M122018 values <= ("Dec 31 2018 11:59:59:999PM") on test_part
)
go

CREATE NONCLUSTERED INDEX idx2
        ON dummy_table(dummy_timestamp1) local index dummy_M12018 on test_part, dummy_M22018 on test_part, dummy_M32018 on test_part, dummy_M42018 on test_part, dummy_M52018 on test_part, dummy_M62018 on test_part, dummy_M72018 on test_part, dummy_M82018 on test_part, dummy_M92018 on test_part, dummy_M102018 on test_part, dummy_M112018 on test_part, dummy_M122018 on test_part
go

4. Add a new partition

ALTER TABLE dummy_table
ADD PARTITION (dummy_M12020 values <=   ("Jan 31 2020 11:59:59:999PM") on test_part)
go

5. Use sp_helpindex will find the non-clustered local index placed at 'default' segment

sp_helpindex dummy_table
go
Object has the following indexes
 
 index_name           index_keys   
...omit...
 index_ptn_name               index_ptn_seg                index_ptn_comp                      
 ---------------------------- --------------------------         ------------------------------------

dummy_M122018                test_part                         inherit from index                  
 idx2_690098468                 default                            inherit from index     
...omit...

• 'alter table... add partition' only have parameter let ASE know which segment should keep the data of new partition.
• So the new local index will be placed to 'default' segment when the new partition be added.

Use system procedure 'sp_placeobject' to let all the non-clustered local index be stored in a special segment.

1. Create a new database device and a new segment

disk init
name='test_part1',physname='$SYBASE/data/test_part1.dat',size='10M'
go
alter database on test_part1=10
go
sp_addsegment 'test_part1',test,'test_part1'
go

2. Use sp_placeobject to move all the non-clustered local index to this new segment

sp_placeobject 'test_part1','dummy_table.idx2'
go

3. Use sp_helpindex check the local index segment

 dummy_M122018                test_part1                        inherit from index                  
 idx2_690098468                  test_part1                        inherit from index

KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , How To