query optimizer

No degree of query optimizer intelligence can eliminate the resource cost of these constructs when compared to a less complex query.

Further, the solidDB query optimizer recognizes the different nature of the in-memory tables by estimating execution costs in a new way.

It is possible that the query optimizer might have generated a remote statement that hits the remote server's limitation on performance.

An entry is added to the system catalog to indicate to the query optimizer that such a remote index exists.

If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes.

Information returned by sys. Dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted.

Specifies the name or ID of the indexes to be used by the query optimizer when it processes the statement.

The query optimizer, in consultation with the wrapper, determines how the data is to be accessed.

If such a plan is not possible , the query optimizer returns an error instead of deferring error detection to query execution .

Updating index statistics to make sure the query optimizer has up-to-date information about the distribution of data values in the tables.

When cursors generate a keyset for a result set, the query optimizer estimates the number of rows that will be returned for that result set.

The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.

Consequently, the query optimizer had to make a guess, which may have resulted in the selection of an inefficient query plan.

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition.

Specifies that the query optimizer use an optimization or locking strategy with this table and for this statement.

USE PLAN can be used to force the query optimizer to use a specified query plan for a query.

For each query, the query optimizer compares the execution cost of many alternative execution plans.

If the query optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.

If more than one UNION hint is specified, the query optimizer selects the least expensive strategy from those hints specified.

Note: the query optimizer will not use the R-Tree index unless the statistics on the table are up to date.

The query optimizer bases query-cost estimates on the number of rows to be retrieved from each table.

The SQL explain facility provides detailed information about the access plan that the query optimizer chooses for an SQL statement.

The database server query optimizer automatically uses an index when a suitable index exists and when using one will improve performance.

You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer.

The query optimizer will give priority to an execution plan involving an MQT over a plan that involves access to remote tables.

Forces the query optimizer to try a plan that works for the maximum potential row size, possibly at the expense of performance.

In particular, the query optimizer is less likely to match the query to an indexed view or an index on a computed column.

This defines indexes and keys in server terms and enables the query optimizer to consider any indexes that may exist on this table.

Using FORCE ORDER does not affect possible role reversal behavior of the query optimizer.

Contains information about using the USE PLAN query hint to force the query optimizer to use a specific query plan for a query.

Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized.

The number of processors that are used by the query optimizer typically provides optimal performance.

The illustration shows a query optimizer plan executed with a degree of parallelism equal to 4 and involving a two-table join.

The Index Consultant retrieves the plan for each statement in the workload, as chosen by the Adaptive Server Anywhere query optimizer.

Noting the partition numbers confirms that the query optimizer is performing partition elimination .

Regardless of schema (3NF or Star), the Teradata query optimizer relies on statistics to help it determine the best way to access data.

Tuning the Universal Server's query optimizer for particular types of complex data can vastly improve querying speed on those data types.

Describes how the query optimizer uses statistics to develop optimal query plans.

During plan generation for the query, the query optimizer communicates with the non-relational wrapper.

The query optimizer does not recalculate the profile for tables automatically.

Forces the query optimizer to relax the estimated recompile threshold for a query.

Given accurate statistics, the query optimizer generally comes up with correct selectivity estimates.

In general, the query optimizer selects a plan that will return the entire result set in the shortest amount of time.

The query optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

Forces the query optimizer not to recompile a query due to changes in statistics.

The query optimizer does not consider the index when creating query execution plans.

Plan forcing removes the ability of the query optimizer to adapt to changing data sizes and distributions, new indexes, and other variables.

Fast forward-only cursors vary in when they acquire their locks depending on the execution plan chosen by the query optimizer.

Although this is an option, the query optimizer can generally pick the best optimization method without hints being specified.

The query optimizer may use indexed views to speed up the query execution.

With this feature, the query optimizer can immediately consider the index when determining an access plan.

After the query optimizer creates the plan, the query optimizer chooses the most efficient physical operator for each logical operator.

The tuning process relies on the query optimizer, and the recommendations it generates are based in part on the underlying hardware.

Accurate index information and statistics are fundamental to cost-based decisions in the query optimizer.

The query optimizer uses this statistical information to choose the most efficient plan for retrieving or updating data.

The push-down decision is made by the wrapper during the planning phase, while working with the query optimizer.

as data changes or indexes are added or dropped , the query optimizer automatically adapts its behavior by using alternative plans.

The following statement returns the value Index, which means that the query optimizer probed an index to estimate the selectivity.

Added information about when the query optimizer does not use a parallel execution plan.

Approximate query execution costs are estimated by the query optimizer.

Recommend the best mix of indexes for databases by using the query optimizer to analyze queries in a workload.

The query optimizer does not consider constraints that are defined WITH NOCHECK.

Awareness of all applicable remote indexes can help the federated query optimizer generate better execution plans.

Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query.

Provides the source for selectivity estimates used by the query optimizer.

Helps the query optimizer estimate the selectivity of query predicates on string patterns.

SET FORCEPLAN essentially overrides the logic used by the query optimizer to process a Transact-SQL SELECT statement.

The query optimizer typically selects the most efficient method when executing queries.

A new feature of the query optimizer supports a new type of index scan, called an index self-join path.

Additional information helpful to the query optimizer is provided.

This can cause the query optimizer to choose a less-efficient query plan than otherwise expected.

Node IDs in Showplans identify each operation the query optimizer performs when it generates a query execution plan.

Query execution costs are estimated by the query optimizer.

The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost.

Doing so may affect the execution plan chosen by the query optimizer.

The cost to the query optimizer for executing this operation.

These statistics are used by the query optimizer to determine the most efficient way to execute a query.

Only one network round trip is necessary, and the query optimizer can better optimize the single query.

The query optimizer uses the index in the query execution plan.

These views include columns containing statistical information that is useful to the query optimizer.

STEP D: We need to explain the queries to view the access plans chosen by the DB2 query optimizer.

The hints can prevent the query optimizer from choosing a better execution plan.

The component that does this is called the query optimizer.

The query optimizer relies heavily on these statistics -- especially card, colcard, high2key and low2key.

Event is occurring when the query optimizer generates an execution plan, you should also collect a Showplan event class in the trace.

The indexed view is used by the query optimizer to produce the query plan.

This makes sure that the plan guide is used by the query optimizer.

For example, the user may specify a dynamic cursor, but the query optimizer is permitted to convert this cursor type to a static cursor.

The act of using the USE PLAN query hint to force the SQL Server query optimizer to use a specified query plan for a query.

Specifies that the SQL Server query optimizer use one join hint, or execution algorithm, per join specified in the query FROM clause.

Specifies that the SQL Server query optimizer use one join hint, or execution algorithm, per join specified in the query FROM clause.

When useful indexes are available, the SQL Server query optimizer is efficient at selecting rapid, efficient joins between tables.

Specifies the parameterization rules that the SQL Server query optimizer applies to the query when it is compiled.

Query optimizer hints can also be used in queries to affect how SQL Server processes the SELECT statement.

When the SQL Server query optimizer recognizes a query that matches statement_text, plan_guide_name takes effect.

When the PARAMETERIZATION database option is SET to SIMPLE, the SQL Server query optimizer may choose to parameterize the queries.

The query optimizer in SQL Server 2005 reliably chooses the most effective index in the vast majority of cases.

Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query.

The query optimizer will try to reduce the number of messages sent to the data source.

The query optimizer also tries to reduce the number of messages sent to the data source if the network is slow.

This occurs because Database Engine Tuning Advisor often needs to make several calls to the query optimizer during the tuning process.

The overhead results from the many calls made by Database Engine Tuning Advisor to the query optimizer during the tuning process.

Can perform faster with the creation of a keyed logical file containing the keys suggested by the query optimizer

The inputs and outputs of the query optimizer during optimization of a single SELECT statement are illustrated in the following diagram

The Database Engine query optimizer almost always chooses the correct locking level.

The SQL Server query optimizer does not use a parallel execution plan for a query if any one of the following conditions is true

Design and Realization of a Parallel Query Optimizer

myisamchk -analyze - updates statistics for use by query optimizer for the MyISAM storage engine

Object-Oriented Framework for Query Optimizer

The query optimizer may use the PATH index for expressions such as those shown in the following

For information about how the SQL Server query optimizer enforces the FORCE ORDER hint when a query contains a view, see

How Indexes Are Used by the Query Optimizer

The SQL Server Mobile query optimizer considers all available indexes when processing a query, including