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
<ul>
<li>fast deletion of data
<li>simple bulk replacement of data
<li>ultra high volume OLTP, via round-robin distribution
<li>better query performance through partition elimination (homing in on
a single partition)
<li>super-large tables (hundreds of gigabytes) that are manageable
</ul>
All ASE 15 tables are partitioned (most tables will have a single partition)
Computed columns
<ul>
<li>materialized (stored, calc'd upon datachange) or non-materialized (calc'd upon retrival)
<li>computed columns can be indexed (function-based index)
</ul>
Statistics
<ul>
<li>Update statistics is not necessary after index rebuild
<li>sp_recompile is not necessary after index rebuild
</ul>
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
<ul>
<li>Joins / searches now optimized for queries with calculated columns
e.g. select account_id from account_header where floor(gic_subindustry/100)
= 451020
<li>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'
<li>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
<li>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
</ul>
|
|
