8 Replies Latest reply on Nov 12, 2013 8:20 PM by Rinne

    PSTART/ PSTOP: key vs number

    Rinne

      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.

       

      My biggest concerns are:

      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?

       

      Please help!

       

       

      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            
      
        • 1. Re: PSTART/ PSTOP: key vs number

          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.

           

          My biggest concerns are:

          And in the forums one of our 'biggest concerns' is people that open multiple threads for pretty much the same problem or for related problems don't bother to post the thread links so that people wanting to help can get information from the other threads that might be useful.

           

          It also doesn't help when you don't bother responding to, or answering, the questions that people ask you in your other threads.

           

          This was your last thread and is clearly related to this one

          https://forums.oracle.com/thread/2596464?start=30&tstart=0

           

          And before that you ask about using SDO_DISTANCE and how to optimize it. But then seemingly abandoned this thread and didn't answer the questions the last person trying to help you ask. I notice now that you ARE using that SDO_DISTANCE in the above query knowing full well that it was one of the problems you ask about in this thread:

          https://forums.oracle.com/thread/2592533

           

          Performance can only be as good as the WORST part of the process (weakest link and all that). So if you have a KNOWN issue with the embedded SDO_DISTANCE part of the subquery there isn't much point in nesting that within an outer query and then wondering why the outer query ALSO has a performance issue.

           

          You have to troubleshoot from the inside out; that is, go back to the innermost query and test/execute it separately until it performs as needed based on what it is doing and the amount of data it is doing it with.

           

          Then you can build on that inner component.

           

          THERE ARE NO SHORTCUTS! Abandoning the thread that is related to the poor performance of your query is just going to delay and complicate solving your problem.

          • 2. Re: PSTART/ PSTOP: key vs number
            Rinne

            Thanks for your advice. The queries are similar, but they discuss different problems. I've been working on this for a while and the previous threads helped me resolve the distance calculation. As you can see, I am using one of the advices they gave me and I just marked them as correct answers. This question was about the partitions being so large. The previous thread was about the distance calculation.

            • 3. Re: PSTART/ PSTOP: key vs number
              Rinne

              The distance issue is no longer a performance problem. I was able to verify that the query performs just fine. It is when I use the partitioning on the date in the fact table that causes the issue, so I asked this question

              • 4. Re: PSTART/ PSTOP: key vs number

                Have you run an actual trace to see what plan is actually being used?

                 

                Because you said it was taking a minute but what you posted only shows 16 seconds.

                • 5. Re: PSTART/ PSTOP: key vs number
                  Jonathan Lewis

                  It looks like you're trying to write a star-transformation by hand - your "IN subquery" constructs look like the rewrite the optimizer would do to create what it calls semi-join drivers.  (Have you set star_transformation_enabled to true - it may be necessary for the type of plan you want ?)

                   

                  Your problem (Cartesian merge join) looks like a basic stats problem; the standard optimizer arithmetic suggests the intermediate results sets are so small that a Cartesian join would be okay - the worst offender looks like the rk_make_engine_dim table, though only you can tell how bad the estimates are for the rk_junk_dim2 table.  I would give the optimimizer a little help by creating some extended_stats on column groups on those two tables so that the optimizer has a better chance of producing the right estimates before it hits the Cartesian merge.

                   

                  I think the 1048575 appears because your table is INTERVAL partitioned, not just range partitioned, and your predicate is a "greater than" which means Oracle needs to scan from a starting partition to the last potential partition (which is number 1048575)

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: PSTART/ PSTOP: key vs number
                    Mohamed Houri

                    Jonathan,

                     

                    Thanks for showing us this clue about 1048575 appearing in the PSTOP. I tested it right now

                     

                    SQL> CREATE TABLE partition_interval_tab (

                           n1           NUMBER

                          ,trade_date   DATE

                          ,n2           number

                        )

                        PARTITION BY RANGE (trade_date)

                        INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

                        (

                          PARTITION p_1 values LESS THAN (TO_DATE(' 2013-11-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

                         ,PARTITION p_2 values LESS THAN (TO_DATE(' 2013-12-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

                       );


                    SQL> insert into partition_interval_tab values (1, trunc(sysdate), 100);

                    SQL> insert into partition_interval_tab values (2, trunc(sysdate + 20), 200);

                    SQL> commit;

                     

                     

                    SQL> select * from partition_interval_tab where trade_date = trunc(sysdate);

                     

                            N1 TRADE_DATE                N2

                    ---------- ----------------- ----------

                             1 20131108 00:00:00        100

                    ----------------------------------------------------------------------------------------------------------------

                    | Id  | Operation              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                    -----------------------------------------------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT       |                        |       |       |    15 (100)|          |       |       |

                    |   1 |  PARTITION RANGE SINGLE|                        |     1 |    35 |    15   (0)| 00:00:01 |   KEY |   KEY |

                    |*  2 |   TABLE ACCESS FULL    | PARTITION_INTERVAL_TAB |     1 |    35 |    15   (0)| 00:00:01 |   KEY |   KEY |

                    -----------------------------------------------------------------------------------------------------------------

                    Predicate Information (identified by operation id):

                    ---------------------------------------------------

                       2 - filter("TRADE_DATE"=TRUNC(SYSDATE@!))

                     

                    SQL> select * from partition_interval_tab where trade_date >= trunc(sysdate);

                     

                            N1 TRADE_DATE                N2

                    ---------- ----------------- ----------

                             1 20131108 00:00:00        100

                             2 20131128 00:00:00        200

                    -------------------------------------------------------------------------------------------------------------------

                    | Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                    -------------------------------------------------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT         |                        |       |       |    28 (100)|          |       |       |

                    |   1 |  PARTITION RANGE ITERATOR|                        |     2 |    70 |    28   (0)| 00:00:01 |   KEY |1048575|

                    |*  2 |   TABLE ACCESS FULL      | PARTITION_INTERVAL_TAB |     2 |    70 |    28   (0)| 00:00:01 |   KEY |1048575|

                    -------------------------------------------------------------------------------------------------------------------

                    Predicate Information (identified by operation id):

                    ---------------------------------------------------

                       2 - filter("TRADE_DATE">=TRUNC(SYSDATE@!))

                     

                    Best regards

                    Mohamed Houri

                    • 7. Re: PSTART/ PSTOP: key vs number
                      Rinne

                      It looks like you're trying to write a star-transformation by hand - your "IN subquery" constructs look like the rewrite the optimizer would do to create what it calls semi-join drivers.  (Have you set star_transformation_enabled to true - it may be necessary for the type of plan you want ?)

                       

                      Yes, I have tried with star_transformation hint but it won't transform it. After some research, one of the conditions is that if I use bind variables, star_transformation doesn't get used. Is using bind variables in data warehouse queries that get executed thousands of times a day a bad practice? I understand about not using bind variables if the queries take minutes. But these queries take less than a minute. Where to draw the line?

                       

                      Your problem (Cartesian merge join) looks like a basic stats problem; the standard optimizer arithmetic suggests the intermediate results sets are so small that a Cartesian join would be okay - the worst offender looks like the rk_make_engine_dim table, though only you can tell how bad the estimates are for the rk_junk_dim2 table.  I would give the optimimizer a little help by creating some extended_stats on column groups on those two tables so that the optimizer has a better chance of producing the right estimates before it hits the Cartesian merge.

                       

                      I gather stats by running the following on RK_MAKE_ENGINE_DIM, RK_JUNK_DIM2 and on VS_COMP_DATA_SPAT_PRT_REF_AGE.

                       

                         select
                         DBMS_STATS.CREATE_EXTENDED_STATS
                         ('VLTN_PX', 'RK_MAKE_ENGINE_DIM', '(ENGINE_CYL, ENGINE_DISP,ENGINE_FUEL, ENGINE_ASP , ENGINE_CARB, MDL_YR, DIVISION_ID, MODEL, TRIM, STYLE_NM_WO_TRIM, MAKE_DESC, STYLE_ID)')
                      FROM DUAL;
                      
                      
                         select
                         DBMS_STATS.CREATE_EXTENDED_STATS
                         ('VLTN_PX', 'RK_JUNK_DIM2', '(RK_JUNK_DIM_ID, VS_VEH_TYPE_CD, VS_BODY_TYPE_CD,VEH_NORMAL,   RK_PRICE_SOLD,ODOM_PLUS_0, ACTV_FLG)')
                      FROM DUAL;
                      
                      
                         select
                         DBMS_STATS.CREATE_EXTENDED_STATS
                         ('VLTN_PX', 'VS_COMP_DATA_SPAT_PRT_REF_AGE', '(RK_MAKE_ENGINE_DIM_ID,RK_LOCATION_ID,RK_JUNK_DIM_ID)')
                      FROM DUAL;
                      
                      
                      
                      EXEC DBMS_STATS.GATHER_TABLE_STATS('VLTN_PX', 'RK_MAKE_ENGINE_DIM', NULL, CASCADE => TRUE, GRANULARITY=>'ALL');
                      
                      EXEC DBMS_STATS.GATHER_TABLE_STATS('VLTN_PX', 'RK_JUNK_DIM2', NULL, CASCADE => TRUE, GRANULARITY=>'ALL');
                      
                      EXEC DBMS_STATS.GATHER_TABLE_STATS('VLTN_PX', 'VS_COMP_DATA_SPAT_PRT_REF_AGE', NULL, CASCADE => TRUE, GRANULARITY=>'ALL');
                      
                      

                       

                      Then I executed the same query, and I get the following:

                       

                      VARIABLE P_ACTV_FLG VARCHAR2(1);
                      VARIABLE P_MDL_YR    NUMBER;
                      VARIABLE P_DIVISION_ID   NUMBER;
                      VARIABLE P_MODEL        VARCHAR2(100);
                      VARIABLE P_TRIM  VARCHAR2(100);
                      VARIABLE P_STYLE_NM_WO_TRIM  VARCHAR2(100);
                      VARIABLE P_VEH_COMP_SRC_CD1      VARCHAR2(100);
                      VARIABLE P_VEH_COMP_SRC_CD2      VARCHAR2(100);
                      VARIABLE P_VEH_COMP_SRC_CD3      VARCHAR2(100);
                      VARIABLE P_VEH_COMP_SRC_CD4      VARCHAR2(100);
                      VARIABLE P_SOLD_PRICE   NUMBER;
                      VARIABLE P_TAKE_PRICE NUMBER;
                      VARIABLE P_ASK_PRICE  NUMBER;
                      VARIABLE P_VS_BODY_TYPE_CD1  VARCHAR2(100);
                      VARIABLE P_VS_BODY_TYPE_CD2  VARCHAR2(100);
                      VARIABLE P_VS_BODY_TYPE_CD3  VARCHAR2(100);
                      VARIABLE P_MARKET_PRICE_FLG    VARCHAR2(100);
                      VARIABLE P_VEH_ODOM    NUMBER;
                      VARIABLE P_VEH_COMP_AGE_BASE    VARCHAR2(100);
                      VARIABLE P_VEH_COMP_LONGITUDE1 NUMBER;
                      VARIABLE P_VEH_COMP_LONGITUDE2 NUMBER;
                      VARIABLE P_VEH_COMP_LATITUDE1 NUMBER;
                      VARIABLE P_VEH_COMP_LATITUDE2 NUMBER;
                      VARIABLE SDO_GEOMETRY1 NUMBER;
                      VARIABLE SDO_GEOMETRY2 NUMBER;
                      VARIABLE SDO_POINT_TYPE1 NUMBER;
                      VARIABLE SDO_POINT_TYPE2 NUMBER;
                      VARIABLE SDO_POINT_TYPE3 NUMBER;
                      VARIABLE SDO_POINT_UNIT VARCHAR2(100);
                      VARIABLE SDO_POINT_DISTANCE NUMBER;
                        VARIABLE P_PSTL_CD varchar2(5);
                      VARIABLE MIN NUMBER;
                      VARIABLE MAX NUMBER;
                      EXEC :MIN := 36000;
                      EXEC :MAX  := 66000;
                        EXEC :P_PSTL_CD := '23294';
                      EXEC :P_ACTV_FLG := 'Y';
                      EXEC :P_VEH_ODOM        := 0;
                      EXEC :P_VEH_COMP_AGE_BASE    := '12-May-2013';
                      EXEC :SDO_POINT_DISTANCE := 500;
                      EXEC :SDO_GEOMETRY1 := 2001;
                      EXEC :SDO_GEOMETRY2 := 8307;
                      --
                      EXEC :P_MDL_YR  :=2012;
                      EXEC :P_DIVISION_ID  :=39;
                      EXEC :P_MODEL  :='Corolla';
                      EXEC :P_TRIM  :='LE';
                      EXEC :P_STYLE_NM_WO_TRIM  :='4dr Sdn Auto (Natl)';
                      EXEC :P_VEH_COMP_LONGITUDE1  :=73;
                      EXEC :P_VEH_COMP_LONGITUDE2  :=93;
                      EXEC :P_VEH_COMP_LATITUDE1  :=32;
                      EXEC :P_VEH_COMP_LATITUDE2  :=52;
                      EXEC :SDO_POINT_TYPE1  :=83.2177;
                      EXEC :SDO_POINT_TYPE2  :=42.1361;
                      --
                      EXEC :SDO_POINT_TYPE3 := 1;
                      EXEC :SDO_POINT_UNIT := 'unit=MILE';
                      SELECT /*+ STAR_TRANSFORMATION */
                      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
                      ----------------------------------------------------------                                                                                                                                            
                             1361  recursive calls                                                                                                                                                                          
                                0  db block gets                                                                                                                                                                            
                           153996  consistent gets                                                                                                                                                                          
                                1  physical reads                                                                                                                                                                           
                               96  redo size                                                                                                                                                                                
                             1104  bytes sent via SQL*Net to client                                                                                                                                                         
                              331  bytes received via SQL*Net from client                                                                                                                                                   
                                2  SQL*Net roundtrips to/from client                                                                                                                                                        
                                2  sorts (memory)                                                                                                                                                                           
                                0  sorts (disk)                                                                                                                                                                             
                               81  rows processed  
                      
                      

                      I don't see a difference from the previous execution plan before I gathered stats.  Did I miss something? On the execution plan, the cardinality on the fact table (VS_COMP_DATA_SPAT_PRT_REF_AGE) shows as 1. Shouldn't that be 81 since there were 81 rows processed?

                       

                      I think the 1048575 appears because your table is INTERVAL partitioned, not just range partitioned, and your predicate is a "greater than" which means Oracle needs to scan from a starting partition to the last potential partition (which is number 1048575)

                       

                      You are correct. Would it be a good practice to always use a range for the date? Would Oracle do less work if I provide a range vs using a "greater than" alone?

                      • 8. Re: PSTART/ PSTOP: key vs number
                        Rinne

                        So I tested getting rid of bind variables and got it to use the star_transformation hint. But it still uses the merge join cartesian below in a different area (lines 29,30). Is this joining between RK_JUNK_DIM2 and RK_LOCATION tables? these are two dimensional tables and can't be joined together. What is Oracle doing here?

                         

                        14:14:39 SQL>
                        14:14:39 SQL>   SHOW PARAMETER STAR_TRANSFORMATION_ENABLED;
                        14:14:39 SQL>
                        14:14:39 SQL> set arraysize 4000;
                        14:14:39 SQL>
                        14:14:39 SQL>
                        14:14:39 SQL> SELECT /*+ STAR_TRANSFORMATION */
                        14:14:39   2  DL_COMPARABLE_ID           ,
                        14:14:39   3  DL_VEH_ID                ,
                        14:14:39   4  VEH_SRC_ID               ,
                        14:14:39   5  VEH_COMP_VERIFD_CD       ,
                        14:14:39   6  VEH_COMP_SRC_CD          ,
                        14:14:39   7  VEH_COMP_SRC_TYP         ,
                        14:14:39   8  loc.VEH_COMP_LONGITUDE       ,
                        14:14:39   9  loc.VEH_COMP_LATITUDE        ,
                        14:14:39  10  VEH_COMP_AGE_BASE        ,
                        14:14:39  11  VEH_COMP_STATUS_CD       ,
                        14:14:39  12  ADV_PHONE                ,
                        14:14:39  13  ADV_PHONE1               ,
                        14:14:39  14  loc.CITY                     ,
                        14:14:39  15  loc.ST_CD                    ,
                        14:14:39  16  loc.PSTL_CD                  ,
                        14:14:39  17  ASK_PRICE                ,
                        14:14:39  18  TAKE_PRICE               ,
                        14:14:39  19  MARKET_PRICE             ,
                        14:14:39  20  SOLD_PRICE               ,
                        14:14:39  21  MARKET_PRICE_FLG         ,
                        14:14:39  22  DEALER_NM                ,
                        14:14:39  23  CONTACT_PERSON_NM        ,
                        14:14:39  24  DSTOCK_NUM               ,
                        14:14:39  25  loc.CCC_REGION               ,
                        14:14:39  26  loc.NADA_REGION              ,
                        14:14:39  27  INSPECTED_DT             ,
                        14:14:39  28  FLG_STOCK_PLATE          ,
                        14:14:39  29  loc.COUNTRY_CD               ,
                        14:14:39  30  ADV_PHONE_EXT            ,
                        14:14:39  31  ADV_PHONE1_EXT           ,
                        14:14:39  32  comparable0_.STYLE_ID                 ,
                        14:14:39  33  VIN                      ,
                        14:14:39  34   VS_VEH_TYPE_CD           ,
                        14:14:39  35   VS_BODY_TYPE_CD          ,
                        14:14:39  36   MAKE_DESC                ,
                        14:14:39  37  MODEL                    ,
                        14:14:39  38  MDL_YR                   ,
                        14:14:39  39  VEH_ODOM                 ,
                        14:14:39  40  ENGINE_CYL               ,
                        14:14:39  41  ENGINE_DISP              ,
                        14:14:39  42  ENGINE_FUEL              ,
                        14:14:39  43  ENGINE_ASP               ,
                        14:14:39  44  ENGINE_CARB              ,
                        14:14:39  45  VEH_COND_CD              ,
                        14:14:39  46  EXTR_COLOR               ,
                        14:14:39  47  STYLE_NM_WO_TRIM         ,
                        14:14:39  48  TRIM                     ,
                        14:14:39  49  NUM_DOORS                ,
                        14:14:39  50  DIVISION_ID              ,
                        14:14:39  51  STYLE_ENGINE_VARIANT_ID  ,
                        14:14:39  52  VEH_COMP_SRC_CD
                        14:14:39  53        FROM VS_COMP_DATA_SPAT_PRT_REF_AGE comparable0_,
                        14:14:39  54      RK_MAKE_ENGINE_DIM veh,
                        14:14:39  55        RK_JUNK_DIM2 dim,
                        14:14:39  56      RK_LOCATION LOC
                        14:14:39  57        WHERE
                        14:14:39  58        comparable0_.RK_MAKE_ENGINE_DIM_ID = veh.RK_MAKE_ENGINE_DIM_ID
                        14:14:39  59        and comparable0_.RK_JUNK_DIM_ID = DIM.RK_JUNK_DIM_ID
                        14:14:39  60      and comparable0_.RK_LOCATION_ID = loc.RK_LOCATION_ID
                        14:14:39  61        and dim.ACTV_FLG        ='Y'
                        14:14:39  62        AND veh.MDL_YR               =2012
                        14:14:39  63        AND veh.DIVISION_ID       =30
                        14:14:39  64        AND veh.MODEL        ='Quest'
                        14:14:39  65        AND veh.TRIM        ='S'
                        14:14:39  66        and veh.STYLE_NM_WO_TRIM ='4dr'
                        14:14:39  67        AND RK_PRICE_SOLD = 'Y'
                        14:14:39  68        and dim.veh_normal = 'Y'
                        14:14:39  69         and odom_plus_0 = 'Y'
                        14:14:39  70        AND comparable0_.VEH_COMP_AGE_BASE     >=to_date('12-May-2013', 'dd-Mon-YYYY')
                        14:14:39  71  and SDO_WITHIN_DISTANCE(loc.shape, get_long_lat_pt(118.3936, 33.9607), 'distance=500 unit=MILE')='TRUE'
                        14:14:39  72  and OPTION_ADJUSTMENT between 36000 and 66000
                        14:14:39  73  and  PACKAGE_ADJUSTMENT between 36000 and 66000
                        14:14:39  74  and  MILEAGE_ADJUSTMENT between 36000 and 66000
                        14:14:39  75  and  CONDITION_ADJUSTMENT between 36000 and 66000
                        14:14:39  76  and  CV_TOT_ADJUSTMENT between 36000 and 66000
                        14:14:39  77  and  MODEL_ADJUSTMENT between 36000 and 66000
                        14:14:39  78  ;
                        
                        233 rows selected.
                        
                        
                        Execution Plan
                        ----------------------------------------------------------                                                                                                                                             
                        Plan hash value: 1926257196                                                                                                                                                                            
                                                                                                                                                                                                                               
                        ---------------------------------------------------------------------------------------------------------------------------------------                                                                
                        | Id  | Operation                             | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                
                        ---------------------------------------------------------------------------------------------------------------------------------------                                                                
                        |   0 | SELECT STATEMENT                      |                               |     1 |   494 |    63   (4)| 00:00:01 |       |       |                                                                
                        |   1 |  TEMP TABLE TRANSFORMATION            |                               |       |       |            |          |       |       |                                                                
                        |   2 |   LOAD AS SELECT                      | SYS_TEMP_0FDA6EF86_D4A37CB2   |       |       |            |          |       |       |                                                                
                        |   3 |    PARTITION LIST SINGLE              |                               |     1 |    75 |     1   (0)| 00:00:01 |   KEY |   KEY |                                                                
                        |   4 |     PARTITION HASH SINGLE             |                               |     1 |    75 |     1   (0)| 00:00:01 |     2 |     2 |                                                                
                        |*  5 |      TABLE ACCESS BY LOCAL INDEX ROWID| RK_MAKE_ENGINE_DIM            |     1 |    75 |     1   (0)| 00:00:01 |   114 |   114 |                                                                
                        |   6 |       BITMAP CONVERSION TO ROWIDS     |                               |       |       |            |          |       |       |                                                                
                        |*  7 |        BITMAP INDEX SINGLE VALUE      | B_STYLE_NM_WO_TRIX            |       |       |            |          |   114 |   114 |                                                                
                        |*  8 |   HASH JOIN                           |                               |     1 |   494 |    56   (2)| 00:00:01 |       |       |                                                                
                        |   9 |    PARTITION RANGE ITERATOR           |                               |     1 |   304 |    51   (2)| 00:00:01 |   138 |1048575|                                                                
                        |* 10 |     TABLE ACCESS BY LOCAL INDEX ROWID | VS_COMP_DATA_SPAT_PRT_REF_AGE |     1 |   304 |    51   (2)| 00:00:01 |   138 |1048575|                                                                
                        |  11 |      BITMAP CONVERSION TO ROWIDS      |                               |       |       |            |          |       |       |                                                                
                        |  12 |       BITMAP AND                      |                               |       |       |            |          |       |       |                                                                
                        |  13 |        BITMAP MERGE                   |                               |       |       |            |          |       |       |                                                                
                        |  14 |         BITMAP KEY ITERATION          |                               |       |       |            |          |       |       |                                                                
                        |  15 |          BUFFER SORT                  |                               |       |       |            |          |       |       |                                                                
                        |  16 |           TABLE ACCESS STORAGE FULL   | SYS_TEMP_0FDA6EF86_D4A37CB2   |     1 |     6 |     2   (0)| 00:00:01 |       |       |                                                                
                        |* 17 |          BITMAP INDEX RANGE SCAN      | I_MAKE_DIM_ID_F_AGE           |       |       |            |          |   138 |1048575|                                                                
                        |  18 |        BITMAP MERGE                   |                               |       |       |            |          |       |       |                                                                
                        |  19 |         BITMAP KEY ITERATION          |                               |       |       |            |          |       |       |                                                                
                        |  20 |          BUFFER SORT                  |                               |       |       |            |          |       |       |                                                                
                        |* 21 |           TABLE ACCESS STORAGE FULL   | RK_JUNK_DIM2                  |    33 |   462 |     3   (0)| 00:00:01 |       |       |                                                                
                        |* 22 |          BITMAP INDEX RANGE SCAN      | I_JUNK_DIM_IDX_AGEA           |       |       |            |          |   138 |1048575|                                                                
                        |  23 |        BITMAP MERGE                   |                               |       |       |            |          |       |       |                                                                
                        |  24 |         BITMAP KEY ITERATION          |                               |       |       |            |          |       |       |                                                                
                        |  25 |          BUFFER SORT                  |                               |       |       |            |          |       |       |                                                                
                        |  26 |           TABLE ACCESS BY INDEX ROWID | RK_LOCATION                   |   107 |  9951 |     0   (0)| 00:00:01 |       |       |                                                                
                        |* 27 |            DOMAIN INDEX               | RK_LOCATION_SHAPE_IDXK        |       |       |     0   (0)| 00:00:01 |       |       |                                                                
                        |* 28 |          BITMAP INDEX RANGE SCAN      | I_LOC_ID_AGE                  |       |       |            |          |   138 |1048575|                                                                
                        |  29 |    MERGE JOIN CARTESIAN               |                               |  3509 |   651K|     5   (0)| 00:00:01 |       |       |                                                                
                        |  30 |     MERGE JOIN CARTESIAN              |                               |    33 |  3201 |     5   (0)| 00:00:01 |       |       |                                                                
                        |  31 |      TABLE ACCESS STORAGE FULL        | SYS_TEMP_0FDA6EF86_D4A37CB2   |     1 |    75 |     2   (0)| 00:00:01 |       |       |                                                                
                        |  32 |      BUFFER SORT                      |                               |    33 |   726 |     3   (0)| 00:00:01 |       |       |                                                                
                        |* 33 |       TABLE ACCESS STORAGE FULL       | RK_JUNK_DIM2                  |    33 |   726 |     3   (0)| 00:00:01 |       |       |                                                                
                        |  34 |     BUFFER SORT                       |                               |   107 |  9951 |     2   (0)| 00:00:01 |       |       |                                                                
                        |  35 |      TABLE ACCESS BY INDEX ROWID      | RK_LOCATION                   |   107 |  9951 |     5   (0)| 00:00:01 |       |       |                                                                
                        |* 36 |       DOMAIN INDEX                    | RK_LOCATION_SHAPE_IDXK        |       |       |     0   (0)| 00:00:01 |       |       |                                                                
                        ---------------------------------------------------------------------------------------------------------------------------------------                                                                
                                                                                                                                                                                                                               
                        Predicate Information (identified by operation id):                                                                                                                                                    
                        ---------------------------------------------------                                                                                                                                                    
                                                                                                                                                                                                                               
                           5 - filter("VEH"."TRIM"='S' AND "VEH"."MDL_YR"=2012 AND "VEH"."MODEL"='Quest')                                                                                                                      
                           7 - access("VEH"."STYLE_NM_WO_TRIM"='4dr')                                                                                                                                                          
                           8 - access("COMPARABLE0_"."RK_MAKE_ENGINE_DIM_ID"="C0" AND "COMPARABLE0_"."RK_JUNK_DIM_ID"="DIM"."RK_JUNK_DIM_ID" AND                                                                               
                                      "COMPARABLE0_"."RK_LOCATION_ID"="LOC"."RK_LOCATION_ID")                                                                                                                                  
                          10 - filter("OPTION_ADJUSTMENT"<=66000 AND "MILEAGE_ADJUSTMENT"<=66000 AND "CONDITION_ADJUSTMENT"<=66000 AND                                                                                         
                                      "MODEL_ADJUSTMENT"<=66000 AND "PACKAGE_ADJUSTMENT"<=66000 AND "CV_TOT_ADJUSTMENT"<=66000 AND "CV_TOT_ADJUSTMENT">=36000 AND                                                              
                                      "PACKAGE_ADJUSTMENT">=36000 AND "CONDITION_ADJUSTMENT">=36000 AND "MODEL_ADJUSTMENT">=36000 AND "MILEAGE_ADJUSTMENT">=36000                                                              
                                      AND "OPTION_ADJUSTMENT">=36000 AND "COMPARABLE0_"."VEH_COMP_AGE_BASE">=TO_DATE(' 2013-05-12 00:00:00', 'syyyy-mm-dd                                                                      
                                      hh24:mi:ss'))                                                                                                                                                                            
                          17 - access("COMPARABLE0_"."RK_MAKE_ENGINE_DIM_ID"="C0")                                                                                                                                             
                          21 - storage("ODOM_PLUS_0"='Y' AND "RK_PRICE_SOLD"='Y' AND "DIM"."ACTV_FLG"='Y' AND "DIM"."VEH_NORMAL"='Y')                                                                                          
                               filter("ODOM_PLUS_0"='Y' AND "RK_PRICE_SOLD"='Y' AND "DIM"."ACTV_FLG"='Y' AND "DIM"."VEH_NORMAL"='Y')                                                                                           
                          22 - access("COMPARABLE0_"."RK_JUNK_DIM_ID"="DIM"."RK_JUNK_DIM_ID")                                                                                                                                  
                          27 - access("MDSYS"."SDO_WITHIN_DISTANCE"("LOC"."SHAPE","GET_LONG_LAT_PT"(118.3936,33.9607),'distance=500 unit=MILE')='TRUE')                                                                        
                          28 - access("COMPARABLE0_"."RK_LOCATION_ID"="LOC"."RK_LOCATION_ID")                                                                                                                                  
                          33 - storage("DIM"."ACTV_FLG"='Y' AND "RK_PRICE_SOLD"='Y' AND "ODOM_PLUS_0"='Y' AND "DIM"."VEH_NORMAL"='Y')                                                                                          
                               filter("DIM"."ACTV_FLG"='Y' AND "RK_PRICE_SOLD"='Y' AND "ODOM_PLUS_0"='Y' AND "DIM"."VEH_NORMAL"='Y')                                                                                           
                          36 - access("MDSYS"."SDO_WITHIN_DISTANCE"("LOC"."SHAPE","GET_LONG_LAT_PT"(118.3936,33.9607),'distance=500 unit=MILE')='TRUE')                                                                        
                                                                                                                                                                                                                               
                        Note                                                                                                                                                                                                   
                        -----                                                                                                                                                                                                  
                           - star transformation used for this statement                                                                                                                                                       
                        
                        
                        Statistics
                        ----------------------------------------------------------                                                                                                                                             
                                257  recursive calls                                                                                                                                                                           
                                  0  db block gets                                                                                                                                                                             
                              50478  consistent gets                                                                                                                                                                           
                                  0  physical reads                                                                                                                                                                            
                                  0  redo size                                                                                                                                                                                 
                              18808  bytes sent via SQL*Net to client                                                                                                                                                          
                                998  bytes received via SQL*Net from client                                                                                                                                                    
                                  2  SQL*Net roundtrips to/from client                                                                                                                                                         
                                  2  sorts (memory)                                                                                                                                                                            
                                  0  sorts (disk)                                                                                                                                                                              
                                233  rows processed