如何在表格列的SQL中访问varray元素 [英] How to access varray element in SQL in the table column

问题描述

我正在尝试在 SQL查询的表列中找到一种简单方法来访问varray元素.varray类型的列ID，但需要以每个元素作为列显示给数据库客户端.类似于column_name(1).x，column_name(1).y，.. column_name(20).y当前，我们使用一个函数将数组元素返回为列

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;
/

但是，每行调用此函数40次需要大约40％的查询时间.因此，我想知道是否存在一种简单有效的SQL替代方法来访问 SQL查询中的元素.我尝试了下面的方法，但是它没有比PLSQL函数更有效！?

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

我缺少一些有效的 SQL 解决方案吗?

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

提前谢谢

推荐答案

最好的解决方案是删除类型和VARRAY，并将所有内容存储在纯表中.

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

如果不是这样，则可以通过将VARRAY包装为对象类型并通过成员函数访问元素来显着提高性能.这种方法比透视VARRAY的结果快几倍.

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.

下面的代码有些痛苦，但是它是对20列具有100,000个示例行的功能全面的测试.

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

使用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;
/

对象包含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性能

前N行-4.5秒.

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;

所有行-17秒

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
);

对象性能

前N行-0.4秒

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;

所有行-2.5秒

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
);

这篇关于如何在表格列的SQL中访问varray元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！