SAP Knowledge Base Article - Public

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

Symptom

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

Environment

SAP ASE 15.7 or above

Reproducing the Issue

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

 1> sp_helpindex dummy_table
2> 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...

Cause

'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.

Resolution

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

Keywords

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

Product

SAP Crystal Reports 2013