This discussion is archived
3 Replies Latest reply: Dec 10, 2012 10:00 AM by user5716448 RSS

Star-transformation + additional join +performance+partitioning - Oracle 11

user5716448 Explorer
Currently Being Moderated
Hi,

Using Oracl;e 11.2.0.3

We are about to load a data warehouse and have a large cnetral fact table with surroagte keys which link to the associated dimensions.

Star transformation is enabled.

We currently have 2 choices either hash-partition on the product_id (surrogate key + similarly hash partition the product dimesnions by this).

Whilts this will give us partition-wise joins less useful from houskeeping angle.

I am considering adding another date to the fact table product_on_sale_date (housekeeping coudl then drop partitions older than certain time frame.

This would turn the join pattern from

sales.product_id = product.product_id

to sales.product_id = product.product_id
and sales.product_on_sale_date to product.on_sale_date.

Would this allow partition-wise joins if the partitioning stratgey is partitiong sales fact and product dimension on product_on_sale_date

i.e. only 1 of the columns in the join is partitioned or would we need to range partition on product_on_sale_date then hash sub-partitions on product_id.


Thoughts?

Thanks
  • 1. Re: Star-transformation + additional join +performance+partitioning - Oracle 11
    damorgan Oracle ACE Director
    Currently Being Moderated
    Consider this possibility ... you currently have column for product_id and product_on_sale_date ... use the existing date column for partitioning.

    Then create a function based index using the ORA_HASH built-in function so that you can still join based on the hash.
    http://www.morganslibrary.org/reference/ora_hash.html
    hashing can also be done using DBMS_SQLHASH
    http://www.morganslibrary.org/reference/pkgs/dbms_sqlhash.html
    and DBMS_CRYPTO
    http://www.morganslibrary.org/reference/pkgs/dbms_crypto.html
    built-in packages.

    Can it work? Yes. Will it work better? Only testing with your hardware, software, and data will tell.
  • 2. Re: Star-transformation + additional join +performance+partitioning - Oracle 11
    rp0428 Guru
    Currently Being Moderated
    >
    We are about to load a data warehouse and have a large cnetral fact table with surroagte keys which link to the associated dimensions.

    Star transformation is enabled.

    We currently have 2 choices either hash-partition on the product_id (surrogate key + similarly hash partition the product dimesnions by this).

    Whilts this will give us partition-wise joins less useful from houskeeping angle.

    I am considering adding another date to the fact table product_on_sale_date (housekeeping coudl then drop partitions older than certain time frame.

    This would turn the join pattern from

    sales.product_id = product.product_id

    to sales.product_id = product.product_id
    and sales.product_on_sale_date to product.on_sale_date.

    Would this allow partition-wise joins if the partitioning stratgey is partitiong sales fact and product dimension on product_on_sale_date

    i.e. only 1 of the columns in the join is partitioned or would we need to range partition on product_on_sale_date then hash sub-partitions on product_id.

    Thoughts?
    >
    My first thought is how can you be 'about to load a data warehouse' when you haven't finish designing, testing or implementing the architecture? Doesn't that sound like you've got the cart before the horse?

    My second thought is that you could easily have answered your own question if you had created a simple prototype table, examined the execution plan and done some basic testing.

    Why are you apparently reluctant to put fingers to keyboard and do this testing yourself?
    >
    Would this allow partition-wise joins if the partitioning stratgey is partitiong sales fact and product dimension on product_on_sale_date
    >
    Yes - is the answer to your question. It would 'allow' partition-wise joins. But the more important question is whether Oracle will actually use partition-wise joins. And the answer to that question depends on the type of queries you are using and what their filter predicates are.

    You should have tested this first and answered your own question. Then you could have presented your findings and ask if there are any other considerations that you need to take into account.

    An experienced developer/architect would always prototype and test regardless and would never rely on any answer obtained from a forum or other third-party sources.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points