Use hash function to improve query speed in Oracle

Source: Internet
Author: User
Tags date hash insert query relative
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.



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.