Materialized View, index view, and function index. DESC is used when an index is created, and desc is materialized.

Materialized View, index view, and function index. DESC is used when an index is created, and desc is materialized.

I searched the information for a task assigned by the boss and thought it would be better to summarize it. What if I use it later? There are two topics: one is the ability to create an index on the view, and the other is whether the DESC keyword can be used when creating an index.

1. Can I create an index on a view?

Because a common view does not store actual information and the data it operates on comes from a basic table, you cannot create an index on a common view.

If you run the following statement in oracle, the error "view does not apply here" is reported.

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

What should I do when a large number of views need to be queried and the query efficiency is low? There are several methods:
Materialized views in Oracle can be used to create indexes, while index views in SQL Server can be used to create indexes. function-based indexes in Oracle can also solve this problem.

1. Materialized View

The Materialized View of Oracle is a database object that includes query results. It is a local copy of remote data or is used to generate a summary table based on the sum of data tables. Materialized View stores data based on remote tables, also known as snapshots.
 
Materialized views can be used to pre-calculate and save the results of time-consuming operations such as table join or aggregation. In this way, these time-consuming operations can be avoided during query execution, and get results quickly. Materialized views are similar to indexes in many aspects: materialized views are used to improve query performance, while materialized views are transparent to applications, adding or deleting materialized views does not affect the correctness and validity of SQL statements in applications. materialized views occupy storage space. materialized views should also be refreshed when the base table changes.
 
Materialized views can be used to query tables, views, and Other materialized views.
 
A materialized view is usually called a master table (during replication) or a schedule (in a data warehouse ).
For replication, materialized views allow you to locally maintain copies of remote data, which are read-only. To modify a local copy, you must use the advanced copy function. When you want to extract data from a table or view, you can extract data from the materialized view.
For a data warehouse, the created materialized view is usually an aggregation view, a single table aggregation view, and a connection view.
In the replication environment, the created materialized view usually contains the primary key, rowid, and subquery view.
 
Materialized views can create indexes because they exist physically.

For more information about how to create a materialized view and how to use it, see the following article.

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

Compare common views

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

Materialized views are physical and can be used to create indexes.

Reference: differences between a common view and a Materialized View

2. Index view in SQLServer

You can create an index view in SQLServer, which is similar to the materialized view.

For more information about how to create an index view for SQLServer, see the following link.

Reference: how to create an index on a view

Summary of creating an index view (materialized view) in SQL Server 2008

3. function-based indexes in Oracle

Concept: if an expression is often used as a condition during DML operations, you can create an index based on this function. When creating such an index, Oracle first evaluates the functions or expressions that contain the index column, sorts these values, and then stores them in the index. Function-based indexes can be common B-tree indexes or Bitmap indexes.

Motivation: for example, execute the following SQL statement: select * from emp where upper (ename) = 'King'. Even if an index is created on the ename, the emp table is still scanned in the entire table, change the ename field to uppercase and compare it with the constant KING. If we create a function-based index, for example, create index emp_upper_idx on emp (upper (ename); at this time, we only need to scan a small part of data by range, then obtain the rowid to access the data in the table, which is faster.

A function-based index is similar to a common index, but a common index is created on a column, and it is created on a function. Of course this will have a certain impact on data insertion, because we need to use function compute to generate an index. However, insert data is usually inserted in a small amount, while query data usually has a large amount of data. To optimize the query speed, it is sufficient to slightly reduce the point insertion speed.

Example

When talking about any operations on columns, full table scan may occur, for example:

select * from emp where substr(ename,1,2)=’SM’; 

However, this type of query is frequently used in the customer service system. We can create an Oracle function index with the substr function,

create index emp_ename_substr on eemp ( substr(ename,1,2) ); 

In this way, the function-based INDEX will be used when the preceding query statement is executed, and the Execution Plan will be (index range scan ).
In the above example, we have created a function-based index, but if you execute the following query:

select * from emp where substr(ename,1,1)=’S’ 

The expected execution plan will still be (table access full), because function-based indexes will take effect only when the data column can match equations, in this way, the requirements for the planning and maintenance of such indexes are high. Note that adding an index to a table is very dangerous because it will lead to many changes to the query execution plan. However, if we use a function-based index, this issue will not occur because Oracle uses this type of index only when matching built-in functions are used for queries. (In my understanding, creating a common index will lead to changes to the query execution plan. However, when creating a function index, the execution plan will only consider this index when the query conditions match .)

Reference: Oracle Database Application and Development

Oracle function-based index

Improve query efficiency using Oracle function indexes

Ii. Whether DESC can be used to create an index

The syntax for creating an index in Oracle 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>]

ASC and DESC indicate that the created indexes are sorted in ascending and descending order. When creating an index, you can specify multiple fields or expressions, which are separated by commas.

For example:

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