|Backup and Recover
||There is little question that the best tool for backup, restoration, and recovery is RMAN. But RMAN is only 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. Are they B*Tree because someone carefully considered all of Oracle's index types or just because somone typed "CREATE INDEX" and didn't apply any additional synaptic energy.
- If your index is supporting a primary key and PCT_FREE is not set to 0 ... reconsider your design decisions. What do you know, or not know, about the index that makes your decision for a PCT_FREE value valid?
- Have you used MONITORING to verify indexes are being used in development and pre-production testing?
- 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 decisions
- Have you considered index-organized tables for lookup tables and other purposes?
- Have you considered ROWDEPENDENCIES? If not why not?
- Have you enabled ROW MOVEMENT? If not why not?
- Have you considered index and table COMPRESSION? If not why not?
- 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.
- Does your FORALL statement contain a SAVE EXCEPTIONS clause? If not reconsider your decision or create an error table with DBMS_ERRLOG.
- If you are writing code that tests whether a record already exists, updates it if it does and inserts
if it does not ... examine using MERGE instead. Also consider using DBMS_ERRLOG.
- 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, and Oracle MultiMedia.
|NOT NULL Constraints
- 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 example.
|Number of columns in a table
Oracle stores columns in variable length format
Each row is parsed in order to retrieve one or more columns
Each subsequently parsed column introduces a cost of 20 CPU cycles whether or not it is used
Never let the number of columns in a table exceed 255.
Thanks to Ed Prochak for the suggestion.
- 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';
- Enable Force Logging
- Enable Supplemental Logging