diff options
Diffstat (limited to 'doc/src/sgml/ref/create_index.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_index.sgml | 415 |
1 files changed, 201 insertions, 214 deletions
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 1629a600547..860a793c6c2 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -1,133 +1,124 @@ -<REFENTRY ID="SQL-CREATEINDEX"> - <REFMETA> - <REFENTRYTITLE> +<refentry id="SQL-CREATEINDEX"> + <refmeta> + <refentrytitle> CREATE INDEX - </REFENTRYTITLE> - <REFMISCINFO>SQL - Language Statements</REFMISCINFO> - </REFMETA> - <REFNAMEDIV> - <REFNAME> + </refentrytitle> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + <refnamediv> + <refname> CREATE INDEX - </REFNAME> - <REFPURPOSE> + </refname> + <refpurpose> Constructs a secondary index - </REFPURPOSE> + </refpurpose> </refnamediv> - <REFSYNOPSISDIV> - <REFSYNOPSISDIVINFO> - <DATE>1998-09-09</DATE> - </REFSYNOPSISDIVINFO> - <SYNOPSIS> + <refsynopsisdiv> + <refsynopsisdivinfo> + <date>1998-09-09</date> + </refsynopsisdivinfo> + <synopsis> CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable> [ USING <replaceable class="parameter">acc_name</replaceable> ] ( <replaceable class="parameter">column</replaceable> [ <replaceable class="parameter">ops_name</replaceable>] [, ...] ) CREATE [ UNIQUE ] INDEX <replaceable class="parameter">index_name</replaceable> ON <replaceable class="parameter">table</replaceable> [ USING <replaceable class="parameter">acc_name</replaceable> ] - ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">column</replaceable> [, ... ]) <replaceable class="parameter">ops_name</replaceable> ) - </SYNOPSIS> - - <REFSECT2 ID="R2-SQL-CREATEINDEX-1"> - <REFSECT2INFO> - <DATE>1998-09-09</DATE> - </REFSECT2INFO> - <TITLE> + ( <replaceable class="parameter">func_name</replaceable>( <replaceable class="parameter">r">col</replaceable>le> [, ... ]) <replaceable class="parameter">ops_name</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 non-duplicate data 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. - </PARA> - </LISTITEM> - </VARLISTENTRY> - <VARLISTENTRY> - <TERM> - <replaceable class="parameter">table</replaceable> - </TERM> - <LISTITEM> - <PARA> - The name of the table to be indexed. - </PARA> - </LISTITEM> - </VARLISTENTRY> - <VARLISTENTRY> - <TERM> - <replaceable class="parameter">acc_name</replaceable> - </TERM> - <LISTITEM> - <PARA> - the name of the access method which is to be used for - the index. The default access method is BTREE. - Postgres provides three access methods for secondary indexes: - <variablelist> - <varlistentry> - <term>BTREE</term> - <listitem> - <para> - an implementation of the Lehman-Yao - high-concurrency btrees. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>RTREE</term> - <listitem> - <para>implements standard rtrees using Guttman's - quadratic split algorithm. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term>HASH</term> - <listitem> - <para> - an implementation of Litwin's linear hashing. - </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. - The following select list returns all ops_names: - -<programlisting> + </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 non-duplicate data 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. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="parameter">table</replaceable></term> + <listitem> + <para> + The name of the table to be indexed. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="parameter">acc_name</replaceable></term> + <listitem> + <para> + the name of the access method which is to be used for + the index. The default access method is BTREE. + Postgres provides three access methods for secondary indexes: + <variablelist> + <varlistentry> + <term>BTREE</term> + <listitem> + <para> + an implementation of the Lehman-Yao + high-concurrency btrees. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>RTREE</term> + <listitem> + <para>implements standard rtrees using Guttman's + quadratic split algorithm. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>HASH</term> + <listitem> + <para> + an implementation of Litwin's linear hashing. + </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. + The following select list returns all ops_names: + + <programlisting> SELECT am.amname AS acc_name, opc.opcname AS ops_name, opr.oprname AS ops_comp @@ -137,75 +128,72 @@ SELECT am.amname AS acc_name, amop.amopclaid = opc.oid AND amop.amopopr = opr.oid ORDER BY acc_name, ops_name, ops_comp -</programlisting> - - </PARA> - </LISTITEM> - </VARLISTENTRY> - <VARLISTENTRY> - <TERM> - <replaceable class="parameter">func_name</replaceable> - </TERM> - <LISTITEM> - <PARA> + </programlisting> + </para> + </listitem> + </varlistentry> + <varlistentry> + <term><replaceable class="parameter">func_name</replaceable></term> + <listitem> + <para> A user-defined function, which returns a value that can be indexed. - </PARA> - </LISTITEM> - </VARLISTENTRY> + </para> + </listitem> + </varlistentry> </variablelist> </para> - </REFSECT2> - - <REFSECT2 ID="R2-SQL-CREATEINDEX-2"> - <REFSECT2INFO> - <DATE>1998-09-09</DATE> - </REFSECT2INFO> - <TITLE> + </refsect2> + + <refsect2 id="R2-SQL-CREATEINDEX-2"> + <refsect2info> + <date>1998-09-09</date> + </refsect2info> + <title> Outputs - </TITLE> - <PARA> + </title> + <para> - <VARIABLELIST> - <VARLISTENTRY> - <TERM> - <ReturnValue>CREATE</ReturnValue> - </TERM> - <LISTITEM> - <PARA> - The message returned if the index is successfully created. - </PARA> - </LISTITEM> - </VARLISTENTRY> + <variablelist> + <varlistentry> + <term><computeroutput> +CREATE + </computeroutput></term> + <listitem> + <para> + The message returned if the index is successfully created. + </para> + </listitem> + </varlistentry> - <VARLISTENTRY> - <TERM> - <ReturnValue>ERROR: Cannot create index: 'index_name' already exists.</ReturnValue> - </TERM> - <LISTITEM> - <PARA> + <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> + </refsect2> + </refsynopsisdiv> + + <refsect1 id="R1-SQL-CREATEINDEX-1"> + <refsect1info> + <date>1998-09-09</date> + </refsect1info> + <title> Description - </TITLE> - <PARA> + </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. @@ -225,45 +213,45 @@ SELECT am.amname AS acc_name, operator class is the appropriate operator class for that field type. </para> - <para> - In the second syntax, an index is defined - on the result of a user-defined function - <replaceable class="parameter">func_name</replaceable> applied - to one or more attributes of a single class. These functional - indexes 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. - </para> - - <REFSECT2 ID="R2-SQL-CREATEINDEX-3"> - <REFSECT2INFO> - <DATE>1998-09-09</DATE> - </REFSECT2INFO> - <TITLE> + <para> + In the second syntax, an index is defined + on the result of a user-defined function + <replaceable class="parameter">func_name</replaceable> applied + to one or more attributes of a single class. These functional + indexes 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. + </para> + + <refsect2 id="R2-SQL-CREATEINDEX-3"> + <refsect2info> + <date>1998-09-09</date> + </refsect2info> + <title> Notes - </TITLE> - <PARA> + </title> + <para> Currently, only the BTREE access method supports multi-column indexes. Up to 7 keys may be specified. - </PARA> + </para> <para> Use <command>DROP INDEX</command> to remove an index. </para> - </REFSECT2> + </refsect2> </refsect1> - <REFSECT1 ID="R1-SQL-CREATEINDEX-2"> - <TITLE> + <refsect1 id="R1-SQL-CREATEINDEX-2"> + <title> Usage - </TITLE> - <PARA>To create a btree index on the field <literal>title</literal> + </title> + <para>To create a btree index on the field <literal>title</literal> in the table <literal>films</literal>: - </PARA> - <ProgramListing> + </para> + <programlisting> CREATE UNIQUE INDEX title_idx ON films (title); - </ProgramListing> + </programlisting> <!-- <comment> @@ -282,37 +270,36 @@ SELECT * FROM points </programlisting> --> - </REFSECT1> + </refsect1> - <REFSECT1 ID="R1-SQL-CREATEINDEX-3"> - <TITLE> + <refsect1 id="R1-SQL-CREATEINDEX-3"> + <title> Compatibility - </TITLE> - <PARA> - </PARA> + </title> + <para> + </para> - <REFSECT2 ID="R2-SQL-CREATEINDEX-4"> - <REFSECT2INFO> - <DATE>1998-09-09</DATE> - </REFSECT2INFO> - <TITLE> + <refsect2 id="R2-SQL-CREATEINDEX-4"> + <refsect2info> + <date>1998-09-09</date> + </refsect2info> + <title> SQL92 - </TITLE> - <PARA> + </title> + <para> CREATE INDEX is a <productname>Postgres</productname> language extension. - </PARA> + </para> <para> There is no <command>CREATE INDEX</command> command in SQL92. </para> </refsect2> </refsect1> -</REFENTRY> - +</refentry> <!-- Keep this comment at the end of the file Local variables: mode: sgml -sgml-omittag:t +sgml-omittag:nil sgml-shorttag:t sgml-minimize-attributes:nil sgml-always-quote-attributes:t |