Long execution time before open a form for choosing columns for indexing in table editing mode

[Albert]

Hello!
See subject, please
In that time a developer is repeatedly executing such a query:

declare
c integer := 0;
p1 varchar2(500);
p2 varchar2(500);
expr varchar2(500);
dblink varchar2(500);
part1_type integer;
object_number integer;
dp integer;
guard_error exception;
pragma exception_init(guard_error, -16224);
begin
bject_type := null;
bject_owner := null;
bject_name := null;
:sub_object := null;
expr := art1;
if art2 is not null then expr := expr || '.' || art2; end if;
if art3 is not null then expr := expr || '.' || art3; end if;
loop
begin
sys.dbms_utility.name_resolve(name => expr,
context => c,
schema => bject_owner,
part1 => p1,
part2 => p2,
dblink => dblink,
part1_type => part1_type,
object_number => object_number);
if part1_type = 1 then bject_type := 'INDEX'; end if;
if part1_type = 2 then bject_type := 'TABLE'; end if;
if part1_type = 4 then bject_type := 'VIEW'; end if;
if part1_type = 5 then bject_type := 'SYNONYM'; end if;
if part1_type = 6 then bject_type := 'SEQUENCE'; end if;
if part1_type = 7 then bject_type := 'PROCEDURE'; end if;
if part1_type = 8 then bject_type := 'FUNCTION'; end if;
if part1_type = 9 then bject_type := 'PACKAGE'; end if;
if part1_type = 12 then bject_type := 'TRIGGER'; end if;
if part1_type = 13 then bject_type := 'TYPE'; end if;
if part1_type = 28 then bject_type := 'JAVA SOURCE'; end if;
if part1_type = 29 then bject_type := 'JAVA CLASS'; end if;
if bject_type is null then
select object_type into bject_type
from sys.dba_objects
where object_id = object_number;
end if;
exception
when guard_error then
raise;
when others then
null;
end;
c := c + 1;
if c > 9 then
dp := instr(expr, '.', -1);
if dp > 0 then
if :sub_object is not null then
:sub_object := '.' || :sub_object;
end if;
:sub_object := upper(substr(expr, dp + 1)) || :sub_object;
expr := substr(expr, 1, dp - 1);
c := 0;
end if;
end if;
exit when object_type is not null) or (c > 9);
end loop;
if bject_type is not null then
if p1 is null then
bject_name := p2;
elsif p2 is null then
bject_name := p1;
if bject_name = art1 and art2 is not null then
:sub_object := art2;
end if;
if bject_name = art2 and art3 is not null then
:sub_object := art3;
end if;
else
bject_name := p1;
:sub_object := p2;
end if;
return;
end if;
begin
if art2 is null and art3 is null then
select 'USER', null, art1
into bject_type, bject_owner, bject_name
from sys.dba_users u
where u.username = art1
and rownum = 1;
return;
end if;
exception
when no_data_found then
null;
end;
begin
if art2 is null and art3 is null and :deep != 0 then
select 'ROLE', null, art1
into bject_type, bject_owner, bject_name
from sys.session_roles r
where r.role = art1
and rownum = 1;
return;
end if;
exception
when no_data_found then
null;
end;
if :deep != 0 then
begin
if art2 is null then
select constraint_type, owner, constraint_name
into bject_type, bject_owner, bject_name
from sys.dba_constraints c
where c.constraint_name = art1
and c.owner = :cur_schema
and rownum = 1;
else
select constraint_type, owner, constraint_name, art3
into bject_type, bject_owner, bject_name, :sub_object
from sys.dba_constraints c
where c.constraint_name = art2
and c.owner = art1
and rownum = 1;
end if;
if bject_type = 'P' then
bject_type := 'PRIMARY KEY';
end if;
if bject_type = 'U' then
bject_type := 'UNIQUE KEY';
end if;
if bject_type = 'R' then
bject_type := 'FOREIGN KEY';
end if;
if bject_type = 'C' then
bject_type := 'CHECK CONSTRAINT';
end if;
return;
exception
when no_data_found then
null;
end;
end if;
end;



I think developer do it for every column in a table
May be possible to run similar query one time for all columns and minimize network traffic?
Such approaches are widespread in the plsql developer
It's a bad practice
Thank you

 

[Marco Kalter]

When exactly does this happen when editing a table?

 

[Albert]

Marco, it happens after clicking on a button "..." in a section "Indexes" and below a columnn title "Columns"

 

[Marco Kalter]

Thanks. We'll check it out.