diff options
Diffstat (limited to 'doc/src/sgml/ref/create_index.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_index.sgml | 458 |
1 files changed, 0 insertions, 458 deletions
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml deleted file mode 100644 index a3af001006a..00000000000 --- a/doc/src/sgml/ref/create_index.sgml +++ /dev/null @@ -1,458 +0,0 @@ -<!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.31 2002/05/18 15:44:47 petere Exp $ -PostgreSQL documentation ---> - -<refentry id="SQL-CREATEINDEX"> - <refmeta> - <refentrytitle id="sql-createindex-title">CREATE INDEX</refentrytitle> - <refmiscinfo>SQL - Language Statements</refmiscinfo> - </refmeta> - <refnamediv> - <refname> - CREATE INDEX - </refname> - <refpurpose> - define a new index - </refpurpose> - </refnamediv> - <refsynopsisdiv> - <refsynopsisdivinfo> - <date>2001-07-15</date> - </refsynopsisdivinfo> - <synopsis> -CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable> - [ USING <replaceable class="parameter">acc_method</replaceable> ] ( <replaceable class="parameter">column</replaceable> [ <replaceable class="parameter">ops_name</replaceable> ] [, ...] ) - [ WHERE <replaceable class="parameter">predicate</replaceable> ] -CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable> - [ USING <replaceable class="parameter">acc_method</replaceable> ] ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) [ <replaceable class="parameter">ops_name</replaceable> ] ) - [ WHERE <replaceable class="parameter">predicate</replaceable> ] - </synopsis> - - <refsect2 id="R2-SQL-CREATEINDEX-1"> - <refsect2info> - <date>1998-09-09</date> - </refsect2info> - <title> - Inputs - </title> - <para> - - <variablelist> - <varlistentry> - <term>UNIQUE</term> - <listitem> - <para> - 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. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">index_name</replaceable></term> - <listitem> - <para> - 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. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">table</replaceable></term> - <listitem> - <para> - The name (possibly schema-qualified) of the table to be indexed. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">acc_method</replaceable></term> - <listitem> - <para> - The name of the access method to be used for the index. The - default access method is <literal>BTREE</literal>. - <application>PostgreSQL</application> provides four access - methods for indexes: - - <variablelist> - <varlistentry> - <term><literal>BTREE</></term> - <listitem> - <para> - an implementation of Lehman-Yao - high-concurrency B-trees. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>RTREE</></term> - <listitem> - <para>implements standard R-trees using Guttman's - quadratic split algorithm. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>HASH</></term> - <listitem> - <para> - an implementation of Litwin's linear hashing. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>GIST</></term> - <listitem> - <para> - Generalized Index Search Trees. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">column</replaceable></term> - <listitem> - <para> - The name of a column of the table. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">ops_name</replaceable></term> - <listitem> - <para> - An associated operator class. See below for details. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">func_name</replaceable></term> - <listitem> - <para> - A function, which returns a value that can be indexed. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><replaceable class="parameter">predicate</replaceable></term> - <listitem> - <para> - Defines the constraint expression for a partial index. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> - - <refsect2 id="R2-SQL-CREATEINDEX-2"> - <refsect2info> - <date>1998-09-09</date> - </refsect2info> - <title> - Outputs - </title> - <para> - - <variablelist> - <varlistentry> - <term><computeroutput> -CREATE INDEX - </computeroutput></term> - <listitem> - <para> - The message returned if the index is successfully created. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><computeroutput> -ERROR: Cannot create index: 'index_name' already exists. - </computeroutput></term> - <listitem> - <para> - This error occurs if it is impossible to create the index. - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> - </refsynopsisdiv> - - <refsect1 id="R1-SQL-CREATEINDEX-1"> - <refsect1info> - <date>1998-09-09</date> - </refsect1info> - <title> - Description - </title> - <para> - <command>CREATE INDEX</command> constructs an index - <replaceable class="parameter">index_name</replaceable> - on the specified <replaceable class="parameter">table</replaceable>. - - <tip> - <para> - Indexes are primarily used to enhance database performance. - But inappropriate use will result in slower performance. - </para> - </tip> - </para> - - <para> - In the first syntax shown above, the key field(s) for the - index are specified as column names. - Multiple fields can be specified if the index access method supports - multicolumn indexes. - </para> - - <para> - In the second syntax shown above, an index is defined on the result - of a user-specified function <replaceable - class="parameter">func_name</replaceable> applied to one or more - columns of a single table. These <firstterm>functional - indexes</firstterm> can be used to obtain fast access to data based - on operators that would normally require some transformation to apply - them to the base data. For example, a functional index on - <literal>upper(col)</> would allow the clause - <literal>WHERE upper(col) = 'JIM'</> to use an index. - </para> - - <para> - <application>PostgreSQL</application> provides B-tree, R-tree, hash, - and GiST access methods for indexes. The B-tree access method is an - implementation of Lehman-Yao high-concurrency B-trees. The R-tree - access method implements standard R-trees using Guttman's quadratic - split algorithm. The hash access method is an implementation of - Litwin's linear hashing. We mention the algorithms used solely to - indicate that all of these access methods are fully dynamic and do - not have to be optimized periodically (as is the case with, for - example, static hash access methods). - </para> - - <para> - When the <command>WHERE</command> clause is present, a - <firstterm>partial index</firstterm> 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 <command>WHERE</command> with - <command>UNIQUE</command> to enforce uniqueness over a subset of a - table. - </para> - - <para> - The expression used in the <command>WHERE</command> clause may refer - only to columns of the underlying table (but it can use all columns, - not only the one(s) being indexed). Presently, sub-SELECTs and - aggregate expressions are also forbidden in <command>WHERE</command>. - </para> - - <para> - All functions and operators used in an index definition must be - <firstterm>immutable</>, that is, their results must depend only on - their input 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, remember to mark the function immutable - when you create it. - </para> - - <para> - Use <xref linkend="sql-dropindex" endterm="sql-dropindex-title"> - to remove an index. - </para> - - <refsect2 id="R2-SQL-CREATEINDEX-3"> - <refsect2info> - <date>1998-09-09</date> - </refsect2info> - <title> - Notes - </title> - - <para> - The <productname>PostgreSQL</productname> - query optimizer will consider using a B-tree index whenever - an indexed attribute is involved in a comparison using one of: - - <simplelist type="inline"> - <member><</member> - <member><=</member> - <member>=</member> - <member>>=</member> - <member>></member> - </simplelist> - </para> - - <para> - The <productname>PostgreSQL</productname> - query optimizer will consider using an R-tree index whenever - an indexed attribute is involved in a comparison using one of: - - <simplelist type="inline"> - <member><<</member> - <member>&<</member> - <member>&></member> - <member>>></member> - <member>@</member> - <member>~=</member> - <member>&&</member> - </simplelist> - </para> - - <para> - The <productname>PostgreSQL</productname> - query optimizer will consider using a hash index whenever - an indexed attribute is involved in a comparison using - the <literal>=</literal> operator. - </para> - - <para> - Currently, only the B-tree and gist access methods support multicolumn - indexes. Up to 16 keys may be specified by default (this limit - can be altered when building - <application>PostgreSQL</application>). Only B-tree currently supports - unique indexes. - </para> - - <para> - An <firstterm>operator class</firstterm> 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 <literal>int4_ops</literal> class; - this operator class includes comparison functions for four-byte - integers. In practice the default operator class for the field'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. There are also some operator - classes with special purposes: - - <itemizedlist> - <listitem> - <para> - The operator classes <literal>box_ops</literal> and - <literal>bigbox_ops</literal> both support R-tree indexes on the - <literal>box</literal> data type. - The difference between them is that <literal>bigbox_ops</literal> - scales box coordinates down, to avoid floating-point exceptions from - doing multiplication, addition, and subtraction on very large - floating-point coordinates. (Note: this was true some time ago, - but currently the two operator classes both use floating point - and are effectively identical.) - </para> - </listitem> - </itemizedlist> - </para> - - <para> - The following query shows all defined operator classes: - - <programlisting> -SELECT am.amname AS acc_method, - opc.opcname AS ops_name, - opr.oprname AS ops_comp - FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr - WHERE opc.opcamid = am.oid AND - amop.amopclaid = opc.oid AND - amop.amopopr = opr.oid - ORDER BY acc_method, ops_name, ops_comp; - </programlisting> - </para> - </refsect2> - </refsect1> - - <refsect1 id="R1-SQL-CREATEINDEX-2"> - <title> - Usage - </title> - <para>To create a B-tree index on the field <literal>title</literal> - in the table <literal>films</literal>: - </para> - <programlisting> -CREATE UNIQUE INDEX title_idx - ON films (title); - </programlisting> - -<!-- -<comment> -Is this example correct? -</comment> - <para> - To create a R-tree index on a point attribute so that we - can efficiently use box operators on the result of the - conversion function: - </para> - <programlisting> -CREATE INDEX pointloc - ON points USING RTREE (point2box(location) box_ops); -SELECT * FROM points - WHERE point2box(points.pointloc) = boxes.box; - </programlisting> ---> - - </refsect1> - - <refsect1 id="R1-SQL-CREATEINDEX-3"> - <title> - Compatibility - </title> - - <refsect2 id="R2-SQL-CREATEINDEX-4"> - <refsect2info> - <date>1998-09-09</date> - </refsect2info> - <title> - SQL92 - </title> - <para> - CREATE INDEX is a <productname>PostgreSQL</productname> language extension. - </para> - <para> - There is no <command>CREATE INDEX</command> command in SQL92. - </para> - </refsect2> - </refsect1> -</refentry> - -<!-- Keep this comment at the end of the file -Local variables: -mode: sgml -sgml-omittag:nil -sgml-shorttag:t -sgml-minimize-attributes:nil -sgml-always-quote-attributes:t -sgml-indent-step:1 -sgml-indent-data:t -sgml-parent-document:nil -sgml-default-dtd-file:"../reference.ced" -sgml-exposed-tags:nil -sgml-local-catalogs:"/usr/lib/sgml/catalog" -sgml-local-ecat-files:nil -End: ---> |