Notes on Webinar called The Evils of Implicit Conversions and other SQL Skullduggery from Red Gate Software by Tom Kyte.
Be aware of the four (4) types of triggers (the compound trigger is just a combination of these):
- Before Statement
- Before Each Row
- After Each Row
- After Statement
Tom Kyte demonstrated that triggers can fire multiple times due to row locking. He demonstrated multiple firings occurring when a statement is blocked and then rolled back to begin again—internally by the Oracle RDBMS engine in order to avoid the ‘lost update’ problem.
He says that:
- Never do anything non-transactional in a trigger
- Pragma autonomous_transaction is a really bad idea in general
- UTL_packages – really bad idea. They do not roll back (utl_file, utl_http, utl_smtp, etc etc etc)
- Even just setting global variables in a trigger can be tricky
This has huge implications for sites that use triggers for auditing purposes as:
- Rolled back changes are recorded usually because of PRAGMA AUTONOMOUS_TRANSACTION
- Multiple instances of the same change are recorded
- Recording a change that never happened—the change could see multiple consistent read images until the final one is read
Triggers are not called during direct path loads via SQL Loader. The presence of a trigger also silently stops direct path loads in SQL.
NULLS and Indexes and Cardinality
Tom Kyte’s mantra is:
Wrong cardinality = Wrong Plan
He demonstrated that replacing NULLs by a ‘fake’ value resulted in an incorrect plan because the column histogram became distorted.
He says that the following myths are not true:
- There is a pervasive myth that indexes and NULLs are like matter and anti-matter
- There is the thought that “where column is null” cannot use an index
- There is a thought that NULLs are not indexed
He demonstrated how to index null values of a column through the use of a concatenated index with the leading column as nullable and the next column was not.
CREATE INDEX t_null_col ON t(null_col, 0);
This I did not know. He used a constant as part of the index key. Because a constant is never NULL, the index will point to all the rows in the table. So, the predicate, NULL_COL IS NULL, will use an INDEX RANGE SCAN, if the CBO thinks so.
In B*Tree indexes, entirely NULL entries are excluded.
In a bit mapped index, NULLs are indexed.
Implicit Conversions are Evil
Tom Kyte says that [i]mplicit conversions…are probably the #2 cause of bugs…. NLS_DATE default settings are a problem waiting to happen. Also, changing the NLS_DATE through the use of constants enables SQL injection.
He says that implicit conversions causes the following performance issues:
- Repeated conversions
- Access path reductions
- Partition elimination eliminated
Useful SQL*Plus commands for compiling PL/SQL code:
ALTER SESSION SET plsql_warnings = 'enable:all'; CREATE OR REPLACE PROCEDURE P ... SHOW ERRORS PROCEDURE P
Implicit conversions wrap the column into a function call which precludes index lookup and partition elimination.
To prevent these problems, Tom Kyte recommends the following setting:
ALTER SESSION SET plsql_warnings = 'error:all';
This elevates all warnings to errors so preventing compilation. He suggests a conversion over several years.