|
Design and document your relational business rules in an
ERM
An Entity Relationship Model (ERM) is a very powerful design- and
documentation method to represent business rules for relational databases. If properly
designed - the subsequent operation will be appreciate.
Choosing Primary Keys
Only use simple data types for primary keys (NUMBER,
VARCHAR) - never use the DATE data type, because this is a structured data type which
different handled by relational databases.
If you have a real world unique value use it -
otherwise use a generated key (surrogate key).
Try to avoid composite primary keys - this will lead
to large indexes and is hardly maintainable in child (detail) tables.
Avoid Cascading Updates
Whenever possible design your data model in a way
that primary keys must never be updated. Oracle does not know a declarative constraint
for cascading updates.
Be careful with Cascading Deletes
Cascading deletes should be avoided, they can lead
to unexpected results if one row in the master table deletes hundreds or thousands of
rows in detail tables. It can be a big performance bottleneck if triggers are fired on
the detail tables due to such cascading deletes.
Reduce PL/SQL in Triggers no Business Rules in
Triggers
Triggers can lead to large performance problems,
often fired «behind the scene» and invisible by other users. Never pack
important business rules in triggers. Note that PL/SQL is not object oriented and is very
hardly to debug. Today, modern developing environments (e.g. .NET) offers much more power
and avoids the maintenance nightmare with stored procedures and triggers. The often heard
factum that only stored procedures will be fast is wrong.
Avoid PL/SQL wrappers «which do
nothing»
Packages which wraps simple INSERTS, UPDATES,
DELETES, SELECTS must be avoided, they lead quickly to maintenance nightmares (e.g. - If
a new column is added to a table many stored procedures must be adapted as well). They
offer no benefit and are not faster than the plain SQL statement.
Avoid Redundant Indexes
Redundant indexes are such indexes where already an
other index exists which can be used by the optimizer to fulfill the query. Redundant
indexes usually have the same leading columns as other indexes.
Avoid Foreign Keys with non matching column
definitions
Foreign key column definitions must match their
parent key definition. For example a foreign key defined as VARCHAR(5) is wrong if its
parent key is defined as VARCHAR(10).
Avoid Foreign Keys with mix of nullable and not nullable
columns
Columns of composite Foreign Keys are all
NULL or NOT NULL
Avoid Unique keys with nullable columns
Oracle allows NULL values in UNIQUE keys (not in
primary key constraints). However NULL values in indexes cannot be used and breaks
therefore an index scan.
Avoid Foreign Keys without matching Indexes (or an
unusable one)
If you set an index on a foreign key, which is
usually necessary when tables are joined in queries, make sure that the indexes on
composite foreign keys can be used by the optimizer., do not even specify unusable
indexes.
Think about Indexes
Use indexes sparingly, never put a default (B*TREE)
index on a column which is not selective (for example gender: male / female). Oracle
knows many more kind of indexes such as:
-
Function based Indexes (if you need an index
based on a function)
-
Bitmapped Indexes (non selective index together
with default index)
-
Index-Organized Tables (all columns within the
index, e.g. for intersection tables)
-
Reverse Indexes to avoid hot spots, usually when
updating the same value
Avoid Object Grants granted to users with Grant
Option
Always put objects which access other objects in the
same schema. For example if you need a view which shows only some part of a table,
then put the view in the same schema as the table - otherwise GRANT ADMIN option is
necessary on the table itself, which leads to complex cascading grants, hardly to
maintain.
Be very careful with ROWNUM and ORDER BY
If an ORDER BY clause follows ROWNUM in the same
query, the rows will be reordered by the ORDER BY clause and the result is not as
expected. Use Inline Views to avoid such problems.
Always check your critical SQL Statements using Execution
Plan
You can never be sure, that the optimizer will use
your indexes. Therefore ALWAYS check the execution plan which can be easily
generated.
Keep Transactions small - especially in combination with
SELECT FOR UPDATE
Long running transactions will blow up the rollback
segments in the UNDO tablespace. Try to design short and cleanly defined transactions.
This is especially very important if using SELECT FOR UPDATE which exclusively locks rows
in a table for other sessions. Often SELECT FOR UPDATES blocks other sessions and can
therefore be serious performance problem.
Tune Subqueries (MINUS, NOT IN, OUTER JOIN, NOT
EXISTS)
Often you need to join two tables based on data not
being in one of the tables. These typical problem can be solved with the operators MINUS,
NOT IN, OUTER JOIN, NOT EXISTS. Measure the execution time and check the execution plan
of each to find the best according your situation.
Always use Bind Variables
A bind variable is a placeholder in a SQL command
for a value that will be supplied at runtime by the application. Because you can change
the value of the bind variable as many times as needed without changing the actual text
of the SQL command, the database can reuse the same statement over and over without
incurring the overhead of reparsing the command each time it's executed.
Try to avoid functions in the WHERE clause - or use
function based index
If you use a function in the WHERE clause, an
existing index can often not be used. For example change to following code
from: .... WHERE
TRUNC(aDate) = TRUNC(sysdate)
to: .... WHERE
aDate BETWEEN TRUNC(sysdate) AND TRUNC(sysdate) + .99999;
Avoid case sensitive names
Oracle uses case insensitive names - Oracle,
ORACLE, orACLe - all are the same. Do NOT break this rule - this can be done with
"Oracle", "ORACLE", "orACLe", in this case the three names are different and will lead to
ambiguous problems in SQL statements if some of the expected values are not
found.
Oracle Char Semantics and Globalization
The VARCHAR2 datatype specifies a variable-length character string.
When you create a VARCHAR2 column, you supply the maximum number of bytes or
characters of data that it can hold.
Oracle subsequently stores each value in the column exactly as you
specify it, provided the value does not exceed the column's maximum length of the column.
If you try to insert a value that exceeds the specified length, then Oracle returns an
ORA-12899 error.
A character is technically a code point of the database character
set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS
parameter, which has a default of bytes. The maximum length of VARCHAR2 data is 4000
bytes.
CREATE TABLE test (
t1 VARCHAR2(10 BYTE),
t2 VARCHAR2(10 CHAR),
t3 VARCHAR2(10)
);
DESC test;
Name
Null?
Type
---------------------------------------------------
T1
VARCHAR2(10)
T2
VARCHAR2(10 CHAR)
T3
VARCHAR2(10)
So, if you want to be sure, that the maximum defined number of
characters must be stored, independent of NLS_LENGTH_SEMANTICS, then specify CHAR as
shown above.
Creating a sequence, what does cache and
nocache options mean
With respect to a sequence, the cache option
specifies how many sequence values will be stored in memory for faster access.
The downside of creating a sequence with a cache is
that if a system failure occurs, all cached sequence values that have not be used, will
be «lost». This results
in a «gap» in the
assigned sequence values. When the system comes back up, Oracle will cache new numbers
from where it left off in the sequence, ignoring the so called «lost» sequence values.
To recover the lost sequence values, you can always
execute an ALTER SEQUENCE command to reset the counter to the correct value.
Nocache means that none of the sequence values are
stored in memory. This option may sacrifice some performance, however, you should not
encounter a gap in the assigned sequence values.
Usually leave the default as it is - 20 sequence
values are cached.
Avoid Self Referencing Grants
The owner of an object has automatically granted the need privileges to
its objects. Therefore self Referencing Grants are useless and must be removed.
GRANT SELECT ON arcos_alarm.user_filter TO arcos_alarm;
Avoid to specify ALL Grants
Due to security risks, never use the ALL Grants - you don't know which
Privileges are granted. Usually many privileges, which are not used are granted and can
be a security problem.
GRANT ALL ON arcos_alarm.alarm TO arcos_config;
Use the following guidelines:
-
Privileges on Tables: SELECT, INSERT, UPDATE, DELETE
-
Privileges on Sequences: SELECT
-
Privileges on Packages, Procedures, Functions: EXECUTE
Note, that you do not have to specify any Privileges to Triggers!
|