Commands

Adaptive Server 15.0.1 introduces syntax and other changes to alter table, create index,create existing table , update statistics, and the set command.

Changed commands
Table Description of change
alter table

Use the alter table command to drop one or more list or range partitions. You cannot use alter table to drop a hash or round-robin partition.

The syntax is:
alter table table_name drop partition
partition_name [, partition_name]...
create index When you create a unique local index on range-, list-, and hash-partitioned tables, the index key list is a superset of the partition-key list.
create existing table
Includes syntax to determine whether an RPC uses the current or a separate connection:
create existing table (<column_list>)
EXTERNAL [non_transactional |transactional] PROCEDURE at 'location'
  • non_transactional – a separate connection is used to execute the RPC.

  • transactional – the existing connection is used to execute the RPC.

The default behavior is transactional.

update statistics
Adaptive Server 15.0.1 adds the ability to run update statistics on a global index.
 update table statistics table_name
[partition data_partition_name]
[index_name [partition index_partition_name]]

Because running update table statistics incurs the I/O cost of running update statistics, use update statistics to generate both column and table statistics.

You can create, and then drop, a global index to generate global statistics.

New set command options
New set command options Description
set literal_autoparam on|off

Enables and disables literal parameterization at the session level.

set opttimeoutlimit

The range of values for opttimeoutlimit has been changed to 0 – 4000, with 0 indicating no optimization limit.

set index_union on | off

When enabled, set index_union limits the scan of a table with an or clause.

Index unions (also known as an or strategy) are used for queries that contain or clauses. For example:
select * from titleauthor where au_id = "409-56-7008" or title_id = "PC8888" 

If you have enabled index_union, this example uses an index on au_id to find the row IDs (RIDs) of all titleauthor tuples with au_id = "409-56-7008", and uses an index on title_id to find the RIDs of all titleauthor tuples with title_id = "PC8888". Adaptive Server then performs a union on all RIDs to eliminate duplicates. The resulting RIDs are joined with a RidJoin to access the data tuples.

If index_union is disabled, Adaptive Server does not use an index union strategy in a query to limit the table scan. Instead, it uses other access paths on the table (in the example above, it would use a table scan for table titleauthor), and applies the or clause as a filter in the scan operator.

See the Reference Manual: Commands.