Below, I have a 15 million record fact table VS_COMP_DATA_SPAT_PRT_REF_AGE which is range partitioned by VEH_COMP_AGE_BASE, which is a date field. I am joining this to three dimension tables: RK_JUNK_DIM2, RK_LOCATION, and RK_MAKE_ENGINE_DIM (partitioned by DIVISION_ID and subpartitioned by MODEL).
This query is currently running in about 1 minute and it needs to run below three seconds.
1. The PSTART and PSTOP for the fact table (VS_COMP_DATA_SPAT_PRT_REF_AGE) is set to: KEY |1048575.
What is 1048575? This table only has 9 partitions.
2. Why is it doing MERGE JOIN CARTESIAN? ID = 25 says that the rows = 1 on the VS_COMP_DATA_SPAT_PRT_REF_AGE table. This is not true. This would be in the thousands... I gathered stats and all, what am I missing?
SELECT
DL_COMPARABLE_ID
FROM VS_COMP_DATA_SPAT_PRT_REF_AGE comparable0_
WHERE
RK_MAKE_ENGINE_DIM_ID in (select RK_MAKE_ENGINE_DIM_ID from RK_MAKE_ENGINE_DIM veh
where
veh.MDL_YR =:P_MDL_YR
AND veh.DIVISION_ID =:P_DIVISION_ID
AND veh.MODEL =:P_MODEL
AND veh.TRIM =:P_TRIM
AND VEH.STYLE_NM_WO_TRIM =:P_STYLE_NM_WO_TRIM)
and RK_JUNK_DIM_ID in (select rk_junk_dim_id from RK_JUNK_DIM2 dim where RK_PRICE_SOLD = 'Y' and dim.veh_normal = 'Y' and odom_plus_0 = 'Y' and dim.ACTV_FLG =:P_ACTV_FLG)
and RK_LOCATION_ID in (select RK_LOCATION_ID from RK_LOCATION LOC where SDO_WITHIN_DISTANCE(loc.shape, get_long_lat_pt(:SDO_POINT_TYPE1, :SDO_POINT_TYPE2), 'distance=500 unit=MILE')='TRUE')
AND comparable0_.VEH_COMP_AGE_BASE >=to_date(:P_VEH_COMP_AGE_BASE, 'DD-Mon-YYYY')
and OPTION_ADJUSTMENT between :MIN and :MAX
and PACKAGE_ADJUSTMENT between :MIN and :MAX
and MILEAGE_ADJUSTMENT between :MIN and :MAX
and CONDITION_ADJUSTMENT between :MIN and :MAX
and CV_TOT_ADJUSTMENT between :MIN and :MAX
AND MODEL_ADJUSTMENT BETWEEN :MIN AND :MAX
;
Execution Plan
----------------------------------------------------------
Plan hash value: 440342739
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | 1257 (1)| 00:00:16 | | |
|* 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 316 | 1257 (1)| 00:00:16 | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 152 | 2 (0)| 00:00:01 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 138 | 0 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY INDEX ROWID | RK_LOCATION | 107 | 9951 | 0 (0)| 00:00:01 | | |
|* 6 | DOMAIN INDEX | RK_LOCATION_SHAPE_IDXK | | | 0 (0)| 00:00:01 | | |
| 7 | BUFFER SORT | | 1 | 45 | 0 (0)| 00:00:01 | | |
| 8 | PARTITION LIST SINGLE | | 1 | 45 | 0 (0)| 00:00:01 | KEY | KEY |
| 9 | PARTITION HASH SINGLE | | 1 | 45 | 0 (0)| 00:00:01 | KEY | KEY |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| RK_MAKE_ENGINE_DIM | 1 | 45 | 0 (0)| 00:00:01 | | |
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 12 | BITMAP AND | | | | | | | |
|* 13 | BITMAP INDEX SINGLE VALUE | B_TRIMDX | | | | | | |
|* 14 | BITMAP INDEX SINGLE VALUE | B_MODELDX | | | | | | |
|* 15 | BITMAP INDEX SINGLE VALUE | B_DIVISION_IDDX | | | | | | |
| 16 | BUFFER SORT | | 33 | 462 | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | RK_JUNK_DIM2 | 33 | 462 | 2 (0)| 00:00:01 | | |
| 18 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 19 | BITMAP AND | | | | | | | |
|* 20 | BITMAP INDEX SINGLE VALUE | B_ODOM_PLUS_0X | | | | | | |
|* 21 | BITMAP INDEX SINGLE VALUE | B_RK_JUNK_DIM_SOLDX | | | | | | |
|* 22 | BITMAP INDEX SINGLE VALUE | B_ACTV_FLG | | | | | | |
|* 23 | BITMAP INDEX SINGLE VALUE | B_VEH_NORMALX | | | | | | |
| 24 | PARTITION RANGE ITERATOR | | 1 | 164 | 1257 (1)| 00:00:16 | KEY |1048575|
|* 25 | TABLE ACCESS BY LOCAL INDEX ROWID | VS_COMP_DATA_SPAT_PRT_REF_AGE | 1 | 164 | 1257 (1)| 00:00:16 | KEY |1048575|
| 26 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 27 | BITMAP AND | | | | | | | |
|* 28 | BITMAP INDEX SINGLE VALUE | I_LOC_ID_AGE | | | | | KEY |1048575|
|* 29 | BITMAP INDEX SINGLE VALUE | I_JUNK_DIM_IDX_AGEA | | | | | KEY |1048575|
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:MIN)<=TO_NUMBER(:MAX))
6 - access("MDSYS"."SDO_WITHIN_DISTANCE"("LOC"."SHAPE","GET_LONG_LAT_PT"(:SDO_POINT_TYPE1,:SDO_POINT_TYPE2),'distance=500
unit=MILE')='TRUE')
10 - filter("VEH"."STYLE_NM_WO_TRIM"=:P_STYLE_NM_WO_TRIM AND "VEH"."MDL_YR"=TO_NUMBER(:P_MDL_YR))
13 - access("VEH"."TRIM"=:P_TRIM)
14 - access("VEH"."MODEL"=:P_MODEL)
15 - access("VEH"."DIVISION_ID"=TO_NUMBER(:P_DIVISION_ID))
20 - access("ODOM_PLUS_0"='Y')
21 - access("RK_PRICE_SOLD"='Y')
22 - access("DIM"."ACTV_FLG"=:P_ACTV_FLG)
23 - access("DIM"."VEH_NORMAL"='Y')
25 - filter("OPTION_ADJUSTMENT">=TO_NUMBER(:MIN) AND "OPTION_ADJUSTMENT"<=TO_NUMBER(:MAX) AND
"PACKAGE_ADJUSTMENT">=TO_NUMBER(:MIN) AND "PACKAGE_ADJUSTMENT"<=TO_NUMBER(:MAX) AND "MILEAGE_ADJUSTMENT">=TO_NUMBER(:MIN) AND
"MILEAGE_ADJUSTMENT"<=TO_NUMBER(:MAX) AND "CONDITION_ADJUSTMENT">=TO_NUMBER(:MIN) AND "CONDITION_ADJUSTMENT"<=TO_NUMBER(:MAX) AND
"CV_TOT_ADJUSTMENT">=TO_NUMBER(:MIN) AND "CV_TOT_ADJUSTMENT"<=TO_NUMBER(:MAX) AND "MODEL_ADJUSTMENT">=TO_NUMBER(:MIN) AND
"MODEL_ADJUSTMENT"<=TO_NUMBER(:MAX) AND "COMPARABLE0_"."VEH_COMP_AGE_BASE">=TO_DATE(:P_VEH_COMP_AGE_BASE,'DD-Mon-YYYY') AND
"RK_MAKE_ENGINE_DIM_ID"="RK_MAKE_ENGINE_DIM_ID")
28 - access("RK_LOCATION_ID"="RK_LOCATION_ID")
29 - access("RK_JUNK_DIM_ID"="RK_JUNK_DIM_ID")
Statistics
----------------------------------------------------------
1345 recursive calls
0 db block gets
153974 consistent gets
4090 physical reads
234328 redo size
1104 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
81 rows processed