If your applications frequently need to search, order, or group on the same group of multiple columns in a particular table, it will be of benefit to create a multi-column index (also known as a composite or complex index).
The optimizer will use a subset of the segments of a multicolumn index to optimize a query if the index. However, queries do not need to be constructed with the exact column list that is defined in the index in order for it to be available to the optimizer. The index can also be used if the subset of columns used in the ORDER BY clause begins with the first column in the multicolumn index.
Firebird can use a single element of composite index to optimize a search if all of the preceding elements of the index are also used. Consider a segmented index on three columns, Col_w, Col_x and Col_y, in that order, as shown in Figure.
Using a segmented index
The index would be picked by the optimizer for this query:SELECT <list of columns> FROM ATABLE ORDER BY COL_w, COL_x;
It would not be picked for either of these queries:SELECT <list of columns> FROM ATABLE ORDER BY COL_x, COL_y; /**/ SELECT <list of columns> FROM ATABLE ORDER BY COL_x, COL_w;
OR Predicates in Queries
If you expect to issue frequent queries against a table where the queries use the OR operator, it is better to create a single-column index for each condition. Since multicolumn indexes are sorted hierarchically, a query that is looking for any one of two or more conditions must search the whole table, losing the advantage of an index.
For example, suppose the search requested... WHERE A > 10000 OR B < 300 OR C BETWEEN 40 AND 80 ...
An index on (A,B,C) would be used to find rows containing eligible values of A, but it could not be used for searching for B or C values. By contrast, individual indexes on A, B, and C would all be used. For A, a descending index would be more useful than an ascending one if the search value is at the high end of a range of stored values.
The same rules that apply to the ORDER BY clause also apply to queries containing a WHERE clause. The next example creates a multicolumn index for the PROJECT table in employee.gdb:CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);
The optimizer will pick the PRODTYPEX index for this query, because the WHERE clause refers to the first segment of the index:SELECT * FROM PROJECT WHERE PRODUCT ='software';
Conversely, it will ignore the index for the next query, because PROJ_NAME is not the first segment:SELECT * FROM PROJECT WHERE PROJ_NAME STARTING WITH 'Firebird 1';
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
Blobs And Arrays
From Drawing Board To Database
Creating And Maintaining A Database
Firebird’s Sql Language
Expressions And Predicates
Querying Multiple Tables
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Developing Psql Modules
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.