dis/advantage of composite partitioning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: dis/advantage of composite partitioning

  1. #1
    Join Date
    Mar 2002
    Posts
    534

    DWH dis/advantage of composite partitioning

    Hi,

    I'm working on a datawarehouse project using Oracle 9iR2 on some SUN server with a SAN.

    We got a large table in which we load about 35M records a month using about 5 GB. The table is range partitioned on a monthly base an there is only one index (pk).

    I saw that hash partitioning this table over a frequently used key would in many cases (90%) imporve a lot the query performance.

    But before proposing it to my boss, I was wundering if any body made some bad experience with composite (range/hash) partitioning ?
    I'm also interested to know how much more ressources are need when records a inserted in a table having 128 subpartitions instead of one with no subpartitions?

    Thanks in advance for any reply
    Mike
    Last edited by mike9; 12-10-2003 at 02:38 AM.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    may I know how hash partitioning improves the query performance :?

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    the key over which it is the partition is hashed is something like a product id havind only about 500 different values.
    90% of the query are limited by that id.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    but using equality operator or IN or < > <= >= :-?, i.e unique scan or range scan

    or simply partition pruning
    Last edited by pando; 12-10-2003 at 01:18 AM.

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    yes my goal with hash subpartitioning is to use partitioning pruning.
    There is (for any reason) no index against this this product key so it always did a full table scan even that we have several product with only 5-10 records (out of 35M) per month.

    I was thinking that hashing would be better then creating an index against this value because it would use less resource when inserting new data in the table. Also as far as i know an index access is only used when it will returne less then 5% of ther rows of the table. But I have about 10 out of 500 product which represants about 90% of the data, and I want that also for these 10 products it doesn't do any FTS anymore but a partition pruning over the hash key. In most queries only one product type is defined in the where clause so only one hash subpartition would be concerned.
    Last edited by mike9; 12-10-2003 at 02:02 AM.

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