Materialized views, indexed views, function indexes, using DESC when creating indexes

The boss gave a task, search the information, think it is better to summarize. What if I use it later? Around two topics: one is the ability to index on a view, and the other is whether the DESC keyword can be used when creating an index.

One, can I create an index on a view

Because the normal view does not store the actual information, the data it operates on is from the base table, so it is not possible to create an index on a normal view.

Execute the following statement in Oracle and report "View not available here" error

Create VIEW test_carasselect licensefrom cars;create index index_vew_car on test_car (license);

What if you need to make a lot of queries on the view, and the query is less efficient? There are several ways to do this:
There are materialized views in Oracle that can be indexed, and indexed views in SQL Server can be indexed, and function-based indexes in Oracle can solve this problem.

1. Materialized view

The materialized view of Oracle is a database pair image that includes a query result, which is a local copy of the remote data, or is used to generate a summary table based on the sum of the data tables. Materialized views store data based on remote tables and can also be referred to as snapshots.

Materialized views can be used to pre-compute and save the results of more time-consuming operations such as table joins or aggregates, so that time-consuming operations can be avoided when queries are executed, and results are obtained quickly. Materialized views are similar in many respects to indexes: the purpose of using materialized views is to improve query performance; Materialized views are transparent to the application, and adding and removing materialized views does not affect the correctness and validity of the SQL statements in the application; materialized views need to occupy storage space , and when the base table is changed, Materialized views should also be refreshed.

Materialized views can query tables, views, and other materialized views.

Typically, materialized views are referred to as primary tables (during replication) or schedules (in the Data Warehouse).
For replication, materialized views allow you to maintain a copy of the remote data locally, which is read-only. If you want to modify the local copy, you must use the Advanced Replication feature. When you want to extract data from a table or view, you can extract it from a materialized view.
For data warehouses, the materialized views created are typically aggregated views, single-table aggregation views, and connection views.
In a replication environment, the materialized views created are typically primary keys, ROWID, and subquery views.

Materialized views can create indexes because they are physically real.

How to create materialized views and use examples is shown in the first reference below.

Reference: http://blog.csdn.net/tianlesoftware/article/details/4713553

Compare Normal view

Normal view does not store physical information, it is just an SQL statement. Therefore, you cannot create an index on it.

Materialized views are physically real, and indexes can be created.

Reference: Normal view and materialized view differences

2. Indexed views in SQL Server

An indexed view can be created in SQL Server, similar to the concept of materialized views.

Several considerations for SQL Server to create indexed views are shown in the following reference links.

Reference: How to create an index on a view

Summary of SQL Server 2008 creation of indexed views (materialized views)

3. Function-based indexing in Oracle

Concept: If an expression is used frequently as a condition in DML operations, an index based on that function can be established. When such an index is created, Oracle first evaluates the function or expression that contains the indexed column, then sorts the values and then stores them in the index. A function-based index can be either a normal B-tree index or a bitmap index.

Motive: For example, execute the following SQL statement: SELECT * from EMP where upper (ename) = ' KING ', even if the index is established on the ename, or the entire table is scanned for the EMP table, the Ename field inside is changed to uppercase with the constant KING to compare. If we create a function-based index, for example: Create INDEX EMP_UPPER_IDX on EMP (Upper (ename)); At this point, we just need to scan a small subset of the data by interval and then get the data from the ROWID Access table, which is faster.

A function-based index is similar to a normal index, except that a normal index is built on a column, and it is built on a function. This time, of course, has a certain effect on inserting data because it needs to be evaluated by a function and then indexed. However, inserting data is generally a small number of inserts, and the query Data General data volume is larger. To optimize the query speed, it is possible to lower the insertion speed slightly.

An example

Talking about any action on a column can result in a full table scan, for example:

But this kind of query is often used in the customer service system, we can create an Oracle function index with SUBSTR function,

This function-based index will come in handy when executing the above query, and the execution plan will be (index RANGE SCAN).
In the example above, we created the function-based index, but if we execute the following query:

The resulting execution plan will still be (TABLE ACCESS full), because the function-based index can only take effect if the data column is able to match the equation, so the requirements for scheduling and maintaining the index are high. Note that adding an index to a table is a very risky operation because it causes many query execution plan changes. However, if we use a function-based index, this is not a problem because Oracle uses this type of index only if the query uses a matching built-in function. （my understanding is that creating a normal index results in a change in the query execution plan, and creating the function index will only be considered by the execution plan when the query criteria match. ）

Reference: Oracle database application and development

Oracle Function-based indexing

Improve query efficiency with Oracle function indexes

Ii. can I use DESC when I create an index?

The syntax for Oracle to create indexes is as follows:

Create [unique | bitmap] index [schema.] < index_name >
on [schema.] < table_name >
(< column_name > | < expression > ASC | DESC,
< column_name > | < expression > ASC | DESC, ...)
[Tablespace < Tablespace_name;]
[Storage (< storage_settings >)]
[Logging | nologging]
[Nosort | reverse]
[Partition | global partition <partition_setting>]

where ASC and DESC indicate that the index created is in ascending, descending order. You can specify multiple fields or multiple expressions when you create an index, separated by commas.

Such as:

CREATE TABLE cars (  license int,  owner varchar (8),  model varchar (8)); CREATE index Idx_carlcs on cars (license DESC);

Reference: Oracle database application and development

Materialized views, indexed views, function indexes, using DESC when creating indexes