OracleDatabase queries generate result sets that contain all rows, from the requested tables, that match the filtering constraints. When most rows will not be needed, the work to generate the much-larger result set and the resources to handle such a result set are not required. Eligible CGI Advantage pages enable virtual result sets to reduce the high memory overhead associated with open database cursors and connections. When the online user wishes to view a different “window” of records, CGI Advantage queries the table again to retrieve the virtual result set to display the appropriate set of records.
Database servers also provide the ability for queries to specify the number of records to be retrieved. Such a Database Optimization Hint allows the database server to generate a result set with only the records that are currently needed. For example, if a page displays a grid of records from a table, typically only ten to twenty are displayed at any one time. If the page will ultimately allow the user to view 1,000 records (whether the user will or will not), it is really not necessary for the database server to generate a result set with the 1,000 records.
Therefore, combining the Database Optimization Hint with virtual result set-enabled pages will optimize the retrieval of records from the database. The database server will quickly return a smaller result set of records to be displayed on the current page’s “window.” And when the user chooses “Next” to view the next set of records, the database server will return the next smaller set.
Listed below are sample SQL SELECT statements that incorporate the Database Optimization Hint for the identified database server.
The following topics discuss how to set up the Database Optimization Hint.