How to access varray element in SQL in the table column

Question

I am trying to find an easy way to access varray element in the table column in a SQL query. Column id of varray type, but needs to be presented to a database client with each element as a column. Something like column_name(1).x, column_name(1).y,..column_name(20).y We currently use a function to return array elements as column

create or replace function get_point_x(
  p_graph in graph_t,
  p_point in PLS_INTEGER
)
return number
is
begin
  return p_graph(p_point).x;
exception
when no_data_found then
  return to_number(null);
end get_point_x;
/

However, calling this function 40 times per row takes about 40% of the query elapsed time. So I wonder if there is a simple and efficient SQL alternative to access an element in SQL query. I tried the approach below but it is not much more efficient than PLSQL function!?

-- A graph point
CREATE TYPE point_t AS object(
x number(6,0),
y number(6,0)
);
/

-- Graph can contain up to 20 points, no more
CREATE TYPE graph_t AS VARRAY(20) OF point_t;
/

-- Customer graphs
create table customer_graphs (customer_id number(9,0), graph graph_t);

insert into customer_graphs values(1, graph_t(point_t(10,10), point_t(20,20), point_t(30,30)));

insert into customer_graphs values(2, graph_t(point_t(5,5), point_t(10,10), point_t(30,30), point_t(40,31)));

-- That works but returns graph points as rows
-- But I need columns x1, y1, ..x20, y20 
select cg.customer_id, g.* from customer_graphs cg, TABLE(cg.graph) g;

-- Of cource I can pivot but it impacts performance with 40 columns
select 
  customer_id,
  max(
    CASE rn
      WHEN 1
      THEN x
    END
  ) x_1,
  max(
    CASE rn
      WHEN 1
      THEN y
    END
  ) y_1,
  max(
    CASE rn
      WHEN 2
      THEN x
    END
  ) x_2,
  max(
    CASE rn
      WHEN 2
      THEN y
    END
  ) y_2,  
  -- ..
  max(
    CASE rn
      WHEN 20
      THEN x
    END
  ) x_20,
  max(
    CASE rn
      WHEN 20
      THEN y
    END
  ) y_20
from (
  select cg.customer_id, g.*, row_number() over(partition by cg.customer_id order by g.x) rn 
  from 
    customer_graphs cg, 
    TABLE(cg.graph) g
)
group by customer_id
;

-- Is there an easy way to access volumn's varray element in SQL??
-- Something like below
select 
  cg.customer_id, 
  cg.graph,
-- From this line on it does not work  
 cg.graph(1).x x_1,
 cg.graph(1).y y_1,
-- ..
 cg.graph(20).x x_20,
 cg.graph(20).y y_20
from customer_graphs cg;

ORA-00904: "CG"."GRAPH": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 258 Column: 2

Is there some efficient SQL solution out there that I am missing?

Thank you in advance

Answer 1

The best solution would be to drop types and VARRAYs and store everything in plain tables.

If that's not an option you can significantly improve performance by wrapping the VARRAY in an object type and access elements through member functions. That approach is several times faster than pivoting results from a VARRAY.

The code below is a bit painful, but it's a fully functioning test of 20 columns with 100,000 sample rows.

Sample Schema with VARRAY

CREATE TYPE point_t AS object(
x number(6,0),
y number(6,0)
);

-- Graph can contain up to 20 points, no more
CREATE TYPE graph_t AS VARRAY(20) OF point_t;

-- Customer graphs
create table customer_graphs (customer_id number(9,0), graph graph_t);

--100K rows, 5.2 seconds.
begin
    for i in 1 .. 100000 loop
        insert into customer_graphs values(i, graph_t(point_t(1,1),point_t(2,2),point_t(3,3),point_t(4,4),point_t(5,5),point_t(6,6),point_t(7,7),point_t(8,8),point_t(9,9),point_t(10,10),point_t(11,11),point_t(12,12),point_t(13,13),point_t(14,14),point_t(15,15),point_t(16,16),point_t(17,17),point_t(18,18),point_t(19,19),point_t(20,20)));
    end loop;
    commit;
end;
/

begin
    dbms_stats.gather_table_stats(user, 'CUSTOMER_GRAPHS');
end;
/

Sample Schema with Object Containing VARRAY

--Create type to store and access graph and X and Y elements.
create or replace type graph_obj as object
(
    graph graph_t,
    member function x(p_index number) return number,
    member function y(p_index number) return number
);

create or replace type body graph_obj is
    member function x(p_index number) return number is
    begin
        return graph(p_index).x;
    end;

    member function y(p_index number) return number is
    begin
        return graph(p_index).y;
    end;
end;
/

-- Customer graphs 2
create table customer_graphs2(customer_id number(9,0), graph graph_obj);

--100K rows, 5.54 seconds.
begin
    for i in 1 .. 100000 loop
        insert into customer_graphs2 values(i, graph_obj(graph_t(point_t(1,1),point_t(2,2),point_t(3,3),point_t(4,4),point_t(5,5),point_t(6,6),point_t(7,7),point_t(8,8),point_t(9,9),point_t(10,10),point_t(11,11),point_t(12,12),point_t(13,13),point_t(14,14),point_t(15,15),point_t(16,16),point_t(17,17),point_t(18,18),point_t(19,19),point_t(20,20))));
    end loop;
    commit;
end;
/

begin
    dbms_stats.gather_table_stats(user, 'CUSTOMER_GRAPHS2');
end;
/

VARRAY PIVOT Performance

First N rows - 4.5 seconds.

select customer_id,
    max(CASE rn WHEN 1 THEN x END) x_1, max(CASE rn WHEN 1 THEN y END) y_1, max(CASE rn WHEN 2 THEN x END) x_2, max(CASE rn WHEN 2 THEN y END) y_2, max(CASE rn WHEN 3 THEN x END) x_3, max(CASE rn WHEN 3 THEN y END) y_3, max(CASE rn WHEN 4 THEN x END) x_4, max(CASE rn WHEN 4 THEN y END) y_4, max(CASE rn WHEN 5 THEN x END) x_5, max(CASE rn WHEN 5 THEN y END) y_5, max(CASE rn WHEN 6 THEN x END) x_6, max(CASE rn WHEN 6 THEN y END) y_6, max(CASE rn WHEN 7 THEN x END) x_7, max(CASE rn WHEN 7 THEN y END) y_7, max(CASE rn WHEN 8 THEN x END) x_8, max(CASE rn WHEN 8 THEN y END) y_8, max(CASE rn WHEN 9 THEN x END) x_9, max(CASE rn WHEN 9 THEN y END) y_9, max(CASE rn WHEN 10 THEN x END) x_10, max(CASE rn WHEN 10 THEN y END) y_10, max(CASE rn WHEN 11 THEN x END) x_11, max(CASE rn WHEN 11 THEN y END) y_11, max(CASE rn WHEN 12 THEN x END) x_12, max(CASE rn WHEN 12 THEN y END) y_12, max(CASE rn WHEN 13 THEN x END) x_13, max(CASE rn WHEN 13 THEN y END) y_13, max(CASE rn WHEN 14 THEN x END) x_14, max(CASE rn WHEN 14 THEN y END) y_14, max(CASE rn WHEN 15 THEN x END) x_15, max(CASE rn WHEN 15 THEN y END) y_15, max(CASE rn WHEN 16 THEN x END) x_16, max(CASE rn WHEN 16 THEN y END) y_16, max(CASE rn WHEN 17 THEN x END) x_17, max(CASE rn WHEN 17 THEN y END) y_17, max(CASE rn WHEN 18 THEN x END) x_18, max(CASE rn WHEN 18 THEN y END) y_18, max(CASE rn WHEN 19 THEN x END) x_19, max(CASE rn WHEN 19 THEN y END) y_19, max(CASE rn WHEN 20 THEN x END) x_20, max(CASE rn WHEN 20 THEN y END) y_20
from (
  select cg.customer_id, g.*, row_number() over(partition by cg.customer_id order by g.x) rn 
  from 
    customer_graphs cg, 
    TABLE(cg.graph) g
)
group by customer_id;

All rows - 17 seconds

select sum(x_1) x
from
(
    select customer_id,
        max(CASE rn WHEN 1 THEN x END) x_1, max(CASE rn WHEN 1 THEN y END) y_1, max(CASE rn WHEN 2 THEN x END) x_2, max(CASE rn WHEN 2 THEN y END) y_2, max(CASE rn WHEN 3 THEN x END) x_3, max(CASE rn WHEN 3 THEN y END) y_3, max(CASE rn WHEN 4 THEN x END) x_4, max(CASE rn WHEN 4 THEN y END) y_4, max(CASE rn WHEN 5 THEN x END) x_5, max(CASE rn WHEN 5 THEN y END) y_5, max(CASE rn WHEN 6 THEN x END) x_6, max(CASE rn WHEN 6 THEN y END) y_6, max(CASE rn WHEN 7 THEN x END) x_7, max(CASE rn WHEN 7 THEN y END) y_7, max(CASE rn WHEN 8 THEN x END) x_8, max(CASE rn WHEN 8 THEN y END) y_8, max(CASE rn WHEN 9 THEN x END) x_9, max(CASE rn WHEN 9 THEN y END) y_9, max(CASE rn WHEN 10 THEN x END) x_10, max(CASE rn WHEN 10 THEN y END) y_10, max(CASE rn WHEN 11 THEN x END) x_11, max(CASE rn WHEN 11 THEN y END) y_11, max(CASE rn WHEN 12 THEN x END) x_12, max(CASE rn WHEN 12 THEN y END) y_12, max(CASE rn WHEN 13 THEN x END) x_13, max(CASE rn WHEN 13 THEN y END) y_13, max(CASE rn WHEN 14 THEN x END) x_14, max(CASE rn WHEN 14 THEN y END) y_14, max(CASE rn WHEN 15 THEN x END) x_15, max(CASE rn WHEN 15 THEN y END) y_15, max(CASE rn WHEN 16 THEN x END) x_16, max(CASE rn WHEN 16 THEN y END) y_16, max(CASE rn WHEN 17 THEN x END) x_17, max(CASE rn WHEN 17 THEN y END) y_17, max(CASE rn WHEN 18 THEN x END) x_18, max(CASE rn WHEN 18 THEN y END) y_18, max(CASE rn WHEN 19 THEN x END) x_19, max(CASE rn WHEN 19 THEN y END) y_19, max(CASE rn WHEN 20 THEN x END) x_20, max(CASE rn WHEN 20 THEN y END) y_20
    from (
      select cg.customer_id, g.*, row_number() over(partition by cg.customer_id order by g.x) rn 
      from 
        customer_graphs cg, 
        TABLE(cg.graph) g
    )
    group by customer_id
);

Object Performance

First N rows - 0.4 seconds

select cg.customer_id, cg.graph.x(1) x_1, cg.graph.y(1) y_1, cg.graph.x(2) x_2, cg.graph.y(2) y_2, cg.graph.x(3) x_3, cg.graph.y(3) y_3, cg.graph.x(4) x_4, cg.graph.y(4) y_4, cg.graph.x(5) x_5, cg.graph.y(5) y_5, cg.graph.x(6) x_6, cg.graph.y(6) y_6, cg.graph.x(7) x_7, cg.graph.y(7) y_7, cg.graph.x(8) x_8, cg.graph.y(8) y_8, cg.graph.x(9) x_9, cg.graph.y(9) y_9, cg.graph.x(10) x_10, cg.graph.y(10) y_10, cg.graph.x(11) x_11, cg.graph.y(11) y_11, cg.graph.x(12) x_12, cg.graph.y(12) y_12, cg.graph.x(13) x_13, cg.graph.y(13) y_13, cg.graph.x(14) x_14, cg.graph.y(14) y_14, cg.graph.x(15) x_15, cg.graph.y(15) y_15, cg.graph.x(16) x_16, cg.graph.y(16) y_16, cg.graph.x(17) x_17, cg.graph.y(17) y_17, cg.graph.x(18) x_18, cg.graph.y(18) y_18, cg.graph.x(19) x_19, cg.graph.y(19) y_19, cg.graph.x(20) x_20, cg.graph.y(20) y_20
from customer_graphs2 cg;

All rows - 2.5 seconds

select sum(x_1)
from
(
    select cg.customer_id, cg.graph.x(1) x_1, cg.graph.y(1) y_1, cg.graph.x(2) x_2, cg.graph.y(2) y_2, cg.graph.x(3) x_3, cg.graph.y(3) y_3, cg.graph.x(4) x_4, cg.graph.y(4) y_4, cg.graph.x(5) x_5, cg.graph.y(5) y_5, cg.graph.x(6) x_6, cg.graph.y(6) y_6, cg.graph.x(7) x_7, cg.graph.y(7) y_7, cg.graph.x(8) x_8, cg.graph.y(8) y_8, cg.graph.x(9) x_9, cg.graph.y(9) y_9, cg.graph.x(10) x_10, cg.graph.y(10) y_10, cg.graph.x(11) x_11, cg.graph.y(11) y_11, cg.graph.x(12) x_12, cg.graph.y(12) y_12, cg.graph.x(13) x_13, cg.graph.y(13) y_13, cg.graph.x(14) x_14, cg.graph.y(14) y_14, cg.graph.x(15) x_15, cg.graph.y(15) y_15, cg.graph.x(16) x_16, cg.graph.y(16) y_16, cg.graph.x(17) x_17, cg.graph.y(17) y_17, cg.graph.x(18) x_18, cg.graph.y(18) y_18, cg.graph.x(19) x_19, cg.graph.y(19) y_19, cg.graph.x(20) x_20, cg.graph.y(20) y_20
    from customer_graphs2 cg
);

Answer 2

Setup

CREATE TYPE point_t AS object(
x number(6,0),
y number(6,0)
);
/
-- Graph can contain up to 20 points, no more
CREATE TYPE graph_t AS VARRAY(20) OF point_t;
/
-- Customer graphs
create table customer_graphs (customer_id number(9,0), graph graph_t);

BEGIN

  FOR i IN 1 .. 100000
  LOOP
    -- build graph as it may contain up to 20 points

    DECLARE
      lv_graph graph_t := graph_t();
    BEGIN

      FOR j IN 1..mod( i, 20 ) + 1
      LOOP

        lv_graph.extend( );
        lv_graph( j ) := point_t( i, i );

      END LOOP;

      INSERT INTO customer_graphs VALUES ( i, lv_graph);

    END;

  END LOOP;

  COMMIT;

END;
/

begin
    dbms_stats.gather_table_stats(user, 'CUSTOMER_GRAPHS');
end;
/

Original PL/SQL Functions

create or replace function x(p_graph graph_t, p_index number) return number is
    begin
        if p_graph.exists(p_index) then
          return p_graph(p_index).x;
        else
          return to_number(null);
        end if;
    end;
/    

create or replace function y(p_graph graph_t, p_index number) return number is
    begin
      if p_graph.exists(p_index) then
        return p_graph(p_index).y;
      else
          return to_number(null);
      end if;
    end;
/

Jon Heller advice(slightly modified)

I added test for NULL values since graph does not always contain all 20 points.

create or replace type graph_obj as object
(
    graph graph_t,
    member function x(p_index number) return number,
    member function y(p_index number) return number
);
/

create or replace type body graph_obj is
    member function x(p_index number) return number is
    begin
        if graph.exists(p_index) then
          return graph(p_index).x;
        else
          return to_number(null);
        end if;
    end;

    member function y(p_index number) return number is
    begin
      if graph.exists(p_index) then
        return graph(p_index).y;
      else
          return to_number(null);
      end if;
    end;
end;
/

This application is very old and used by so many other apps. I am writing an interface for yet another app.. I can see how this application developed over time:-) At first relational tables were used, then they introduced VARRAYs, then nested tables(I assume when object-oriented paradigm was fancy and trendy), and now good old relational tables. I agree it is better to use plain relational tables.

I cannot modify the application. So I created graph_obj objects on the fly. I need all rows. I ran queries in SQL*Plus with

set timing on
set arraysize 5000
set autotrace TRACEONLY statistics

I did not use sum(x_1) trick because Oracle is smart not to execute other 39 calls to calculate y_1,..,x_20,y_20. That is why object approach seemed to be faster than the pivot one.

VARRAY Pivot Performance

-- Pivot 13 secs
select customer_id,
        max(CASE rn WHEN 1 THEN x END) x_1, max(CASE rn WHEN 1 THEN y END) y_1, max(CASE rn WHEN 2 THEN x END) x_2, max(CASE rn WHEN 2 THEN y END) y_2, max(CASE rn WHEN 3 THEN x END) x_3, max(CASE rn WHEN 3 THEN y END) y_3, max(CASE rn WHEN 4 THEN x END) x_4, max(CASE rn WHEN 4 THEN y END) y_4, max(CASE rn WHEN 5 THEN x END) x_5, max(CASE rn WHEN 5 THEN y END) y_5, max(CASE rn WHEN 6 THEN x END) x_6, max(CASE rn WHEN 6 THEN y END) y_6, max(CASE rn WHEN 7 THEN x END) x_7, max(CASE rn WHEN 7 THEN y END) y_7, max(CASE rn WHEN 8 THEN x END) x_8, max(CASE rn WHEN 8 THEN y END) y_8, max(CASE rn WHEN 9 THEN x END) x_9, max(CASE rn WHEN 9 THEN y END) y_9, max(CASE rn WHEN 10 THEN x END) x_10, max(CASE rn WHEN 10 THEN y END) y_10, max(CASE rn WHEN 11 THEN x END) x_11, max(CASE rn WHEN 11 THEN y END) y_11, max(CASE rn WHEN 12 THEN x END) x_12, max(CASE rn WHEN 12 THEN y END) y_12, max(CASE rn WHEN 13 THEN x END) x_13, max(CASE rn WHEN 13 THEN y END) y_13, max(CASE rn WHEN 14 THEN x END) x_14, max(CASE rn WHEN 14 THEN y END) y_14, max(CASE rn WHEN 15 THEN x END) x_15, max(CASE rn WHEN 15 THEN y END) y_15, max(CASE rn WHEN 16 THEN x END) x_16, max(CASE rn WHEN 16 THEN y END) y_16, max(CASE rn WHEN 17 THEN x END) x_17, max(CASE rn WHEN 17 THEN y END) y_17, max(CASE rn WHEN 18 THEN x END) x_18, max(CASE rn WHEN 18 THEN y END) y_18, max(CASE rn WHEN 19 THEN x END) x_19, max(CASE rn WHEN 19 THEN y END) y_19, max(CASE rn WHEN 20 THEN x END) x_20, max(CASE rn WHEN 20 THEN y END) y_20
    from (
      select cg.customer_id, g.*, row_number() over(partition by cg.customer_id order by g.x) rn 
      from 
        customer_graphs cg, 
        TABLE(cg.graph) g
    )
    group by customer_id;

Original PLSQL Functions Performance

-- PLSQL 75 secs. 
select cg.customer_id, x(cg.graph,1) x_1, y(cg.graph,1) y_1, x(cg.graph,2) x_2, y(cg.graph,2) y_2, x(cg.graph,3) x_3, y(cg.graph,3) y_3, x(cg.graph,4) x_4, y(cg.graph,4) y_4, x(cg.graph,5) x_5, y(cg.graph,5) y_5, x(cg.graph,6) x_6, y(cg.graph,6) y_6, x(cg.graph,7) x_7, y(cg.graph,7) y_7, x(cg.graph,8) x_8, y(cg.graph,8) y_8, x(cg.graph,9) x_9, y(cg.graph,9) y_9, x(cg.graph,10) x_10, y(cg.graph,10) y_10, x(cg.graph,11) x_11, y(cg.graph,11) y_11, x(cg.graph,12) x_12, y(cg.graph,12) y_12, x(cg.graph,13) x_13, y(cg.graph,13) y_13, x(cg.graph,14) x_14, y(cg.graph,14) y_14, x(cg.graph,15) x_15, y(cg.graph,15) y_15, x(cg.graph,16) x_16, y(cg.graph,16) y_16, x(cg.graph,17) x_17, y(cg.graph,17) y_17, x(cg.graph,18) x_18, y(cg.graph,18) y_18, x(cg.graph,19) x_19, y(cg.graph,19) y_19, x(cg.graph,20) x_20, y(cg.graph,20) y_20
    from customer_graphs cg;

Object Performance

-- Object 83 secs, 6 times slower than pivot query
-- I assume that additional 8 secs were caused by creating objects on the fly

    select cg.customer_id, cg.graph.x(1) x_1, cg.graph.y(1) y_1, cg.graph.x(2) x_2, cg.graph.y(2) y_2, cg.graph.x(3) x_3, cg.graph.y(3) y_3, cg.graph.x(4) x_4, cg.graph.y(4) y_4, cg.graph.x(5) x_5, cg.graph.y(5) y_5, cg.graph.x(6) x_6, cg.graph.y(6) y_6, cg.graph.x(7) x_7, cg.graph.y(7) y_7, cg.graph.x(8) x_8, cg.graph.y(8) y_8, cg.graph.x(9) x_9, cg.graph.y(9) y_9, cg.graph.x(10) x_10, cg.graph.y(10) y_10, cg.graph.x(11) x_11, cg.graph.y(11) y_11, cg.graph.x(12) x_12, cg.graph.y(12) y_12, cg.graph.x(13) x_13, cg.graph.y(13) y_13, cg.graph.x(14) x_14, cg.graph.y(14) y_14, cg.graph.x(15) x_15, cg.graph.y(15) y_15, cg.graph.x(16) x_16, cg.graph.y(16) y_16, cg.graph.x(17) x_17, cg.graph.y(17) y_17, cg.graph.x(18) x_18, cg.graph.y(18) y_18, cg.graph.x(19) x_19, cg.graph.y(19) y_19, cg.graph.x(20) x_20, cg.graph.y(20) y_20
    from (
    select cgi.customer_id, graph_obj(cgi.graph) graph
    from customer_graphs cgi
    ) cg
;