Note: optimization of the stored procedure, T_text data for more than 100; T_text_standard 2 million data, the stored procedure now seriously affect the efficiency, a great god, please help me to optimize it, or write down the common SQL
T_text table structure, ID, xm, ygid, useful, lx, time, status;

T_text_standard table structure xm, ygid, useful;


The create or replace procedure PROC_text (inputterm in varchar2, return_value out varchar2) is

Cursor bm_cursor
Is the select * from T_text where time=inputterm;

Datarow bm_cursor % rowtype; - data in each row
Data_count number; - data quantity


- inputterm date
- the status status 0 delete 1. Normal
-- ygid employee number
- lx type 01:02: opening an account change 03: pin
Useful account

- T_text_standard standard table
- T_text base table

The begin
Return_value:='yes';

- not to delete the current date data

For datarow bm_cursor in loop
Update T_text t set t.s tatus='0' where ygid=datarow. Ygid and useful=datarow. Useful and time<> Inputterm;
commit;

- query standard library
- standard library circulation query information
The execute immediate 'select count (1) the from T_text_standard where ygid=' '| | datarow. Ygid | |' ' 'and useful=' '| | datarow. Useful | |' ' ' 'into data_count;

If the number of queries & gt; Type 0 without pin, modification for the change
If data_count & gt; 0 then
The execute immediate 'update T_text set lx=' '02' 'where ygid=' '| | datarow. Ygid | |' ' 'and useful=' '| | datarow. Useful | | "' and lx<> "' 03" ';
commit;
The else
- if the number of queries other but not pin, change type to open an account,
The execute immediate 'update T_text set lx=' 01 ' 'where ygid=' '| | datarow. Ygid | |' ' 'and useful=' '| | datarow. Useful | | "' and lx<> "' 03" ';
commit;

- if not in the standard library in information, the copy this information for the account opening information
If datarow. Lx='03' then
The execute immediate 'insert into T_text
Select '012', xm, ygid, lx, useful, status, time
The from T_text where khyhdm="' | | datarow. Id | | '"';
commit;
end if;

end if;


End loop;


End PROC_text;

CodePudding user response:

T_text_standard200 wan data about

Single
Select count (1) the from T_text_standard where ygid=' ' '| | datarow. Ygid | |' ' 'and useful=' ' '| | datarow. Useful | |' "'
See if this statement slow efficiency

CodePudding user response:

T_text_standard 2 million data
So it should be select count (1) the from T_text_standard where ygid=' ' '| | datarow. Ygid | |' ' 'and useful=' ' '| | datarow. Useful | |' ' ' 'out of the question

Is a primary key or index problem?

If there is a primary key or not null columns on the INDEX, so the COUNT (1), COUNT (*), COUNT (ROWID), COUNT (constant), COUNT (primary key), COUNT (not null columns) will first choose primary key indexes on the rapid scanning, the INDEX of FAST FULL SCAN), if the primary key does not exist, would choose not null columns on INDEX, if not null columns no INDEX, must walk on a FULL TABLE SCAN (TABLE ACCESS FULL), among them, the COUNT (ROWID) when walking INDEX slower than other several ways, through the 10053 event can see this several ways besides COUNT (ROWID), other can eventually converted to COUNT (*) to perform,