Performance differences between IN and EXISTS

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 performs…

NOT IN vs. NOT EXISTS when NULL values are involved

NOT IN does not behave as you would expect when NULL values are involved. Suppose you have the following tables: TABLE_A CD_FIELD DS_FIELD 1 ALPHA 2 BETA TABLE_B CD_FIELD DS_FIELD 1 GAMMA NULL DELTA Now try the following query:

Does the previous query return any row? The answer is NO it doesn’t, even if…

How to compute a timestamp in Oracle (PL SQL)

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.

The…

How to commit a single DML statement in Oracle (PL/SQL)

Sometimes you might need to commit a single INSERT, UPDATE or DELETE statement and not the whole main transaction. In such a case you can use an AUTONOMOUS TRANSACTION which is a feature available since Oracle 8i. The trick is to encapsulate the statement in a procedure and use the pragma AUTONOMOUS_TRANSACTION. Here is an…

How to put a set of rows into a single row in Oracle

In Oracle you can execute hierarchical queries using some cool operators–CONNECT BY PRIOR, START WITH and the pseudocolumn SYS_CONNECT_BY_PATH, namely. However, using your fantasy you can exploit these operators to put in a single row–with each item separated by the next through a separator, such as a comma–what you have in multiple rows. For example…

Using dynamic SQL statements from PL/SQL

Sometimes you need to execute dynamic SQL statements. Starting from Oracle8i you can accomplish this task using the “EXECUTE IMMEDIATE” statement. Here are three examples of how you can take advantage of this great statement.

In this first example I showed how you can use EXECUTE IMMEDIATE to execute the query and put the…