How can make this inheritance or override relationship query performance well in Oracle SQL?
===

I have the following (simplified for example) tables in my system:

[View the E-R Diagram][1]

The intent is that features can be specified for a given category and then overridden at a subcategory or product level.

There are different types of features that have varying attributes and those types are represented by "extension" tables that have a 1:1 relationship with feature.

For example, let's say:

* All Computers and Lights have AC Adapters (Category)
* All Computers have CPUs (Category)
* All Laptops and Tablets have Batteries (Sub-Category)
* All MacBook Pros have HDMI Ports (Product)
* All MacBook Pros and iPads have Retina Displays (Product)

This would be modeled as:

Categories:

* feature_container:  id:1
* category:  id:1, name:"Computer"
* feature_container:  id:2
* category:  id:2, name:"Light"

Subcategories:

* feature_container:  id:3
* subcategory:  id:3, category_id: 1, name:"Desktop"
* feature_container:  id:4
* subcategory:  id:4, category_id: 1, name:"Laptop"
* feature_container:  id:5
* subcategory:  id:5, category_id: 1, name:"Tablet"

* feature_container:  id:6
* subcategory:  id:6 category_id: 2, name:"Floor Light"
* feature_container:  id:7
* subcategory:  id:7, category_id: 2, name:"Desk Light"

Products: 

* feature_container:  id:7
* product:  id:7, subcategory_id: 4, name:"Apple MacBook Air"
* feature_container:  id:8
* product:  id:8, subcategory_id: 4, name:"Apple MacBook Pro"
* feature_container:  id:9
* product:  id:9, subcategory_id: 5, name:"Apple iPad"

Features Applied to Categories:

* feature:  id:1, feature_container_id: 1
* has_ac_adapter_feature:  id:1, model_number: "ABC"

* feature:  id:2, feature_container_id: 2
* has_ac_adapter_feature:  id:2, model_number: "DEF"

* feature:  id:3, feature_container_id: 1
* has_cpu_feature:  id:3, model_number: "123"

Features Applied to Sub-Categories:

* feature:  id:4, feature_container_id: 4
* has_battery_feature:  id:4, battery_type: "Lithium-Ion"
* feature:  id:5, feature_container_id: 5
* has_battery_feature:  id:5, battery_type: "Lithium-Ion"

Features Applied to Products:

* feature:  id:6, feature_container_id: 8
* has_retina_display_feature:  id:6
* feature:  id:7, feature_container_id: 9
* has_retina_display_feature:  id:7
* feature:  id:8, feature_container_id: 8
* has_hdmi_port_feature:  id:8

I won't bother inserting data for the Light hierarchy, but you get the idea.  Keep in mind that I have:

* Millions of products
* Thousands of subcategories 
* Hundreds of categories
* A few thousand features, primarily defined on subcategories, but also some on products and categories.
* There's nothing to say that features one would normally define at a category level (AC Adapter) might actually only get defined at the product level.  That's a business call and is driven by flexibility vs maintainability.  In other words, I have to assume that any feature can be assigned at any level.


I want to query for the following:

> Give me all products that have CPUs and AC Adapters and have (Retina Displays or HDMI Ports)

I should get:

* Apple MacBook Pro
* Apple iPad

Here's what I've tried...

I started with something like this:

[http://sqlfiddle.com/#!4/2f6cd/4/0][2]

This tries to deal with the inheritance problem, but then I quickly realized that if I start filtering on this (where product has this feature and that feature), I can't easily find what products have multiple and/or features (or can I?).

I then started doing something like this:

    SELECT prd.id AS prd_id,
       CASE
           WHEN    prd_lvl.ac_ftr = 1
                OR cat_lvl.ac_ftr = 1
                OR scat_lvl.ac_ftr = 1
           THEN
               1
           ELSE
               0
       END
           AS ac_ftr
    FROM product prd
       LEFT JOIN (SELECT prd.id AS prd_id,
                         prd.name AS prd_name,
                         NVL2 (ac_ftr.id, 1, 0) AS ac_ftr,
                         NVL2 (cpu_ftr.id, 1, 0) AS cpu_ftr,
                         NVL2 (bat_ftr.id, 1, 0) AS bat_ftr,
                         NVL2 (hdmi_ftr.id, 1, 0) AS hdmi_ftr,
                         NVL2 (ret_ftr.id, 1, 0) AS ret_ftr
                    FROM feature_container fc
                         INNER JOIN product prd
                             ON fc.id = prd.id
                         INNER JOIN feature ftr
                             ON fc.id = ftr.feature_container_id
                         LEFT JOIN has_ac_adapter_feature ac_ftr
                             ON ftr.id = ac_ftr.id
                         LEFT JOIN has_cpu_feature cpu_ftr
                             ON ftr.id = cpu_ftr.id
                         LEFT JOIN has_battery_feature bat_ftr
                             ON ftr.id = bat_ftr.id
                         LEFT JOIN has_hdmi_port_feature hdmi_ftr
                             ON ftr.id = hdmi_ftr.id
                         LEFT JOIN has_retina_display_feature ret_ftr
                             ON ftr.id = ret_ftr.id) prd_lvl
           ON prd.id = prd_lvl.prd_id
       LEFT JOIN (      --All Products and subcategory-level-features assigned
                  SELECT prd.id AS prd_id,
                         prd.name AS prd_name,
                         NVL2 (ac_ftr.id, 1, 0) AS ac_ftr,
                         NVL2 (cpu_ftr.id, 1, 0) AS cpu_ftr,
                         NVL2 (bat_ftr.id, 1, 0) AS bat_ftr,
                         NVL2 (hdmi_ftr.id, 1, 0) AS hdmi_ftr,
                         NVL2 (ret_ftr.id, 1, 0) AS ret_ftr
                    FROM feature_container fc
                         INNER JOIN subcategory scat
                             ON fc.id = scat.id
                         INNER JOIN feature ftr
                             ON fc.id = ftr.feature_container_id
                         INNER JOIN product prd
                             ON scat.id = prd.subcategory_id
                         LEFT JOIN has_ac_adapter_feature ac_ftr
                             ON ftr.id = ac_ftr.id
                         LEFT JOIN has_cpu_feature cpu_ftr
                             ON ftr.id = cpu_ftr.id
                         LEFT JOIN has_battery_feature bat_ftr
                             ON ftr.id = bat_ftr.id
                         LEFT JOIN has_hdmi_port_feature hdmi_ftr
                             ON ftr.id = hdmi_ftr.id
                         LEFT JOIN has_retina_display_feature ret_ftr
                             ON ftr.id = ret_ftr.id) scat_lvl
           ON prd.id = scat_lvl.prd_id
       LEFT JOIN (         --All Products and category-level-features assigned
                  SELECT prd.id AS prd_id,
                         prd.name AS prd_name,
                         NVL2 (ac_ftr.id, 1, 0) AS ac_ftr,
                         NVL2 (cpu_ftr.id, 1, 0) AS cpu_ftr,
                         NVL2 (bat_ftr.id, 1, 0) AS bat_ftr,
                         NVL2 (hdmi_ftr.id, 1, 0) AS hdmi_ftr,
                         NVL2 (ret_ftr.id, 1, 0) AS ret_ftr
                    FROM feature_container fc
                         INNER JOIN category cat
                             ON fc.id = cat.id
                         INNER JOIN feature ftr
                             ON fc.id = ftr.feature_container_id
                         INNER JOIN subcategory scat
                             ON cat.id = scat.category_id
                         INNER JOIN product prd
                             ON scat.id = prd.subcategory_id
                         LEFT JOIN has_ac_adapter_feature ac_ftr
                             ON ftr.id = ac_ftr.id
                         LEFT JOIN has_cpu_feature cpu_ftr
                             ON ftr.id = cpu_ftr.id
                         LEFT JOIN has_battery_feature bat_ftr
                             ON ftr.id = bat_ftr.id
                         LEFT JOIN has_hdmi_port_feature hdmi_ftr
                             ON ftr.id = hdmi_ftr.id
                         LEFT JOIN has_retina_display_feature ret_ftr
                             ON ftr.id = ret_ftr.id) cat_lvl
           ON prd.id = cat_lvl.prd_id order by prd_id

But that has the same problem: I can't get things on the same row.  Any advice on how to solve this problem?

Thanks!


  [1]: http://postimage.org/image/vaj6i3pu9/
  [2]: http://sqlfiddle.com/#!4/2f6cd/4/0