NAME

CREATE INDEX - 定義一個新索引

SYNOPSIS

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ WHERE predicate ]

DESCRIPTION 描述

CREATE INDEX 在指定的表上構(gòu)造一個名為 index_name 的索引。索引主要用來提高數(shù)據(jù)庫性能。但是如果不恰當(dāng)?shù)氖褂脤?dǎo)致性能的下降。

 索引的鍵字字段是以字段名的方式聲明的，或者是可選的寫在一個圓括弧里面的表達(dá)式。 如果索引方式支持多個字段索引，那么我們也可以聲明多個字段。

 一個索引字段可以是一個使用表的行的一個或多個字段的數(shù)值進(jìn)行計算的表達(dá)式。 整個特性可用于獲取對基本數(shù)據(jù)某種變形的快速訪問。 比如，一個在 upper(col) 上的函數(shù)索引將允許子句 WHERE upper(col) = 'JIM' 使用索引。

PostgreSQL 為從索引提供 B-tree，R-tree，hash（散列） 和 GiST 索引方法。 B-tree 索引方法是一個 Lehman-Yao 高并發(fā) B-trees 的實(shí) 現(xiàn)。R-tree 索引方法用 Guttman 的二次分裂算法實(shí)現(xiàn)了標(biāo)準(zhǔn)的 R-trees。 hash（散列）索引方法是 Litwin 的線性散列的一個實(shí)現(xiàn)。 用戶也可以定義它們自己的索引方法，但這個工作相當(dāng)復(fù)雜。

 如果出現(xiàn)了 WHERE 子句，則創(chuàng)建一個部分索引。 部分索引是一個只包含表的一部分記錄的索引，通常是該表中最讓人感興趣的部分。 比如，如果你有一個表，里面包含已上賬和未上賬的定單， 未上賬的定單只占表的一小部分而且這部分是最常用的部分， 那么你就可以通過只在這個部分創(chuàng)建一個索引來改善性能。 另外一個可能的用途是用 WHERE 和 UNIQUE 強(qiáng)制一個表的某個子集的***性。

 在 WHERE 子句里用的表達(dá)式只能引用下層表的字段（但是它可以使用所有字段，而不僅僅是被索引的字段）。 目前，子查詢和聚集表達(dá)式也不能出現(xiàn)在WHERE里。

 索引定義里的所有函數(shù)和操作符都必須是immutable，（不變的）也就是說， 它們的結(jié)果必須只能依賴于它們的輸入?yún)?shù)，而決不能依賴任何外部的影響（比如另外一個表的內(nèi)容或者當(dāng)前時間）。 這個約束確保該索引的行為是定義完整的。要在一個索引上使用用戶定義函數(shù)，請記住在你創(chuàng)建它的時候把它標(biāo)記為immutable的函數(shù)。  

PARAMETERS 參數(shù)

UNIQUE

 令系統(tǒng)檢測當(dāng)索引創(chuàng)建時（如果數(shù)據(jù)已經(jīng)存在）和每次添加數(shù)據(jù)時表中是否有重復(fù)值。 如果插入或更新的值會導(dǎo)致重復(fù)的記錄時將生成一個錯誤。

name

 要創(chuàng)建的索引名。這里不能包含模式名； 索引總是在同一個模式中作為其父表創(chuàng)建的。

table

 要索引的表名（可能有模式修飾）。

method

 用于索引的方法的名字。可選的名字是 btree， hash，rtree，和 gist。缺省方法是 btree。

column

 表的列/字段名。

expression

 一個基于該表的一個或多個字段的表達(dá)式。 這個表達(dá)式通常必須帶著圓括弧包圍寫出，如語法中顯示那樣。 不過，如果表達(dá)式有函數(shù)調(diào)用的形式，那么圓括弧可以省略。

opclass

 一個關(guān)聯(lián)的操作符表。參閱下文獲取細(xì)節(jié)。

predicate

 為一個部分索引定義約束表達(dá)式。

NOTES 注意

 參閱 ``Indexes'' 獲取有關(guān)何時使用索引，何時不使用索引， 以及哪種情況下是有用的信息。

 目前，只有 B-tree 和 gist 索引方法支持多字段索引。 缺省時最多可以聲明 32 個鍵字（這個限制可以在制作 PostgreSQL 時修改）。 目前只有 B-tree 支持***索引。

 可以為索引的每個列/字段聲明一個 操作符表。 操作符表標(biāo)識將要被該索引用于該列/字段的操作符。 例如， 一個四字節(jié)整數(shù)的 B-tree 索引將使用 int4_ops 表； 這個操作符表包括四字節(jié)整數(shù)的比較函數(shù)。 實(shí)際上，該域的數(shù)據(jù)類型的缺省操作符表一般就足夠了。 某些數(shù)據(jù)類型有操作符表的原因是，它們可能有多于一個的有意義的順序。 例如，我們對復(fù)數(shù)類型排序時有可能以絕對值或者以實(shí)部。 我們可以通過為該數(shù)據(jù)類型定義兩個操作符表，然后在建立索引的時候選擇合適的表來實(shí)現(xiàn)。 有關(guān)操作符表更多的信息在 ``Operator Classes'' 和 ``Interfacing Extensions to Indexes'' 里。

 使用 DROP INDEX [drop_index(7)] 刪除一個索引。  

EXAMPLES 例子

 在表films上的 title字段創(chuàng)建一個 B-tree 索引：

CREATE UNIQUE INDEX title_idx ON films (title);

#p#

NAME

CREATE INDEX - define a new index

SYNOPSIS

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ WHERE predicate ]

DESCRIPTION

CREATE INDEX constructs an index index_name on the specified table. Indexes are primarily used to enhance database performance (though inappropriate use will result in slower performance).

The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses. Multiple fields can be specified if the index method supports multicolumn indexes.

An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index.

PostgreSQL provides the index methods B-tree, R-tree, hash, and GiST. The B-tree index method is an implementation of Lehman-Yao high-concurrency B-trees. The R-tree index method implements standard R-trees using Guttman's quadratic split algorithm. The hash index method is an implementation of Litwin's linear hashing. Users can also define their own index methods, but that is fairly complicated.

When the WHERE clause is present, a partial index is created. A partial index is an index that contains entries for only a portion of a table, usually a portion that is somehow more interesting than the rest of the table. For example, if you have a table that contains both billed and unbilled orders where the unbilled orders take up a small fraction of the total table and yet that is an often used section, you can improve performance by creating an index on just that portion. Another possible application is to use WHERE with UNIQUE to enforce uniqueness over a subset of a table.

The expression used in the WHERE clause may refer only to columns of the underlying table (but it can use all columns, not only the one(s) being indexed). Presently, subqueries and aggregate expressions are also forbidden in WHERE. The same restrictions apply to index fields that are expressions.

All functions and operators used in an index definition must be ``immutable'', that is, their results must depend only on their arguments and never on any outside influence (such as the contents of another table or the current time). This restriction ensures that the behavior of the index is well-defined. To use a user-defined function in an index expression or WHERE clause, remember to mark the function immutable when you create it.  

PARAMETERS

UNIQUE

Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.

name

The name of the index to be created. No schema name can be included here; the index is always created in the same schema as its parent table.

table

The name (possibly schema-qualified) of the table to be indexed.

method

The name of the method to be used for the index. Choices are btree, hash, rtree, and gist. The default method is btree.

column

The name of a column of the table.

expression

An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses may be omitted if the expression has the form of a function call.

opclass

The name of an operator class. See below for details.

predicate

The constraint expression for a partial index.

NOTES

See the chapter called ``Indexes'' in the documentation for information about when indexes can be used, when they are not used, and in which particular situations can be useful.

Currently, only the B-tree and GiST index methods support multicolumn indexes. Up to 32 fields may be specified by default. (This limit can be altered when building PostgreSQL.) Only B-tree currently supports unique indexes.

An operator class can be specified for each column of an index. The operator class identifies the operators to be used by the index for that column. For example, a B-tree index on four-byte integers would use the int4_ops class; this operator class includes comparison functions for four-byte integers. In practice the default operator class for the column's data type is usually sufficient. The main point of having operator classes is that for some data types, there could be more than one meaningful ordering. For example, we might want to sort a complex-number data type either by absolute value or by real part. We could do this by defining two operator classes for the data type and then selecting the proper class when making an index. More information about operator classes is in the sections called ``Operator Classes'' and ``Interfacing Extensions to Indexes'' in the documentation.

Use DROP INDEX [drop_index(7)] to remove an index.  

EXAMPLES

To create a B-tree index on the column title in the table films:

CREATE UNIQUE INDEX title_idx ON films (title);