I am using Oracle 12.1.0.2. Is there any way to get better cardinality estimate of join of two view. Currently, join of two view (activity and activity_status) is showing as 5870 but the actual row is 802. This is causing performance issue when this SQL is joined against other table ( doing full table scan instead of index scan)
SQL_ID 9jjzn872t6cpn, child number 0
-------------------------------------
select A.ACTIVITY_PK from ACTIVITY A inner join ACTIVITY_STATE
AST on (A.ACTIVITY_PK = AST.ACTIVITY_PK) where AST.EFFECTIVE_DT <=
trunc (current_date) and (AST.EXPIRATION_DT is null or
AST.EXPIRATION_DT >= trunc (current_date)) and AST.VISIBLE_CD = 'Y' and
OWNER_PK = :OWNER_PK
Plan hash value: 2705303039
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1484 (100)| | 802 |00:00:00.73 | 7354 | 1903 | | | |
|* 1 | HASH JOIN | | 1 | 5870 | 183K| 1484 (2)| 00:00:01 | 802 |00:00:00.73 | 7354 | 1903 | 1966K| 1966K| 1479K (0)|
|* 2 | VIEW | index$_join$_001 | 1 | 5394 | 53940 | 209 (2)| 00:00:01 | 5560 |00:00:00.33 | 1084 | 1039 | | | |
|* 3 | HASH JOIN | | 1 | | | | | 5560 |00:00:00.33 | 1084 | 1039 | 1272K| 1272K| 1691K (0)|
|* 4 | INDEX RANGE SCAN | TMP_IDX1 | 1 | 5394 | 53940 | 1 (0)| 00:00:01 | 5560 |00:00:00.01 | 7 | 0 | | | |
| 5 | INDEX FAST FULL SCAN| PK_ACTIVITY | 1 | 5394 | 53940 | 258 (1)| 00:00:01 | 743K|00:00:00.13 | 1077 | 1039 | | | |
|* 6 | INDEX RANGE SCAN | TMP_IDX2 | 1 | 168K| 3626K| 1273 (2)| 00:00:01 | 118K|00:00:00.36 | 6270 | 864 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
2 - SEL$5E75DC3A / A@SEL$1
3 - SEL$5E75DC3A
4 - SEL$5E75DC3A / indexjoin$_alias$_001@SEL$5E75DC3A
5 - SEL$5E75DC3A / indexjoin$_alias$_002@SEL$5E75DC3A
6 - SEL$58A6D7F6 / AST@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ACTIVITY_PK"="AST"."ACTIVITY_PK")
2 - filter("A"."OWNER_PK"=5685)
3 - access(ROWID=ROWID)
4 - access("A"."OWNER_PK"=5685)
6 - access("AST"."VISIBLE_CD"='Y' AND "AST"."EFFECTIVE_DT"<=TRUNC(CURRENT_DATE))
filter(("AST"."EFFECTIVE_DT"<=TRUNC(CURRENT_DATE) AND ("AST"."EXPIRATION_DT" IS NULL OR "AST"."EXPIRATION_DT">=TRUNC(CURRENT_DATE))))