If the optimizer decides to use an index, it searches the pages of the index to find the key values required and follows the pointers to locate the indicated rows in the table data pages. Data retrieval is fast because the values in the index are ordered. This allows the system to locate wanted values directly, by pointer, and avoid visiting unwanted rows at all. Using an index typically requires fewer page fetches than “walking through” every row in the table. The index is small, relative to the row size of the table and, provided the index design is good, it occupies relatively fewer database pages than the row data.
Sorting and Grouping
When the columns specified in an ORDER BY or GROUP BY clause are indexed, the optimizer can sequence the output by navigating the index(es) and assemble the ordered sets more directly than it can with non-indexed reads.
A DESCENDING index on the aggregated column can speed up the query for the aggregating function MAX(..), since the row with the maximum value will be the only one it visits.
For joins, the optimizer goes through a process of merging streams of data by matching the values specified in the implicit or explicit “ON” criteria. If an index is available for the column or columns on one side of the join, the optimizer builds its initial stream by using the index to match the join key with its correspondent from the table on the other side of the join. Without an index on either side, it must generate a bitmap of one side first and navigate that, in order to make the selections from the table on the other side of the join.
When an indexed column is evaluated to determine whether it is greater than, equal to, or less than a constant, the value of the index is used for the comparison, and non- matching rows are not fetched. Without an index, all of the candidate rows have to be fetched and compared sequentially.
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.