Oracle Procedures

A procedure or function is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and executed as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values. Procedures and functions allow you to combine the ease and flexibility of SQL with the procedural functionality of a structured programming language.

CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS
commission REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT commission_pct / 100 INTO commission FROM employees WHERE employee_id = emp_id;
IF commission IS NULL THEN RAISE comm_missing;
ELSE
UPDATE employees SET salary = salary + bonus*commission
WHERE employee_id = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.');
commission := 0;
WHEN OTHERS THEN
NULL; -- for other exceptions do nothing
END award_bonus;
/
Calling procedure from a package or procedure:
CALL award_bonus(150, 400);
From SQL: EXECUTE award_bonus(150,400);
If there is a error while compiling a procedure then @ SQL prompt >> SHOW ERRORS PROCEDURE

To drop a procedure @ SQL prompt: drop procedure procedure_name;

Teradata Synonyms

Simplify access to objects by creating a synonym (another name for an object). With synonyms, you can:

• Ease referring to a table owned by another user

• Shorten lengthy object names

CREATE [PUBLIC] SYNONYM synonym FOR object; -- only DBA can drop a public synonym.

To refer to a table owned by another user, you need to prefix the table name with the name of the user who created it followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence,

procedure, or other objects. This method can be especially useful with lengthy object names, such as views.

Guidelines

• The object cannot be contained in a package.

• A private synonym name must be distinct from all other objects owned by the same user.

• Create a shortened name for the DEPT_SUM_VU view. ---- CREATE SYNONYM d_sum FOR dept_sum_vu;

• Drop a synonym. ---- DROP SYNONYM d_sum;

In the Oracle server, the DBA can specifically grant the CREATE PUBLIC SYNONYM privilege to any user, and that user can create public synonyms.

TPUMP in Teradata

TPUMP is a flexible multi-session load utility.  The only characteristic of TPUMP that limits its use is its transaction oriented nature.  If the situation allows execution of Fastload or Multiload, these utilities will always outperform TPUMP in a large batch operation when measuring Teradata resource consumption and response time.  Consideration of TPUMP in a load scenario should occur before that of Fastload or Multiload.  Basically TPUMP has none of the disadvantages of Multiload (table locking, load slot usage, single amp operations), Fastload (empty table only, load slot usage), or BTEQ (no checkpointing or restartability). 

Best practices for the development of BTEQ Applications:

• Logtable and error tables are job specific.  
• The BTEQ clean up step drops the logtable and prepares the target table.
• The file layout section should completely define the input file.  .FILLER should be replaced with .FIELD and any null if logic should be completed in the DML section of the TPUMP. 
• If the script is doing complex updates, ROBUST ON is in the "BEGIN LOAD" statement.  
• If the script is doing a high volume (due to high pack and/or sessions) of simple inserts and the checkpoint is not 1, ROBUST ON is in the "BEGIN LOAD" statement.   
• The PACK factor is lower than the maximum (32K block). You will receive a warning if it is too high.
• SERIALIZE is on for UPSERT scripts.
• If maintaining a table without a unique primary index (a NUPI), you probably need to use the SERIALIZE ON feature to prevent blocking.
• When using SERIALIZE ON, be sure to type ‘KEY’ next to each of the primary index fields in the INFILE_LAYOUT section.
• All dates are formatted.
• All 6 digit dates in non characters fields are converted to char(06) so sliding date rule can be applied properly.  
• The # of sessions are appropriate.

No Primary Index Tables

The syntax for the CREATE TABLE statement has been changed to permit user data tables to be created without a primary index. Such tables are referred to as NoPI (No Primary Index) tables.

This feature provides a performance advantage when using FastLoad or TPump Array INSERT to load data into staging tables. Because NoPI staging tables have no row-ordering constraints, the system can always append rows to the end of a NoPI table. Rows in a NoPI table can also be stored on any AMP, which is advantageous for TPump Array INSERT operations because many rows can then be packed into a single AMP step, thus dramatically reducing the performance burden on both CPU and I/O. After a NoPI staging table has been populated, the table can be processed further using SQL DML statements such as DELETE, INSERT, and SELECT.

While using a NoPI table, you can:

• Manipulate rows directly using most SQL DML statements or you can move its rows into a primary-indexed target table using INSERT… SELECT, MERGE, or UPDATE…FROM SQLs.

• Create unique secondary indexes to avoid full-table scans during row access. For example, while single-AMP retrieval of NoPI rows by means of their primary index is not possible, you can work around this through the appropriate assignment of unique secondary indexes (USIs) to NoPI tables, and by careful construction of request conditions to specify those USIs to access individual NoPI table rows.

• Create nonunique secondary indexes (NUSIs) to facilitate set processing retrieval of rows from NoPI tables.

• Avoid full-table scans when deleting a set of rows from a NoPI table by assigning secondary indexes and specifying them in your request conditions.

Benefits.

• Enhanced performance for FastLoad bulk data loads into staging tables.
• Enhanced performance for TPump Array INSERT minibatch loads into staging tables.

Points to note:

• The absence of a primary index or secondary index in NoPI tables means that all row access is done using full-table scans.

• The drawback to using secondary indexes for NoPI tables is that while they can enhance query processing significantly, they can also reduce load performance.

• You cannot modify a NoPI table using SQL UPDATE or UPSERT.

• This feature introduces a new DBS Control flag, PrimaryIndexDefault, that determines the behavior of a CREATE TABLE statement that does not explicitly specify any of the following:

• PRIMARY INDEX clause
• NO PRIMARY INDEX clause
• PRIMARY KEY or UNIQUE constraints

Derived Tables

Derived tables can be a powerful technique to produce efficient queries, but they can also cause major performance problems when used in inappropriate situations.

Here are some guidelines for the use of derived tables:

Never use a derived table to simply restrict the records of a large table prior to joining it to some other table. Doing this prevents the optimizer from using statistics on the table when it is subsequently joined to another table, since the derived table is pulled into a spool file, and this spool file will not have statistics available to the optimizer to prepare downstream joins.