This article describes the SQL Server 2000 Enterprise Edition's new features - an index view. Explain the index view and discuss some specific programs to improve performance.
What is the index view?
For many years, Microsoft SQL Server? Has been provided to create a virtual table (called views) of the function. In the past, these views are mainly two purposes:
To provide security, the user is confined to one or more base tables in a subset of the data.
Provide a mechanism that allows developers to customize the user how to view a logical way of storing data in the base table.
SQL Server 2000 SQL Server views have been expanded features to improve system performance. It can create a view only clustered index and non-clustered index, the most complex queries can improve data access performance. In SQL Server 2000, the index has a unique view of the cluster is called the index view.
Note: The index view only SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition of a function.
From the database management system (DBMS) point of view, the view is the data (metadata) description. Create a typical view, through the SELECT statement (as defined in Table 1 shows the results of the virtual set) to define the metadata. When the other query FROM clause refers to a view, from the system directory to retrieve the metadata, and be extended to replace the reference to the view. In view of expansion, the query optimizer will build a single query is executed the implementation plan.
If the non-indexed view, the view at run time will be materialized. Any calculations (such as join or aggregate) for each reference in the view query during query execution. (View do not always need to be fully materialized. Query can contain other predicates, joins or aggregation to apply to a table referenced by the view and the view.) In view of the cluster to create a unique index, the view of the results gathering entities immediately and continuously saved in the database of the physical storage space, in order to save such a large amount of resources occupied by operations.
In the implementation of the query, there are two ways you can use the index view. Enquiries can be made direct reference to the index view, more importantly, if the query optimizer to determine the view to replace some or all of the query, and this is the low-cost query plan, you can select the index view. The second scenario is based on the table instead of using the index view and the general index. At this point, no reference to the view in the query, the query optimizer can be used during the implementation of the view query. In this way, existing applications without changing the index can view from the new benefit.
Indexed views to improve performance through
Use the index to improve query performance is not a new concept, but the index view using the standard index also has other performance benefits can not be obtained. Indexed views can improve query performance in the following areas:
Aggregate can be calculated in advance and stored in the index, thereby minimizing the cost in the implementation of the query during the high calculation.
To join the table in advance and store the resulting data sets.
Can store a combination of connection or aggregate.
The following figure illustrates the use of indexed views query optimizer can improve when the number of general performance. The query complexity of different (for example, the number of aggregate calculations, the predicates with the table number or number), and includes the actual production environment from the millions of lines of the big table.
Figure 1. When using indexed views query optimizer can improve when the number of general performance
Secondary index using the view
View supporting other non-clustered indexes can improve query performance. And the auxiliary index table is similar to the view of the auxiliary index can also provide more options to query optimizer during the compilation process to choose from. For example, if the query including the cluster index is not related to the column, the optimizer program can select one or more secondary indexes, indexed views, or to avoid time-consuming global base table scan.
Since the index requires constant maintenance, so the index will increase as the database structure to add additional overhead. Should therefore seriously consider the index to find and maintain a balance between additional overhead.
The benefits of using the index view
Indexed view to achieve before you analyze the database workload. Query, and use their own tools (such as SQL Analyzer) use the knowledge to identify the index view can benefit from the query. If the regular aggregation and connectivity, it is best to use the index view.
Not all queries will benefit from the index view. With the general index of similar, if not using the index view, there is no good at all. In this case, not only can not improve performance, but also increase the disk space occupied, increasing the cost of maintenance and optimization. However, if the index view, they can (orders of magnitude) significantly improve data access performance. This is because the query optimizer are stored in the index view using the pre-calculation, thus greatly reducing the cost of the implementation of the query.
Query optimizer query cost only consider the use of relatively large when the index view. This avoids the cost of query optimization in the use of indexed views beyond the cost savings, the attempt to use a variety of indexed views. When the query cost is lower than 1, almost do not use indexed views.
Using the index view can benefit applications include:
◆ decided to support the workload
◆ Data Mart
◆ online analytical processing (OLAP) database and source
◆ Data Mining workload
The type and model from the query point of view, the application of benefits can be summarized as the application contains the following elements:
◆ large table joins and aggregation
◆ check the repeat mode
◆ repeat the same or overlapping aggregate set of columns
◆ join for the same keywords repeated the same table
◆ combination of the above
On the contrary, contains many written online transaction processing (OLTP) system or update the database frequently, you may also update the view and because the fundamental base table Ershi maintenance costs, it can not use indexed views.
Query optimizer to use index views
SQL Server query optimizer automatically determines when they can view the index for a given query execution. No direct reference to the view query, the optimizer can be used for the query execution plan view. Therefore, existing applications without any change in itself, these applications can use the index view. The only need to do is create the index view.
Optimizer considerations
Query optimizer will consider several criteria to determine the index view to cover part of the inquiry or the inquiry. These conditions meet the FROM clause of a single query and include the following:
◆ query FROM clause in the table must be indexed view FROM clause in a superset of the table.
◆ query join conditions must be the view of the conditions connected superset.
◆ aggregate query is a view out to be a subset of aggregate column.
◆ query select list all the expressions must be derived from the view select list or from the definition does not include the table in the view.
◆ query search condition predicates in the view definition must be a superset of the predicate search criteria. View search predicate in the conjunction of each item must be in the form of the same predicate in the query search.
◆ query search condition predicates in all columns (of the view definition in the table) must appear in one or more of the following:
View definition of the same predicate.
◆ GROUP BY list.
◆ View select list (if there is no GROUP BY list).
If the query contains more than one FROM clause (subqueries, derived tables, UNION), the optimizer can choose to manage multiple indexed view FROM clause of a query with multiple.
Note: there is an exception, that the optimizer may be folded into a two FROM clauses (to be folded into the join sub-query or derived table join folded variants). If such a situation, the index view, replace the original query may cover more than one FROM clause.
This document describes the end of the query examples demonstrate these conditions. The best way is proposed: to query optimizer to determine the query execution plan used by the index (if any).
Use NOEXPAND options
NOEXPAND option to force the query optimizer to treat the cluster index contains the same treatment as ordinary table view. In this case, must be referenced in the FROM clause directly indexed view. For example:
SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND) WHERE ...
Use EXPAND VIEWS options
In addition, end users can query by using the EXPAND VIEWS option, explicitly exclude from consideration the index view. For example:
SELECT Column1, Column2, ... FROM Table1, View1 WHERE ... OPTION (EXPAND VIEWS)
If you use this option, the query optimizer in the assessment of low-cost method (the method involves a query references a column) index of all views will be ignored.
Design considerations
For the database system to find the appropriate index set is quite complex. Despite the general index in the design of many possibilities to consider, but will be added to the structure of the index view will greatly increase the design complexity and potential results.
For example:
Indexed view can be used to query the table referenced by any sub-set. Any sub-set of the query terms (belonging to a subset of the table above) the column grouping. Aggregate function, such as SUM.
Design Table should also view the index and index for each structure from the best results. As the indexes and indexed views may be useful for a given query, so a separate design their proposals will lead to excess, resulting in higher storage and maintenance overhead. In the adjustment of the physical database design must consider all inquiries balanced set of performance requirements and the database system must support the update operation. Therefore, the view for the index to find a reasonable physical design is a very challenging task, which should be possible to use "index tuning wizard."
If there are many indexed views query optimizer for consideration for a specific query, the query optimization cost will increase significantly. Query query optimizer may consider any subset of the definition table for all indexed views. Reject each view, you must parse it, and then study the possibility of a potential replacement body. This may take some time, especially in view of hundreds of these for a given query.
View must meet several requirements, you can create a unique clustered index for its. In the design phase, consider the following requirements:
View and the view of all tables referenced must be in the same database and have the same owner.
Contains no index view for the query optimizer to use all the tables referenced.
Must first create a unique clustered index for the view before it can create other indexes. SET options are set correctly in some disabilities (in this document later in discussion). In addition, if the SET option is correct, the query optimizer will not consider the index view.
View must use the schema bound to create, view, referenced in any user-defined function must be used to create SCHEMABINDING option.
In addition, there are some disk space required to store the data from the indexed view definition.