Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Volgen 1
Ster 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branch: master
Branches Labels
${ item.name }
Maak branch ${ searchTerm }
van 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/indexes-opclass.html

102 lines
8.0KB
Ruw Permalink Blame Geschiedenis 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>11.10. Operator Classes and Operator Families</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="indexes-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes" /><link rel="next" href="indexes-collations.html" title="11.11. Indexes and Collations" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">11.10. Operator Classes and Operator Families</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="indexes-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="indexes.html" title="Chapter 11. Indexes">Up</a></td><th width="60%" align="center">Chapter 11. Indexes</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="indexes-collations.html" title="11.11. Indexes and Collations">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="INDEXES-OPCLASS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">11.10. Operator Classes and Operator Families</h2></div></div></div><a id="id-1.5.10.13.2" class="indexterm"></a><a id="id-1.5.10.13.3" class="indexterm"></a><p>

  3.    An index definition can specify an <em class="firstterm">operator

  4.    class</em> for each column of an index.

  5. </p><pre class="synopsis">

  6. CREATE INDEX <em class="replaceable"><code>name</code></em> ON <em class="replaceable"><code>table</code></em> (<em class="replaceable"><code>column</code></em> <em class="replaceable"><code>opclass</code></em> [<span class="optional"><em class="replaceable"><code>sort options</code></em></span>] [<span class="optional">, ...</span>]);

  7. </pre><p>

  8.    The operator class identifies the operators to be used by the index

  9.    for that column.  For example, a B-tree index on the type <code class="type">int4</code>

  10.    would use the <code class="literal">int4_ops</code> class; this operator

  11.    class includes comparison functions for values of type <code class="type">int4</code>.

  12.    In practice the default operator class for the column's data type is

  13.    usually sufficient.  The main reason for having operator classes is

  14.    that for some data types, there could be more than one meaningful

  15.    index behavior.  For example, we might want to sort a complex-number data

  16.    type either by absolute value or by real part.  We could do this by

  17.    defining two operator classes for the data type and then selecting

  18.    the proper class when making an index.  The operator class determines

  19.    the basic sort ordering (which can then be modified by adding sort options

  20.    <code class="literal">COLLATE</code>,

  21.    <code class="literal">ASC</code>/<code class="literal">DESC</code> and/or

  22.    <code class="literal">NULLS FIRST</code>/<code class="literal">NULLS LAST</code>).

  23.   </p><p>

  24.    There are also some built-in operator classes besides the default ones:

  25. 

  26.    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  27.       The operator classes <code class="literal">text_pattern_ops</code>,

  28.       <code class="literal">varchar_pattern_ops</code>, and

  29.       <code class="literal">bpchar_pattern_ops</code> support B-tree indexes on

  30.       the types <code class="type">text</code>, <code class="type">varchar</code>, and

  31.       <code class="type">char</code> respectively.  The

  32.       difference from the default operator classes is that the values

  33.       are compared strictly character by character rather than

  34.       according to the locale-specific collation rules.  This makes

  35.       these operator classes suitable for use by queries involving

  36.       pattern matching expressions (<code class="literal">LIKE</code> or POSIX

  37.       regular expressions) when the database does not use the standard

  38.       <span class="quote">“<span class="quote">C</span>”</span> locale.  As an example, you might index a

  39.       <code class="type">varchar</code> column like this:

  40. </p><pre class="programlisting">

  41. CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

  42. </pre><p>

  43.       Note that you should also create an index with the default operator

  44.       class if you want queries involving ordinary <code class="literal">&lt;</code>,

  45.       <code class="literal">&lt;=</code>, <code class="literal">&gt;</code>, or <code class="literal">&gt;=</code> comparisons

  46.       to use an index.  Such queries cannot use the

  47.       <code class="literal"><em class="replaceable"><code>xxx</code></em>_pattern_ops</code>

  48.       operator classes.  (Ordinary equality comparisons can use these

  49.       operator classes, however.)  It is possible to create multiple

  50.       indexes on the same column with different operator classes.

  51.       If you do use the C locale, you do not need the

  52.       <code class="literal"><em class="replaceable"><code>xxx</code></em>_pattern_ops</code>

  53.       operator classes, because an index with the default operator class

  54.       is usable for pattern-matching queries in the C locale.

  55.      </p></li></ul></div><p>

  56.   </p><p>

  57.     The following query shows all defined operator classes:

  58. 

  59. </p><pre class="programlisting">

  60. SELECT am.amname AS index_method,

  61.        opc.opcname AS opclass_name,

  62.        opc.opcintype::regtype AS indexed_type,

  63.        opc.opcdefault AS is_default

  64.     FROM pg_am am, pg_opclass opc

  65.     WHERE opc.opcmethod = am.oid

  66.     ORDER BY index_method, opclass_name;

  67. </pre><p>

  68.   </p><p>

  69.    An operator class is actually just a subset of a larger structure called an

  70.    <em class="firstterm">operator family</em>.  In cases where several data types have

  71.    similar behaviors, it is frequently useful to define cross-data-type

  72.    operators and allow these to work with indexes.  To do this, the operator

  73.    classes for each of the types must be grouped into the same operator

  74.    family.  The cross-type operators are members of the family, but are not

  75.    associated with any single class within the family.

  76.   </p><p>

  77.     This expanded version of the previous query shows the operator family

  78.     each operator class belongs to:

  79. </p><pre class="programlisting">

  80. SELECT am.amname AS index_method,

  81.        opc.opcname AS opclass_name,

  82.        opf.opfname AS opfamily_name,

  83.        opc.opcintype::regtype AS indexed_type,

  84.        opc.opcdefault AS is_default

  85.     FROM pg_am am, pg_opclass opc, pg_opfamily opf

  86.     WHERE opc.opcmethod = am.oid AND

  87.           opc.opcfamily = opf.oid

  88.     ORDER BY index_method, opclass_name;

  89. </pre><p>

  90.   </p><p>

  91.     This query shows all defined operator families and all

  92.     the operators included in each family:

  93. </p><pre class="programlisting">

  94. SELECT am.amname AS index_method,

  95.        opf.opfname AS opfamily_name,

  96.        amop.amopopr::regoperator AS opfamily_operator

  97.     FROM pg_am am, pg_opfamily opf, pg_amop amop

  98.     WHERE opf.opfmethod = am.oid AND

  99.           amop.amopfamily = opf.oid

  100.     ORDER BY index_method, opfamily_name, opfamily_operator;

  101. </pre><p>

  102.   </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="indexes-index-only-scans.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="indexes.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="indexes-collations.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">11.9. Index-Only Scans and Covering Indexes </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 11.11. Indexes and Collations</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1