Summary - Designing data warehouse performance — indexing

Lecture

Это окончание невероятной информации про повышение производительности запросов.

...

Suppose we have a hash cluster for the EMPLOYEE table, and the cluster key is defined as the "employee's home area code" ( home_area_code number ); it is then likely that many collisions will occur in the hash cluster if the city where the employees live is small. To avoid such collisions, you can override Oracle's built-in hash function in the CREATE CLUSTER command by adding a HASH IS clause:

CREATE CLUSTER personnel
 (home_area_code  number,
home_prefix     number )
HASHKEYS 20
HASH IS MOD(home_area_code + home_suffix_tel, 101);

In the example, a certain number is added to the home area code to change the distribution of hash key values in order to avoid collisions. The last two digits of the home phone number were used as that number.

In closing the discussion of clustering, let us note the following. Although Oracle makes heavy use of clusters for accessing its system tables, the authors recommend exercising caution when deciding whether to cluster tables when designing a new database. The performance gain may not be very large compared to other design decisions. Designing clusters is "custom" work. It is very useful to know usage statistics for a similar cluster from operating a similar data warehouse in order to build a high-performance cluster. The following rule of thumb should be kept in mind:

Up to 1000 records – a DBMS has no significant advantage over a sequential file.
From 1000 to 10,000 records – the advantage is insignificant.
From 10,000 to 100,000 records – there is no perceptible difference in performance between desktop and enterprise-class DBMSs.
From 100,000 to 1,000,000 records – enterprise-class DBMSs provide acceptable performance without special performance-enhancing techniques.
From 1,000,000 records – you need to start thinking about improving performance.

Summary

In this lecture we looked at three main methods for improving query performance that rely on mechanisms and objects built into the DBMS: indexes, partitions, and clusters.

The basic idea of indexing tables is to create special DBMS objects – indexes – to improve the performance of retrieving table rows in queries. As a rule, the DBMS creates indexes automatically for tables' primary keys.

When creating additional indexes, keep in mind that indexes require the DBMS to expend additional effort to support and maintain them, take up additional space in the file system, and are not always used by the query optimizer when processing a SELECT statement.

The basic idea of table partitioning is to use built-in DBMS commands to split large tables into a number of physical fragments according to some partitioning criterion, in order to reduce the volume of I/O when processing fragments. Partitioning is very often used when working with large-volume tables. If the database being designed is expected to contain large objects (more than 1 GB), the possibility of using partitioning techniques should definitely be considered.

Whether or not to use partitioning mainly depends on how large a table is or how large it may grow, how it is used, and how efficiently it responds to user queries and maintenance operations.

In general, it is worth partitioning a large table if the following two conditions hold:

the table contains (or may accumulate in the future) a large amount of data used in various ways;
queries or updates on the table do not perform as expected, or maintenance costs exceed the planned maintenance windows.

When splitting tables into partitions, keep in mind that partitions require the DBMS to expend additional effort to support and maintain them.

The basic idea of table clustering is to store columns from different tables that are used together in queries on shared physical pages of disk space within the file structure, in order to improve query processing performance.

However, it should be remembered that designing clusters is "custom" work. The performance gain may not be very large compared to other design decisions. To build a high-performance cluster for tables, it is useful to know usage statistics for a similar cluster from operating a similar data warehouse, and this is not always known.