Friday, November 15, 2013

Playing with Oracle Spatial - An important consideration when using SDO_NN operator


I've been playing with Oracle Spatial quite a bit lately and ran into some interesting things that I  plan to write about in the next couple of posts.

This post covers an important consideration, I think, one should make when using SDO_NN spatial operator with sdo_num_res parameter.

But first, just briefly on the operator itself. SDO_NN operator is used to identify the nearest neighbors for a particular geometry. The full operator synopsis can be found in the official documentation

Please note, the database I use in the examples below is Oracle 11.2.0.3.0 Enterprise Edition with no additional patches applied and default Oracle Spatial settings.

Let's consider the following example, there is a table RESTAURANTS having only an id and a geometry column (point).

 CREATE TABLE restaurants
(
 id NUMBER NOT NULL,
 point MDSYS.SDO_GEOMETRY
); 

ALTER TABLE restaurants ADD CONSTRAINT restaurants_pk PRIMARY KEY (id);


Let's create a spatial index on restaruants.point column:

 INSERT INTO user_sdo_geom_metadata 
    (   TABLE_NAME
      , COLUMN_NAME
      , DIMINFO
      ,   SRID)
    VALUES 
   (   'restaurants'
      , 'POINT'
      , SDO_DIM_ARRAY(   
           SDO_DIM_ELEMENT('X', 0, 10000000, 0.005),
           SDO_DIM_ELEMENT('Y', 0, 10, 0.005)
        )
      , null   -- SRID
);
 
CREATE INDEX restaurants_sidx ON restaurants(point) INDEXTYPE IS MDSYS.SPATIAL_INDEX; 


For simplicity let's say there are 500,000 restaurants distributed on a single line (x-axis of two dimensional coordinate system, y=0, x=1,2,3,...500,000).

INSERT INTO restaurants 
(SELECT rownum   --id
    ,   SDO_GEOMETRY(
                      2001 -- 2 dimensional point
                   ,  null  -- SDO SRID
                   ,  SDO_POINT_TYPE(rownum,0,null) –-x=rownum, y=0
                   ,  null
                   ,  null
        )
 FROM dual CONNECT BY rownum<=500000
);

In order to find the first 5 nearest neighbors to point (10,0) one could use the following spatial query:

SQL> SELECT p.id, sdo_nn_distance(1) distance
  2  FROM restaurants p
  3  WHERE SDO_NN(p.point,
  4                      SDO_GEOMETRY(
  5                                     2001 -- 2 dimensional point
  6                             ,       null
  7                             ,       SDO_POINT_TYPE(10,0,null)
  8                             ,       null
  9                             ,       null
 10                             ),
 11                      'sdo_num_res=5',
 12                      1
 13                     )='TRUE'
 14* ORDER BY 2;

        ID   DISTANCE
---------- ----------
        10          0
         9          1
        11          1
        12          2
         8          2

SQL>

Now suppose each restaurant has 5 menu items that are stored in MENU_ITEMS table. The table has 4 columns, a unique identifier ID; a reference to RESTAURANTS table, rest_id; an identifier that identifies an item within the restaurant menu, item_number; and lastly a description, 100 characters long column that I use in this example to make this table a bit bigger.

CREATE TABLE menu_items
(id NUMBER NOT NULL,
 rest_id NUMBER NOT NULL,
 item_number NUMBER NOT NULL,
 description varchar2(100)
);

ALTER TABLE menu_items ADD CONSTRAINT menu_items_pk PRIMARY KEY (id); 

ALTER TABLE menu_items ADD CONSTRAINT menu_items_rest_FK FOREIGN KEY (rest_id) REFERENCES restaurants(id); 

CREATE INDEX menu_items_rest_idx ON menu_items(rest_id);

 
INSERT INTO menu_items 
(SELECT rownum
      , b.rn
      , a.rn 
      , rpad(rownum,100,'A')
 FROM 
     (SELECT rownum rn FROM dual CONNECT BY rownum<=5) a, --5 menu items per rest.
     (SELECT rownum rn FROM dual CONNECT BY rownum<=500000) b
);

commit;

Suppose you want to get the menu items of the top 5 nearest restaurants to a particular location (10,0) for an example. One way would be with the following spatial query (but first of course collect the necessary stats):

BEGIN
        DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'restaurants',cascade=>TRUE);
        DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'menu_items',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');
END;

SELECT t1.id
     , t2.id
     , t2.restaurants_id
     , t2.item_number
FROM restaurants t1
   , menu_items t2
WHERE t1.id=t2.restaurants_id
  AND SDO_NN(t1.point,
              SDO_GEOMETRY(
                            2001 -- 2 dimensional point
                         ,  null 
                         ,  SDO_POINT_TYPE(10,0,null)
                         ,  null
                         ,  null
              ),
             'sdo_num_res=5',
             1 
       )='TRUE'
ORDER BY t1.id, t2.item_number;


The query produces the desired result. However, let's look how it performed. I re-run the query, but this time I used gather_plan_statistics hint to get the execution statistics for the query.

SQL>SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
  2  FROM restaurants t1
  3     , menu_items t2
  4  WHERE t1.id=t2.restaurants_id
  5    AND SDO_NN(t1.point,
  6                      SDO_GEOMETRY(
  7                                     2001 -- 2 dimensional point
  8                             ,       null
  9                             ,       SDO_POINT_TYPE(10,0,null)
 10                             ,       null
 11                             ,       null
 12                             ),
 13                      'sdo_num_res=5',
 14                      1
 15                     )='TRUE'
 16  ORDER BY t1.id, t2.item_number; 

        ID         ID RESTAURANTS_ID ITEM_NUMBER
---------- ---------- -------------- -----------
         8          8              8           1
         8     500008              8           2
         8    1000008              8           3
         8    1500008              8           4
         8    2000008              8           5
         9          9              9           1
         9     500009              9           2
         9    1000009              9           3
         9    1500009              9           4
         9    2000009              9           5
        10         10             10           1
        10     500010             10           2
        10    1000010             10           3
        10    1500010             10           4
        10    2000010             10           5
        11         11             11           1
        11     500011             11           2
        11    1000011             11           3
        11    1500011             11           4
        11    2000011             11           5
        12         12             12           1
        12     500012             12           2
        12    1000012             12           3
        12    1500012             12           4
        12    2000012             12           5

25 rows selected.


The query took 1.32 seconds to complete as you can see from the execution statistics:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gwpqub3k0awqm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id,
t2.item_number FROM restaurants t1    , menu_items t2 WHERE
t1.id=t2.restaurants_id   AND SDO_NN(t1.point,     SDO_GEOMETRY(
2001 -- 2 dimensional point     ,  null     ,
SDO_POINT_TYPE(10,0,null)     ,  null     ,  null     ),
'sdo_num_res=5',     1    )='TRUE' ORDER BY t1.id, t2.item_number

Plan hash value: 2076547507


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |     25 |00:00:01.32 |   41540 |  41465 |       |       |          |
|   1 |  SORT ORDER BY                |                  |      1 |  24678 |     25 |00:00:01.32 |   41540 |  41465 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN                   |                  |      1 |  24678 |     25 |00:00:01.32 |   41540 |  41465 |  1517K|  1517K|  886K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| RESTAURANTS      |      1 |   5000 |      5 |00:00:00.01 |      64 |      0 |       |       |          |
|*  4 |     DOMAIN INDEX              | RESTAURANTS_SIDX |      1 |        |      5 |00:00:00.01 |      63 |      0 |       |       |          |
|   5 |    TABLE ACCESS FULL          | MENU_ITEMS       |      1 |   2500K|   2500K|00:00:00.62 |   41476 |  41465 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------


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

   2 - access("T1"."ID"="T2"."RESTAURANTS_ID")
   4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='
              TRUE')


29 rows selected.

SQL> 


The problem

Well, one could say that 1.32 seconds is not that bad. However, if you look at the plan more carefully you can notice that CBO used a HASH JOIN method to join RESTAURANTS and MENU_ITEMS tables and as a result it performed full table scan on MENU_ITEMS table. Now imagine if MENU_ITEMS table was quite big (suppose you have stored the items of all restaurants across US)?!

What made CBO to pick a HASH JOIN? Look at the estimated (5,000) vs. actual rows (5) selected from RESTAURANTS table. We were only after the 5 nearest neighbors. We used sdo_num_res=5 in our SDO_NN call. One could expect that CBO would recognize that fact and estimate cardinality of 5. But, no, CBO applied selectivity of 1% (500,000 restaurants x 1% = 5,000)

Why selectivity of 1%?

SDO_NN is an operator that is bound to the implementation of the function NN that is part of PRVT_IDX package owned by MDSYS.

By default, statistics for PRVT_IDX package is defined by SDO_STATISTICS type. That is Extensible Optimizer feature is used to define how cardinality and the cost will be calculated for functions/procedures defined in the package. (if statistics are not associated, CBO uses default selectivity of 1%)

Seems like the logic implemented in ODCIStatsSelectivity() function is not good enough to detect that we are only after the first N rows, as defined with SDO_NUM_RES parameter.

You can clearly see this if you create 10053 trace file in the section where MDSYS.SDO_STATISTICS.ODCIStatsSelectivity procedure is used to calculate selectivity.



  MDSYS.SDO_STATISTICS.ODCIStatsFunctionCost returned:
    CPUCost      : 100000000000000
    IOCost       : 100000000000000
    NetworkCost  : -1
  Calling user-defined selectivity function...
    predicate: "MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE'
  declare
     sel number;
     obj0 "MDSYS"."SDO_GEOMETRY" := "MDSYS"."SDO_GEOMETRY"(NULL, NULL, NULL, NULL, NULL);

    begin
      :1 := "MDSYS"."SDO_STATISTICS".ODCIStatsSelectivity(
                     sys.ODCIPREDINFO('MDSYS',
                            'PRVT_IDX',
                            'NN',
                            45),
                     sel,
                     sys.ODCIARGDESCLIST(sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(2, 'RESTAURANTS', 'TEST', '"POINT"', NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL)),
                     :3,
                     :4
                     , obj0, "MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL), :5,
                     sys.ODCIENV(:6,:7,:8,:9));
      if sel IS NULL then
        :2 := -1.0;
      else
        :2 := sel;
      end if;
      exception
        when others then
          raise;
    end;
Bind :3 Value 'TRUE'
Bind :4 Value 'TRUE'
Bind :5 Value 'sdo_num_res=5'
ODCIEnv Bind :6 Value 0
ODCIEnv Bind :7 Value 0
ODCIEnv Bind :8 Value 0
ODCIEnv Bind :9 Value 3
  MDSYS.SDO_STATISTICS.ODCIStatsSelectivity returned selectivity: 1.00000000%
  Table: RESTAURANTS  Alias: T1
    Card: Original: 500000.000000  Rounded: 5000  Computed: 5000.00  Non Adjusted: 5000.00




If statistics are not associated CBO would also use selectivity of 1%. So what is the differences? Under what circumstances one could take advantage of the extensible optimizer feature and generate better selectivity when SDO_NN operator is used along with sdo_num_res parameter? I couldn't find any article or documentation piece that will help me answer these questions.

What I do know however, is that it shouldn't be that difficult to implement a piece of logic that will make SDO_STATISTICS.ODCIStatsSelectivity procedure generate more accurate numbers.

As a matter of fact, I played a bit with this.

Disclaimer: Please be aware the following examples are for demonstration purposes only and shouldn't be used in an actual, supported, database environment. The logic I show below is quite simple and is for illustration only. Please contact Oracle Support if you want to implement something similar in your environment.

I created a simple type named SDO_STATISTICS_CUSTOM under MDSYS schema using the Extensible Optimizer interface (link )

I implemented ODCIStatsSelectivity procedure as shown below (one more time, this implementation is to demonstrate that with appropriate logic ODCIStatsSelectivity can return desired values):



STATIC FUNCTION ODCIStatsSelectivity (
                      pred_info      IN  SYS.ODCIPredInfo,
                      sel          OUT NUMBER,
                      args           IN  SYS.ODCIArgDescList,
                      p_start          IN  VARCHAR2,
                      p_stop           IN  VARCHAR2,
                      geometry1      IN MDSYS.SDO_GEOMETRY,
        geometry2      IN MDSYS.SDO_GEOMETRY,
               param          IN VARCHAR2,
                      env            IN  SYS.ODCIEnv
                      ) RETURN NUMBER IS
      BEGIN
   
    *******************  
         if param=NULL then 
  sel:=1;
  else if lower(substr(param,1,instr(param,'=')-1)) = 'sdo_num_res' then
   SELECT  decode(num_rows,null,1,
     decode(to_number(trim(substr(param,instr(param,'=')+1))),0,1,to_number(trim(substr(param,instr(param,'=')+1)))/num_rows)*100 
    )
           INTO   sel
           FROM   dba_tables where owner=args(3).TABLESCHEMA and table_name=args(3).TABLENAME;

   if sel>100 then 
    sel:=100 ;
   end if;   

  end if;
  end if;
      
****************     
          
         RETURN ODCIConst.success;
      END ODCIStatsSelectivity; 
 
 

The logic I chose is simple, if sdo_num_res parameter is defined, get the number of records from the statistics available for the table and set the selectivity variable (sel) to (sdo_num_res/num_rows)*100.

Let's see how it works:


SQL> EXPLAIN PLAN FOR
  2  SELECT t1.id, t2.id, t2.restaurants_id, t2.item_number
  3  FROM restaurants t1
  4     , menu_items t2
  5  WHERE t1.id=t2.restaurants_id
  6    AND SDO_NN(t1.point,
  7                           SDO_GEOMETRY(
  8                                          2001 -- 2 dimensional point
  9                                  ,       null
 10                                  ,       SDO_POINT_TYPE(10,0,null)
 11                                  ,       null
 12                                  ,       null
 13                                  ),
 14                           'sdo_num_res=5',
 15                           1
 16                          )='TRUE'
 17* ORDER BY t1.id, t2.item_number
SQL> /

Explained.

SQL> set line 200 pagesize 9999
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2341889131

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |    25 |  1000 |    36   (3)| 00:00:01 |
|   1 |  SORT ORDER BY                |                            |    25 |  1000 |    36   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                |                            |    25 |  1000 |    35   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| RESTAURANTS                |     5 |    80 |     0   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX              | RESTAURANTS_SIDX           |       |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| MENU_ITEMS                 |     5 |   120 |     7   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | MENU_ITEMS_RESTAURANTS_IDX |     5 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,N
              ULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
   6 - access("T1"."ID"="T2"."RESTAURANTS_ID")

20 rows selected.


As you can see the cardinality is correctly calculated which resulted CBO to produce better execution plan. With this plan in place, the query completes in less than 1 cs.

If you look into 10053 trace file you can also see that the calculated selectivity is 0.001%:




  MDSYS.SDO_STATISTICS_CUSTOM.ODCIStatsFunctionCost returned:
    CPUCost      : 10000
    IOCost       : 10000
    NetworkCost  : 10000
  Calling user-defined selectivity function...
    predicate: "MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE'
  declare
     sel number;
     obj0 "MDSYS"."SDO_GEOMETRY" := "MDSYS"."SDO_GEOMETRY"(NULL, NULL, NULL, NULL, NULL);

    begin
      :1 := "MDSYS"."SDO_STATISTICS_CUSTOM".ODCIStatsSelectivity(
                     sys.ODCIPREDINFO('MDSYS',
                            'PRVT_IDX',
                            'NN',
                            45),
                     sel,
                     sys.ODCIARGDESCLIST(sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(2, 'RESTAURANTS', 'TEST', '"POINT"', NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL)),
                     :3,
                     :4
                     , obj0, "MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL), :5,
                     sys.ODCIENV(:6,:7,:8,:9));
      if sel IS NULL then
        :2 := -1.0;
      else
        :2 := sel;
      end if;
      exception
        when others then
          raise;
    end;
Bind :3 Value 'TRUE'
Bind :4 Value 'TRUE'
Bind :5 Value 'sdo_num_res=5'
ODCIEnv Bind :6 Value 0
ODCIEnv Bind :7 Value 0
ODCIEnv Bind :8 Value 0
ODCIEnv Bind :9 Value 3
  MDSYS.SDO_STATISTICS_CUSTOM.ODCIStatsSelectivity returned selectivity: 0.00100000%


Anyway, since the current implementation of SDO_STATISTICS type returns selectivity of 1%, the next logical question is what one can do in order to workaround the problem.

Workaround

The problem can be addressed by providing CBO that extra bit of information, that is how many rows we expect to get back after applying SDO_NN operator. In order to do this one could consider using the cardinality hint or rewrite the query and add one more predicate ROWNUM<=5 that would tell CBO how many rows we are expecting to select from RESTAURANTS table. (there may be other strategies available too)

Use cardinality hint:

SELECT /*+ cardinality(t1 5) gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
FROM restaurants t1
   , menu_items t2
WHERE t1.id=t2.restaurants_id
  AND SDO_NN(t1.point,
              SDO_GEOMETRY(
                            2001 -- 2 dimensional point
                         ,  null 
                         ,  SDO_POINT_TYPE(10,0,null)
                         ,  null
                         ,  null
              ),
             'sdo_num_res=5',
             1 
      )='TRUE'
ORDER BY t1.id, t2.item_number;


The execution statistics in this case look like:

Plan hash value: 2341889131


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |      1 |        |     25 |00:00:00.01 |      92 |      5 |       |       |          |
|   1 |  SORT ORDER BY                |                            |      1 |     25 |     25 |00:00:00.01 |      92 |      5 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS                |                            |      1 |     25 |     25 |00:00:00.01 |      92 |      5 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| RESTAURANTS                |      1 |      5 |      5 |00:00:00.01 |      55 |      0 |       |       |          |
|*  4 |     DOMAIN INDEX              | RESTAURANTS_SIDX           |      1 |        |      5 |00:00:00.01 |      54 |      0 |       |       |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| MENU_ITEMS                 |      5 |      5 |     25 |00:00:00.01 |      37 |      5 |       |       |          |
|*  6 |     INDEX RANGE SCAN          | MENU_ITEMS_RESTAURANTS_IDX |      5 |      5 |     25 |00:00:00.01 |      12 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
   6 - access("T1"."ID"="T2"."RESTAURANTS_ID")


29 rows selected. 


Rewrite the query and use ROWNUM<=n predicate where n is the number associated with sdo_num_rows parameter:

SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
FROM (SELECT * 
      FROM restaurants 
      WHERE
           SDO_NN(point,
                  SDO_GEOMETRY(
                                   2001 -- 2 dimensional point
                                ,  null 
                                ,  SDO_POINT_TYPE(10,0,null)
                                ,  null
                                ,  null
                    ),
                 'sdo_num_res=5',
                 1 
           )='TRUE'
           AND rownum<=5
    ) t1
   , menu_items t2
WHERE t1.id=t2.restaurants_id    
ORDER BY t1.id, t2.item_number;


… and the execution statistics :

Plan hash value: 3570932640

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |      1 |        |     25 |00:00:00.01 |     101 |       |       |          |
|   1 |  SORT ORDER BY                   |                            |      1 |     25 |     25 |00:00:00.01 |     101 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS                   |                            |      1 |        |     25 |00:00:00.01 |     101 |       |       |          |
|   3 |    NESTED LOOPS                  |                            |      1 |     25 |     25 |00:00:00.01 |      76 |       |       |          |
|   4 |     VIEW                         |                            |      1 |      5 |      5 |00:00:00.01 |      64 |       |       |          |
|*  5 |      COUNT STOPKEY               |                            |      1 |        |      5 |00:00:00.01 |      64 |       |       |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| RESTAURANTS                |      1 |      5 |      5 |00:00:00.01 |      64 |       |       |          |
|*  7 |        DOMAIN INDEX              | RESTAURANTS_SIDX           |      1 |        |      5 |00:00:00.01 |      63 |       |       |          |
|*  8 |     INDEX RANGE SCAN             | MENU_ITEMS_RESTAURANTS_IDX |      5 |      5 |     25 |00:00:00.01 |      12 |       |       |          |
|   9 |    TABLE ACCESS BY INDEX ROWID   | MENU_ITEMS                 |     25 |      5 |     25 |00:00:00.01 |      25 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------

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

   5 - filter(ROWNUM<=5)
   7 - access("MDSYS"."SDO_NN"("POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
   8 - access("T1"."ID"="T2"."RESTAURANTS_ID")


34 rows selected.


As you can see, Oracle CBO made correct cardinality estimates in the last two examples and used NESTED LOOPS method to join both tables that resulted with performance improvement.

I hope you got some valuable information out of this post.