Category: Oracle

Performance differences between IN and EXISTS

Posted: 2008-06-04   |   Categories: Database, Oracle, Programming   |   Tags: EXISTS, IN, Optimization, Performance

Very often I hear people wondering if it’s better the EXISTS or the IN operator. Let’s start by saying that the two operators behave differently when NULL values are involved (see this post).

If you are sure that the field used to join the tables does not include NULL values then the IN operator “generally” performs better than EXISTS. I say “generally” because this is not always the case. For example consider the following query:

NOT IN vs. NOT EXISTS when NULL values are involved

Posted: 2008-06-04   |   Categories: Database, Oracle, Programming   |   Tags: NOT EXISTS, NOT IN,

NOT IN does not behave as you would expect when NULL values are involved. Suppose you have the following tables:

Now try the following query:

   
SELECT *
FROM table_a a
WHERE a.cd_field NOT IN (SELECT b.cd_field FROM table_b b);

Does the previous query return any row? The answer is NO it doesn’t, even if there is a value (2) in table_a.cd_field which is not present in table_b.cd_field.

How to compute a timestamp in Oracle (PL SQL)

Posted: 2007-10-09   |   Categories: Oracle, Programming

Many sources use the term timestamp to refer specifically to Unix time, the number of seconds since 00:00:00 UTC on January 1, 1970. In Oracle you can compute this number very easily. For example, the following query computes the number of seconds between 00:00:00 UTC on January 1, 1970 and October 9, 2007.

   
SELECT (to_date('09-10-2007', 'DD-MM-YYYY') -
to_date('01-01-1970', 'DD-MM-YYYY')) * 60 * 60 * 24
FROM dual

The result of the preceding query should be 1191888000.

How to create a copy/backup of a table in Oracle

Posted: 2007-07-04   |   Categories: Oracle, Programming

You can create a backup of a table (structure and data) in Oracle by using the following syntax:

 
CREATE TABLE customers_backup AS (SELECT * FROM customers)

The previous example creates the customers_backup table which mirrors the structure and data of the customers table.