Point of sale : Open Source porject list

https://github.com/topics/point-of-sale

Database : Single-column index vs. multicolumn index

The two types of indexes are single-column indexes and multicolumn indexes. A single-column index is an index based on the values in one column of a table. A multicolumn index is an index based on the values in multiple columns of a table.

Organization of this subsection

(1) Creating a single-column index

(2) Creating a multicolumn index

(1) Creating a single-column index

A single-column index should be created when retrieval will be executed using one column only as the key.

(2) Creating a multicolumn index

A multicolumn index should be created in the cases discussed below.

(a) Retrieval of data that satisfies multiple conditions

A multicolumn index should be created when data satisfying multiple conditions is to be retrieved, such as when a complex-condition retrieval using the AND operator with multiple columns as the key is executed.

http://itdoc.hitachi.co.jp/manuals/3020/3020635200e/W3520279.HTM#ID00988

Database : Guidelines for Application-Specific Indexes

You can create indexes on columns to speed up queries. Indexes provide faster access to data for operations that return a small portion of a table's rows.

In general, you should create an index on a column in any of the following situations:

• The column is queried frequently.
• A referential integrity constraint exists on the column.
• A UNIQUE key integrity constraint exists on the column.

You can create an index on any column; however, if the column is not used in any of these situations, creating an index on the column does not increase performance and the index takes up resources unnecessarily.

Although the database creates an index for you on a column with an integrity constraint, explicitly creating an index on such a column is recommended.

You can use the following techniques to determine which columns are best candidates for indexing:

• Use the EXPLAIN PLAN feature to show a theoretical execution plan of a given query statement.
• Use the V$SQL_PLAN view to determine the actual execution plan used for a given query statement.

Sometimes, if an index is not being used by default and it would be most efficient to use that index, you can use a query hint so that the index is used.

https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_in.htm