[Search for users] [Overall Top Noters] [List of all Conferences] [Download this site]

Conference orarep::nomahs::rdb_wish

Title:Oracle Rdb Wishes and Suggestions
Notice:Please READ note 1.0 before using WRITE or REPLY
Moderator:NOVA::SMITHI
Created:Fri Apr 07 1989
Last Modified:Mon Jun 02 1997
Last Successful Update:Fri Jun 06 1997
Number of topics:809
Total number of notes:4111

804.0. "Hash ordered groups" by svrav1.au.oracle.com::MBRADLEY (I was dropped on my head as a baby. What's your excuse?) Tue May 13 1997 23:30

I am working this week on a site that has a monotonicly increasing number 
as a key to a table of millions of rows. They need very fast store, delete 
and exact match retrieval from this table.

As it stands, it appears to be a good candidate for hash ordered index, 
however, the problem with hash ordered is that say my transactions stores 
half a dozen records, it would be great if I could contrive to have those 
adjacent key values stored on the same page.

Suppose that I knew I wanted to fit say five records per page, I could 
invent a key which is the actual key value divided by five and use hash 
ordered. In this way every five adjacent records would go on the same page 
(using placement).

The down side would be that I have to retrieve all five rows using the 
modified key and filter out the one I want.

Idealy, Rdb could setup a modified hash ordered algorythm that would do 
this for me.

G'day,

Mark.
T.RTitleUserPersonal
Name		DateLines
804.1UKVMS3::PJACKSONOracle UK Rdb SupportWed May 14 1997 07:4114
    I did something like this on a DBMS database. I had a table with a
    numeric key for which most accesses would be random, but sometimes
    access in key order was needed. The records where small, so about 30
    would fit on a 2 block page. I change the key into a group field with
    two parts: the leading digits and the trailing one. I used only the
    leading digits part for the hash key, so the records were stored in
    groups of 9, 10 or 6 (the last two digits ranged from 01 to 25). This made
    ordered access many times faster, and had no noticable effect on random
    access.
    
    Being able to modify the has algorithm would be useful for both ordered
    and scattered hashed indexes.
    
    Peter
804.2NOVA::SMITHIDon't understate or underestimate Rdb!Wed May 14 1997 09:0116
I think what you really want is a KEY composed of two parts:  the first part
is used to locate the page and the full key is used for retrieval.

A good use of this (not just for ORDERED HASH) is in the case of ORDER_HEADER
with clustered ORDER_LINES.  The unique key for ORDER_HEADER is
(ORDER_NUMBER), but to get the clustering the ORDER_LINES need the same key
(ORDER_NUMBER) but it must allow DUPLICATES.  To enforce a UNIQUE constraint
on (ORDER_NUMBER, LINE_NUMBER) requires an additional SORTED index.

If we could HASH on just ORDER_NUMBER but perform validation on the full key
(even if it just read through all the values in the HASH bucket) we could save
quite a bit of space and I/O.

(in short I like this idea :-)

Ian
804.3svrav1.au.oracle.com::MBRADLEYI was dropped on my head as a baby. What's your excuse?Wed May 14 1997 21:3417
Ian,

I know you can use two fields, but this data will never have two fields as 
the key.

It's just the simple case of a "sequence" type key, and we are not in a 
position to redesign the application to make it a two part key (and in fact 
I wouldn't like to make that sort of decision if I could avoid it... it 
just complicates the application and the database).

Besides, there really isn't a natural group here like order headers and 
lines, I just want to optimise storage and retrieval of adjacent key 
values.

G'day,

Mark.
804.4NOVA::SMITHIDon't understate or underestimate Rdb!Wed May 14 1997 22:138
~Besides, there really isn't a natural group here like order headers and 
~lines, I just want to optimise storage and retrieval of adjacent key 
~values.

Fine I wasn't suggesting you change anything, I was giving another example of
how this would be useful...

Ian