Oracle optimization problem-CodePudding

 create or replace procedure test as 
Cursor carddata is the select distinct g from CFSJ; 
STR varchar2 (100); 
Mark number (1); 
The begin 

For x in carddata loop 
STR:='; 
Mark:=1; 
For y in (select k from CFSJ t where t.g=x.g) loop 
If mark=1 then 
STR:=y.k; 
Mark:=2; 
end if; 
STR: STR=| | ', '| | y.k. 
end loop; 
The delete from CFSJ t1 where rowid!=(select Max (rowid) from CFSJ t2 where t1. G=t2. G) and t1, g=x.g; 
The update CFSJ set k=STR where g=x.g; 
end loop; 

end;

Good slow oh

CodePudding user response:

Table structure is stuck, contains the index case, only the process, no judgment,

CodePudding user response:

Double loop was unhappy

CodePudding user response:

 create table CFSJ 
(
A VARCHAR2 (1000), 
B VARCHAR2 (1000), 
C VARCHAR2 (1000), 
D VARCHAR2 (1000), 
E VARCHAR2 (1000), 
F VARCHAR2 (1000), 
G VARCHAR2 (1000), 
H VARCHAR2 (1000), 
I VARCHAR2 (1000), 
J VARCHAR2 (1000), 
K VARCHAR2 (1000), 
L VARCHAR2 (1000), 
M VARCHAR2 (1000), 
N VARCHAR2 (1000) 
) 
In tablespace USERS 
Pctfree 10 
Initrans 1 
Maxtrans 255 
Storage 
(
Initial 64 k 
Next 1 m 
Minextents 1 
The maxextents unlimited 
);

CodePudding user response:

G column to add an index,

CodePudding user response:

Your cycle of circulation, why is not in the loop of the outside with a commit, the result eventually you have to have N number of undo, but also time consuming

CodePudding user response:

The create or replace procedure test as
Cursor carddata is the select distinct g from CFSJ;
STR varchar2 (100);
Mark number (1);
The begin

For x in carddata loop
STR:=';
Mark:=1;
For y in (select k from CFSJ t where t.g=x.g) loop
If mark=1 then
STR:=y.k;
Mark:=2;
end if;
STR: STR=| | ', '| | y.k.
end loop;
The delete from CFSJ t1 where rowid!=(select Max (rowid) from CFSJ t2 where t1. G=t2. G) and t1, g=x.g;
The update CFSJ set k=STR where g=x.g;
end loop;

end;
The meaning of this process is only taking CFSJ exterior-interior press g group is the largest rowid, and put all the K column according to the grouping together g free column value? Or table data quantity is too large, if the large amount of data can be used in the temporary table, store the biggest listed the rowid rows and processing data and then truncate table, the temporary table data will be inserted entity tables