Chciałbym zaproponować inne podejście przy użyciu funkcji tabeli PIPELINED. Jest to trochę podobne do techniki XMLTABLE, z tym wyjątkiem, że udostępniasz własną niestandardową funkcję do dzielenia ciągu znaków:
-- Create a collection type to hold the results
CREATE OR REPLACE TYPE typ_str2tbl_nst AS TABLE OF VARCHAR2(30);
/
-- Split the string according to the specified delimiter
CREATE OR REPLACE FUNCTION str2tbl (
p_string VARCHAR2,
p_delimiter CHAR DEFAULT ','
)
RETURN typ_str2tbl_nst PIPELINED
AS
l_tmp VARCHAR2(32000) := p_string || p_delimiter;
l_pos NUMBER;
BEGIN
LOOP
l_pos := INSTR( l_tmp, p_delimiter );
EXIT WHEN NVL( l_pos, 0 ) = 0;
PIPE ROW ( RTRIM( LTRIM( SUBSTR( l_tmp, 1, l_pos-1) ) ) );
l_tmp := SUBSTR( l_tmp, l_pos+1 );
END LOOP;
END str2tbl;
/
-- The problem solution
SELECT name,
project,
TRIM(COLUMN_VALUE) error
FROM t, TABLE(str2tbl(error));
Wyniki:
NAME PROJECT ERROR
---------- ---------- --------------------
108 test Err1
108 test Err2
108 test Err3
109 test2 Err1
Problem z tego typu podejściem polega na tym, że często optymalizator nie zna liczności funkcji tabeli i będzie musiał zgadnąć. Może to być potencjalnie szkodliwe dla planów wykonania, więc to rozwiązanie można rozszerzyć, aby zapewnić statystyki wykonania dla optymalizatora.
Możesz zobaczyć to oszacowanie optymalizatora, uruchamiając EXPLAIN PLAN dla powyższego zapytania:
Execution Plan
----------------------------------------------------------
Plan hash value: 2402555806
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16336 | 366K| 59 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 16336 | 366K| 59 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 2 | 42 | 3 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 28 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Mimo że kolekcja ma tylko 3 wartości, optymalizator oszacował dla niej 8168 wierszy (wartość domyślna). Na początku może się to wydawać nieistotne, ale optymalizatorowi może wystarczyć podjęcie decyzji o planie nieoptymalnym.
Rozwiązaniem jest użycie rozszerzeń optymalizatora w celu zapewnienia statystyk dla kolekcji:
-- Create the optimizer interface to the str2tbl function
CREATE OR REPLACE TYPE typ_str2tbl_stats AS OBJECT (
dummy NUMBER,
STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
RETURN NUMBER,
STATIC FUNCTION ODCIStatsTableFunction ( p_function IN SYS.ODCIFuncInfo,
p_stats OUT SYS.ODCITabFuncStats,
p_args IN SYS.ODCIArgDescList,
p_string IN VARCHAR2,
p_delimiter IN CHAR DEFAULT ',' )
RETURN NUMBER
);
/
-- Optimizer interface implementation
CREATE OR REPLACE TYPE BODY typ_str2tbl_stats
AS
STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
RETURN NUMBER
AS
BEGIN
p_interfaces := SYS.ODCIObjectList ( SYS.ODCIObject ('SYS', 'ODCISTATS2') );
RETURN ODCIConst.SUCCESS;
END ODCIGetInterfaces;
-- This function is responsible for returning the cardinality estimate
STATIC FUNCTION ODCIStatsTableFunction ( p_function IN SYS.ODCIFuncInfo,
p_stats OUT SYS.ODCITabFuncStats,
p_args IN SYS.ODCIArgDescList,
p_string IN VARCHAR2,
p_delimiter IN CHAR DEFAULT ',' )
RETURN NUMBER
AS
BEGIN
-- I'm using basically half the string lenght as an estimator for its cardinality
p_stats := SYS.ODCITabFuncStats( CEIL( LENGTH( p_string ) / 2 ) );
RETURN ODCIConst.SUCCESS;
END ODCIStatsTableFunction;
END;
/
-- Associate our optimizer extension with the PIPELINED function
ASSOCIATE STATISTICS WITH FUNCTIONS str2tbl USING typ_str2tbl_stats;
Testowanie powstałego planu wykonania:
Execution Plan
----------------------------------------------------------
Plan hash value: 2402555806
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 59 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 23 | 59 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 2 | 42 | 3 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 1 | 2 | 28 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Jak widać, liczność na powyższym planie nie jest już domyślną wartością 8196. Wciąż nie jest to poprawne, ponieważ przekazujemy do funkcji kolumnę zamiast literału ciągu.
Pewne poprawki w kodzie funkcji byłyby konieczne, aby dać dokładniejsze oszacowanie w tym konkretnym przypadku, ale myślę, że ogólna koncepcja jest tutaj dość dobrze wyjaśniona.
Funkcja str2tbl użyta w tej odpowiedzi została pierwotnie opracowana przez Toma Kyte'a:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061
Koncepcję kojarzenia statystyk z typami obiektów można dokładniej zbadać, czytając ten artykuł:
http://www.oracle-developer.net/display.php?id=427
Opisana tutaj technika działa w 10g +.
REGEXP
,XMLTABLE
orazMODEL
klauzuli zobaczyć Podział rozdzielany przecinkami ciągi w tabeli przy użyciu Oracle SQL