Archive

Archive for the ‘SQL’ Category

Restoring a dropped table

ImageCatastrophe! You’ve just accidentally dropped a table which contained really rather important data. What to do?

One thing you can do to recover the situation quickly (if you’re running 10g or later, that is) is to run the following command:

FLASHBACK TABLE MY_SCHEMA.MY_SUPER_IMPORTANT_TABLE TO BEFORE DROP;

If the table is still in the Recycle Bin, it’ll be recovered straight away. You can check whether the table is still available in the recycle bin and whether it can be recovered this way, with the following SELECT statement:

SQL> select original_name, can_undrop from recyclebin;

ORIGINAL_NAME CAN
-------------------------------- ---
MY_SUPER_IMPORTANT_TABLE YES

You can read about the Recyle Bin and Flashback Drop here: http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

Categories: SQL Tags:

An Interesting Feature of NOT IN and Multi-Row Subqueries

March 1, 2013 4 comments

Take the following simple SQL statement:

SELECT *
FROM dual
WHERE 'x' NOT IN
(SELECT 'a' FROM dual);

Since ‘x’ cannot be found in our subquery, you’d expect this to return a row from Dual right? Indeed it does:

SQL> SELECT *
  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual);

D
-
X

What about in the following case?

SELECT *
FROM dual
WHERE 'x' NOT IN
(SELECT 'a' FROM dual
 UNION ALL
 SELECT NULL FROM dual);

You might expect this to return a row as well since ‘x’ is still not in our subquery (which now returns two values: ‘x’ and NULL).

You’d be wrong, however. This query will in fact return no rows:

SQL> SELECT *
  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual
  5   UNION ALL
  6   SELECT NULL FROM dual);

no rows selected

If the subquery referenced by your NOT IN statement contains any NULL values, the NOT IN condition will evaluate to unknown.

It’s worth noting that this is not the case if you use an IN as opposed to a NOT IN:

SQL>  SELECT *
  2   FROM dual
  3   WHERE 'a' IN
  4   (SELECT 'a' FROM dual
  5    UNION ALL
  6    SELECT NULL FROM dual);

D
-
X
Categories: SQL Tags: ,

Using Dual to reduce function calls (and getting your 5-a-day)

February 7, 2011 1 comment

An introduction to an interesting feature of
Oracle SQL through the medium of fruit…

 

 

Make a FRUITS table.

CREATE TABLE fruits (fruit_name varchar2(30));

Put in 5 bananas, 7 apples and 3 blueberries.
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('blueberry');
INSERT INTO fruits VALUES ('blueberry');
INSERT INTO fruits VALUES ('blueberry');

Create a sequence for keeping track of the number of times our function below is called.
CREATE SEQUENCE seq START WITH 1;

Make our function which returns a fruit’s colour (and increments our sequence each time it runs).
CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)
RETURN varchar2
IS
l_num number;
BEGIN
SELECT seq.nextval INTO l_num FROM dual;

CASE p_fruit_name
WHEN 'banana' THEN RETURN 'yellow';
WHEN 'apple' THEN RETURN 'green';
WHEN 'blueberry' THEN RETURN 'blue';
END CASE;
END get_colour;
/

Get the colour of each fruit in our table.
SELECT get_colour(fruit_name) FROM fruits;

QUESTION ONE: What will the following return?
SELECT seq.nextval FROM dual;

Well, there are 15 rows in our table so we’d expect the function to have run 15 times. And indeed it has: seq.nextval will show a value of 16.

Now, let’s reset the sequence for the next experiment.
DROP SEQUENCE seq;
CREATE SEQUENCE seq START WITH 1;

And again get the colour of all of our fruits using our function but this time wrapping it inside a SELECT FROM dual.
SELECT (SELECT get_colour(fruit_name) FROM dual)
FROM fruits;

QUESTION TWO: What will the following return now?
SELECT seq.nextval FROM dual;

Well, we might be tempted to think that the function would have run 15 times as before and hence our sequence’s nextval would now be 16, as before. However, this is not the case. You’ll find the value returned is in fact 4, meaning that the function ran 3 times.

What happened there?
How can the function have run only 3 times if we gave it 15 bits of data to process (i.e. 15 fruits, one for each row from our table) and it returned the correct answer in each case?

The answer is Scalar Subquery Caching whereby the result of a “SELECT some_function(x) FROM dual” will be remembered for x as soon as it is run once. Hence, the function need only be run for each of the different fruits in the table and since we have only 3 different fruits (blueberry, apple and banana), the function is only run 3 times.

Tom Kyte talks about it here.

Nifty, eh?

Categories: SQL

SQL: SELECTing a random subset of data

February 6, 2009 2 comments

Show me some data, Baby

Have a table with a lot of data in and want to work with a random subset of that data? The answer is the Sample Clause of the SELECT statement. The syntax is often described as something like the following:

SELECT *
FROM my_table SAMPLE (percentage_of_rows);

For example:

SQL> CREATE TABLE ANDY.objs AS SELECT * FROM all_objects;

Table created.

SQL> SELECT COUNT(*) FROM ANDY.objs;

COUNT(*)
———-
71277

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7107

"Some data" is not always the same "some data"

The above is demonstrably returning a random 10% of data from the ANDY.objs table. Great. Very useful. However, an (arguably) interesting thing happens if we run the exact same SELECT statement again:

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7020

And again:

SQL> SELECT COUNT(*) FROM ANDY.objs SAMPLE(10);

COUNT(*)
———-
7209

We get a different number of rows each time. It’s still close enough to 10% of the data, you might say. Maybe so but it does highlight an interesting point: The Sample Clause does not bring back x% of rows (as is often thought) but rather gives each row an x% chance of being returned.

From the Oracle Documentation:
"This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table."

The moral of this tale

Use the Sample Clause to work with a random subset of data but don’t rely on it to bring back exactly the expected number of rows. Complete details on the Sample Clause can be found here and more examples can be found here.

Categories: SQL