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