| Topic |
Discussion |
| 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:
|
| Create Index |
- 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.
|
| Create Table |
- 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?
|
| Cursor Loops |
- 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.
|
| Array Processing |
- Does your FORALL statement contain a SAVE EXCEPTIONS clause? If not reconsider your decision or create an error table with DBMS_ERRLOG.
|
| DML |
- 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.
|
| Loading Data |
- 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 most cases doing so 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. |
| PL/SQL Code |
- Put all PL/SQL procedures and functions into packages
- In each package put a WHOAMI function that returns the package's version information.
For example:
FUNCTION whoami RETURN VARCHAR2 IS
RETURN 'My Package, Version 1.1.5, 19-Jan-2019';
END whoami
Thanks to Ed Prochak for the suggestion. |
| Predefined Inquiry Directives |
Because these are a free feature included in the database you've likely never heard of them. [Click here], run the demos and learn about these valuable parts of the product you've already paid for. |
| Replication |
- Enable Force Logging
- Enable Supplemental Logging
|
