Accessing Data Stored in Fragmented Tables
Rows that are stored in nonfragmented tables can be accessed by several methods. One method is to reference the rowid of the row that you are seeking. The term rowid refers to an integer that defines the physical location of a row. The database server assigns rows in a nonfragmented table a unique rowid, which allows applications access to a particular row in a table.
Rows in fragmented tables, in contrast, are not assigned a rowid. If you wish to access data by rowid, you must explicitly create a rowid column as described in "Creating a Rowid Column". If user applications attempt to reference a rowid in a fragmented table that does not contain a rowid that you explicitly created, Universal Server displays an appropriate error message, and execution of the application is halted.
Using Primary Keys Instead of Rowids
Informix recommends that you use primary keys rather than rowids as a method of access in your applications. Because primary keys are defined in the ANSI specification of SQL, using them to access data makes your applications more portable.
For a complete description of how to define and use primary keys to access data, see the Informix Guide to SQL: Reference and the Informix Guide to SQL: Syntax.
Rowid in a Fragmented Table
From the viewpoint of an application, the functionality of a rowid column in a fragmented table is identical to that of a rowid of a nonfragmented table. However, unlike the rowid of a nonfragmented table, the database server uses an index to map the rowid to a physical location. Accessing data in a fragmented table by rowid is significantly slower than accessing data in a nonfragmented table by rowid. Accessing data in a fragmented table by rowid is no faster than accessing data using a primary key. In addition, primary-key access can lead to significantly improved performance in many situations, particularly when access is in parallel.
When Universal Server accesses a row in a fragmented table using the rowid column, it uses an index to look up the physical address of the row before it attempts to access the row. For a nonfragmented table, Universal Server uses direct physical access without having to do an index lookup. Consequently, accessing a row in a fragmented table using rowid takes slightly longer than accessing a row using rowid in a nonfragmented table. You should also expect a small performance impact on the processing of inserts and deletes due to the cost of maintaining the rowid index for fragmented tables.
The section that follows explains how to create a rowid in a fragmented table.
Creating a Rowid Column
If, for some reason, you find that your applications must access data in a fragmented table using a rowid column, you must create a rowid column for the fragmented table.
You can create the column at the same time that you create the table by using the WITH ROWIDS clause of the CREATE TABLE statement. When you issue the CREATE TABLE...WITH ROWIDS statement, Universal Server creates a rowid column that adds 4 bytes to each row in the fragmented table. In addition, Universal Server creates an internal index that it uses to access the data in the table by rowid. After the rowid column is created, Universal Server inserts a row in the sysfragments catalog table, which indicates the existence and attributes of the rowid column.
If you decide that you need a rowid column after you build the fragmented table, use the ADD ROWIDS clause of the ALTER TABLE statement or the INIT clause of the ALTER FRAGMENT statement.
You can drop the rowid column from a fragmented table with the DROP ROWIDS clause of the ALTER TABLE statement. For more information, see the ALTER TABLE statement in Chapter 1 of the Informix Guide to SQL: Syntax.
You cannot create or add a rowid column by naming it as one of the columns in a table that you create or alter. For example, you will receive an error if you execute the following statement:
You will get the following error:
Granting and Revoking
You need to have a strategy for controlling data distribution if you want to grant useful fragment privileges. Fragmenting data records by expression is such a strategy. The round-robin data-record distribution strategy, on the other hand, is not a useful strategy because each new data record is added to the next fragment. This distribution nullifies any clean method of tracking data distribution and therefore eliminates any real use of fragment authority. Because of this difference between expression-based distribution and round-robin distribution, the GRANT FRAGMENT and REVOKE FRAGMENT statements apply only to tables that are fragmented by an expression strategy.
When you create a fragmented table, no default fragment authority exists. Use the GRANT FRAGMENT statement to grant insert, update, or delete authority on one or more of the fragments. If you want to grant all three privileges at once, use the ALL keyword of the GRANT FRAGMENT statement. However, you cannot grant fragment privileges by merely naming the table that contains the fragments. You must name the specific fragments.
When the time comes to revoke insert, update, or delete privileges, use the REVOKE FRAGMENT statement. This statement revokes privileges on one or more fragments of a fragmented table from one or more users. If you want to revoke all privileges that currently exist for a table, you can use the ALL keyword. If no fragments are specified in the command, the permissions being revoked apply to all fragments in the table that currently have permissions.
For more information, see the GRANT FRAGMENT, REVOKE FRAGMENT, and SET statements in the Informix Guide to SQL: Syntax.
|
