Oracle WITH clause
This is one of the coolest things ever. One of our wonderfully spectacular DBAs (Hi, Brian!) pointed this out and it has saved us a couple times. I know there are better ways to implement this specific example, but it’s to illustrate a point. Here’s what we do now:
CREATE TABLE temp_1 (
user_id NUMBER(3)
);
INSERT INTO temp_1
(user_id)
SELECT user_id
FROM table1;
SELECT user_name, user_info
FROM users, temp_1
WHERE users.user_id=temp_1.user_id;
And here’s using the WITH clause:
WITH temp_1 AS (
SELECT user_id
FROM table1
)
SELECT user_name, user_info
FROM users, temp_1
WHERE users.user_id = temp_1.user_id
Oracle creates a temporary table internally and uses it in the query. You might be thinking, “not a big deal, I do things like this all the time.” Here’s the kicker: There’s NO redo generated. For those of use with physical and/or logical standby’s, we NEED this kind of functionality to keep redo down on the backup databases. YAY!
Posted: October 18th, 2007 under Oracle.
Comments: 4
Comments
Comment from WestonFire22
Time: November 21, 2007, 7:56 pm
So real world example, if I have an order table with say 4 million rows in it and I pull all the orders for skua into a temp table using the WITH clause, is it going to be any faster to find all people who bought skua with first name of Bob? Or is it going to be just as efficient to query the main 4 million row table? How do indexes work with the temp table or are there any?
Comment from Praveen
Time: May 7, 2008, 8:40 am
I have a Small question using WITH Clause.
Can we use WITH Clause in Cusror. i.e.
CUSRSOR C1 is
WITH WITH temp_1 AS (
SELECT user_id
FROM table1
)
SELECT user_name, user_info
FROM users, temp_1
WHERE users.user_id = temp_1.user_id
Please Let me know if we can use it or not.
Comment from Daniel
Time: August 5, 2008, 9:15 pm
Praveen,
I’m not sure as I don’t actually use cursors, but I can tell you that anytime I’ve had an issue using the WITH clause, it’s been mostly because of Oracle having SQL parsing issues, e.g., “expected SELECT” or something like that. A good example is a CTAS:
CREATE TABLE deleted_users
AS
WITH status_flag AS (
SELECT user_id, flag
FROM statuses
)
SELECT username, flag
FROM users, status_flag
WHERE users.user_id=status_flag.user_id
FROM b;
This won’t work because Oracle is expecting a “SELECT” to be there. I would revise this query like this:
CREATE TABLE deleted_users
AS
SELECT * FROM (
WITH status_flag AS (
SELECT user_id, flag
FROM statuses
)
SELECT username, flag
FROM users, status_flag
WHERE users.user_id=status_flag.user_id
FROM b
);
Comment from Daniel
Time: August 5, 2008, 9:19 pm
WestonFire22,
Good questions. I haven’t actually used the WITH clause in a situation where 4M rows were in the table. The thing to keep in mind generally is that the data is stored in memory temporarily. This is critical as you don’t want to keep it there too long.
As for indexes, you’re out of luck, though their location (in memory) helps with that. Also, most of your filtering, where indexes are helpful, should have been taken care of earlier and you should be using at least 10% of the data in the WITH, if not 100%.
Write a comment