DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Hash Clusters

  1. #1
    Join Date
    Sep 2000
    Posts
    20
    I have a huge table created over a hash cluster, where the hash key is an id. I'm planning to split that big table into 4 smaller ones (i'll divide them according to dates) but I'd like some advise about the cluster. What's the best to do?? Create 4 clusters and one table on each cluster? or just create one cluster and all 4 tables over that one??

  2. #2
    Join Date
    Sep 2000
    Posts
    20
    please! any reply?!?

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Isn't it good option to use partitioning if its 8+ version database since, you are trying to split that tabe on date. Just a thought....
    Reddy,Sam

  4. #4
    Join Date
    Sep 2000
    Posts
    20
    Tks. Sam, unfortunately it's Oracle 7.3.4 I'm working with... the table splitting will be done before upgrading

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    How about using partition view ? Iam not sure how hash cluster works internally so I can't advise the method you described will not get you into performance issues.
    Reddy,Sam

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688

    Exclamation

    About hash cluster:
    1) Table access very fast if you make a query based on hash key.
    2) Hash joins very fast as well.
    3) You have to chose correct hash function AND NUMBER OF HASH KEYS it depend what type of information do you have.
    4) Hash based tables takes more space.
    5) You will not be able truncate table.
    For example:
    I did search system which contains 4 tables:
    A) table_info: (table_name,column_name,table_id)
    B) global_text_dict(
    WORD NOT NULL VARCHAR2(100) - just word
    WORD_ID NOT NULL NUMBER(10)
    SOUND NOT NULL NUMBER(10)) - goto ***
    C) index table:
    idx$member_tbl(
    ROW_ID CHAR(18) - rowid from member_tbl
    WORD_ID NUMBER
    SOUND NUMBER(10) - goto ***
    COLUMN_ID NUMBER) - goto (*)
    D) the main table might be any:
    member_tbl (...)

    *** - this field is calculated based on soundex function:
    sound = trim(upper('hello')),to_number(ascii(trim(upper('hello'))) ||substr(soundex(trim(upper('hello'))),2)))

    (*) just for avoiding join I calculate coulmn_id by the rule:
    column_id=
    create or replace function tidx.get_column_id(table_name varchar2,column_name varchar2) return number is
    ln number;
    id number;
    line varchar2(60);
    begin
    line := upper(trim(table_name) || trim(column_name));
    ln := length(trim(table_name) || trim(column_name));
    id := ln;
    for counter in 1..ln loop
    id := id + ascii(substr(line,counter,1));
    end loop;
    return id;
    end get_column_id;

    SO,the search time before was > 1 min.
    NOW, < 1 second.

    I have 7500 hash keys for that hash function but for now in use only around 4500.

    Best wishes!
    Dmitri

  7. #7
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    I did not split the table as it might take to many code changes.
    If any other question about it: dmitri.zaikine@voxpilot.com
    In real several millions record for oracle means nothing if the are saved correctly. :-)
    Best wishes.

  8. #8
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    What the size of your table?
    If several Gb , I think it is better to split it because in this case you will be able to locate your parts in different HD for increasing performance.

  9. #9
    Join Date
    Sep 2000
    Posts
    20
    sreddy, kgb thanks for the replies!
    the table is around 26 Gb.. and it's actually mandatory to split it, so my main concern is wether to have them all in one cluster, or create a cluster for each table

  10. #10
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    I would split this table and create hash cluster for each table, but you have to find the spliting rules, one of join conditions in sql query has to based on hash value, you have to find a good hash function. Then create view which join all parts of the table and trigger on the view which makes DML operation on this tables. Don't forget that you may make indexes on this table for increasing responce time. That is my opinion.

    Best wishes!
    Dmitri

    Originally posted by CeciMego
    sreddy, kgb thanks for the replies!
    the table is around 26 Gb.. and it's actually mandatory to split it, so my main concern is wether to have them all in one cluster, or create a cluster for each table

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width