It is essential to standardize the way the SQL statements are written. This not only ensures that identical statements performing the same tasks are shared but also provides the ease of reading the statements.
If the list of employees whose salary is greater than the average salary of their respective department is to be found: -
The inappropriate way of writing the query
select ename,job,sal,comm,hiredate,deptno from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno);
The appropriate way of writing the query
SELECT ename,job,sal,comm,hiredate,deptno
FROM emp E1
WHERE sal >
(SELECT AVG(sal)
FROM emp E2
WHERE E2.deptno=E1.deptno);
There is also a technical reason to use a set style throughout the organization. If the statement it is trying to parse, does not match a statement in the shared pool precisely, character by character, and case for case, Oracle will not re-use the parsed statement and instead will opt to create a new cursor. This means your statement will have to undergo the Oracle parse, bind, execute, and fetch phases again. Also the extra resources in the library cache will be added to maintain this copy of a statement. You may actually age out a statement frequently used if the shared pool is too small.
In the following section we would be looking into various situations. Under each scenario the appropriate way to write the query and an inappropriate way to write the query is discussed. It should be noted that both the compared queries return the same results, the only difference being the efficiency. The whole section deals with two tables, namely EMP and DEPT. Their structures are given below: -
EMP table
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NOT NULL NUMBER(2)
DEPT Table
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
DEPTNO from the EMP table references the DEPT table. EMPNO column is the primary key in the EMP table and DEPTNO is the primary key in the DEPT table. EMP table has 9.5 lac rows. DEPT table has 4 rows. It is assumed that none of the columns have indexes unless specified. The cost depicted in the results may vary according to the size of the table and the number of distinct values in the columns. Also note that the tables used here are created in a test environment and more real time examples will be added later as and when they come up considering the fact that this is the first iteration of the document. It is also worth mentioning that Indexes should be made use of on column, only after carefully considering the frequency and type of activities on the column in question.
If the list of employees whose salary is greater than the average salary of their respective department is to be found: -
The inappropriate way of writing the query
select ename,job,sal,comm,hiredate,deptno from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno);
The appropriate way of writing the query
SELECT ename,job,sal,comm,hiredate,deptno
FROM emp E1
WHERE sal >
(SELECT AVG(sal)
FROM emp E2
WHERE E2.deptno=E1.deptno);
There is also a technical reason to use a set style throughout the organization. If the statement it is trying to parse, does not match a statement in the shared pool precisely, character by character, and case for case, Oracle will not re-use the parsed statement and instead will opt to create a new cursor. This means your statement will have to undergo the Oracle parse, bind, execute, and fetch phases again. Also the extra resources in the library cache will be added to maintain this copy of a statement. You may actually age out a statement frequently used if the shared pool is too small.
In the following section we would be looking into various situations. Under each scenario the appropriate way to write the query and an inappropriate way to write the query is discussed. It should be noted that both the compared queries return the same results, the only difference being the efficiency. The whole section deals with two tables, namely EMP and DEPT. Their structures are given below: -
EMP table
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NOT NULL NUMBER(2)
DEPT Table
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
DEPTNO from the EMP table references the DEPT table. EMPNO column is the primary key in the EMP table and DEPTNO is the primary key in the DEPT table. EMP table has 9.5 lac rows. DEPT table has 4 rows. It is assumed that none of the columns have indexes unless specified. The cost depicted in the results may vary according to the size of the table and the number of distinct values in the columns. Also note that the tables used here are created in a test environment and more real time examples will be added later as and when they come up considering the fact that this is the first iteration of the document. It is also worth mentioning that Indexes should be made use of on column, only after carefully considering the frequency and type of activities on the column in question.
Posts
