diff options
Diffstat (limited to 'doc/src/sgml/ref/cluster.sgml')
| -rw-r--r-- | doc/src/sgml/ref/cluster.sgml | 246 |
1 files changed, 0 insertions, 246 deletions
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml deleted file mode 100644 index c8499ca0581..00000000000 --- a/doc/src/sgml/ref/cluster.sgml +++ /dev/null @@ -1,246 +0,0 @@ -<!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/cluster.sgml,v 1.16 2002/04/23 02:07:15 tgl Exp $ -PostgreSQL documentation ---> - -<refentry id="SQL-CLUSTER"> - <refmeta> - <refentrytitle id="sql-cluster-title">CLUSTER</refentrytitle> - <refmiscinfo>SQL - Language Statements</refmiscinfo> - </refmeta> - <refnamediv> - <refname> - CLUSTER - </refname> - <refpurpose> - cluster a table according to an index - </refpurpose> - </refnamediv> - <refsynopsisdiv> - <refsynopsisdivinfo> - <date>1999-07-20</date> - </refsynopsisdivinfo> - <synopsis> -CLUSTER <replaceable class="PARAMETER">indexname</replaceable> ON <replaceable class="PARAMETER">tablename</replaceable> - </synopsis> - - <refsect2 id="R2-SQL-CLUSTER-1"> - <refsect2info> - <date>1998-09-08</date> - </refsect2info> - <title> - Inputs - </title> - <para> - </para> - <variablelist> - <varlistentry> - <term><replaceable class="PARAMETER">indexname</replaceable></term> - <listitem> - <para> - The name of an index. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><replaceable class="PARAMETER">table</replaceable></term> - <listitem> - <para> - The name (possibly schema-qualified) of a table. - </para> - </listitem> - </varlistentry> - </variablelist> - </refsect2> - - <refsect2 id="R2-SQL-CLUSTER-2"> - <refsect2info> - <date>1998-09-08</date> - </refsect2info> - <title> - Outputs - </title> - <para> - - <variablelist> - <varlistentry> - <term><computeroutput> -CLUSTER - </computeroutput></term> - <listitem> - <para> - The clustering was done successfully. - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><computeroutput> -ERROR: relation <<replaceable class="PARAMETER">tablerelation_number</replaceable>> inherits "<replaceable class="PARAMETER">table</replaceable>" - </computeroutput></term> - <listitem> - <para> - <comment> - This is not documented anywhere. It seems not to be possible to - cluster a table that is inherited. - </comment> - </para> - </listitem> - </varlistentry> - <varlistentry> - <term><computeroutput> -ERROR: Relation <replaceable class="PARAMETER">table</replaceable> does not exist! - </computeroutput></term> - <listitem> - <para> - <comment> - The specified relation was not shown in the error message, - which contained a random string instead of the relation name. - </comment> - </para> - </listitem> - </varlistentry> - </variablelist> - </para> - </refsect2> - </refsynopsisdiv> - - <refsect1 id="R1-SQL-CLUSTER-1"> - <refsect1info> - <date>1998-09-08</date> - </refsect1info> - <title> - Description - </title> - <para> - <command>CLUSTER</command> instructs <productname>PostgreSQL</productname> - to cluster the table specified - by <replaceable class="parameter">table</replaceable> approximately - based on the index specified by - <replaceable class="parameter">indexname</replaceable>. The index must - already have been defined on - <replaceable class="parameter">tablename</replaceable>. - </para> - - <para> - When a table is clustered, it is physically reordered - based on the index information. The clustering is static. - In other words, as the table is updated, the changes are - not clustered. No attempt is made to keep new instances or - updated tuples clustered. If one wishes, one can - re-cluster manually by issuing the command again. - </para> - - <refsect2 id="R2-SQL-CLUSTER-3"> - <refsect2info> - <date>1998-09-08</date> - </refsect2info> - <title> - Notes - </title> - - <para> - The table is actually copied to a temporary table in index - order, then renamed back to the original name. For this - reason, all grant permissions and other indexes are lost - when clustering is performed. - </para> - - <para> - In cases where you are accessing single rows randomly - within a table, the actual order of the data in the heap - table is unimportant. However, if you tend to access some - data more than others, and there is an index that groups - them together, you will benefit from using <command>CLUSTER</command>. - </para> - - <para> - Another place where <command>CLUSTER</command> is helpful is in - cases where you use an - index to pull out several rows from a table. If you are - requesting a range of indexed values from a table, or a - single indexed value that has multiple rows that match, - <command>CLUSTER</command> will help because once the index identifies the - heap page for the first row that matches, all other rows - that match are probably already on the same heap page, - saving disk accesses and speeding up the query. - </para> - - <para> - There are two ways to cluster data. The first is with the - <command>CLUSTER</command> command, which reorders the original table with - the ordering of the index you specify. This can be slow - on large tables because the rows are fetched from the heap - in index order, and if the heap table is unordered, the - entries are on random pages, so there is one disk page - retrieved for every row moved. <productname>PostgreSQL</productname> has a cache, - but the majority of a big table will not fit in the cache. - </para> - - <para> - Another way to cluster data is to use - - <programlisting> -SELECT <replaceable class="parameter">columnlist</replaceable> INTO TABLE <replaceable class="parameter">newtable</replaceable> - FROM <replaceable class="parameter">table</replaceable> ORDER BY <replaceable class="parameter">columnlist</replaceable> - </programlisting> - - which uses the <productname>PostgreSQL</productname> sorting code in - the ORDER BY clause to match the index, and which is much faster for - unordered data. You then drop the old table, use - <command>ALTER TABLE...RENAME</command> - to rename <replaceable class="parameter">newtable</replaceable> to the old name, and - recreate the table's indexes. The only problem is that <acronym>OID</acronym>s - will not be preserved. From then on, <command>CLUSTER</command> should be - fast because most of the heap data has already been - ordered, and the existing index is used. - </para> - </refsect2> - </refsect1> - - <refsect1 id="R1-SQL-CLUSTER-2"> - <title> - Usage - </title> - <para> - Cluster the employees relation on the basis of its salary attribute: - </para> - <programlisting> -CLUSTER emp_ind ON emp; - </programlisting> - </refsect1> - - <refsect1 id="R1-SQL-CLUSTER-3"> - <title> - Compatibility - </title> - - <refsect2 id="R2-SQL-CLUSTER-4"> - <refsect2info> - <date>1998-09-08</date> - </refsect2info> - <title> - SQL92 - </title> - <para> - There is no <command>CLUSTER</command> statement 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: ---> |