Databases Reference
In-Depth Information
The reason for using such a cluster is simple: we can gain very fast performance in retrieving
data from this kind of storage structure, when we access data by cluster key value; in this
case, the I/O is better optimized than for regular hash clusters.
We have seen in the previous section that we can define a personalized hash function to use
in a hash cluster. Commonly, with single-table hash clusters it is very simple to define a hash
function based on the primary key value, which ensures that there will be no collisions at all.
Here follows an example of a single-table hash cluster:
CREATE CLUSTER SINGLE_CLUSTER (id NUMBER(6))
SIZE 8192 HASHKEYS 100
single table
hash is id;
CREATE TABLE SCL_EMPLOYEES
CLUSTER SINGLE_CLUSTER (employee_id) AS
SELECT * FROM EMPLOYEES;
Indexing the correct way
When a table grows in size, it's very difficult and time-consuming to find the data we need by
scanning the entire table data.
The well-known solution to this problem is indexing. We can build an index, which is a particular
storage structure, to identify quickly where data is stored in the table. In the real world, indexes
are often used, for example, in a topic like this, so we are accustomed to using them.
In this recipe, we will see how indexes work and when to use them, and we'll also avoid over-
indexing; we will introduce the B-tree indexes, and then look at other types of indexes and
more details on their use.
How to do it...
In this recipe, we will use the
CUSTOMERS
table of
SH
schema. There are more than 55000
rows in the table. We will create several indexes on this table, and after the creation of each
index we will execute the following queries (we will call them
TEST CASE
onwards):
SET AUTOT TRACE EXP
SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH,
CUST_EMAIL FROM CUSTOMERS WHERE CUST_LAST_NAME = 'WADE';
SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH,
CUST_EMAIL FROM CUSTOMERS WHERE CUST_LAST_NAME = 'Wade';
