Many people like:
Select count (*) Into t_count from t where condition; if t_count> 0 Then ...
The problem with this method is that we need to exist, not the total number of records. The total number of query records pays unnecessary performance costs.
Two cases:
1. If it is determined whether or not there is a record, you want to query the information in the record, or decide to perform INSERT / UPDATE operations on the table, typical operation is: a. Select country (*) Into t_count from t where condition ; if t_count> 0 THEN SELECT COLS INTO T_COLS from T Where Condition; ELSE Otherstatement;
B. Select count (*) INTO T_COUNT from T where condition; if t_count> 0 THEN UPDATE ...; ELSE INSERT ...;
These two operations can be used in direct operation, and then perform this exemplary judgment at all, it is not performed at all!
Overwrite a. Begin Select Cols Into T_cols from T Where Condition; Exception When No_Data_Found The Begin Statement-Block2; End; When Others The Begin Raise Error ... End;
Recorded B.
Update T set ... where condition; if SQL% Notfound Then Insert INTO T ... END IF;
Or: Begin Insert Into T ... EXCEPTION WHEN DUP_VAL_ON_INDEX THEN BEGIN UPDATE T set ... End;
Which of these two methods depends on which situation you think may be higher.
2. If there is a record to determine if other operations are performed, such as the next example Select Count (*) INTO T_COUNT from T Where Condition; if t_count> 0 Then ...
It can be changed to such a statement:
Select Count (*) Into T_Count from Dual WHERE EXISTS (SELECT 1 from T Where Condition); if t_count> 0 Then ...
With the rewritable statement, most of the cases should have better performance than the original statement. (Of course, if you want to query the table itself is a single line or only a few lines of records, the direct query should be better)
Favorite this: http://blog.itpub.net/post/6/9018
