Skip to Content

Setting up semantic partitions ASE

Running  ASE 15.5

We have a  non-partitioned table  with 640 million rows. We are looking at setting up range semantic partitions  by create date

There are 200 + million rows for 2013 and 380 + million rows for 2014

I am thinking about setting up following partitions by create date:

Partitions 1  -  4 :  For 2015  By quarter   

Partition 5 -  For year 2014


Partition 6   -  2013 and and earlier

Add new Partitons for each new  year  ...

Only updating  current data  --  i.e. any data more than month old is no longer updated ..

Is this a viable breakdown ?

1st attempt at partitioning  ..

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 29, 2014 at 10:03 PM

    Sounds ok to me, especially if you plan on using local indexes => should definitely reduce your maintenance window (eg, update stats, reorgs) if you only have to worry about the most recent couple months of data/indexes.

    For each new year I'm assuming you'll be adding new partitions on a quarterly (3-mos) basis, too?

    Do you plan on doing any sort of delete/purge processing on the 'old' rows at some point, and if so under what criteria?  If you can partition based on your delete/purge criteria you'll likely find 'alter table/drop partition' to be a bit faster than running DELETEs (NOTE: global indexes will still need to be rebuilt).

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks Mark

      That is Correct I will set up  quarterly partitions on yearly basis

      at this point I am not sure how long I need to keep data  --  Part of the process we are going through will determine that --

      Thanks ..

  • Jan 06, 2015 at 05:40 PM

    One more question  -- before we can partition -- what would be best practice for running update stats on large  tables -- say 300 million  or more rows

    Current default  command is :

    update index statistics table name using 25 values with sampling = 10

    Add comment
    10|10000 characters needed characters exceeded

    • Actually, I would like to comment that there are some nuances with partitioning and stats to be aware of.....but as far as your question goes, a lot depends on your version of ASE.   For pre-ASE 15.7, sampling works, but the best practice taught in classes was to do a full non-sampled stats first, then do 2-3 updates with sampling, then a full non-sampled stats in cycle - so if doing update stats weekly, the first run of the month would be a full non-sampled and other weeks in the month would be sampled.    However, what this is doing is trying to help you determine if stats sampling is working similarly to a non-sampled stats by virtue of the fact that you may have performance issues the latter weeks of the month using sampled stats vs. non-sampled.   How well this works often depends on how values are added to the different columns - e.g. scattered around evenly or monotonically increasing.    I personally have found that in later revs of 15.7 (e.g. sp100+) that running stats with hashing is much faster than stats with sampling and generates more accurate stats.   I know Mark has seen some issues - not sure where/why - but then I have seen problems with just update stats generically in which we have had to delete stats before re-running update stats....so not sure if the problem was caused by starting with non-hashed stats and then trying to update with hashed stats or not (I have always started with hashed stats).

      Now, there is an interesting nuance of update stats with partitioning.    Yes, you can run update stats on partition basis.......but it doesn't update table stats (problem #1) and it can also lead to stats explosion.   I am not saying don't run update stats on a partition basis - I actually encourage it  - but suggest you know what is going to happen.    For example, partitioning - especially range partitioning - works best as far as maintenance commands - when you get in the 30+ partition range - and especially in the 50-100 partition range - assuming evenly distributed partitions.  In your case, you will likely get the same effect on 2014 and 2015 partitions as they will be much smaller.  When you run update stats on each partition, (assuming the default histogram steps) you will get 20 steps PER partition.....which can mean 1000+ for the entire table (if 50 partitions).  Not necessarily a problem unless the query needs to hit all the partitions (or some significant number of them) at which point the query will need considerable proc cache to load those stats.  Sooo......when using partitions, keep in mind that you may need to increase proc cache to handle the increase use during optimization.    On the table stats perspective, what it means is that periodically you might want to run update statistics (not update index statistics) on the table.....however, in my experience this hasn't been as necessary as one would think....and might only be necessary if you see the optimizer picking a table/partition scan when you think it should be choosing an index.

      In your case, you might only have 20 steps for the whonking huge partition and then 20 steps for 2014 and 20 steps for each of the 2015 quarterly partition.    You might want to run the update stats for the 2013 and before partition with a larger step count (e.g. 100) and then run it with 20 or so for the other partitions.

      Using partitions the way you are doing is interesting in a different perspective.   The current data is extremely small and therefore fast access (fewer index levels) and you don't get quite the penalty for queries that span a lot of partitions - e.g. a 5 year query doesn't have to hit 20 partitions the way it would for complete quarterly partitions.   However, this assumes the scheme is:

      Partition 1 = data 2+ previous years

      Partition 2 = data for previous year

      Partitions 3-6 = data for current year by quarter

      Which means at the end (or so) of each year, you will need to merge partitions.   Whenever you merge partitions, you will then need to run update stats again.

      If the scheme instead is just to have historical partitions but going forward each year will simply have data by quarter, you might want to see what the impact on queries are - especially reports on non-unique indexes where the date range spans a lot of partitions or date range is not part of the query.