Как получить доступ к элементу varray в SQL в столбце таблицы

Я пытаюсь найти простой способ доступа к элементу varray в столбце таблицы в запросе SQL. Идентификатор столбца типа varray, но он должен быть представлен клиенту базы данных с каждым элементом в виде столбца. Что-то вроде имя_столбца(1).x, имя_столбца(1).y,..имя_столбца(20).y В настоящее время мы используем функцию для возврата элементов массива в виде столбца

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!?

-- 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, которое мне не хватает?

заранее спасибо


sql object-oriented-database element oracle varray
person Vladimir    schedule 27.04.2016    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Лучшим решением было бы отказаться от типов и VARRAY и хранить все в простых таблицах.

Если это не вариант, вы можете значительно повысить производительность, заключив VARRAY в тип объекта и получив доступ к элементам через функции-члены. Этот подход в несколько раз быстрее, чем сводка результатов из VARRAY.

Приведенный ниже код немного болезненный, но это полностью функционирующий тест с 20 столбцами и 100 000 выборочных строк.

Пример схемы с 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
);
person Jon Heller    schedule 28.04.2016
comment
Спасибо за ваше время и интересную идею. Ваш ответ помог мне найти настоящего виновника проблемы с производительностью. Оказалось, что это другая функция pl/sql, которая потребляла 35% времени выполнения запроса. Я посмотрел на функции-члены graph_obj и понял, что они по сути такие же, как оригинальные функции pl/sql, и вы доказали эффективность своего подхода. Спасибо большое за вашу помощь! Как только я исправил эту другую функцию pl/sql, стало очевидно, что базовый подход работает быстрее, чем у pl/sql или объекта. P.S. Я опубликовал ответ, так как не мог вписать свои выводы в этот комментарий. - person Vladimir; 28.04.2016

arrow_upward
0
arrow_downward

Настраивать

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

Оригинальные функции PL/SQL

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

Совет Джона Хеллера (слегка измененный)

Я добавил тест для значений NULL, так как график не всегда содержит все 20 точек.

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

Это приложение очень старое и используется многими другими приложениями. Я пишу интерфейс для еще одного приложения. Я вижу, как это приложение развивалось с течением времени :-) Сначала использовались реляционные таблицы, затем они представили VARRAY, затем вложенные таблицы (я полагаю, когда объектно-ориентированная парадигма была причудливой и модной ), а теперь старые добрые реляционные таблицы. Я согласен, что лучше использовать простые реляционные таблицы.

Я не могу изменить приложение. Поэтому я создал объекты graph_obj на лету. Мне нужны все строки. Я выполнял запросы в SQL * Plus с помощью

set timing on
set arraysize 5000
set autotrace TRACEONLY statistics

Я не использовал трюк с суммой (x_1), потому что Oracle умнее не выполнять другие 39 вызовов для вычисления y_1,..,x_20,y_20. Поэтому объектный подход оказался более быстрым, чем опорный.

Производительность VARRAY Pivot

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

Производительность исходных функций PLSQL

-- 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 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
;
person Vladimir    schedule 28.04.2016