I need to execute something like:
select
[very big SQL]
where phone_number in(:SQL2)
Is it possible to use bind variable for SQL2?
I want to save the execution plan of the major SQL.
Thanks.
Add a comment
|
3 Answers
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
)
4 Comments
Avi K.
But the problem is that many users can execute this SQL at the same time
Quassnoi
@Hec: which
SQL? SQL1, SQL2, both?Avi K.
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).
Quassnoi
@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.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.
Comments
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:
- populate the temporary table
- execute your query pulling from the temporary table
- rollback.
There's no risk of conflicting/overlapping with another session's data.
1 Comment
Jeffrey Kemp
also: step (3) can be COMMIT or ROLLBACK.