Oracle CREATE INDEX thinking

In an Oracle database, creating an index is relatively straightforward. However, it is more difficult to create indexes reasonably. The author believes that when creating an index to do three appropriate, that is, on the appropriate table, the appropriate number of columns on the creation of an appropriate quantity of indexes. Although this can be summed up in a sentence to summarize the basic criteria of an optimized index, to do so requires a great effort on the part of the database administrator. Specifically, to achieve this three appropriate has the following several requirements.
One, create an index based on the size of the table.
While creating indexes on tables, you can improve the efficiency of your queries. But the database administrator needs to be aware that the index also requires a certain amount of overhead. This does not mean that indexes are created for all tables, so you can improve the performance of your database. This understanding is wrong. On the contrary, if the index is created for all tables regardless of 3,721, it will adversely affect the performance of the database. This is because the cost of abusing the index may be far greater than the performance benefit. So the author believes that the database administrator first needs to do, for the appropriate table to index, rather than to index all the tables.
In general, you do not need to create indexes for smaller tables. As in the database of an ERP system, the Department table is used to store information about the Enterprise Department. The general enterprise part also has more than 10, the maximum no more than 100. These 100 records may be a lot more for people. But it's not enough for a computer to rubber cork stoppling his teeth. Therefore, it is not necessary to index a similar small table. Because even if the index is established, its performance will not be greatly improved. Instead, the cost of indexing, such as maintenance costs, is larger than this. That is to say, pay more than get, obviously contrary to common sense.
In addition, it is not necessary to index an oversized table. Some tables, though larger, have a very large number of records. However, it is appropriate to index the table at this time. If there is a table in the system, it is mainly used to save some change information in the database. This information is often used only for database administrators. It is not appropriate to index this table at this time. Because this watch is seldom used, it needs to be viewed only when there is a problem. Second, even if the view, the need to query the record is not much, it may be the last week of update records and so on. For some super-large tables, indexing sometimes does not achieve the expected results. and indexing on a table is a lot more expensive than a normal table. So do you want to index the big table? I think, the main is to look at two aspects of the content. The first is to pay attention to the number of records that are frequently required to be queried in this large table. In general, it is not necessary to index a data that is frequently required to be queried if it does not exceed 10% to 15%. This is because the cost of indexing can be much larger than the performance improvement. This ratio is only an empirical data. If the database administrator needs to arrive at a more accurate conclusion, a test analysis is required. That is, the database administrator needs to test the time of the full table scan to see if it is longer or shorter than the indexed query time. If it is long, then it is necessary to establish an index. But if not, then the full-table scan is fast. There is no need to index it at this point.
In summary, it is generally not necessary to index a small table when considering whether to index the table. And for the table, it is necessary to carry out practical analysis of the actual situation. Simple, can be determined according to the approximate ratio. If you want to be precise, you can perform a full table scan performance analysis to determine whether the database performance is actually improved as expected after indexing.
Create an index based on the characteristics of the column.
column has different characteristics, the effect of index creation is also different. The database administrator needs to know which columns are indexed to create a less effective effect. It is also necessary to understand which columns create indexes instead of the effect of less effort. This helps them to understand exactly what kind of field to index.
Based on the author's experience, it is often possible to create an index for columns of the following characteristics to have a more obvious effect. For columns with fewer duplicates, especially for columns that have unique constraints defined. Indexing on these columns can often be a very good effect. If you are mixing a column with a null value for a column with a non-null value, it is a good idea to index the column if the user needs to query the columns of all non-null value records frequently. If you frequently need multiple table join queries, you can achieve a multiplier effect by setting the index on the column with the connection.
It can be seen that the index settings are appropriate, not only related to database design architecture, but also related to the business's economic business. For this reason, for some packaged software, although the database administrator has already done the optimization work of indexing. But with the subsequent increase in economic data, the index will be less effective. This is mainly because the table of records affects the effect of index optimization. Therefore, I suggest that the database administrator, even if the use of big-brand software package software, but also need a period of time, such as a year, the index of the database optimization. The removal of the removal, the adjustment adjustment to improve the performance of the database.
If there is a table in the database that is used to hold user information. There is a field ID number, which is a unique field. At the time of database design, an index was created for this field. However, when the database is put into use, the user does not enter the user's ID number. And usually do not press this number to inquire. When the record month to month, this ID number on the Index field not only can not improve the database query performance, but become a chicken. For these columns that have a lot of null values and do not frequently query all the columns of non-null value records, the database administrator has to make a decision even if the indexes on those columns are cleared.
Therefore, the optimization and adjustment of the index is a dynamic process, not that the database design is not necessary after the adjustment. Database administrators often need to make appropriate changes based on the changes in the records. To improve the effect of the index.
Iii. How many indexes are appropriate to create on a table?
Although there is no limit to the number of indexes created on a table, it is never the more the better. In other words, 1+1〉2 often does not hold up in the event of creating an index. Sometimes, the more indexes you create, the more counterproductive it can be. So how many indexes are appropriate to create on a table? This does not have a clear standard. Instead, the database administrator is required to make judgments based on the actual usage and the records in the database.
In general, the more indexes a table has, the faster it will be queried. However, the update speed of the table is reduced. This is mainly due to the speed at which the table is updated (such as inserting a record into the table), but increases as the index increases. This is mainly because the relevant index information needs to be updated while the record is being updated. To do this, it is necessary to strike a balance between this update speed and the query speed in order to create the correct number of indexes in the table. For some data warehouse or decision-making database system, it is mainly used for querying. The related records are often dumped when the database is initialized. At this point, you can increase the query performance of your database by setting a little more index. At the same time, because the record is not updated, so the index is more than the case, it will not affect the speed of the update. Even if you need to import a large amount of data at the beginning, you can disable the index first. Wait until the data has been imported, and then enable indexing. You can reduce the impact of indexes on data updates in this way. Conversely, if records are frequently needed in those tables, such as transactional applications, data update operations are commonplace. If you create too many indexes in a single table, the speed of the update will be affected. Because the update operation is more frequent, the negative effect on it is much higher than the query efficiency. At this point, you need to limit the number of indexes, and only the necessary fields are indexed.
In the usual database optimization, I tend to set the index for the column according to the purpose of these tables. You can query the relevant dynamic view to see if the operation for this table is a large percentage of update operations (including updates, deletions, insertions, and so on) or a large percentage of query operations. When too many indexes have affected the speed of the update operation, the database administrator needs to disable some indexes first to improve the performance of the database.
In summary, establish an appropriate index on the appropriate table and on the appropriate columns. This sentence contains a lot of meaning, the above content is only part of the content. As the saying goes, the teacher leads the door, practice on their own. The author here refers to being able to donuts. Some of the specific index optimization content is still required by the reader in the daily work to understand and summarize.

Http://www.webjx.com/exam/oracle-13846.html




Note: The default is B-tree

Drop index RANDOM_EMPNO_BMX;

Index dropped.

Sql> CREATE index Random_empno_idx on test_random (empno);

Index created.


It can also be a bitmap index

Sql> Create bitmap index normal_empno_bmx on test_normal (empno);

Index created.


Here's how to create an index:


Proper use of indexes can improve data retrieval speed and can create indexes on fields that frequently require queries

Oracle's index is divided into 5 types: Unique index, combined index, reverse key index, bitmap index, function-based index

Standard syntax for creating indexes:

CREATE index index name on table name (column name)

tablespace table space name;

To create a unique index:

CREATE Unique index index name on table name (column name)

tablespace table space name;

To create a composite index:

CREATE index index name on table name (column name 1, column name 2)

tablespace table space name;

To create a reverse key index:

CREATE index index name on table name (column name) reverse

tablespace table space name;





1. Index classification

A) unique index, the role of data constraints, to ensure that the data is unique, there is data index, improve query efficiency

b) General index, only the role of the data index,


2. Creation of a unique index

Create unique index index name on table name (field name)

OK, suppose you have a emploeyy table with a empname field, and we'll add a unique index to EmpName

Create unique index Idx_empname on employee (EmpName);


3. General Index

Create index index name on table name (field name)

OK, now we add a generic index for the employee's address field

CREATE index idx_address on employee (address);


We can also index more than two fields

Create unique index idx_test on employee (FIELD1,FIELD2);

This adds a unique index to FIELD1,FIELD2, and the combination of field1 and Field2 is unique.


You can also specify index ordering

CREATE INDEX Idx_test employee (field1, field2 desc);;


4. Function index

If the function is used in our query criteria, then the index is not available.

You can solve this problem by creating a function index.

For example:

SELECT * FROM product where NVL (price,0.0) >1000.0;

Here, NVL (price,0.0) uses the function, and the index cannot take advantage of the index on the Price field.

OK, let's create the function index.

CREATE index Index_price on product (NVL (price,0.0));


5. Deletion of indexes

Drop INDEX Index name

Drop index Idx_empname;


6. Other

The unique index can greatly improve the query speed, but also the role of a unique constraint

General index, can only increase the speed of about 30%

frequently inserted, modified, should be in the case of the query allows to minimize the index, because add index, insert, modify and other operations, need more time

The index can be added in the order by field, where Condition field, join's associated field

Like what:

SELECT * FROM table1 t1

Left join Table2 T2 on T1. field a=t2. Field B

where t1. field c = ' value '

Order by T1. Field D

Here, the A,b,c,d field, should be added to the index

1. Create a new table structure, creating an index, and importing millions or tens of data using Insert to import the table.
2. Create a new table structure, import millions or tens of data using Isnert to the table, and then build the index.

1, single from the speed of consideration, you should first insert data, and then build the index.
2, the actual combat also depends on business requirements, and some businesses may also need to index and constraints on the data validation, so that the index should be established (for example: Primary key)