本文介绍了如何在表格列的SQL中访问varray元素的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试在 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).yWe 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元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！