Special Features of Operator Classes

14.7. Special Features of Operator Classes

There are two special features of operator classes that we have not discussed yet, mainly because they are not very useful with the default B-tree index access method.

Normally, declaring an operator as a member of an operator class means that the index access method can retrieve exactly the set of rows that satisfy a WHERE condition using the operator. For example,

SELECT * FROM table WHERE integer_column < 4;

can be satisfied exactly by a B-tree index on the integer column. But there are cases where an index is useful as an inexact guide to the matching rows. For example, if an R-tree index stores only bounding boxes for objects, then it cannot exactly satisfy a WHERE condition that tests overlap between nonrectangular objects such as polygons. Yet we could use the index to find objects whose bounding box overlaps the bounding box of the target object, and then do the exact overlap test only on the objects found by the index. If this scenario applies, the index is said to be "lossy" for the operator, and we add RECHECK to the OPERATOR clause in the CREATE OPERATOR CLASS command. RECHECK is valid if the index is guaranteed to return all the required tuples, plus perhaps some additional tuples, which can be eliminated by performing the original operator comparison.

Consider again the situation where we are storing in the index only the bounding box of a complex object such as a polygon. In this case there's not much value in storing the whole polygon in the index entry --- we may as well store just a simpler object of type box. This situation is expressed by the STORAGE option in CREATE OPERATOR CLASS: we'd write something like

CREATE OPERATOR CLASS polygon_ops
    DEFAULT FOR TYPE polygon USING gist AS
        ...
        STORAGE box;

At present, only the GiST access method supports a STORAGE type that's different from the column data type. The GiST compress and decompress support routines must deal with data-type conversion when STORAGE is used.

Home
Online Resources
General
Beginner Tutorials
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Contact Us
Random quote of the moment:
If you have nothing to do, don't do it here.
 
http://www.sql.org/
 
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!