SQL Analysis

[gvenzl]

SELECT s.*
FROM sales s, products p
WHERE s.prod_id = p.prod_id
      AND p.prod_category = 1
      AND p.prod_subcategory != 'abc'
UNION
SELECT s.*
FROM sales s, products p, customers c
WHERE s.prod_id = p.prod_id
      AND s.quantity_sold > 100;


-----------------------------------------------------------------
| Id  | Operation                        | Name                 |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                      |
|   1 |  HASH UNIQUE                     |                      |
|   2 |   UNION-ALL                      |                      |
|*  3 |    HASH JOIN                     |                      |
|   4 |     JOIN FILTER CREATE           | :BF0000              |
|*  5 |      TABLE ACCESS FULL           | PRODUCTS             |
|   6 |     JOIN FILTER USE              | :BF0000              |
|   7 |      PARTITION RANGE ALL         |                      |
|*  8 |       TABLE ACCESS FULL          | SALES                |
|   9 |    MERGE JOIN CARTESIAN          |                      |
|  10 |     NESTED LOOPS SEMI            |                      |
|  11 |      PARTITION RANGE ALL         |                      |
|* 12 |       TABLE ACCESS FULL          | SALES                |
|* 13 |      INDEX UNIQUE SCAN           | PRODUCTS_PK          |
|  14 |     BUFFER SORT                  |                      |
|  15 |      BITMAP CONVERSION TO ROWIDS |                      |
|  16 |       BITMAP INDEX FAST FULL SCAN| CUSTOMERS_GENDER_BIX |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("S"."PROD_ID"="P"."PROD_ID")
   5 - filter(TO_NUMBER("P"."PROD_CATEGORY")=1 AND
              "P"."PROD_SUBCATEGORY"<>'abc')
   8 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))
  12 - filter("S"."QUANTITY_SOLD">100)
  13 - access("S"."PROD_ID"="P"."PROD_ID")

SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------

   1 -  SET$1
           -  The query block contains UNION which may be expensive.
              Consider using UNION ALL if duplicates are allowed or
              uniqueness is guaranteed.

   5 -  SEL$1 / "P"@"SEL$1"
           -  The following columns have predicates which preclude their
              use as keys in index range scan. Consider rewriting the
              predicates.
                "PROD_CATEGORY"
                "PROD_SUBCATEGORY"

   9 -  SEL$2
           -  The query block has 1 cartesian product which may be
              expensive. Consider adding join conditions or removing the
              disconnected tables or views.