ansaurus

Question

Answer 1

+2 A:

Create a temporary table and save SQL2's results there prior to executing SQL1:

CREATE GLOBAL TEMPORARY TABLE mytemptable (id INT NOT NULL)

CREATE OUTLINE ol_sql1
ON
SELECT  *
FROM    sql1
WHERE   id IN
        (
        SELECT  id
        FROM    mytemptable
        )

INSERT
INTO    mytemptable
SELECT  *
FROM    sql2

SELECT  *
FROM    sql1
WHERE   id IN
        (
        SELECT  id
        FROM    mytemptable
        )

Quassnoi 2010-01-27 14:21:20

But the problem is that many users can execute this SQL at the same time

Avi K. 2010-01-27 14:24:18

@Hec: which `SQL`? `SQL1`, `SQL2`, both?

Quassnoi 2010-01-27 14:58:29

OK i`ll describe it a bit more. here is a simple SQL (the real main sql in much more complicated ofcourse): [select * from phones where phone_number in(select column1 from temp_table1)]. many users executes the entire SQL but for each execute the system creates new temp table (so the inner SQL changed).

Avi K. 2010-01-27 15:38:15

@Hec: the system will not create the new temp table, the system will fill the existing temp table with the new values. Your outer `SQL` won't change and hence can be outlined. Everything that changes is the data within the temp table.

Quassnoi 2010-01-27 15:51:35

Answer 2

A:

Further to Quassanoi's point. It sounds like you may not be familiar with temporary tables. This is a good introduction.

You only create the table once. Then within a given session you first 1) populate the temporary table, 2) execute your query pulling from the temporary table, 3) rollback. There's no risk of conflicting/overlapping with another session's data.

Dan 2010-01-27 19:19:22

also: step (3) can be COMMIT or ROLLBACK.

Jeffrey Kemp 2010-01-28 05:29:22

Answer 3

A:

If this query gets executed a lot of times, I wouldn't use a temporary table for it.

There is a 'trick' to bind an inlist, which Tom Kyte describes on his blog:

http://tkyte.blogspot.com/2006/06/varying-in-lists.html

I would bet on that being much more efficient. It should be easy to prove with a SQL Trace.

Stephen ODonnell 2010-01-28 14:34:07

ansaurus

tags:

views:

answers:

Passing SQL to an Oracle bind variable

related questions