Для оптимальной производительности лучше избегать использования иерархических запросов (CONNECT BY) в функции сплиттера.
Следующая функция сплиттера значительно улучшает работу с большими объемами данных
CREATE OR REPLACE FUNCTION row2col(p_clob_text IN VARCHAR2)
RETURN sys.dbms_debug_vc2coll PIPELINED
IS
next_new_line_indx PLS_INTEGER;
remaining_text VARCHAR2(20000);
next_piece_for_piping VARCHAR2(20000);
BEGIN
remaining_text := p_clob_text;
LOOP
next_new_line_indx := instr(remaining_text, ',');
next_piece_for_piping :=
CASE
WHEN next_new_line_indx <> 0 THEN
TRIM(SUBSTR(remaining_text, 1, next_new_line_indx-1))
ELSE
TRIM(SUBSTR(remaining_text, 1))
END;
remaining_text := SUBSTR(remaining_text, next_new_line_indx+1 );
PIPE ROW(next_piece_for_piping);
EXIT WHEN next_new_line_indx = 0 OR remaining_text IS NULL;
END LOOP;
RETURN;
END row2col;
/
Эта разница в производительности может наблюдаться ниже (я использовал сплиттер функции, как было указано ранее в этом обсуждении).
SQL> SET TIMING ON
SQL>
SQL> WITH SRC AS (
2 SELECT rownum||',a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'||rownum txt
3 FROM DUAL
4 CONNECT BY LEVEL <=10000
5 )
6 SELECT NULL
7 FROM SRC, TABLE(SYSTEM.row2col(txt)) t
8 HAVING MAX(t.column_value) > 'zzz'
9 ;
no rows selected
Elapsed: 00:00:00.93
SQL>
SQL> WITH SRC AS (
2 SELECT rownum||',a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'||rownum txt
3 FROM DUAL
4 CONNECT BY LEVEL <=10000
5 )
6 SELECT NULL
7 FROM SRC, TABLE(splitter(txt)) t
8 HAVING MAX(t.column_value) > 'zzz'
9 ;
no rows selected
Elapsed: 00:00:14.90
SQL>
SQL> SET TIMING OFF
SQL>
Вы не можете INSERT
войти в CTE. CTE - это логическая таблица, это псевдоним набора результатов. Вы можете SELECT
из CTE. Не совсем уверен, чего вы там добиваетесь.
ОШИБКА: отношение "matrix_cte" не существует
blockquote>Это сообщение об ошибке означает, что вы можете
INSERT
только в отношения (таблицы). CTE - это не таблица, это не постоянный объект в базе данных, и в вашей базе данных нет таблицы с именемmatrix_cte
.
Чтобы сгенерировать все отношения, как прямые, так и обратные, вы можете
UNION
два набора результатов вместе. Если ваша исходная таблица имеет только отношения в одном направлении, то вы можете использоватьUNION ALL
, и запрос будет быстрее. Я имею в виду, если исходная таблица никогда не имеет двух строк для одной и той же пары единиц:unit1, unit2, 1 unit2, unit1, 2
, тогда вы можете использовать
UNION ALL
ниже. Если в оригинальной таблице могут быть такие дубликаты, вы должны использоватьUNION
для удаления лишних дубликатов.-- all direct relationships as they are SELECT lookup_unit, lookup_unit_2, lookup_unit_relationship FROM register_unit_matrix UNION -- inverse all relationships SELECT lookup_unit_2, lookup_unit, CASE WHEN lookup_unit_relationship = 1 THEN 2 ELSE 1 END AS lookup_unit_relationship FROM register_unit_matrix
Вы можете поместить этот запрос выше в представление или использовать как есть.