Every time a SQL statement is executed, Oracle needs to perform many internal processing steps; the statement needs to be parsed, indexes evaluated, variables bound, and data blocks read. The Oracle database tries to reuse as much work as possible by caching information in the Shared Pool and accessing previously used blocks in the Buffer Cache. However, the more the number of database accesses are reduced, the more overheads can be saved. Reducing the physical number of trips to the database is particularly beneficial in client-server configurations in which the database may need to be accessed over a network.
The following examples show four distinct ways of retrieving data about employees who have employee numbers 0342 or 0291. Method 1 is the least efficient, method 2 is more efficient, method 3 is even more efficient, and method 4 is the most efficient of all.
Method 1 shows two separate database accesses:
SELECT emp_name, salary, grade
FROM emp
WHERE emp_no = 0342;
SELECT emp_name, salary, grade
FROM emp
WHERE emp_no = 0291;
Method 2 shows the use of one cursor and two fetches:
DECLARE
CURSOR C1 (E_no number) IS
SELECT emp_name, salary, grade
FROM emp
WHERE emp_no = E_no;
BEGIN
OPEN C1 (342);
FETCH C1 INTO ..., ..., ...;
.
.
.
OPEN C1 (291);
FETCH C1 INTO ..., ..., ...;
CLOSE C1;
END;
Method 3 shows a SQL table join:
SELECT A.emp_name, A.salary, A.grade,
B.emp_name, B,salary, B.grade
FROM emp A,
emp B
WHERE A.emp_no = 0342
AND B.emp_no = 0291 ;
In this example, the same table is identified by two aliases, A and B, that are joined by a single statement. In this way, Oracle uses only one cursor and performs only one fetch.
Method 4 shows how one query can be used to return the two rows:
SELECT emp_name, salary, grade,
FROM emp A
WHERE emp_no IN (0342,0291) ;
The lesson here is that using one SQL statement instead of multiple statements can significantly improve database performance. If possible, have the application retrieve all the information that it needs in one operation rather than making multiple trips to the database.
Note: One simple way to increase the number of rows of data that can be fetched with one database access, and thus reduce the number of physical calls needed, is to reset the ARRAYSIZE parameter in SQL*Plus, SQL*Forms, and Pro*C. A setting of 200 is suggested.
0 comments:
Post a Comment