Index Properties (Magic xpa 4.x)
Index properties are associated properties that define the parameters of a particular data type. The Index properties are as follows:
|
|
|
Direction
|
Two-Way tells Magic xpa that it will be necessary to enable forward and backward movement over this table, typically in an Online task for databases that do not support two-way movement. The SQL database will create ascending and descending cursors for two-way direction. It is usually beneficial to define indexes as One-Way if they are to be used only in Batch processing and if the database does not support two-way indexes. While some database gateways will ignore this property and provide two-way indexes by default, others, such as SQL, can use this information to decide whether one index is sufficient or two are required. The default value is Two-Way.
One-Way means that only forward access is required.
|
|
All of the databases that Magic xpa works with support two-way. Unless you specifically need to set the direction to one-way, leave the direction as two-way. Otherwise, the end user will not be able to jump to the end/start of the data view. In other words, it is recommended not to change this setting.
|
|
|
Range Mode
|
Quick (default) means that the defined index supports a fast range execution by using the index.
Full means that the range operation is executed by sequential scanning. A Range Mode specified as Full results in significantly slower performance.
|
|
|
|
Information for SQL Database
|
This optional property lets you supply database-dependent information that Magic xpa can pass to the underlying RDBMS. See also Database Information - Database Property.
|
|
DB Index Name
|
This is the actual name of the index as defined in the underlying database. This name has the limitations of the specified database.
When adding a new index to a table, Magic xpa copies the name of the index to the DB Index name and replaces blanks with underscores. You can overwrite the DB Index name, but you cannot leave the DB Index name column blank.
|
|
Index Type
|
Specifies whether the index is contained in the database or defined only in Magic xpa. The permissible variables are Real (default) or Virtual. When creating a new table, if the index is created in Magic xpa, the index type must be specified as Real.
The Index Type property has no effect on existing tables in runtime. For example, when a view is accessed, a virtual index needs to be added.
Virtual indexes are not recommended to replace the sorting operation in the program, especially when the Sort Using RDBMS feature can be accessed. This enables the records to be fetched in a required order, so that sorting is not necessary in Magic xpa.
|
|
Hint
|
Some RDBMSs allow hinting the optimizer from processing a query. In this column the programmer can enter a string that will be concatenated in the SELECT statement.
Relevant gateways: MSSQL and Oracle
See also How Do I Affect the Database Optimizer Behavior?
|
|
Clustered
|
Specifies whether the index will be clustered when tables are created via Magic xpa. You can decide which index is clustered, otherwise the table is created without a clustered index. Assigning a clustered index to a table with a high insert ratio may cause performance problems.
A clustered index refers to the physical data, which is stored in the order of the index. A clustered index is efficient for scanning sets of data in the order of the index, and less efficient when trying to access one of the records directly.
Relevant gateways: MSSQL
Default value: No
|
SQL Considerations
