Skip to end of metadata
Go to start of metadata

Partial Index is the concept introduced in Sybase ASE 15.7 SP130 which takes help of the DDLGEN

Partial Index is referred to the local index where some index partitions have been created on some data partitions, but some others are not. After all index partitions are created, the index becomes to a normal local index.

CREATE COMMAND is enhanced to create partition index. Only "partial index" related items are new:

create [unique] [nonclustered] index index_name on [[database.]owner.]table_name
(column_expression [asc | desc]
[, column_expression [asc | desc]]...)
[with {fillfactor = pct, max_rows_per_page = num_rows, reservepagegap = num_pages, ignore_dup_key, statistics using num_steps values}]
[on segment_name]
[index_partition_clause]

index_partition_clause::=
[local index [[partition_name [on segment_name]
[, partition_name [on segment_name]...]
| partial index [index_partition_name]
[on segment_name]
for data_partition_name

When generating DDL for partial index, DDLGen may generate one CREATE INDEX command for each existing index partition.
In system, the bit corresponding to INT value 256 in sysindexes.status3 indicates this is a partial index.

For example, below SQL can retrieve the paritial index for table ‘t1’:


select indid, name from sysindexes where id = object_id('t1') and status3 & 256 = 256

Each row in sysparitions with this objid and indid indicates an index partition has been built. 

For example, below SQL can retrieve all the built index partitions (suppose partial index id is 2).

select indid, partitionid, name, data_partitionid from syspartitions where id = object_id('t1') and indid = 2 

The data partition for each index partition can be determined by index partition’s data_partitionid.


Ex:
======================================================
create table Tab_1 (c1 int, c2 char(10))
lock datarows
partition by range (c1) ( cb1 values < = (1000), cb2 values < = (2000), cb3 values < = (3000), cb4 values < = (MAX))
go

set nocount on
go

declare @cnt int
select @cnt = 0
while (@cnt < 4000)
begin
insert into Tab_1 values (@cnt,'abc')
select @cnt = @cnt + 5
end
go

create index idx_partital on Tab_1(c1) partial index for cb1
go
create index idx_not_partial on Tab_1(c1)
go
select indid, status3, status3 & 256, left(name,15) as name
from  sysindexes
where  id = object_id("Tab_1")
and  status3 & 256 = 256
go
 indid  status3          name  
------ ------- -----------          ------------------------------------------------------------      
2     264         256          idx_partital
========================================================

 

  • No labels