Shulou(Shulou.com)06/01 Report--

Oracle built-in object

Sequence, view, index

Sequence

Create sequence aaa start with 1

Use

View

After it is created,

Then use it directly.

Just OK.

With views, you can replace subqueries, making sql concise

The view does not store real data, but query statements.

The base table can be modified through the view

The premise is that the data structure of the view is the same as that of the base table.

Read-only view

But the view is for viewing, not for changing.

So how do you avoid making changes later when you create a view? The answers are as follows:

Check the view

In another case, if you want to modify the view, then the view column itself is unconstrained, then you can check the constraints in the base table with the check view.

The above are all simple views

The result of multi-table join check is a complex view, which can only be used to simplify the query.

Note: you can use the view wherever you can use the table!

Finally, the index!

The function of index

Interview questions: how to query massive data quickly: index + optimization

OK, let's learn about indexing.

When we create constraints on columns, some constraints automatically create indexes for us.

There are many kinds of indexes, and the ones automatically added above are called unique value indexes.

Next, we build our own index, which is called a general index.

Single field index:

Multi-field index

In fact, an index is a table, just like a book catalog, query things can be quickly located, do not have to turn a page to find.

In addition, the index is the hard disk and memory space of the station database, that is, the so-called resource consumption in exchange for speed

The most important thing is that after indexing the field, it can be exchanged for faster speed, but at the same time, it will reduce the speed of adding, deleting and changing, because when doing the operation of adding, deleting and changing, the index table will be updated, which is equivalent to twice the speed!

Index tables are automatically maintained by the database for us, without our own maintenance, which is another biggest advantage of the index.

Special indexes: bitmap index

There is a situation where there are only a few types of data for some columns created, such as

Gender: only men and women

The class in the student table of a school: only Grade one, Grade two to Grade six.

So I want to quickly locate these data. For example, I want to check the information of the second graders among the 100000 students in a school.

So we need a special index called a bitmap index.

When I inquire

When you say this, it will be very fast.

Indexing rules. Only commonly used where qualified columns are indexed.

For example, if I want to check the time period frequently, that is, the time period defined in where, then we will index the time column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.