Spool's SQL statement is very complicated. In order to reduce running time improvement performance, I consider first identifying the total number of records in accordance with the index, and whether the SQL to be run is added to the SQL to run according to the total number.
Such as:
SQL> Variable A2 Number;
SQL> SELECT Count (*) INTO: A2 from Table Where Length (ZD) = 2;
In the SQL, select ... from Table Where ..... and: a2> 0;
Of course, we need to be cautious roster conditions to calculate A2 to make it maximize in line with our requirements. In short, there is more than to improve performance.
I specific examples are as follows:
- Number of characters per row
SET LINESIZE 999
- Do not generate new pages
Set PageSize 50000
- Settings
--COL UserName Format A4
--Col A Format 999, 999, 999
- Output column header
Set heading on
- Disable the counting feedback from the last line
Set feedback off
- When executing a command file, whether the command itself is displayed on the screen
Set echo off
-
Set Termout Off
- Empty extra spaces, such as: LineSize is too long
Set trimout on
Set trimspool on
- The following business logic
......
- Var A2, A3
Variable A2 Number;
Variable A3 Number;
Begin
Select Count (*) INTO: A2 from Table Where Where (ZTB) = 2;
SELECT Count (*) Into: A3 from Table Where Length (ZTB) = 3;
END;
/
Spool e: /output/tt_clzt_1.txt;
The PROMPT XXX value is not in the specified YYY table;
Select ..... from .... where .... and: a3> 0;
Spool OFF;
