create_index man page on IRIX

Man page or keyword search:  
man Server   31559 pages
apropos Keyword Search (all sections)
Output format
IRIX logo
[printable version]



     CREATE INDEX(lSQL - Language Statements (2002-11-2CREATE INDEX(l)

     NAME
	  CREATE INDEX - define a new index

     SYNOPSIS
	  CREATE [ UNIQUE ] INDEX index_name ON table
	      [ USING acc_method ] ( column [ ops_name ] [, ...] )
	      [ WHERE predicate ]
	  CREATE [ UNIQUE ] INDEX index_name ON table
	      [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
	      [ WHERE predicate ]

	INPUTS
	  UNIQUE
	       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.

	  index_name
	       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.

	  table
	       The name (possibly schema-qualified) of the table to be
	       indexed.

	  acc_method
	       The name of the access method to be used for the index.
	       The default access method is BTREE.  PostgreSQL
	       provides four access methods for indexes:

	       BTREE
		    an implementation of Lehman-Yao high-concurrency
		    B-trees.

	       RTREE
		    implements standard R-trees using Guttman's
		    quadratic split algorithm.

	       HASH an implementation of Litwin's linear hashing.

	       GIST Generalized Index Search Trees.

	  column
	       The name of a column of the table.

	  ops_name
	       An associated operator class. See below for details.

     Page 1					     (printed 3/24/03)

     CREATE INDEX(lSQL - Language Statements (2002-11-2CREATE INDEX(l)

	  func_name
	       A function, which returns a value that can be indexed.

	  predicate
	       Defines the constraint expression for a partial index.

	OUTPUTS
	  CREATE INDEX
	       The message returned if the index is successfully
	       created.

	  ERROR: Cannot create index: 'index_name' already exists.
	       This error occurs if it is impossible to create the
	       index.

     DESCRIPTION
	  CREATE INDEX constructs an index index_name on the specified
	  table.

	       Tip: Indexes are primarily used to enhance database
	       performance.  But inappropriate use will result in
	       slower performance.

	  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.

	  In the second syntax shown above, an index is defined on the
	  result of a user-specified function func_name applied to one
	  or more columns of a single table. 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. For example, a functional index on
	  upper(col) would allow the clause WHERE upper(col) = 'JIM'
	  to use an index.

	  PostgreSQL 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).

	  When the WHERE clause is present, a partial index is
	  created.  A partial index is an index that contains entries
	  for only a portion of a table, usually a portion that is

     Page 2					     (printed 3/24/03)

     CREATE INDEX(lSQL - Language Statements (2002-11-2CREATE INDEX(l)

	  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
	  WHERE with UNIQUE to enforce uniqueness over a subset of a
	  table.

	  The expression used in the WHERE clause may refer only to
	  columns of the underlying table (but it can use all columns,
	  not only the one(s) being indexed). Presently, subqueries
	  and aggregate expressions are also forbidden in WHERE.

	  All functions and operators used in an index definition must
	  be 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.

	  Use DROP INDEX [drop_index(l)] to remove an index.

	NOTES
	  The PostgreSQL query optimizer will consider using a B-tree
	  index whenever an indexed attribute is involved in a
	  comparison using one of:  <, <=, =, >=, >

	  The PostgreSQL query optimizer will consider using an R-tree
	  index whenever an indexed attribute is involved in a
	  comparison using one of:  <<, &<, &>, >>, @, ~=, &&

	  The PostgreSQL query optimizer will consider using a hash
	  index whenever an indexed attribute is involved in a
	  comparison using the = operator.

	  Testing has shown PostgreSQL's hash indexes to be similar or
	  slower than B-tree indexes, and the index size and build
	  time for hash indexes is much worse. Hash indexes also
	  suffer poor performance under high concurrency. For these
	  reasons, hash index use is discouraged.

	  Currently, only the B-tree and gist access methods support
	  multicolumn indexes. Up to 32 keys may be specified by
	  default (this limit can be altered when building
	  PostgreSQL). Only B-tree currently supports unique indexes.

	  An operator class can be specified for each column of an
	  index. The operator class identifies the operators to be

     Page 3					     (printed 3/24/03)

     CREATE INDEX(lSQL - Language Statements (2002-11-2CREATE INDEX(l)

	  used by the index for that column. For example, a B-tree
	  index on four-byte integers would use the int4_ops 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:

	  o The operator classes box_ops and bigbox_ops both support
	    R-tree indexes on the box data type.  The difference
	    between them is that bigbox_ops 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.)

	  The following query shows all defined operator classes:

	  SELECT am.amname AS acc_method,
		 opc.opcname AS ops_name
	      FROM pg_am am, pg_opclass opc
	      WHERE opc.opcamid = am.oid
	      ORDER BY acc_method, ops_name;

     USAGE
	  To create a B-tree index on the field title in the table
	  films:

	  CREATE UNIQUE INDEX title_idx
	      ON films (title);

     COMPATIBILITY
	SQL92
	  CREATE INDEX is a PostgreSQL language extension.

	  There is no CREATE INDEX command in SQL92.

     Page 4					     (printed 3/24/03)

[top]

List of man pages available for IRIX

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net