RedHat 5.2

GNU/Linux	RedHat 5.2 (Apollo)
	create_index(l)

CREATE INDEX

NAME
SYNOPSIS
DESCRIPTION
EXAMPLES

NAME

create index - construct a secondary index

SYNOPSIS

create [unique] index index-name

		on classname [using am-name]
		( attname [type_class], ... )

create [unique] index index-name

	on classname [using am-name]
	( funcname ( attname−1 { , attname−i } ) type_class )

DESCRIPTION

This command constructs an index called index-name.

Am-name is the name of the access method which is used for the index. The default access method is btree.

In the first syntax shown above, the key fields for the index are specified as attribute names. It 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 int4_ops class; this operator class includes comparison functions for four-byte integers. The default operator class is the appropriate operator class for that field type.

Note: currently, only btree access method supports multi-attribute indices. Up to 7 keys may be specified.

In the second syntax shown above, an index can be defined on the result of a user-defined function funcname 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 be applied to the base data. For example, say you have an attribute in class “myclass” called “pt” that consists of a 2D point type. Now, suppose that you would like to index this attribute but you only have index operator classes for 2D polygon types. You can define an index on the point attribute using a function that you write (call it “point_to_polygon”) and your existing polygon operator class; after that, queries using existing polygon operators that reference “point_to_polygon(myclass.pt)” on one side will use the precomputed polygons stored in the functional index instead of computing a polygon for each and every instance in “myclass” and then comparing it to the value on the other side of the operator. Obviously, the decision to build a functional index represents a tradeoff between space (for the index) and execution time.

The unique keyword 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.

Postgres provides btree, rtree and hash access methods for secondary indices. The btree access method is an implementation of the Lehman-Yao high-concurrency btrees. The rtree access method implements standard rtrees 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).

This list was generated from the Postgres system catalogs with the query:

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;

The int24_ops operator class is useful for constructing indices on int2 data, and doing comparisons against int4 data in query qualifications. Similarly, int42_ops support indices on int4 data that is to be compared against int2 data in queries.

The operator classes oidint2_ops, oidint4_ops, and oidchar16_ops represent the use of functional indices to simulate multi-key indices. These are no longer needed now that multi-key indexes are supported.

The Postgres query optimizer will consider using btree indices in a scan whenever an indexed attribute is involved in a comparison using one of:

< <= = >= >

Both box classes support indices on the “box” datatype in Postgres. 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. If the field on which your rectangles lie is about 20,000 units square or larger, you should use bigbox_ops. The poly_ops operator class supports rtree indices on “polygon” data.

The Postgres query optimizer will consider using an rtree index whenever an indexed attribute is involved in a comparison using one of:

<< &< &> >> @ ~= &&

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

EXAMPLES

--
--Create a btree index on the emp class using the age attribute.
--
create index empindex on emp using btree (age int4_ops)
--
--Create a btree index on employee name.
--
create index empname

on emp using btree (name char16_ops)

--
--Create an rtree index on the bounding rectangle of cities.
--
create index cityrect

on city using rtree (boundbox box_ops)

--
--Create a rtree index on a point attribute such that we
--can efficiently use box operators on the result of the
--conversion function. Such a qualification might look
--like "where point2box(points.pointloc) = boxes.box".
--
create index pointloc

on points using rtree (point2box(location) box_ops)

create_index(l)

GNU/Linux man pages

Livre :
Expressions régulières,
Syntaxe et mise en oeuvre :

ISBN : 978-2-7460-9712-4
EAN : 9782746097124
(Editions ENI)

GNU/Linux

create_index(l)

CREATE INDEX

NAME

SYNOPSIS

DESCRIPTION

EXAMPLES

GNU/Linux man pages

Livre :Expressions régulières,Syntaxe et mise en oeuvre :

ISBN : 978-2-7460-9712-4 EAN : 9782746097124 (Editions ENI)

GNU/Linux

RedHat 5.2

create_index(l)

CREATE INDEX

NAME

SYNOPSIS

DESCRIPTION

EXAMPLES

Livre :
Expressions régulières,
Syntaxe et mise en oeuvre :

ISBN : 978-2-7460-9712-4
EAN : 9782746097124
(Editions ENI)