Oracle SQL and PL/SQL Developer Best Practices
Version 19c

Overview
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Our industry is full of advice, most of it of near zero value, for DBAs defining "best practice." I too have created a DBA "Best Practices" page with the acknowledged understanding that any discussion of what is "best" in Oracle should always begin with "it depends."

That said ... there are some things in SQL and PL/SQL development that should always be considered while developing a solution. This page is where I will accumulate them.
 
Basic Principle
Always code to optimize the following:
  • least work for the computer
  • maintainable code
How you prioritize between the two is the measure of an adequate developer and a really senior developer. If you can write easily maintainable code that makes the computer do the least possible work: Low I/O, low cpu utilization, low memory usage, you win.

You will see that some items here are also on the DBA Best Practices page. Why? Because if the developer hasn't done a good job in the first place ... it becomes the DBA's job to clean up the inevitable mess.

As you read this do not forget ... these are guidelines. What is "best" always has an "it depends" component that must be considered.
 
Low Work
1. Code reviews are essential. If they exist take part ... if they don't start them.
2. Design tables based on the principle of least i/o To minimize i/o utilize partitioning to avoid reading and writing blocks that do not need to be touched. Once you have paid for the partitioning license it is in your best interest to fully leverage this valuable asset. Most likely there are advantages in subpartitioning and reference partitioning: Consider them.
2. Design tables based on the principle of least cpu Order columns such that the primary key is at the far left. Order the columns to the right of the primary key column based on most to least frequently accessed as part of WHERE clause filtering. The sooner you can discard a row excluded from a DML statement the less work you have performed. Then build editioning views, on top of the tables, to create a human understandable structure for queries and reports.

These two tables contain the exact same information. Consider the implication if you were sending out many thousands of emails.

CREATE TABLE uwclass.person1 (
per_id       NUMBER,
given_name   VARCHAR2(25),
middl_inits  VARCHAR2(4),
family_name  VARCHAR2(30),
address1     VARCHAR2(30),
address2     VARCHAR2(30),
city         VARCHAR2(30),
state_prov   VARCHAR2(2),
country      VARCHAR2(2),
postal_code  VARCHAR2(6),
country_code VARCHAR2(3),
home_phone   VARCHAR2(12),
cell_phone   VARCHAR2(12),
pref_email   VARCHAR2(40),
member_flag  VARCHAR2(1),
member_level VARCHAR2(1),
ok_2_email   VARCHAR2(1),
expire_date  DATE);

CREATE TABLE uwclass.person2 (
per_id       NUMBER,
ok_2_email   VARCHAR2(1),
expire_date  DATE,
pref_email   VARCHAR2(40),
family_name  VARCHAR2(30),
given_name   VARCHAR2(25),
address1     VARCHAR2(30),
address2     VARCHAR2(30),
city         VARCHAR2(30),
state_prov   VARCHAR2(2),
country      VARCHAR2(2),
postal_code  VARCHAR2(6),
country_code VARCHAR2(3),
home_phone   VARCHAR2(12),
cell_phone   VARCHAR2(12),
member_flag  VARCHAR2(1),
member_level VARCHAR2(1),
middl_inits  VARCHAR2(4));

CREATE OR REPLACE EDITIONING VIEW human_readable AS
SELECT per_id, given_name, middl_inits, family_name, address1, address2,
city, state_prov, country, postal_code, country_code, home_phone, cell_phone,
pref_email, member_flag, member_level, ok_2_email, expire_date
FROM uwclass.person2;

INSERT INTO uwclass.human_readable VALUES
(1, 'Daniel', 'A', 'Morgan', '1234 Main St', NULL, 'Mississagua', 'ON', 'CA', '1X1AB3',
'001', '206-555-1212', '206-555-1212', 'damorgan12c@gmail.com', 'Y', 'G', 'Y', SYSDATE);

INSERT INTO uwclass.person2 VALUES
(
3. Avoid single row processing You have to move 100 oranges from one room to the other. You can do it one at a time or put 100 oranges in a box and make one trip: Which one is fastest? which one requires the least work? We all know the answer. Use BULK COLLECT and FORALL with the LIMIT and SAVE EXCEPTIONS clauses.
4. Fully qualified names conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE w_aliasing AUTHID DEFINER IS
 p POSITIVE;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    SELECT SUM(s.srvr_id)
    INTO p
    FROM uwclass.servers s;
  END LOOP;
END w_aliasing;
/

CREATE OR REPLACE PROCEDURE wo_aliasing AUTHID DEFINER IS
 n POSITIVE;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    SELECT SUM(srvr_id)
    INTO n
    FROM servers;
  END LOOP;
END wo_aliasing;
/

alter system flush buffer_cache;
ALTER SESSION SET tracefile_identifier = 'test_plan1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
exec w_aliasing;
ALTER SESSION SET EVENTS '10046 trace name context OFF';

alter system flush buffer_cache;
ALTER SESSION SET tracefile_identifier = 'test_plan2';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
exec wo_aliasing;
ALTER SESSION SET EVENTS '10046 trace name context OFF';
5. Constants and variables  
6. Native compilation  
7. Alias tables and views conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE w_aliasing AUTHID DEFINER IS
 p POSITIVE;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    SELECT SUM(s.srvr_id)
    INTO p
    FROM servers s;
  END LOOP;
END w_aliasing;
/

CREATE OR REPLACE PROCEDURE wo_aliasing AUTHID DEFINER IS
 n POSITIVE;
BEGIN
  FOR i IN 1 .. 100000 LOOP
    SELECT SUM(srvr_id)
    INTO n
    FROM servers;
  END LOOP;
END wo_aliasing;
/

alter system flush buffer_cache;
ALTER SESSION SET tracefile_identifier = 'test_plan1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
exec w_aliasing;
ALTER SESSION SET EVENTS '10046 trace name context OFF';

alter system flush buffer_cache;
ALTER SESSION SET tracefile_identifier = 'test_plan2';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
exec wo_aliasing;
ALTER SESSION SET EVENTS '10046 trace name context OFF';
8. PL/SQL Warnings Simply put: If you do not enable PL/SQL warnings you have absolutely no idea in the world whether the PL/SQL code you are writing has major issues: Click on the link at page bottom.
9. Reserved Words  
10. Put all code into PL/SQL packages  
11. Secure Views To improve source-code view security write them in the form: SELECT * FROM TABLE(pipeline_table_function);
   
   
   
   
   
   
 
Maintainable Code
1. Code reviews are essential. If they exist take part ... if they don't start them.
2. Make liberal use of PRAGMA INLINE.
3.  
   
   
   
   
   

Related Topics
DBA Best Practice Guidelines
Oracle Best Practice Guidelines
Pipelined Table Functions
PL/SQL Warnings
Pragma Inline
Troubleshooting Best Practice Guidelines
What's New In 21c
What's New In 23c