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.