Partial index
   HOME

TheInfoList



OR:

In
databases In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and ana ...
, a partial index, also known as filtered index is an
index Index (: indexes or indices) may refer to: Arts, entertainment, and media Fictional entities * Index (''A Certain Magical Index''), a character in the light novel series ''A Certain Magical Index'' * The Index, an item on the Halo Array in the ...
which has some condition applied to it so that it includes a subset of rows in the
table Table may refer to: * Table (database), how the table data arrangement is used within the databases * Table (furniture), a piece of furniture with a flat surface and one or more legs * Table (information), a data arrangement with rows and column ...
. This allows the index to remain small, even though the table may be rather large, and have extreme selectivity. Suppose you have a transaction table where entries start out with STATUS = 'A' (active), and then may pass through other statuses ('P' for pending, 'W' for "being worked on") before reaching a final status, 'F', at which point it is no longer likely to be processed again. In PostgreSQL, a useful partial index might be defined as: create index partial_status on txn_table (status) where status in ('A', 'P', 'W'); This index would not bother storing any of the millions of rows that have reached "final" status, 'F', and would allow queries looking for transactions that still "need work" to efficiently search via this index. Similarly, a partial index can be used to index only those rows where a column is not null, which will be of benefit when the column usually is null. create index partial_object_update on object_table (updated_on) where updated_on is not null; This index would allow the following query to read only the updated tuples: select * from object_table where updated_on is not null order by updated_on; It is not necessary that the condition be the same as the index criterion; Stonebraker's paper below presents a number of examples with indexes similar to the following: create index partial_salary on employee(age) where salary > 2100;


Support

In SQL Server, this type of index is called a ''filtered index''. Partial indexes have been supported in
PostgreSQL PostgreSQL ( ) also known as Postgres, is a free and open-source software, free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transaction processing, transactions ...
since version 7.2, released in February 2002.
SQLite SQLite ( "S-Q-L-ite", "sequel-ite") is a free and open-source relational database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it ...
supports partial indexes since version 3.8.0.
MongoDB MongoDB is a source-available, cross-platform, document-oriented database program. Classified as a NoSQL database product, MongoDB uses JSON-like documents with optional database schema, schemas. Released in February 2009 by 10gen (now MongoDB ...
supports partial indexes since version 3.2.


References


External links


The Case For Partial Indexes
{{DEFAULTSORT:Partial Index Database management systems PostgreSQL