Use hash function to improve query speed in Oracle

oracle| Function | speed

When data is stored in a normal table, the records are physically saved to the allocated block in the order they were inserted into the database. For example, if you have a table for storing employee information, the employee names will be stored in the table in the order they were inserted into the table.

If the employee records a lot, the response time of the datasheet slows down. You can increase the speed of a query by selecting a column with a relative distribution of values (such as the employee's department number) and creating a cluster table.

In a clustered table, if employees belong to the same department, their records are physically stored in the same series of blocks. This can improve the speed of finding employee information because the number of database blocks that need to be read is reduced when retrieving employees in a particular department. Finding employees in a non-clustered table may require access to each database block.

When there are a large number of key values in the table, you begin to see performance problems that result from the existence of many cluster blocks. One way to avoid this problem is to use a hash function to constrain the number of cluster blocks. The hash function will give a value to limit the estimated range of cluster blocks, but the resulting values are relative distributions. For example, you can create a hash function that compares only the last two digits of the department number.

One problem with the hash function is that the value of the function disrupts the order in which it was originally recorded. You can solve the problem by an order by, but in many cases the number of records is very large. In Oracle 10g, you can define a data as "natural order," so you can solve the problem by not sorting and retrieving hash data in the sequence you want.

For example, suppose you have a database of credit card business. You decide to use the credit card number as a cluster primary key will benefit the data storage distribution. However, because of the large number of credit card numbers, you can use a hash function to constrain the number of cluster blocks. And you want the data in most of your reports to be in chronological order, then use the sort hash cluster for each query operation, instead of using order by. The relevant statements are given below:

Create cluster credit_cluster
(
card_no varchar2 (),
TransDate Date so　　RT
)
Hashkeys 10000 hash is Ora_hash (card_no)
size 256;


CREATE TABLE Credit_orders
(
card_no varchar2 (),
transdate date,
Amount number
)
cluster Credit_cluster (card_no,transdate);　　


Alter session set Nls_date_format = "Yyyymmddhh24miss";
　　Insert into Credit_orders (card_no,transdate,amount)
values (' 4111111111111111 ', ' 20050131000123 ', 57.99);
　　Insert into Credit_orders (card_no,transdate,amount)
values (' 4111111111111111 ', ' 20050130071216 ', 16.59);
　　Insert into Credit_orders (card_no,transdate,amount)
values (' 4111111111111111 ', ' 20050131111111 ', 39.00);
Insert into Credit_orders (card_no,transdate,amount)
values (' 4111111111111111 ', ' 20050130081001 ', 25.16);
&NBSP

　　
You can see I'm using a new function Ora_hash here to create a hash value for the credit card. Now, you can simply query a credit card data and return the results of the automatic sorting.