3 Replies Latest reply: Dec 10, 2012 12:00 PM by user5716448 RSS

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

    user5716448
      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
          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
            >
            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.