VECTOR: who bats like Karthik Raman?

The "AI" in Oracle AI Database is mostly about one data type: VECTOR, a list of numbers, and fast ways to find the vectors nearest to another one. In real applications the vectors come from an AI model (an "embedding" of a sentence, an image, a product), and "nearest" means "most similar in meaning". That's how semantic search works.

You don't need a model to understand the SQL, though. Here we'll make the vectors ourselves from batting statistics, and ask which batters are most similar.

The idea

Describe every batter with three numbers, each roughly between 0 and 1:

Each batter becomes a point in three dimensions. Batters who play alike are close together, and the straight-line (Euclidean) distance between two points says how alike they are.

The test case

Using both seasons, and only batters who faced at least 100 balls, which five batters are the closest match to Karthik Raman?

Leeds' two openers bat most like Chennai's: quick, lots of boundaries, not many dots. Flattering for Leeds or for Karthik, depending on which dressing room you ask.

Way 1: VECTOR and VECTOR_DISTANCE

26ai only

with faced as (
  select batter_id,
         count(*)                                         as balls,
         sum(runs_batter)                                 as runs,
         sum(is_four + is_six)                            as boundaries,
         sum(case when runs_batter = 0 then 1 else 0 end) as dots
  from   cr_balls_v
  where  extra_type is null or extra_type <> 'WD'
  group  by batter_id
  having count(*) >= 100
),
styles as (
  select batter_id,
         to_vector('[' || to_char(round(runs / balls / 2, 4), 'FM0.0000') || ','
                       || to_char(round(boundaries / balls, 4), 'FM0.0000') || ','
                       || to_char(round(dots / balls, 4), 'FM0.0000') || ']',
                   3, float64) as style
  from   faced
),
karthik as (
  select s.style
  from   styles s
  join   cr_players p on p.player_id = s.batter_id
  where  p.player_name = 'Karthik Raman'
)
select p.player_name,
       round(cast(vector_distance(s.style, k.style, euclidean) as number), 4) as distance
from   styles s
cross  join karthik k
join   cr_players p on p.player_id = s.batter_id
where  p.player_name <> 'Karthik Raman'
order  by distance, p.player_name
fetch  first 5 rows only;

• TO_VECTOR('[0.75,0.23,0.36]', 3, FLOAT64) turns text into a vector of 3 dimensions stored as 64-bit floating point numbers. (The default, FLOAT32, uses half the space and is what embeddings normally use; we want exact agreement with the 19c version below, so 64-bit it is.)
• TO_CHAR(..., 'FM0.0000') writes 0.7500 rather than Oracle's default .75, because the vector text has to be valid JSON-style numbers.
• VECTOR_DISTANCE(a, b, EUCLIDEAN) is the straight-line distance. Other metrics: COSINE (the usual one for text embeddings: compares direction, not length), DOT, MANHATTAN.
• CAST(... AS NUMBER) because the distance comes back as BINARY_DOUBLE, which some tools print as 1.49E-002.

With thousands of rows you'd add a vector index (create vector index ... organization neighbor partitions or inmemory neighbor graph) so the nearest-neighbour search doesn't compare against every row. For 29 batters, scanning all of them is instant.

Way 2: the same distance with arithmetic (19c)

with faced as (
  select batter_id,
         count(*)                                         as balls,
         sum(runs_batter)                                 as runs,
         sum(is_four + is_six)                            as boundaries,
         sum(case when runs_batter = 0 then 1 else 0 end) as dots
  from   cr_balls_v
  where  extra_type is null or extra_type <> 'WD'
  group  by batter_id
  having count(*) >= 100
),
styles as (
  select batter_id,
         round(runs / balls / 2, 4)    as pace,
         round(boundaries / balls, 4)  as boundary_rate,
         round(dots / balls, 4)        as dot_rate
  from   faced
),
karthik as (
  select s.*
  from   styles s
  join   cr_players p on p.player_id = s.batter_id
  where  p.player_name = 'Karthik Raman'
)
select p.player_name,
       round(sqrt(  power(s.pace          - k.pace, 2)
                  + power(s.boundary_rate - k.boundary_rate, 2)
                  + power(s.dot_rate      - k.dot_rate, 2)), 4) as distance
from   styles s
cross  join karthik k
join   cr_players p on p.player_id = s.batter_id
where  p.player_name <> 'Karthik Raman'
order  by distance, p.player_name
fetch  first 5 rows only;

Pythagoras in three dimensions: the square root of the sum of squared differences. It gives the same answer, and for three numbers it's perfectly fine. Real embeddings have hundreds or thousands of dimensions (SkySutra's have 3,072), and that's where a native type, a built-in distance and a vector index stop being a convenience and become the only practical way.

19c and 26ai

Gotchas

• Scale your features. Distance adds the differences up, so a feature measured in hundreds (a raw strike rate of 150) would drown out ones measured in fractions. That's why strike rate is divided by 200 here.
• Euclidean for "how alike", cosine for embeddings. Text embeddings are compared by direction, so COSINE is the usual choice there. Use whatever the model's documentation says.
• FLOAT32 vectors are approximate. Fine for similarity, not for exact arithmetic or tests that compare to four decimals.