When a table is created in Sybase Adaptive Server Enterprise (ASE), the default locking scheme is the server-wide
setting for the configuration parameter lock scheme. The default value for this parameter is "allpages," or APL.
DOL (data only locked) tables include everything that is not in allpages (APL) locking. DOL is divided in two categories, data page locking (DPL) and data row locking (DRL). ASE allows you to set the table-locking scheme to "datapages" (DPL) or "datarows" (DRL).
In this tip, we'll discuss some performance benefits of using DPL and DRL.
The three options differ in the following ways:
- In APL, the data page and the index pages are locked during the operation.
- In DPL, only the data page is locked; no index page locking.
- In DRL, only the rows are locked; no index page locking.
In APL, table data pages are linked into a doubly-linked list of pages by pointers on each page. In contrast, DPL and DRL tables do not link data pages into a page chain.
In an APL table, each page stores a pointer to the next page and to the previous page in the chain. When new pages need to be inserted, the pointers on the two adjacent pages change to point to the new page. When ASE scans an APL table, it reads the pages in order, following these page pointers. This has certain performance implications.
In contrast, both DPL/DRL tables use object allocation map (OAM), the allocation pages and row IDs to locate rows in the table. This turns out to be far more efficient for optimizing searches than the page chaining used in APL tables.
With all that in mind, let us focus on DPL and DRL tables performance benefits. At this time, I will not consider the maintenance benefits/overhead of DPL/DRL tables and neither will I cover the main use of DPL/DRL tables for avoiding high concurrency/locking contention.
For the reasons outlined above, DPL/DRL tables perform much better in table scans and can be used effectively as part of a parallel hash-based table scan. Take note of a semantically partitioned DPL/DRL table compared to the same APL table.
DPL/DRL indexes take advantage of a certain amount of "compression" as mentioned by not storing the full key at non-leaf rows (only values that distinguish the values represented by the next lowest level) and not storing the key repeatedly when the values are the same from index row to row.
DPL/DRL tables keep their shape much better; that is, they get less fragmented under constant hits compared to APL tables .
Overflow pages and the resultant large logical I/O consumption
Many people have seen the classic example of overflow pages when an APL table happens to have a non-unique clustered index. This is because over a period of time, each key can have many rows by virtue of being non-unique.
The typical perceived solution is to make a table a heap by dropping the clustered index and recreating the index as non-clustered. In most cases, we have seen the disadvantages of a heap table and its impact. A better solution is to make the table a DPL table. This allows the optimizer to use the allocation pages (as opposed to page chain) to locate the rows, which usually results in a dramatic drop in the number of logical I/Os.
With APL tables, the following limitations apply to cursors:
- If the cursor is not declared for update, a unique index is preferred over a table scan or a non-unique index. But a unique index is not required.
- If the cursor is declared for update without a for update of list, a unique index is required. An error is raised if no unique index exists.
- If the cursor is declared for update with a for update of list, then only a unique index without any columns from the list (i.e a safe index) can be chosen. An error is raised if no unique index qualifies.
DPL and DRL tables do not require a unique index or safe index to be present for updatable cursors. Sometimes it is cheaper to make a table a DPL or DRL rather than creating a unique index on it.
Intelligent scan selectivity reduction
ASE has a feature called intelligent scan selectivity reduction. This only applies to DPL and DRL tables.
This feature allows the optimizer to use a composite index, even when the index prefix column in a SQL statement as a predicate has been omitted. This means that you will not have to create an additional index to provide faster access to your data. But this is only useful if the cardinality of the prefix column is fairly low.
ASE uses an Intelligent Scan algorithm to determine the domain of distinct values for the index prefix column and then iterate through each distinct value in this domain. For each distinct value, the optimizer will perform a regular index scan on that portion of the index. To put it another way, ASE will treat the composite index search as a number of small sub-indexes to retrieve the row IDs. One sub-index search will be performed for each distinct value within the index prefix column.
The Intelligent Scan feature will function as if you issued a sequence of SQL statements having each statement specifying a single value for the index prefix column. In general, an intelligent scan selectivity reduction operation is faster than a table scan. Just to illustrate:
create table T1 ( n1 tinyint NULL, ind_pad varchar(40) NULL, n2 tinyint NULL, small_vc varchar(10) NULL, padding varchar(200) NULL ) lock datarow go create index T1_I1 on T1(n1,ind_pad, n2) go
Now I populate this table with 10,000 records. n1 has 25 distinct values (0-24); ind_pad has a single value for all columns. Now if I do a simple SELECT ignoring column n1 in the predicate list,
select small_vc from T1 where n2 = 2 go
The optimizer will pick up and use the index rather than doing a table scan as shown in the output of showplan below
QUERY PLAN FOR STATEMENT 1 (at line 1). 1 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SCAN Operator | FROM TABLE | T1 | Index : T1_I1 | Forward Scan. | Positioning at index start. | Using I/O Size 16 Kbytes for index leaf pages. | With LRU Buffer Replacement Strategy for index leaf pages. | Using I/O Size 16 Kbytes for data pages. | With LRU Buffer Replacement Strategy for data pages.
Did you find this tip useful? Email us and let us know what tips you'd like to see.