create_index man page on UnixWare

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


       CREATE INDEX - define a new index

       CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
	   ( { column | ( expression ) } [ opclass ] [, ...] )
	   [ WITH ( storage_parameter = value [, ... ] ) ]
	   [ TABLESPACE tablespace ]
	   [ WHERE predicate ]

       CREATE  INDEX  constructs  an  index index_name on the specified table.
       Indexes are primarily used  to  enhance	database  performance  (though
       inappropriate use can result in slower performance).

       The key field(s) for the index are specified as column names, or alter‐
       natively as expressions written in parentheses.	Multiple fields can be
       specified if the index method supports multicolumn indexes.

       An  index field can be an expression computed from the values of one or
       more columns of the table row. This feature can be used to obtain  fast
       access  to  data	 based	on  some transformation of the basic data. For
       example, an index computed on upper(col) would allow the	 clause	 WHERE
       upper(col) = 'JIM' to use an index.

       PostgreSQL  provides  the  index	 methods  B-tree, hash, GiST, and GIN.
       Users can also define their own index methods, but that is fairly  com‐

       When  the  WHERE clause is present, a partial index is created.	A par‐
       tial index is an index that contains entries for only a	portion	 of  a
       table, usually a portion that is more useful for indexing 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. See in the documentation for more

       The expression used in the WHERE clause may refer only  to  columns  of
       the  underlying	table,	but  it can use all columns, not just the ones
       being indexed. Presently, subqueries and aggregate expressions are also
       forbidden  in  WHERE.  The same restrictions apply to index fields that
       are expressions.

       All functions and  operators  used  in  an  index  definition  must  be
       ``immutable'',  that  is, their results must depend only on their argu‐
       ments 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 expression or WHERE clause, remember to mark the function
       immutable when you create it.

       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.

	      When  this option is used, PostgreSQL will build the index with‐
	      out taking any locks that prevent concurrent  inserts,  updates,
	      or  deletes  on  the table; whereas a standard index build locks
	      out writes (but not reads) on the table until it's done.	 There
	      are  several caveats to be aware of when using this option — see
	      Building Indexes Concurrently [create_index(5)].

       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.

       method The name of the index method to  be  used.  Choices  are	btree,
	      hash, gist, and gin. The default method is btree.

       column The name of a column of the table.

	      An  expression  based  on	 one or more columns of the table. The
	      expression usually must be written with surrounding parentheses,
	      as  shown in the syntax. However, the parentheses may be omitted
	      if the expression has the form of a function call.

	      The name of an operator class. See below for details.

	      The name of  an  index-method-specific  storage  parameter.  See
	      below for details.

	      The  tablespace  in which to create the index. If not specified,
	      default_tablespace is used, or the database's default tablespace
	      if default_tablespace is an empty string.

	      The constraint expression for a partial index.

       The  WITH clause can specify storage parameters for indexes. Each index
       method can have its own set of allowed storage parameters. The built-in
       index methods all accept a single parameter:

	      The  fillfactor for an index is a percentage that determines how
	      full the index method will try to pack index pages. For B-trees,
	      leaf  pages  are	filled to this percentage during initial index
	      build, and also when extending the index at the  right  (largest
	      key  values). If pages subsequently become completely full, they
	      will be split, leading to gradual	 degradation  in  the  index's
	      efficiency.  B-trees  use	 a  default  fillfactor of 90, but any
	      value from 10 to 100 can be selected.  If the  table  is	static
	      then  fillfactor	100  is	 best to minimize the index's physical
	      size, but for heavily updated tables  a  smaller	fillfactor  is
	      better  to  minimize  the	 need for page splits. The other index
	      methods use fillfactor in different but roughly analogous	 ways;
	      the default fillfactor varies between methods.

       Creating	 an  index can interfere with regular operation of a database.
       Normally PostgreSQL locks the table to be indexed  against  writes  and
       performs	 the entire index build with a single scan of the table. Other
       transactions can still read the table,  but  if	they  try  to  insert,
       update,	or  delete  rows  in the table they will block until the index
       build is finished. This could have a severe effect if the system	 is  a
       live  production	 database.  Large  tables  can	take  many hours to be
       indexed, and even for smaller tables, an index build can lock out writ‐
       ers for periods that are unacceptably long for a production system.

       PostgreSQL  supports  building indexes without locking out writes. This
       method is invoked by  specifying	 the  CONCURRENTLY  option  of	CREATE
       INDEX.	When this option is used, PostgreSQL must perform two scans of
       the table, and in addition it must wait for all	existing  transactions
       to terminate. Thus this method requires more total work than a standard
       index build and takes significantly longer to complete. However,	 since
       it  allows normal operations to continue while the index is built, this
       method is useful for adding new indexes in a production environment. Of
       course,	the  extra  CPU and I/O load imposed by the index creation may
       slow other operations.

       If a problem arises during the second scan of  the  table,  such	 as  a
       uniqueness  violation  in a unique index, the CREATE INDEX command will
       fail but leave behind an ``invalid'' index. This index will be  ignored
       for  querying  purposes	because	 it may be incomplete; however it will
       still consume update overhead. The recommended recovery method in  such
       cases  is  to drop the index and try again to perform CREATE INDEX CON‐
       CURRENTLY. (Another possibility is to rebuild the index	with  REINDEX.
       However,	 since REINDEX does not support concurrent builds, this option
       is unlikely to seem attractive.)

       Another caveat when building a unique index concurrently	 is  that  the
       uniqueness  constraint is already being enforced against other transac‐
       tions when the second table scan begins.	 This  means  that  constraint
       violations could be reported in other queries prior to the index becom‐
       ing available for use, or even in cases where the index	build  eventu‐
       ally  fails.  Also,  if	a  failure  does occur in the second scan, the
       ``invalid'' index continues to enforce its uniqueness constraint after‐

       Concurrent  builds  of  expression indexes and partial indexes are sup‐
       ported.	Errors occurring in the evaluation of these expressions	 could
       cause  behavior	similar	 to that described above for unique constraint

       Regular index builds permit other regular index builds on the same  ta‐
       ble to occur in parallel, but only one concurrent index build can occur
       on a table at a time. In both cases, no other types of schema modifica‐
       tion  on	 the table are allowed meanwhile. Another difference is that a
       regular CREATE INDEX command can	 be  performed	within	a  transaction
       block, but CREATE INDEX CONCURRENTLY cannot.

       See  in	the  documentation  for	 information about when indexes can be
       used, when they are not used, and in which particular  situations  they
       can be useful.

       Currently,  only	 the B-tree and GiST index methods support multicolumn
       indexes. Up to 32 fields 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 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 func‐
       tions for four-byte integers. In practice the  default  operator	 class
       for  the	 column'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. More  information
       about  operator	classes is in in the documentation and in in the docu‐

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

       Indexes are not used for IS NULL clauses by default.  The best  way  to
       use indexes in such cases is to create a partial index using an IS NULL

       Prior releases of PostgreSQL also had  an  R-tree  index	 method.  This
       method  has  been removed because it had no significant advantages over
       the GiST method.	 If USING rtree is specified, CREATE INDEX will inter‐
       pret it as USING gist, to simplify conversion of old databases to GiST.

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

       CREATE UNIQUE INDEX title_idx ON films (title);

       To  create  an index on the expression lower(title), allowing efficient
       case-insensitive searches:

       CREATE INDEX lower_title_idx ON films ((lower(title)));

       To create an index with non-default fill factor:

       CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

       To create an index on the column code in the table films and  have  the
       index reside in the tablespace indexspace:

       CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;

       To create an index without locking out writes to the table:

       CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

       CREATE  INDEX  is  a PostgreSQL language extension. There are no provi‐
       sions for indexes in the SQL standard.

       ALTER INDEX [alter_index(5)], DROP INDEX [drop_index(l)]

SQL - Language Statements	  2008-01-03			CREATE INDEX()
                             _         _         _ 
                            | |       | |       | |     
                            | |       | |       | |     
                         __ | | __ __ | | __ __ | | __  
                         \ \| |/ / \ \| |/ / \ \| |/ /  
                          \ \ / /   \ \ / /   \ \ / /   
                           \   /     \   /     \   /    
                            \_/       \_/       \_/ 
More information is available in HTML format for server UnixWare

List of man pages available for UnixWare

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]
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