native barcode generator for crystal reports free download TRANSACTIONS in Objective-C

Draw DataMatrix in Objective-C TRANSACTIONS

and then run the query to count the rows once more, you would discover that the database does not, in fact cannot, use your index: ops$tkyte%ORA11GR2> select count(*) from t; Execution Plan ---------------------------------------------------------Plan hash value: 2966233522 ------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T | 45 | 3 (0)| 00:00:01 | ------------------------------------------------------------------It full-scanned the table. It had to full-scan the table in order to count the rows. This is due to the fact that in an Oracle B*Tree index, index key entries that are entirely null are not made. That is, the index will not contain an entry for any row in the table T such that all of the columns in the index are null. Since X is allowed to be null temporarily, the optimizer has to assume that X might be null and therefore would not be in the index on X. Hence a count returned from the index might be different (wrong) from a count against the table. We can see that if X had a non-deferrable constraint placed on it, this limitation is removed; that is, column X is in fact as good as column Y if the NOT NULL constraint is not deferrable: ops$tkyte%ORA11GR2> alter table t drop constraint x_not_null; Table altered. ops$tkyte%ORA11GR2> alter table t modify x constraint x_not_null not null; Table altered. ops$tkyte%ORA11GR2> set autotrace traceonly explain ops$tkyte%ORA11GR2> select count(*) from t; Execution Plan ---------------------------------------------------------Plan hash value: 995313729 -----------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | -----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| T_IDX | 45 | 1 (0)| 00:00:01 | -----------------------------------------------------------------So, the bottom line is, only use deferrable constraints where you have an identified need to use them. They introduce subtle side effects that could cause differences in your physical implementation (non-unique vs. unique indexes) or in your query plans as just demonstrated!

Many developers have some bad habits when it comes to transactions. I see this frequently with developers who have worked with a database that supports but does not promote the use of transactions. For example, in Informix (by default), Sybase, and SQL Server, you must explicitly BEGIN a transaction; otherwise, each individual statement is a transaction all by itself. In a similar manner to the way in which Oracle wraps a SAVEPOINT around discrete statements, these databases wrap a BEGIN WORK/COMMIT or ROLLBACK around each statement. This is because, in these databases, locks are precious resources, and readers block writers and vice versa. In an attempt to increase concurrency, these databases want you to make the transaction as short as possible sometimes at the expense of data integrity. Oracle takes the opposite approach. Transactions are always implicit, and there is no way to have an autocommit unless an application implements it (see the Using Autocommit section later in this chapter for more details). In Oracle, every transaction should be committed when it must and never before. Transactions should be as large as they need to be. Issues such as locks, blocking, and so on should not really be considered the driving forces behind transaction size data integrity is the driving force behind the size of your transaction. Locks are not a scarce resource, and there are no contention issues between concurrent readers and writers of data. This allows you to have robust transactions in the database. These transactions do not have to be short in duration they should be exactly as long as they need to be (but no longer). Transactions are not for the convenience of the computer and its software; they are to protect your data.