If you create a table on Adaptive Server, but do not create a clustered index, the table is stored as a heap. The data rows are not stored in any particular order. This section describes how select, insert, delete, and update operations perform On Heaps When there is no "useful" index to aid in retrieving data.
The phrase "no useful index" is important in describing the optimizer's decision to perform a table scan. Sometimes, an index exists on the columns named in a where clause, but the optimizer determines that it would be more costly to use the index than to Perform a table scan.
Other Chapters in this book describe how the optimizer costs queries Using Indexes and how you can get more information about what cho..
.
For more information, see "index covering".
The Following Sections Describe How Adaptive Server Locates Rows WHEN A TABLE HAS No Useful Index.
LOCK Schemes and Differences Between Heaps
THE DATA PAGED TABLE ARE LINKED INTO A DOUBLY-LINKED LIST OF PAGES IN DATA-On or-Locked Tables Are Not LINKED INTO A page chain.
In an allpages-locked table, each page stores a pointer to the next page in the chain 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 .,.....................
Pages are also doubly-linked at each index level of allpages-locked tables, and the leaf level of indexes on data-only-locked tables. If an allpages-locked table is partitioned, there is one page chain for each partition.Another difference between allpages-locked tables and data-only-locked tables is that data-only-locked tables use fixed row IDs. This means that row IDs (a combination of the page number and the row number on the page) do not change in a Data-only-locked Table During Normal Query Processing.
.
For Information On How Fixed Row IDs Affect Heap Operations, See "Deleting from a Data-Only Locked Heap Table" AND "Data-Only-Locked Heap Tables".
Select Operations on Heaps
When you issue a select query on a heap, and there is no useful nonclustered index, Adaptive Server must scan every data page in the table to find every row that satisfies the conditions in the query. There may be one row, many rows, or No Rows That Match.allpages-Locked Heap Tables
For allpages-locked tables, Adaptive Server reads the first column in sysindexes for the table, reads the first page into cache, and follows the next page pointers until it finds the last page of the table. Data-only locked heap tables
Since the pages of data-only-locked tables are not linked in a page chain, a select query on a heap table uses the table's OAM and the allocation pages to locate all the rows in the table. The OAM page points to the allocation pages , Which point to the extents and feet.
Inserting Data Into an Allpages-Locked Heap Table
When you insert data into an allpages-locked heap table, the data row is always added to the last page of the table. If there is no clustered index on a table, and the table is not partitioned, the sysindexes.root entry for the heap table stores a pointer to the last page of the heap to locate the page where the data needs to be inserted.If the last page is full, a new page is allocated in the current extent and linked onto the chain. If the extent is Full, Adaptive Server Looks for Empty Pages On Other Extents Being Used by The Table. If No Pages Are Available, A New Extent Is Allocated to Table.conflicts During Heap Inserts
One of the severe performance limits on heap tables that use allpages locking is that the page must be locked when the row is added, and that lock is held until the transaction completes. If many users are trying to insert into an allpages-locked heap table At the Same Time, Each Insert Must Wait for the Preceding Transaction To Complete.
This Problem of Last-Page Conflicts On Heaps Is True for:
SINGLE ROW INSERTS Using INSERT MULTIPLE ROW INSERTS Using SELECT INTO OR INSERT ... SELECT, OR SEVERAL INSERT Statements in a Batch Bulk Copy Into the Table
Some Workarounds for Last-Page Conflicts On Heaps include:
Switching to datapages or datarows locking Creating a clustered index that directs the inserts to different pages Partitioning the table, which creates multiple insert points for the table, giving you multiple "last pages" in an allpages-locked table
Other Guidelines That Apply to All Transactions Where The BE BE LOCK CONFLICTS INCLUDE:
Keeping Transactions Short Avoiding Network Activity and User Intertion WHENEVER POSSIBLE, ONCE A Transaction Acquires LOCKS
Inserting data into a data-only-locked heap tableWhen users insert data into a data-only-locked heap table, Adaptive Server tracks page numbers where the inserts have recently occurred, and keeps the page number as a hint for future tasks that need space ................................... ...CRITATION such.
Blocking while many users are simultaneously inserting data is much less likely to occur during inserts to data-only-locked heap tables. When blocking occurs, Adaptive Server allocates a small number of empty pages and directs new inserts to those pages using these newly allocated pages AS Hints.
For datarows-locked tables, blocking occurs only while the actual changes to the data page are being written;. Although row locks are held for the duration of the transaction, other rows can be inserted on the page The row-level locks allow multiple transaction To Hold Locks on the page.
There may be slight blocking on data-only-locked tables, because Adaptive Server allows a small amount of blocking after many pages have just been allocated, so that the newly allocated pages are filled before additional pages are allocated.If conflicts occur during heap inserts
Conflicts During Inserts to Heap Tables Are Great Reduced for Data-Only-Locked Tables, But Can Still Take Place. If The Conflicts Slow Inserts, Some Workarounds Can Be Used, Including:
Switching to datarows locking, if the table uses datapages locking Using a clustered index to spread data inserts Partitioning the table, which provides additional hints and allows new pages to be allocated on each partition when blocking takes place
Deleting data from a heap tableWhen you delete rows from a heap table, and there is no useful index, Adaptive Server scans the data rows in the table to find the rows to delete. It has no way of knowing how many rows match the conditions in The Query without Examining Every Row.DeleTing from an Allpages-Locked Heap Table
When a data row is deleted from a page. Rows That Follow It on the page remains contiust. Deleting from a data-only locate TABLE STAINTER THELETING, THE PAGE
.
The space on the page is not recovered immediately. Rows in data-only-lock IDs, and need to be reinserted in The Same Place If The Transaction Is Rolled Back.
After a Delete Transaction Completes, One of the Following Processes Shifts Rows on The Page To Make The Space Usage Contiuous:
The Housekeeper Process An Insert That Needs To Find Space on The Page THE RECLAIM_SPACE Command
Dleting the last row on a page
If you delete the last row on a page, the page is deallocated. The page can be used by the table, the page can be used.
IF All Other Pages on the Extent Are Empty, The Entire Extent IS DEALLOCATED. IT CAN Be Allocated to Other Objects in The Database. The First Data Page for a Table OR An Index Is Never DEAllocated.
Updating Data on a Heap Table
Like other operations on heaps, an update that has no useful index on the columns in the where clause performs a table scan to locate the rows that need to be changed.Allpages-locked heap tablesUpdates on allpages-locked heap tables can be performed in several Ways:
If the length of the row does not change, the updated row replaces the existing row, and no data moves on the page. If the length of the row changes, and there is enough free space on the page, the row remains in the same place on the page, but other rows move up or down to keep the rows contiguous on the page. The row offset pointers at the end of the page are adjusted to point to the changed row locations. If the row does not fit on the page , the row is deleted from its current page, and the "new" row is inserted on the last page of the table. This type of update can cause a conflict on the last page of the heap, just as inserts do. If there are Any Nonclustered Indexes on the table, all index references to the row need to be added.
Data-only-locked Heap Tables
One of the requirements for data-only-locked tables is that the row ID of a data row never changes (except during intentional rebuilds of the table). Therefore, updates to data-only-locked tables can be performed by the first two methods Described Above, As Long As The Row Fits on The Page.
BUT WHEN A ROW IN A DATA-ONLY-LOCKED TABLE IS Updated SO That It No Longer Fits on The Page, a Process Called Row Forwarding Performs The Following Steps:
.
INDEXES Do Not NEED TOBE MODIFIED WHEN ROWS ARE Forwarded. All Indexes Still Point To The Original Row ID.
If the row need, the original location is update to point to the new page - The forward it.
Row forwarding increases concurrency during update operations because indexes do not have to be updated. It can slow data retrieval, however, because a task needs to read the page at the original location and then read the page where the forwarded data is stored.
Forwarded Rows Can Be Cleared from a Table Using The Reorg Command.
For more information on updates, see "how Update Operations are perform".
