SQL> CREATE INDEX act_idx ON activities(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

SQL> CREATE INDEX rdsmjr_idx ON roads_major(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

SQL> CREATE INDEX nat_idx ON natural(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

************************************************************************ PART B: Creating and Using Spatial Indexes - 1 ************************************************************************

SQL> select a2.id as A2ID, a1.id as A1ID from activities a1, activities a2 where a2.id = 1 and mdsys.SDO_NN(a1.geom, a2.geom, 'sdo_num_res=3') = 'TRUE';

      A2ID       A1ID
---------- ----------
         1          1
         1        500
         1        568

		 
************************************************************************ PART B: Creating and Using Spatial Indexes - 2 ************************************************************************		 

SQL> select a.id, r.osm_id from activities a, roads_major r where mdsys.SDO_NN(a.geom, r.geom, 'sdo_num_res=1') = 'TRUE';
.....
2625 rows selected.

************************************************************************ PART B: Creating and Using Spatial Indexes - 3 ************************************************************************
		 
SQL> select r.osm_id as Road_OSM_Id, r.name as Road_Name, n.osm_id as WaterFeature_OSM_Id from roads_major r, natural n where mdsys.SDO_OVERLAPS(r.geom, n.geom) = 'TRUE' and n.type='water';

ROAD_OSM_ID ROAD_NAME                                        WATERFEATURE_OSM_ID
----------- ------------------------------------------------ -------------------
   32808786 Calle De Travera Yagua                                      25339712
   31615634                                                             25339712
   31615631                                                             25339712
   48389801 Nationale No 1                                              48392194
   48271578                                                             48483282
   48565472 Nationale No 1                                              48565469
   48570359                                                             48570801
   31623574 Carretera Duarte                                            48770175
   49067956 Carretera Vincent                                           49354786

9 rows selected.

************************************************************************ PART B: Creating and Using Spatial Indexes - 4 ************************************************************************		 

SQL> select n1.osm_id,n1.type from natural n1, natural n2 where n2.type = 'riverbank' and mdsys.SDO_OVERLAPS(n1.geom, n2.geom) = 'TRUE' and n1.type='riverbank';

    OSM_ID TYPE
---------- ----------------
  48484177 riverbank
  48484044 riverbank
  48484033 riverbank
  48467295 riverbank
  48466867 riverbank
  48466073 riverbank
  48484033 riverbank
  48465496 riverbank
  48472800 riverbank
  48472954 riverbank
  48471719 riverbank

    OSM_ID TYPE
---------- ----------------
  48473090 riverbank
  48472800 riverbank
  48472954 riverbank
  48600858 riverbank
  48600315 riverbank
  48600964 riverbank
  48600640 riverbank
  48600858 riverbank
  48600640 riverbank

20 rows selected.

************************************************************************ PART B: Creating and Using Spatial Indexes - 5 ************************************************************************		 

SQL> select n1.osm_id,n1.type from natural n1, natural n2 where n2.type='forest' and mdsys.SDO_TOUCH (n1.geom, n2.geom) = 'TRUE' and n1.type='riverbank';

    OSM_ID TYPE
---------- ----------------
  48705322 riverbank
  50064556 riverbank
  48528762 riverbank
  48368967 riverbank
  48612499 riverbank
  48647151 riverbank
  48664549 riverbank
  53622443 riverbank
  53622514 riverbank
  53622443 riverbank
  53622514 riverbank

    OSM_ID TYPE
---------- ----------------
  53622443 riverbank
  53622514 riverbank
  50788697 riverbank
  50788704 riverbank
  50224264 riverbank
  50788697 riverbank
  50796588 riverbank
  50788704 riverbank
  50796588 riverbank
  50796591 riverbank
  48368967 riverbank

22 rows selected.

************************************************************************ PART C: Impact on Query Response Time ************************************************************************		 

SQL> set timing on;

************************************************************************ PART C: Impact on Query Response Time - 1 ************************************************************************		 

SQL> select n1.osm_id, n1.type from natural n1, natural n2 where SDO_GEOM.WITHIN_DISTANCE(n1.geom,1, n2.geom, .0005) = 'TRUE' and n1.type ='park' and n2.type='park';  
.....
2942 rows selected.

Elapsed: 00:00:12.76

************************************************************************ PART C: Impact on Query Response Time - 2 ************************************************************************		 

SQL> select n1.osm_id, n1.type from natural n1, natural n2 where SDO_WITHIN_DISTANCE(n1.geom,n2.geom, 'distance = 1') = 'TRUE' and n1.type ='park' and n2.type='park';  
.....
2942 rows selected.

Elapsed: 00:00:04.99

There is a significant difference between the execution times of first and second query of part C. The second query is faster and hence it is efficient to execute. 
The SDO_WITHIN_DISTANCE function has better performance because it uses the index. Thus the second query takes only 4.99 secs in comparison to 12.76 secs of first query.