In our application the following data model constitutes to one of the most expensive queries in the database. The question is this: find the item_groups that has a given property in a given store
A given item group belongs to a store if both of the following conditions are satisfied 1. All the items in the item group should belong to the store in ITEM_LIST_IN_STORE. 2. All the items in the item group should have a translation in the ITEM_NAME for the country/language to which the store belongs I have tried several ways to answer this question and have always ended up thinking there should be a better way to answer this. I have found it literally impossible to do this without generating a cartesian product at some point in the query. I am placing this problem in this forum in the hope that some of you may be able to figure out a better solution than me. CREATE TABLE ITEM_LIST_IN_STORE ( ITEM_NO VARCHAR2(15), STORE_NO VARCHAR2(5), DELETE_DATE DATE ) / CREATE TABLE ITEM_GROUPS ( ITEM_GROUP_NO VARCHAR2(15), ITEM_NO_CHILD VARCHAR2(15) ) / CREATE TABLE ITEM_NAME ( ITEM_NO VARCHAR2(15), ITEM_NAME VARCHAR2(55), CTY_CODE VARCHAR2(5), LANG_CODE VARCHAR2(5), DELETE_DATE DATE ) / CREATE TABLE ITEM_PROPS ( ITEM_NO VARCHAR2(15), ITEM_NAME_OFFICIAL VARHCAR2(55), PRODUCT_NAME_NO VARHCAR2(55), PRODUCT_TYPE_NO VARCHAR2(55) ) / CREATE TABLE STORE_CTY_LANG ( STORE_NO VARCHAR2(5), CTY_CODE VARCHAR2(5), LANG_CODE VARCHAR2(5) DELETE_DATE DATE ) / -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
