diff options
Diffstat (limited to 'doc/src/sgml/ref/create_index.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_index.sgml | 330 |
1 files changed, 330 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml new file mode 100644 index 00000000000..36ecd53267e --- /dev/null +++ b/doc/src/sgml/ref/create_index.sgml @@ -0,0 +1,330 @@ +<REFENTRY ID="SQL-CREATEINDEX-1"> + <REFMETA> + <REFENTRYTITLE> + CREATE INDEX + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + CREATE INDEX + </REFNAME> + <REFPURPOSE> + Constructs a secondary index + </REFPURPOSE> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</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-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <function>UNIQUE</function> + </TERM> + <LISTITEM> + <PARA> + <function>UNIQUE</function> causes the system to check for + duplicate values 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 indices: + <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 + FROM pg_am am, pg_amop amop, + pg_opclass opc, pg_operator opr + WHERE amop.amopid = am.oid AND + 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> + A user-defined function, which returns a value that can + be indexed. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CREATEINDEX-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATE</ReturnValue> + </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> + This error occurs if it is impossible to create the index. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CREATEINDEX-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + This command constructs an index called <replaceable class="parameter">index_name</replaceable>. + </PARA> + <para> + In the first syntax shown above, the key fields for the + index are specified as column names; a column may also have + an associated operator class. An operator class is used + to specify the operators to be used for a particular + index. For example, a btree index on four-byte integers + would use the <literal>int4_ops</literal> class; + this operator class includes + comparison functions for four-byte integers. The default + 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 + indices 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-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Currently, only the BTREE access method supports multi-column + indices. Up to 7 keys may be specified. + </PARA> + <para> + Use the <citerefentry> + <refentrytitle>DROP INDEX</refentrytitle> + </citerefentry> + statement to remove indexes. + </para> + </REFSECT2> + + <REFSECT1 ID="R1-SQL-CREATEINDEX-2"> + <TITLE> + Usage + </TITLE> + <PARA>To create a btree index on the field <literal>title</literal> + in the table <literal>films</literal>: + </PARA> + <ProgramListing> + CREATE UNIQUE INDEX title_idx + ON films (title); + </ProgramListing> + <para> + To create a rtree 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; +<comment> +Is this example correct? +</comment> + </programlisting> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CREATEINDEX-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-CREATEINDEX-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + CREATE INDEX is a PostgreSQL language extension. + </PARA> + <para> + There is no CREATE INDEX command in SQL92. + </para> + </refsect2> + </refsect1> +</REFENTRY> + + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +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: +--> |