exec dbms_random.seed(1);

create table points(id, x, y) as
select level, dbms_random.value * 10, dbms_random.value * 10
from dual
connect by level <= 2222;

with t as
(
select t.*,
       (select count(*)
        from points
        where x between t.x - 2 and t.x + 2 and y between t.y - 2 and t.y + 2) cnt
from points t
)
select sum(cnt) from t;

  SUM(CNT)
----------
    636356

Elapsed: 00:00:00.94

with t as
(
select *
from points
model
  dimension by(x, y)
  measures(0 as cnt)
  rules
  (cnt [any, any] order by x, y = count(*)[x between cv(x) - 2 and cv(x) + 2, y between cv(y) - 2 and cv(y) + 2])
)
select sum(cnt) from t;

  SUM(CNT)
----------
    636356

Elapsed: 00:00:01.75

with t as
(
select --+ NO_XML_QUERY_REWRITE
       id,
       x,
       y,
       xmlcast(
         xmlquery(
           'count($D/ROW/VAL[./X >= $X - 2 and ./X <= $X +2 and ./Y >= $Y -2 and ./Y <= $Y +2])'
           passing xmlelement("ROW", (xmlagg(xmlelement(val, xmlelement(x, x), xmlelement(y, y))) over())) as d, x as x, y as y
           returning content) as number) as cnt
from points
)
select sum(cnt) from t;

  SUM(CNT)
----------
    636356

Elapsed: 00:00:43.56

with t as
(
select max(connect_by_root id) id, max(connect_by_root x) x, max(connect_by_root y) y, count(*) cnt
from points t
connect by level <= 2 and id <> prior id and x between prior x - 2 and prior x + 2 and y between prior y - 2 and prior y + 2
group by connect_by_root id, connect_by_root x, connect_by_root y
)
select sum(cnt) from t;

  SUM(CNT)
----------
    636356

Elapsed: 00:02:39.95

with t as
(
select ...
from points t
unpivot ...

)
select sum(cnt) from t;

  SUM(CNT)
----------
    636356

Elapsed: 00:00:05.82

select id_ id, x_ x, y_ y, cnt
from points t
unpivot (id for type in (id as 'original', id as 'new')) upvt
match_recognize
(
  order by type desc, x, y
  measures
    --match_number() mn,
    --classifier() cls,
    first(id) id_,
    first(x) x_,
    first(y) y_,
    count(p.type) cnt
   after match skip to next row
   pattern (strt (p|pp)+)
   define
     strt as strt.type = 'original',
     p as p.type = 'new' and
          p.x >= first(x) - 2 and p.x <= first(x) + 2 and
          p.y >= first(y) - 2 and p.y <= first(y) + 2
) mr
order by 1;

select id_ id, x_ x, y_ y, sum(dist) dist
from points t
unpivot (id for type in (id as 'original', id as 'new')) upvt
match_recognize
(
  order by type desc, x, y
  measures
    --match_number() mn,
    --classifier() cls,
    first(id) id_,
    first(x) x_,
    first(y) y_,
    sqrt(power(p.x - first(x), 2) + power(p.y - first(y), 2)) dist
   all rows per match
   after match skip to next row
   pattern (strt (p|pp)+)
   define
     strt as strt.type = 'original',
     p as p.type = 'new'
) mr
group by id_, x_, y_
order by 1;

select id_ id, x_ x, y_ y, max(cnt) cnt, sum(dist) dist
from points t
unpivot (id for type in (id as 'original', id as 'new')) upvt
match_recognize
(
  order by type desc, x, y
  measures
    --match_number() mn,
    --classifier() cls,
    first(id) id_,
    first(x) x_,
    first(y) y_,
    count(p2.type) cnt,
    sqrt(power(p.x - first(x), 2) + power(p.y - first(y), 2)) dist
   all rows per match
   after match skip to next row
   pattern (strt (p2|p_|pp)+)
   subset p = (p2, p_)
   define
     strt as strt.type = 'original',
     p2 as p2.type = 'new' and
          p2.x >= first(x) - 2 and p2.x <= first(x) + 2 and
          p2.y >= first(y) - 2 and p2.y <= first(y) + 2,     
     p_ as p_.type = 'new'
) mr
group by id_, x_, y_
order by 1;

select t.*,
       (select count(*) from points where x between t.x and t.x + 2 and y between t.y and t.y + 2) cnt
from points t;