ASE : Sybase ASE 15 Enhancements

Table Structure Enhancements

Partitioned tables: Tables can be partitioned by range, value, or round-robin (Semantic Partitioning)

What is a partition?

A partition is a segment of a table, usually with rows sharing a common value (e.g. period_id);

Partitions allow for
• fast deletion of data
• simple bulk replacement of data
• ultra high volume OLTP, via round-robin distribution
• better query performance through partition elimination (homing in on a single partition)
• super-large tables (hundreds of gigabytes) that are manageable

All ASE 15 tables are partitioned (most tables will have a single partition)


Computed columns
• materialized (stored, calc'd upon datachange) or non-materialized (calc'd upon retrival)
• computed columns can be indexed (function-based index)


Statistics
• Update statistics is not necessary after index rebuild
• sp_recompile is not necessary after index rebuild


Encrypted columns : meets US Government encryption standards


Object names (tables, etc) can be up to 255 characters long


Temp table (#temp) improvements, including naming


Object level recovery: allows single objects to be recovered from dump files


Query engine enhancements
• Joins / searches now optimized for queries with calculated columns e.g. select account_id from account_header where floor(gic_subindustry/100) = 451020
• Joins / searches now optimized for queries with mismatched data types e.g. -- join an integer with a real/float select t1.account_id from account_header t1, account_attribute t2 where t1.acct_id = t2.svalue and t2.cat_cd = 'BC'
• Hash joins * i/o is dramatically reduced for non-index type queries, dramatically improving performance. e.g. -- join 2 tables, against non-indexed or computed columns select t2.sales_nm, count(1) from account_header t1, sales_historical_data t2 where convert( integer, ( left(convert(varchar(10),account_id),5) ) ) = t2.sales_id ** ASE 15 is up to 20 times faster than ASE 12 for these types of queries
Work table optimization, hash-based distinct e.g. select count(distinct round(svalue) ) from account_attribute ** ASE 15 is up to 20 times faster than ASE 12 for these types of queries