Details

    • Type: Sub-task
    • Status: In Progress
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 3.0.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      Inner Join Elimination based on Informational RI constraints

      This transformation detects RI joins and eliminates the parent/PK table if none of its columns, other than the PK columns, are referenced in the query.

      Typical examples that benefit from this rewrite are queries over complex views.

      View using TPC-DS schema:

      create view customer_purchases_2002 (id, last, first, product, store_id, month, quantity) as
      select c_customer_id, c_last_name, c_first_name, i_product_name, s_store_id, d_moy, ss_quantity 
      from store_sales, date_dim, customer, item, store
      where d_date_sk = ss_sold_date_sk and
            c_customer_sk = ss_customer_sk and 
            i_item_sk = ss_item_sk and
            s_store_sk = ss_store_sk and
            d_year = 2002
      
      

      The view returns customer purchases made in year 2002. It is a join between fact table store_sales and dimensions customer, item, store, and date. The tables are joined using RI predicates.

      If we write a query that only selects a subset of columns from the view, for example, we are only interested in the items bought and not the stores, internally, the Optimizer, will first merge the view into the query, and then, based on the primary key – foreign key join predicate analysis, it will decide that the join with the store table is not needed, and therefore the store table is removed.

      Query:

      select id, first, last, product, quantity 
      from customer_purchases_2002
      where product like ‘bicycle%’ and
            month between 1 and 2
      

      Internal query after view expansion:

      select c_customer_id as id, c_first_name as first, c_last_name as last,
                 i_product_name as product,ss_quantity as quantity 
      from store_sales, date_dim, customer, item, store
      where d_date_sk = ss_sold_date_sk and
            c_customer_sk = ss_customer_sk and 
            i_item_sk = ss_item_sk and
            s_store_sk = ss_store_sk and
            d_year = 2002 and
            month between 1 and 2 and
            product like ‘bicycle%’
      

      Internal optimized query after join elimination:

      select c_customer_id as id, c_first_name as first, c_last_name as last,
                 i_product_name as product,ss_quantity as quantity 
      from store_sales, date_dim, customer, item
      where d_date_sk = ss_sold_date_sk and
            c_customer_sk = ss_customer_sk and 
            i_item_sk = ss_item_sk and
            d_year = 2002 and
            month between 1 and 2 and
            product like ‘bicycle%’
      

      The join with store table can be removed since no columns are retrieved from the table, and every row from the store_sales fact table will find a match in store based on the RI relationship.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              ioana-delaney Ioana Delaney
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated: