Logical Structure with Indentation
Indentation is one of the most common and effective techniques used to display a program's logic via format. As illustrated in the following examples, programs that are indented are easier to read than those that are not indented. Here is an unintended IF statement:
> 22 THEN
IF max_totals = 0 THEN
ELSE WHILE more_data
indentation not only adequately reveals the logical
structure of the code but also keeps the statements close enough
together to read comfortably. Here is the three-space
indented version of the previous nested IF statement:
IF to_number(the_value) > 22 THEN
IF max_totals = 0 THEN
Using Case to Aid Readability
PL/SQL code is made up of many different components: variables, form items, report fields, procedures, functions, loops, declarations, control elements, etc. But they break down roughly into two types of text: reserved words and application-specific names or identifiers. Reserved words are those names of language elements that are reserved by PL/SQL and have a special meaning for the compiler. Some examples of reserved words in PL/SQL are:
Application-specific identifiers are the names that
you give to data and program structures that are specific to your
application and that vary from system to system. The compiler treats
these two kinds of text very differently. You can improve the
readability of your code greatly by reflecting this difference in the
way the text is displayed. Many developers make no distinction between
reserved words and application-specific identifiers. Consider the
following lines of code:
to_number(the_value)>22 and num1 between lval and hval then
newval := 100;
elsif to_number(the_value) < 1 then
While the use of indentation makes it easier to follow
the logical flow of the IF statement, all the words in the statements
tend to blend together. It is difficult to separate the reserved words
and the application identifiers in this code. Changing entirely to
uppercase also will not improve matters. Indiscriminate, albeit
consistent, use of upper- or lowercase for your code reduces its
readability. The distinction between reserved words and
application-specific identifiers is ignored in the formatting. This
translates into a loss of information and comprehension for a developer.
You can easily solve this problem by adopting a guideline for using a mix of upper- and lowercase to your code. I have recoded my previous example below, this time using the UPPER-lower style: all reserved words are written in UPPERCASE and all application names are kept in lowercase:
IF to_number(the_value) > 22 AND num1 BETWEEN lval AND hval THEN
newval := 100;
ELSIF TO_NUMBER (the_value) < 1 THEN
calc_tots (TO_DATE ('12-jan-95'));
Using a mixture of upper- and lowercase words
increases the readability of the code by giving a sense of dimension to
the code. The eye can more easily cruise over the text and pick the
different syntactical elements of each statement. You can focus quickly
on the lowercase words for the application-specific content. Consistent
use of this method makes the program listings more attractive and
accessible at a glance.
Most of your code consists of individual statements, such as assignments, calls to modules, and declarations. A consistent approach to formatting and grouping such statements will improve the readability of your program as a whole. This section suggests some guidelines.
· Use at most one statement per line
PL/SQL uses the semicolon (;) as the logical terminator for a statement, as a result you can have more than one statement on a line and you can continue a single executable statement over more than one line. You will sometimes be tempted to place several statements on a single line, particularly if they are very simple. Consider the following line:
new_id := 15; calc_total (new_id); max_dollars := 105 * sales_adj;
It is very difficult to pick out the individual
statements in this line, in addition to the fact that a procedure is
called in the middle of the line. By placing each statement on its own
line you mirror the complexity of a program--the simple lines look
simple and the complex statements look complex--and reinforce the
top-to-bottom logic of the program:
new_id := 15;
max_dollars := 105 * sales_adj;
whitespace inside a statement
You can use all the indentation and blank lines you want to reveal the logic of a program and still end up with some very dense and unreadable code. It is also important to employ whitespace within a single line to make that one statement more comprehensible. Always include a space between every identifier and separator in a statement. Instead of this:
WHILE(total_sales<maximum_sales AND company_type='NEW')LOOP
WHILE (total_sales < maximum_sales AND company_type = 'NEW') LOOP
Because PL/SQL is an extension to the SQL language; you can place SQL statements directly in your PL/SQL programs. You can also define cursors based on SELECT statements. This section summarizes my suggestions for formatting SQL statements and cursors for maximum readability.PL/SQL supports the use of four SQL DML (Data Manipulation Language) statements: INSERT, UPDATE, DELETE, and SELECT. Each of these statements is composed of a series of "clauses," as in the WHERE clause and the ORDER BY clause. SQL statements can be very complex, to say the least. Without a consistent approach to indentation and alignment inside these statements, you can end up with a real mess. I have found the following guidelines useful:
Right-align the reserved words for the clauses against the DML statement.
I recommend that you visually separate the SQL reserved words which
identify the separate clauses from the application-specific column and
table names. The following table shows how I use right-alignment on the reserved words to create a vertical border between them and the rest of
the SQL statement:
Here are some examples of this format in use:
SELECT last_name, first_name
WHERE department_id = 15
AND hire_date < SYSDATE;
SUM(salary) AS total_salary
GROUP BY department_id
ORDER BY total_salary DESC;
INSERT INTO employee
(employee_id, ... )
VALUES (105 ... );
SET hire_date = SYSDATE
WHERE hire_date IS NULL
AND termination_date IS NULL;
This right alignment makes it very easy to identify the different clauses of the SQL statement, particularly with extended SELECTs. You might also consider placing a blank line between clauses of longer SQL statements (this is possible in PL/SQL, but is not acceptable in "native" SQL executed in SQL*Plus).
Use meaningful abbreviations for table and column aliases
impossible to read a program when a query has a six-table join and
the tables have been assigned aliases A, B, C, D, E, and F. How can you
possibly decipher the WHERE clause in the following SELECT?
SELECT ... select list ...
FROM employee A, company B, history C, bonus D, profile E, sales F
WHERE A.company_id = B.company_id
AND A.employee_id = C.employee_id
AND B.company_id = F.company_id
AND A.employee_id = D.employee_id
AND B.company_id = E.company_id;
With more sensible table
aliases (including no tables aliases at all where the table name was
short enough already), the relationships are much clearer:
SELECT ... select list ...
FROM employee EMP, company CO, history HIST, bonus, profile PROF, sales
WHERE EMP.company_id = CO.company_id
AND EMP.employee_id = HIST.employee_id
AND CO.company_id = SALES.company_id
AND EMP.employee_id = BONUS.employee_id
AND CO.company_id = PROF.company_id;
Formatting Exception Handlers
PL/SQL provides a very powerful facility for dealing with errors. An entirely separate exception section contains one or more "handlers" to trap exceptions and execute code when that exception occurs. Logically, the exception section is structured like a conditional CASE statement (which, by the way, is not supported by PL/SQL). As you might expect, the format for the exception section should resemble that of an IF statement. Here is a general example of the exception section:
WHEN NO_DATA_FOUND THEN
WHEN DUP_VAL_ON_INDEX THEN
WHEN OTHERS THEN
The PL/SQL block structure forms the backbone of your code. A consistent formatting style for the block, therefore, is critical. This formatting should make clear these different sections. Consider the following function:
company_name (company_id_in IN company.company_id%TYPE) RETURN
VARCHAR2 IS cname company.company_id%TYPE; BEGIN
SELECT name INTO cname FROM company
WHERE company_id = company_id_in;
EXCEPTION WHEN NO_DATA_FOUND THEN
You know that this program is a function because the first word in the program is FUNCTION. Other than that, however, it is very difficult to follow the structure of this program. Where is the declaration section? Where does the executable section begin and end? Here is that same function after we apply some straightforward formatting rules to it:
SELECT name INTO cname FROM company
WHERE company_id = company_id_in;
WHEN NO_DATA_FOUND THEN
The declaration section, which comes
after the IS and before the BEGIN, clearly consists of a single
declaration of the cname variable. The executable section consists of
all the statements after the BEGIN and before the EXCEPTION statement;
these are indented in from the BEGIN. Finally, the exception section
shows a single specific exception handler and a WHEN OTHERS exception.
Generally, indent the statements for a given section from the reserved
words which initiate the section. You can also include a blank line
before each section, as I do above, for the executable section (before
BEGIN) and the exception section (before EXCEPTION). I usually place
the IS keyword on its own line to clearly differentiate between the
header of a module and its declaration section.
You shouldn't spend a lot of time formatting your comments. You need to develop a style that is clean and easy to read, but also easy to maintain. When you have to change a comment, you shouldn't have to reformat every line in the comment. Lots of fancy formatting is a good indication that you have a high-maintenance documentation style. Please use some very simple documentation guidelines to clarify the structure of the code. Here are my recommendations:
CREATE OR REPLACE Procedure FDATA_ISSUEDATA_IMPORT
-- PURPOSE: Import Issuedata Files
-- PARAMETERS: None
-- VERSION: FraudGuard 8.35x Issuedata Process using Java
-- NOTES: An oracle DB job will call this procedure
-- The logic of this procedure is the following:
-- -- An Oracle directory called ISSUEDATA is needed, that directory is created
-- during installation. If directory is modified after that, will get that modification
-- -- Create a unique external table based on the definitions loaded on the ISSUEMAPFIELD table
-- That external table will load the issuedata file
-- -- Copy all those rows to the TEMP_ISSUEDATA Table
-- -- After that I can perform massive loads of data into the ISSUEDATA Table
--RESTRICTIONS: Customer CAN REMOVE a field from ISSUEMAPFIELD
-- Customer CAN modify STARTPOSITION,FIELDLENGTH or DATAFORMAT on ISSUEMAPFIELD
-- Customer CAN modify IssuePollExtension and PollingLocation
-- CONSIDERATIONS FOR THIS CUSTOMER: Run it at 8:45 am, 10:45 am, 12:45 pm, and 2:15pm
-- REVISIONS: (when, who, what)
-- 01/09/06 - Developed by Diego Pafumi
-- 05/22/07 - Added Logic to process 2 payees for PCR 15365
Table names should reflect the data in the table. The names are a mix of upper and lowercase letter with no underscores and in singular. The names should be fully spelled out and in singular. It is a bad idea to use a prefix or suffix to identify tables as '_TAB' suffix for all tables.
Table columns should be named in the same way as tables are named.
Examples: FormID, ScanDate, ZoneName.
Following the prefix should be an abbreviation for a verb, or action word. Some typical actions on data are select (sel), insert (ins), update (upd) and delete (del). If a combination of actions is possible in a stored procedure, then use the first letters of the action. For example, if a stored procedure can do an insert or update, then use iu. An underscore character should then follow the verb abbreviation.
Next, the name of the table affected or some other noun should be used to describe what is affected. Each word in this part of the spec is a mix of upper and lowercase, where the first character is uppercase and the rest lowercase. There should be no underscores in this part of the name.
Examples are listed below:
ap_ins_Form - This procedure name is an Atlantis stored procedure that inserts a record into the Form table.
ap_iu_Document - This procedure name is Atlantis stored procedure that inserts or updates a record into the Document table.
ap_sel_Zone - This procedure name is an Atlantis stored procedure that selects records from the Zonetable.
p_del_Account - This procedure is project specific procedure that deletes records from the Zone table.
Following this prefix is an indicator describing the action that triggers the trigger. Use del for delete, ins for insert and upd for update. If a trigger is used for more than one of these operations, then just include the first letter. For example, use iu for an insert/update trigger. Next put an underscore followed by the suffix. The suffix contains an upper/lowercase name that includes the table name and/or any other descriptive text.
Examples: at_del_Document, t_iu_UserProfile.
Atlantis views will have a prefix of "av_". A project specific view should have a prefix of "v_" or "view_".
Following the prefix the view name should contain some sort of descriptive reference. If the view contains a simple join of two tables, then include the table names. For example: v_Table1Table2.
The suffix should be upper/lowercase.
Index names should have an "in_", "fk_", "pk_" or "un_" prefix followed by table name and fields used on the index. I'd suggest:
"fk_" for FK Indexes
"pk_" for PK Indexes
"un_" for Unique Indexes
"in_", for Indexes created for speed reasons.
Primary keys are to be prefixed with "pk_", unique keys with "un_" and foreign keys start with "fk_".
The remainder of the name usually contains the name of the field(s) included in the key.
Sequence names begin with an "s_", followed by an underscore and then the field name (i.e. s_Field). If field name is ambiguous, then precede the field name with table name s_TableField.
Prefix scalar variable names start with v_ like v_ErrorCode.
Prefix parameters with p_ like p_AccountNumber
Prefix constants with c_ like c_Tax.
Prefix record collections with r_ (alternatively suffix with _record)
Prefix %rowtype% collections with rt_ (alternatively suffix with _record_type)
Prefix pl/sql tables with t_ (alternatively suffix with _table)
Prefix table types with tt_ (alternatively suffix with _table_type)
The following section offers some concrete advice on potential issues you might encounter when declaring variables in PL/SQL
variables to database datatypes using %TYPE and %ROWTYPE.
When you declare a variable using %TYPE or %ROWTYPE, you “anchor” the type of that data to another, previously defined element. If your program variable has the same datatype as a column in a table or view, use %TYPE to define it from that column. If your record has the same structure as a row in a table or view, use %ROWTYPE to define it from that table. Your code will automatically adapt to underlying changes in data structures.
Oracle architectural design tips
Use RAM data caching
You must be aware that Oracle9i allows very large memory regions in order to cache frequently referenced row information. The caching of frequently referenced information should be a major design goal primarily because RAM access is two orders of magnitude (more than 10,000 times) faster than row access from disk. The larger the Oracle data block buffer cache, the faster the SQL queries will execute. The size of the RAM data buffers will have a direct impact on Oracle performance, and all systems run fastest when fully cached in the data buffers.
Buy fast processors
The CPU speed of the Oracle database server has a direct impact on performance. High-performance 64-bit CPUs will often perform 10 times faster than 32-bit processors. The 64-bit processors are available on all major platforms and include:
Use a 64-bit version of Oracle
It is highly recommended that Oracle systems exist on a dedicated database server with a 64-bit CPU architecture and a 64-bit version of Oracle. The 64-bit version of Oracle lets you create large SGA regions, and large projects commonly require more than 20 gigabytes of RAM data buffers. A serious shortcoming of 32-bit Oracle is the 1.7-gigabyte size limitation for the SGA.
Use large data blocks on indexes to minimise disk I/O
Oracle index access performs best with 16K and 32K Oracle block sizes. You should consult your application-specific OS documentation and create index tablespaces of the largest value for your environment.
Use Oracle Parallel Query
All table access should be tuned to avoid large-table full-table scans, but often, such scans are required. Make certain all full-table scans fully utilize Oracle Parallel Query to improve performance.
Choose proper SQL optimisation
The choice of optimiser mode is critical to Oracle SQL performance. In Oracle9i, approximately half of all queries run faster with rule-based optimisation; the other half run fastest with cost-based optimisation.
Always design for package pinning
All frequently referenced PL/SQL packages should be pinned into the shared pool by using the dbms_shared_pool.keep procedure. Doing so will greatly speed Oracle PL/SQL execution.
Adventage of Multi-Block Reads
On most servers, Oracle can read up to 1 Mb (typically 64-128 blocks) at one time. That is why a full table scan can be performed so quickly. Keep in mind, however, that 2 conditions must be met:
1) The database parameter Db_File_Multiblock_Read_Count must be set correctly; and
2) The table or index being scanned must have extent sizes of at least 1Mb.
If the multiblock parameter is set too low at the database level, you can easily alter your session to set the parameter higher. The second point above recognizes that Oracle will not continue a multi-block scan across extent boundaries. If most of the extents are great than 1 megabyte, it’s probably okay. (This is one of those cases where extent sizing really does matter.) It is especially important to optimize your reads if you are using Oracle parallelism. Why launch many extra processes if you don’t first optimize what a single process can do?
Avoid Massive Deletes
Oracle is simply not very fast at deleting millions of rows. Instead, copy and temporarily store the rows you DO want, truncate the table, and then put the rows back in. This can easily be 10x faster. If this method is not feasible due to business considerations, consider multi-threading (discussed later.)
Use Summary Tables and Materialized Views
If you repeatedly access a large table, consider building an aggregate table, or materialized view. A materialized view used in this manner is just like a table, but you can rebuild it with a single command. Remember to use Oracle parallelism to speed-up the refresh. Whether you use a materialized view, or actual table, the idea is to create a “pre-digested” form of the data. You include only the columns and rows that meet your conditions. Once you build the summary table, you can also build custom indexes that optimize your query. A commit after every row will usually wreck performance. Most jobs should commit no sooner than every 1,000 rows. Committing every 10,000 rows would be a good rule of thumb. Unless you are working with many millions of rows, further increasing the commit interval doesn’t really offer much improvement.
If you build and populate a new table, don’t forget to gather statistics. It’s very common to see a performance bottleneck caused by incomplete or inaccurate table statistics. It’s not necessary to sample all the rows when gathering statistics; the “estimate” option is usually fine.
Speed Up Inserts
In cases where you don’t really need transaction logging, you can speed up inserts a bit by using the Nologging feature. For inserting rows, set the table NOLOGGING (using Alter Table …) , then use this syntax: INSERT /*+APPEND */ .
Remember, however, that all the new rows will be placed at the end of the table--above the “high water” mark. This means that if you are performing deletes from the table, these “holes” will never be filled. The table will grow rapidly. Eventually, you will need to rebuild the table to crunch it down.
Use Oracle Parallelism
For scans of huge tables or indexes, it is often good practice to invoke Oracle Parallel Query Option (PQO.) On most systems, using a parallel degree of 6 gives excellent performance. Of course, this assumes that you have the spare resources (disk and cpu). For example:
Select /*+Parallel (T 6) */ Emp, Name from Giant_Table T
Remember that Oracle will actually start a total of 2x the degree specified (one set for reading, another set to process/sort). So, the example above will actually result in 12 processes. It is usually best to invoke parallelism via a SQL hint, rather than setting the table to a degree higher than one. If you set the table to invoke parallelism, Oracle will tend to start up the slave processes for many queries, whether you actually want parallelism or not.
Use Bind Variables
If you will be repeating a massive number of SQL statements, it is very important to properly use bind variables. In this way, the database engine avoids a re-parse of each statement. Also, a large number of unique SQL statements tends to flood the shared pool, causing other SQL statements to be released as the engine makes room for the new SQL. This will annoy the DBA.