Skip to content

Commit d85f88d

Browse files
jgebaljgebal
committed
Improved performance of get_reporters_list
by avoiding the costly and slow `connect by` Resolves utPLSQL#814
1 parent abe6848 commit d85f88d

File tree

8 files changed

+92
-23
lines changed

8 files changed

+92
-23
lines changed

‎source/api/ut_runner.pkb‎

Lines changed: 10 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -225,32 +225,21 @@ create or replace package body ut_runner is
225225
end;
226226

227227
function get_reporters_list return tt_reporters_info pipelined is
228-
l_cursor sys_refcursor;
229228
l_owner varchar2(128) := upper(ut_utils.ut_owner());
230-
l_results tt_reporters_info;
231-
c_bulk_limit constant integer := 10;
232-
l_view_name varchar2(200) := ut_metadata.get_dba_view('dba_types');
229+
l_reporters ut_reporters_info;
230+
l_result t_reporter_rec;
233231
begin
234-
open l_cursor for q'[
235-
SELECT
236-
owner || '.' || type_name,
237-
CASE
238-
WHEN sys_connect_by_path(owner||'.'||type_name,',') LIKE '%]' || l_owner || q'[.UT_OUTPUT_REPORTER_BASE%'
239-
THEN 'Y'
240-
ELSE 'N'
241-
END is_output_reporter
242-
FROM ]'||l_view_name||q'[ t
243-
WHERE instantiable = 'YES'
244-
CONNECT BY supertype_name = PRIOR type_name AND supertype_owner = PRIOR owner
245-
START WITH type_name = 'UT_REPORTER_BASE' AND owner = ']'|| l_owner || '''';
246232
loop
247-
fetch l_cursor bulk collect into l_results limit c_bulk_limit;
248-
for i in 1 .. l_results.count loop
249-
pipe row (l_results(i));
233+
l_reporters := ut_utils.get_child_reporters( l_reporters );
234+
exit when l_reporters is null or l_reporters.count = 0;
235+
for i in 1 .. l_reporters.count loop
236+
if l_reporters(i).is_instantiable = 'Y' then
237+
l_result.reporter_object_name := l_owner||'.'||l_reporters(i).object_name;
238+
l_result.is_output_reporter := l_reporters(i).is_output_reporter;
239+
pipe row( l_result );
240+
end if;
250241
end loop;
251-
exit when l_cursor%notfound;
252242
end loop;
253-
close l_cursor;
254243
end;
255244

256245
end ut_runner;

‎source/api/ut_runner.pks‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -126,8 +126,8 @@ create or replace package ut_runner authid current_user is
126126

127127

128128
type t_reporter_rec is record (
129-
reporter_object_name varchar2(250),
130-
is_output_reporter varchar2(1) --Y/N flag
129+
reporter_object_name varchar2(250), -- full reporter name in format: owner.name
130+
is_output_reporter varchar2(1) -- Y/N indication of reporter providing output for API
131131
);
132132
type tt_reporters_info is table of t_reporter_rec ;
133133

‎source/core/types/ut_reporter_info.tps‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
create or replace type ut_reporter_info as object (
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2018 utPLSQL Project
5+
6+
Licensed under the Apache License, Version 2.0 (the "License"):
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing, software
13+
distributed under the License is distributed on an "AS IS" BASIS,
14+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15+
See the License for the specific language governing permissions and
16+
limitations under the License.
17+
*/
18+
object_name varchar2(250),
19+
is_output_reporter varchar2(1),
20+
is_instantiable varchar2(1),
21+
is_final varchar2(1)
22+
)
23+
/

‎source/core/types/ut_reporters_info.tps‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
create or replace type ut_reporters_info as
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2018 utPLSQL Project
5+
6+
Licensed under the Apache License, Version 2.0 (the "License"):
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing, software
13+
distributed under the License is distributed on an "AS IS" BASIS,
14+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15+
See the License for the specific language governing permissions and
16+
limitations under the License.
17+
*/
18+
table of ut_reporter_info;
19+
/

‎source/core/ut_utils.pkb‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -692,5 +692,32 @@ create or replace package body ut_utils is
692692
return l_result;
693693
end;
694694

695+
function get_child_reporters(a_for_reporters ut_reporters_info := null) return ut_reporters_info is
696+
l_for_reporters ut_reporters_info := a_for_reporters;
697+
l_results ut_reporters_info;
698+
begin
699+
if l_for_reporters is null then
700+
l_for_reporters := ut_reporters_info(ut_reporter_info('UT_REPORTER_BASE','N','N','N'));
701+
end if;
702+
703+
select /*+ cardinality(f 10) */
704+
ut_reporter_info(
705+
object_name => t.type_name,
706+
is_output_reporter =>
707+
case
708+
when f.is_output_reporter = 'Y' or t.type_name = 'UT_OUTPUT_REPORTER_BASE'
709+
then 'Y' else 'N'
710+
end,
711+
is_instantiable => case when t.instantiable = 'YES' then 'Y' else 'N' end,
712+
is_final => case when t.final = 'YES' then 'Y' else 'N' end
713+
)
714+
bulk collect into l_results
715+
from user_types t
716+
join (select * from table(l_for_reporters) where is_final = 'N' ) f
717+
on f.object_name = supertype_name;
718+
719+
return l_results;
720+
end;
721+
695722
end ut_utils;
696723
/

‎source/core/ut_utils.pks‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -351,5 +351,10 @@ create or replace package ut_utils authid definer is
351351
*/
352352
function replace_multiline_comments(a_source clob) return clob;
353353

354+
/**
355+
* Returns list of sub-type reporters for given list of super-type reporters
356+
*/
357+
function get_child_reporters(a_for_reporters ut_reporters_info := null) return ut_reporters_info;
358+
354359
end ut_utils;
355360
/

‎source/install.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,8 @@ alter session set current_schema = &&ut3_owner;
4646
@@install_component.sql 'core/types/ut_object_names.tps'
4747
@@install_component.sql 'core/types/ut_key_value_pair.tps'
4848
@@install_component.sql 'core/types/ut_key_value_pairs.tps'
49+
@@install_component.sql 'core/types/ut_reporter_info.tps'
50+
@@install_component.sql 'core/types/ut_reporters_info.tps'
4951
@@install_component.sql 'core/ut_utils.pks'
5052
@@install_component.sql 'core/ut_metadata.pks'
5153
@@install_component.sql 'core/ut_utils.pkb'

‎source/uninstall_objects.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -255,6 +255,10 @@ drop package ut_event_manager;
255255

256256
drop type ut_event_item force;
257257

258+
drop type ut_reporters_info force;
259+
260+
drop type ut_reporter_info force;
261+
258262
drop type ut_key_anyval_pair force;
259263

260264
drop type ut_key_anyval_pairs force;

0 commit comments

Comments
 (0)