Use of Bind Variables in Dynamic SQLs

One need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL. It, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. We need to avoid the hard parse when it is submitted:

"create or replace procedure dsal(p_empno in number)
as
begin
execute immediate 'update emp set sal = sal*2 where empno = '||p_empno; commit;
end;

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:
create or replace procedure dsal(p_empno in number) as
begin
execute immediate 'update emp set sal = sal*2 where empno = :x' using p_empno;
commit;
end;
/"

Use Bulk Collect/ Object Type for batch Processing

Whenever there is a need to fetch large volumes of data BULK Collect with Limits is one the best ways. The most important thing to remember when you learn about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.

Enable Oracle to perform hash joins

In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table. However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins. For large tables, hash joins requires lots of RAM.

Front End Field Validation

Apply Validation on the Front End. Don’t let DB constraint drive front end validation

Avoid Distinct if you can

Avoid Distinct (AVOID Join and instead use EXISTS)  

SELECT DISTINCT dept_no, dept_name FROM dept D, emp E WHERE D.dept_no = E.dept_no;

SELECT dept_no, dept_name FROM dept D WHERE EXISTS ( SELECT 'X' FROM emp E WHERE E.dept_no = D.dept_no);

Avoid queries with condition IS NULL

Avoid queries with condition IS NULL ( Rather create column with NOT NULL Default value). If IS NULL is required, consider replacing it with a NVL call and a function based index, while this may not help with outer joins, it will help with IS NULL type selects. select count(*) from test where object_id is null; ( TABLE ACCESS (FULL)) select count(*) from test where nvl(object_id,-1)=-1; - INDEX (RANGE SCAN) OF 'FBI_TEST' (NON-UNIQUE)"

Use GOTO Statements

The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labelled statement or block.

Example:

"DECLARE

v_Counter BINARY_INTEGER := 1;

BEGIN

LOOP INSERT INTO MyTable VALUES (v_Counter, 'Loop count');

v_Counter := v_Counter + 1;

IF v_Counter > 50 THEN GOTO l_EndOfLoop; END IF;

END LOOP;

<>
INSERT INTO MyTable (char_col) VALUES ('Done!');
END;/"