I have question about Sybase ASE 15.7 Partitioning

[lisa_sayre]

Environment ASE 15.7 SP132 on Solaris 64bit with Partitions enabled.

I am trying to keep my table partitions in a separate defined segment/device and my index

partitions on that partition table in a separate defined segment/device.

My problem is that when I go to define a local index on the partitioned table, I specify a segment

that I want to contain just the indexes (partitioned indexes), but it appears that the partitioned

table and the indexes now belong to the segment/device that is created just for the indexes.

I have a partitioned table, which is partitioned by Range, based on date range.  The date range is

monthly.  12 months worth of data (1 year of data in the table) is stored in one defined segment

which belongs to one physical device.

For example:

Here is my partitioned table.  It's range partitioned, and each year's worth of data is stored in a

segment that is defined/created in a separate physical device. 

Segment infraaudit2015seg is created in device infra_device1. 

Segment infraaudit2016seg is created in device infra_device2.

Segment infraaudit2017seg is created in device infra_device3.

Segment infraaudit2018seg is created in device infra_device4.

Segment infraaudit2019seg is created in device infra_device5.

I want a year's worth of partitioned data stored in one device.  Each year has it's own physical

device.  Each of those devices have the the default and system segments dropped.  However, there is

one device associated to the database that this table belongs to that has a default segment still

defined.

CREATE TABLE audit_event (

audit_event_id                  NUMERIC(19,0) identity,

audit_event_timestamp           DATETIME not null,

audit_action                    VARCHAR(8) not null,

audit_admin                     VARCHAR(255) not null,

audit_organization              VARCHAR(255) null,

audit_object                    VARCHAR(32) not null,

audit_object_id                 INT not null,

audit_sequence                  TINYINT not null,

audit_column_name               VARCHAR(125) not null,

column_old_value                UNIVARCHAR(2000) null,

column_new_value                UNIVARCHAR(2000) null,

audit_identity                  UNIVARCHAR(512) not null)

lock datarows

PARTITION BY RANGE (audit_event_timestamp) (

        event_M62015 values <=  ("Jun 30 2015 11:59:59:999PM") on infraaudit2015seg,

        event_M72015 values <=  ("Jul 31 2015 11:59:59:999PM") on infraaudit2015seg,

        event_M82015 values <=  ("Aug 31 2015 11:59:59:999PM") on infraaudit2015seg,

        event_M92015 values <=  ("Sep 30 2015 11:59:59:999PM") on infraaudit2015seg,

        event_M102015 values <= ("Oct 31 2015 11:59:59:999PM") on infraaudit2015seg,

        event_M112015 values <= ("Nov 30 2015 11:59:59:999PM") on infraaudit2015seg,

        event_M122015 values <= ("Dec 31 2015 11:59:59:999PM") on infraaudit2015seg,

        event_M12016 values <=  ("Jan 31 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M22016 values <=  ("Feb 29 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M32016 values <=  ("Mar 31 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M42016 values <=  ("Apr 30 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M52016 values <=  ("May 31 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M62016 values <=  ("Jun 30 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M72016 values <=  ("Jul 31 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M82016 values <=  ("Aug 31 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M92016 values <=  ("Sep 30 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M102016 values <= ("Oct 31 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M112016 values <= ("Nov 30 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M122016 values <= ("Dec 31 2016 11:59:59:999PM") on infraaudit2016seg,

        event_M12017 values <=  ("Jan 31 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M22017 values <=  ("Feb 28 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M32017 values <=  ("Mar 31 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M42017 values <=  ("Apr 30 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M52017 values <=  ("May 31 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M62017 values <=  ("Jun 30 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M72017 values <=  ("Jul 31 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M82017 values <=  ("Aug 31 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M92017 values <=  ("Sep 30 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M102017 values <= ("Oct 31 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M112017 values <= ("Nov 30 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M122017 values <= ("Dec 31 2017 11:59:59:999PM") on infraaudit2017seg,

        event_M12018 values <=  ("Jan 31 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M22018 values <=  ("Feb 28 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M32018 values <=  ("Mar 31 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M42018 values <=  ("Apr 30 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M52018 values <=  ("May 31 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M62018 values <=  ("Jun 30 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M72018 values <=  ("Jul 31 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M82018 values <=  ("Aug 31 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M92018 values <=  ("Sep 30 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M102018 values <= ("Oct 31 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M112018 values <= ("Nov 30 2018 11:59:59:999PM") on infraaudit2018seg,

        event_M122018 values <= ("Dec 31 2018 11:59:59:999PM") on infraaudit2018seg,

    event_M12019 values <=  ("Jan 31 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M22019 values <=  ("Feb 28 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M32019 values <=  ("Mar 31 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M42019 values <=  ("Apr 30 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M52019 values <=  ("May 31 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M62019 values <=  ("Jun 30 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M72019 values <=  ("Jul 31 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M82019 values <=  ("Aug 31 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M92019 values <=  ("Sep 30 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M102019 values <= ("Oct 31 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M112019 values <= ("Nov 30 2019 11:59:59:999PM") on infraaudit2019seg,

        event_M122019 values <= ("Dec 31 2019 11:59:59:999PM") on infraaudit2019seg

)

GO

I want to define local indexes on the audit_event partition table, such that the indexes pick up

the same partitioning scheme.  When I create the indexes using just the word local, example:

CREATE nonclustered index idx2_audit_event

ON audit_event(audit_event_timestamp, audit_sequence, audit_organization, audit_object,

audit_column_name, LOWER(audit_identity)) local index

GO

Then, the index and all of the partitions are associated to the default segment.  The audit_event

partitions no longer belong to the infraauditYYYYseg segments that I created. I do not want this.

If I create a new device called partition_indexes, and drop the default and system segments from

that device, and create a new segment in that device called index_infraseg, and then try to create

the index using this syntax:

CREATE nonclustered index idx2_audit_event

ON audit_event(audit_event_timestamp, audit_sequence, audit_organization, audit_object,

audit_column_name, LOWER(audit_identity)) on index_infraseg local index idx2ae on index_infraseg

GO

Then the index appears to be part of the index_infraseg, but then so do all of the partitions.  The

partitions no longer belong to the infraauditYYYYseg segments, and now belong to the index_infraseg

segment.

How can I keep my audit_event partitions in separate segments/devices from the indexes which I want

in the index_infraseg segment/parition_index device?

Thanks

Lisa Sayre

[lisa_sayre]

Hi Mark,

One more quick question....hopefully...

Would you recommend putting the local index into the same partition as where the data is going?

For instance, I have the audit_event table partitioned with a partition name of event_M72015 that represents data for the month of July, and resides in segment infraaudit2015seg.

If I create a local index on the audit_event table, and provide it with the same partition name of event_M72015 and segment name infraaudit2015seg, I am then assuming that both the index and data will reside in the same partition and segment.

I'm worried that this may not be good for performance and I/O operations.  I have read Sybase documentation and some technical white papers, but none of them talk specific about isolating the indexes from the data in separate partitions.  I could of missed it somewhere though.....

So I'm considering placing the index into a separate partition name, but same segment.  So for example, the local index would be in the idx1_event_M72015 partition (a new partition for the index), but the same segment of infraaudit2015seg.

Appreciate any thoughts on the matter!  thanks!

Lisa

[lisa_sayre]

Ah....

And, when I capture query execution plans, the optimizer tends to favor the clustered index with respect to the types of queries we are writing, and so far I am seeing great performance with large data sets.  So I would prefer to keep my clustered index.

Thank you for all of your assistance.  You have been extremely helpful!!!

Lisa

[lisa_sayre]

Hi Mark,

So, I retried as per your suggestion, and I'm still seeing my problem.  Maybe it's works differently with ASE 15.7 SP132.   Attached is a log named beforeindex.txt.   In this log, I have created the audit_event partition/table and performed a sp_help audit_event.  You can see the partitions are in the various segments.  Attached is a log named afterindex.sql.  This is what I see after I run the modified index DDL that reflects similar to how you created your indexes.  I now see the partitions for the audit_event table belong to the index_infraseg segment, and no longer belong to the segments that are specified in the create table DDL.  Sigh.....

Maybe I'll try just running your scripts.  Since I have been looking at this for so long, I may be overlooking something trivial.  But I am content to have the index partitions along with the table partitions in a specified segment.  I may just dump my original design, and go with that.

thanks again for all your help.

beforeindex.txt.zip

afterindex.txt.zip

[lisa_sayre]

Hi Mark,

I am new to this site, and so I am struggling to figure out how to add an attachment.  Sorry!

I just figured it out, and have made edits to this thread since then.

In the helpbefore.out file, there is the DDL for the table partition audit_event and output from sp_help audit_event.  In this output, you can see the table partitions belong to the various segments that span across multiple devices.  At this point, the audit_event table/partition has no indexes defined.  In the helpafter.out file, there is the DDL for the indexes on the audit_event table, and output from sp_help audit_event.  In this output, you can see the table partitions now belonging to the segment that is assigned to the indexes - index_infraseg.

However, if you look at the index DDL, you can see that I was specifying the segment name prior to the local index partition clause.  So, you will see an extra "ON index_infraseg" before the local index statement.  I'm wondering if this is the culprit.  I am going to remove that statement and try again.  I'll post shortly my results. Thank you so much for your assistance.

Lisa

helpafter.txt.out.zip

helpbefore.txt.out.zip

[lisa_sayre]

Thank you for your suggestion!  Unfortunately, when I do this, it places the partition dtrange2015q3 into the iseg15 segment, and it no longer lives in the dseg15 segment.  This happens to all table partitions.  They move from the specified segment in the create table statement, to the segment specified in the create index statement.  The segments you have in your example belong to the same database and device.  The segments in my example, belong to the database database, but different devices.  Perhaps that is what is causing this to occur.  I did adjust my scripts to specify the table partition and segment.  This seems to be keeping my table partition in the segment that is specified in the create table statement, and create the index partition in the same segment as the table partition.  I guess I'm okay with that design.  I was hoping to separate the index partitions out into a separate segment/device. 

The create index syntax from Sybase documentation for the index partitions is the following:

index_partition_clause::=

[local index [partition_name [on segment_name]

[,partition_name [on segment_name]...]]]

So, then I created the my index this way:

CREATE clustered index idx1_audit_event

        ON audit_event(audit_event_timestamp, audit_sequence, audit_object, audit_column_name, audit_object_id) ON index_infraseg local index event_M62015 on infraaudit2015seg, event_M72015 on infraaudit2015seg, event_M82015 on infraaudit2015seg, event_M92015 on infraaudit2015seg, event_M102015 on infraaudit2015seg, event_M112015 on infraaudit2015seg, event_M122105 on infraaudit2015seg, event_M12016 on infraaudit2016seg, event_M22016 on infraaudit2016seg, event_M32016 on infraaudit2016seg, event_M42016 on infraaudit2016seg, event_M52016 on infraaudit2016seg, event_M62016 on infraaudit2016seg, event_M72016 on infraaudit2016seg, event_M82016 on infraaudit2016seg, event_M92016 on infraaudit2016seg, event_M102016 on infraaudit2016seg, event_M112016 on infraaudit2016seg, event_M122016 on infraaudit2016seg, event_M12017 on infraaudit2017seg, event_M22017 on infraaudit2017seg

GO

This syntax is keeping the table partitions in the infraauditYYYYseg segments, and placing the index partitions into the infraauditYYYYseg as well.  Not what I was going for, but it may be the best I can do.  At least the data and indexes are now into the different segments/devices.  I'm just trying to reduce the amount of I/O on the physical devices by keeping monthly data partitions for 1 year into 1 device.  Maybe that is moot point with Sybase.  I know with Oracle, DBA's like to have the data and indexes partitioned across many tablespaces for backup, maintenance and high availability (RAID) solutions.

Thanks again for your help.