|Backup and Recover
||There is little question that the best tool for backup, restoration, and
recovery is RMAN. But RMAN is but one piece in the puzzle that constitutes best
practice. Here are some other topics you might wish to explore:
- If every index is a B*Tree index ... reconsider the design decisions
- If your index is supporting a
primary key and PCT_FREE is not set to 0 ... reconsider your
- Have you used MONITORING to verify indexes are being used?
- Consider using Function Based
Indexes. Especially to enforce business rules and in cases where
the cardinality is skewed and a B*Tree index might not be used
most of the time.
- Will you be running queries where
you are looking for NULL? If so consider the use of function based
indexes with an expression that substitutes a value for NULL.
- If every table is a heap table ... reconsider the design decisions
- If you are using the default
values for PCT_FREE and PCT_USED ... reconsider your design
- Have you considered index-organized tables for lookup tables and other
- ROW MOVEMENT
- There is essentially no valid reason
to use a cursor loop in any version of Oracle since 9.0.1. If you
are about to create a cursor loop ... stop ... and write the code
using BULK COLLECT with the LIMIT
clause and FORALL to perform the insert, update, and/or delete.
- If you are writing code that tests
whether a record already exists, updates it if it does and inserts
if it does not ... use a MERGE instead.
- The best solution is not always
SQL*Loader. Look at external tables, look at using the UTL_FILE
built-in package. If the data includes BLOBs consider UTL_FILE, DBMS_LOB,
- If a column contains no NULLs then
create a NOT NULL constraint in cases where it will improve the
quality of information available to the Cost Based Optimizer. For
- Put all PL/SQL procedures and
functions into packages
- In each package put a WHOAMI
function that returns the package's version information.
FUNCTION whoami RETURN VARCHAR2 IS
RETURN 'My Package, Version 1.1.5, 29-Feb-2008';
Thanks to Ed
Prochak for the suggestion.
- Enable Force Logging
- Enable Supplemental Logging