Oracle indexed (Create, intro, tips, how to view)

I. INTRODUCTION of Index
1. Index equivalent to Directory
2, the index is a set of sorted index keys to replace the default full-table scan retrieval method, so as to improve retrieval efficiency.
3, the creation of the index to moderate, more will affect the efficiency of adding and removing changes, less will affect the efficiency of the query, the index is best created in the value of scattered columns, to avoid creating too many indexes on the same table
4, the use of the index is transparent to the user, the system to determine when to use the index.
5. Oracle supports multiple types of indexes, which can be categorized by the number of columns, whether the index value is unique, and the organization of indexed data to meet the requirements of various tables and query conditions. (see annex)
A. Single-column indexes and composite indexes
b B-Tree index (default type when CREATE INDEX)
All leaf nodes in the B-tree index have the same depth, so the query speed is basically the same regardless of the query condition. Another B-Tree index can adapt to a variety of query criteria, including precise query, fuzzy query and comparison query
--unique Unique index value is unique, but NULL is allowed, the primary key has a unique index by default, but the column cannot be null
--non-unique: A non-unique index whose index value can be duplicated and allowed to be null. By default, the indexes created by Oracle are non-unique indexes
--reverse key: Reverse keyword index. By specifying the "REVERSE" keyword when creating an index, you can create a reverse keyword index that is stored in reverse for each data column that is indexed
, but still keep the order of the original data columns
C Bitmap indexing (less range of column values, such as gender politics, rather than the default B-Tree index)
C Function index
When you need to access some functions or expressions frequently, you can store them in the index, and the next time you access them, because that value is already calculated, you can greatly improve those that contain the function in the WHERE clause or
The speed of the query operation of an expression;
A function index can use either a B-tree index or a bitmap index.
II. Principles for managing indexes
The use of indexes should follow some basic principles.
1.Small tables do not need to be indexed。
2. For large tables, indexes can be created if the number of records that are queried frequently is less than 15% of the total number of records in the table. This ratio is not absolute, it is inversely proportional to the full table scan speed.
3. Indexes can be indexed for columns where most column values are not duplicated.
4. For columns with large cardinality, a B-tree index is appropriate, whereas for columns with small cardinality it is appropriate to create a bitmap index.
5. The index should be indexed for columns that have many null values, but are frequently queried for all non-null value records.
6. Long and long raw columns cannot create an index.
7. Indexes should be created on columns that are frequently connected to queries.
8. When you create a query with the CREATE INDEX statement, the most frequently queried columns are placed in front of the other columns.
9. Maintaining indexes requires overhead, especially when inserting and deleting tables, so you limit the number of indexes in the table. For tables that are primarily for reading, there are many advantages to indexing, but if a table is frequently changed, the index should be less.
10. Creates an index after inserting data into the table. If an index is created before the data is loaded, Oracle must change each index when each row is inserted.
Three, long type (can store 2G) is mainly used to do not need to do string search long string of data, if you want to do character search will use VARCHAR2 type, storage so long please use Pstat1.setcharacterstream () method, information see annex P26
Four, create the index syntax as follows
The syntax for the CREATE index statement is as follows:
CREATE [UNIQUE] | [BITMAP] INDEX index_name
On table_name ([Column1 [asc| Desc],column2
[asc| DESC],...] | [Express])
[Tablespace Tablespace_name]
[PCTFREE N1]
[STORAGE (INITIAL N2)]
[Nologging]
[NoLine]
[Nosort];

Five
1. View index information can be in the all_indexs table
2. View index information and referenced column all_ind_columns
3. View function index information all_ind_expressions

4, Oracle is more intelligent, and sometimes even if you create an index will not be used, for example, in the case of low data volume, may not be indexed
5, when the full table scan, without indexing efficiency will be better
6, the query may use the cache, so if you find that the execution speed is faster, does not necessarily mean that your SQL is better, it is possible to use the cache
7, using the "explain plan" function in Plsql can compare the consumption of execution plan, and then write better SQL

Oracle indexed (Create, intro, tips, how to view)