SQL WITH Clause Options

[Dick B]

I have a very complex query that I am trying to optimize using the WITH clause. I'm running 10g 10.2.0.4 on a PA-RISC HP-UX platform.

Here is the excerpted code.....

CREATE TABLE test_combined
AS
WITH tmp_sv AS
(SELECT lots-of-stuff),
tmp_clm AS
(SELECT lots-of-stuff)
SELECT * FROM tmp_sv, tmp_clm WHERE tmp_sv.key = tmp_clm.key;

What's happening is that when I run this query in either TOAD or SQL Developer, it works perfectly. Whenever I try running it in SQL Plus on my HP-UX box, it errors out with ORA-00942, pointing to tmp_clm in the above case. When I switch the order of tmp_sv and tmp_clm in the FROM clause, the error points to tmp_sv. It is obvious that there is nothing wrong with the construction of the query due to the successful execution in TOAD and Oracle's SQL Developer. Any ideas out there on how to get SQL*Plus to execute this without errors? I've also tried using the JOIN syntax, and the same errors occur.

[burleson]

Hi d***,

>> when I run this query in either TOAD or SQL Developer, it works perfectly. Whenever I try running it in SQL Plus

The WITH clause is relatively new, and you obviously have an issue with SQL*Plus, probably a bug . . .

Check the bugs database:

http://support.oracle.com

As a workaround, use global temporary tables instead:

http://www.dba-oracle.com/t_temporary_tables_sql.htm

It does exactly the same thing, just different syntax.

[SteveC]

select lots_of_stuff from where/who? Who are you logged in as in the tools compared to your SQL*Plus login? The temp table was never created because the underlying select failed. What happens if you run the select lots_of_stuff by itself? ORA-00942.

The WITH clause was supported in SQL 1999 standard. Oracle supported it in Oracle 9i, release 1, so it's older than dirt, relatively speaking, and a bug in this at this point in time would be exceedingly rare (i.e., don't bet on it; your problem is permissions of the user exec-ing the query).

[burleson]

Hi Steve,

>> and a bug in this at this point in time would be exceedingly rare

I disagree!

If

1 - it works in SQL Developer

2 - It's valid syntax

3 - It does not work in SQL*Plus

my money is on a bug . . . .

[Dick B]

select lots_of_stuff from where/who? Who are you logged in as in the tools compared to your SQL*Plus login? The temp table was never created because the underlying select failed. What happens if you run the select lots_of_stuff by itself? ORA-00942.

The WITH clause was supported in SQL 1999 standard. Oracle supported it in Oracle 9i, release 1, so it's older than dirt, relatively speaking, and a bug in this at this point in time would be exceedingly rare (i.e., don't bet on it; your problem is permissions of the user exec-ing the query).

Steve,

I'm logging into the same database with the same user id. The "Lots of Stuff" is a collection of columns from the inner query in both cases. As I'm running 10g, I'm going along with Don B that it's probably a bug in SQL*Plus - another typical gotcha while running 10g. We're going to upgrade to 11g shortly, so I'm hoping that all of the bugs in 10g will finally be eliminated by 11g.

[Dick B]

Hi d***,

>> when I run this query in either TOAD or SQL Developer, it works perfectly. Whenever I try running it in SQL Plus

The WITH clause is relatively new, and you obviously have an issue with SQL*Plus, probably a bug . . .

Check the bugs database:

http://support.oracle.com

As a workaround, use global temporary tables instead:

http://www.dba-oracle.com/t_temporary_tables_sql.htm

It does exactly the same thing, just different syntax.

Thanks - that was my opinion as well. One of the issues I was trying to avoid is the generation of massive amounts of archivelog entries to be applied to our standbys - the process that I'm writing will end up moving around about 12m rows through three or four iterations of temporary tables - I've read that temp tables don't generate log entries, but this WITH clause seemed to be the most promising approach. Performance was a bit less than creating permanent tables and then indexing them, but certainly within reasonable levels. I'm going to convert the process to GTT today and see how it goes. Thanks for the quick reply!

d***.

[rastl]

in case of problems due to old SQLPLUS version with "with clause" try:

SELECT * FROM (
WITH
(<sub_query>) ...
<main_query>
);


btw. why do you think subquery factoring will help you in this case to improve performance ?
Compare execution plans to

SELECT * FROM (SELECT lots-of-stuff) tmp_sv, (SELECT lots-of-stuff) tmp_clm WHERE tmp_sv.key = tmp_clm.key;

Best regards,
Rainer Stenzel

[Dick B]

in case of problems due to old SQLPLUS version with "with clause" try:

SELECT * FROM (
WITH
(<sub_query>) ...
<main_query>
);


btw. why do you think subquery factoring will help you in this case to improve performance ?
Compare execution plans to

SELECT * FROM (SELECT lots-of-stuff) tmp_sv, (SELECT lots-of-stuff) tmp_clm WHERE tmp_sv.key = tmp_clm.key;

Best regards,
Rainer Stenzel

Actually, it won't. I'm trying to fit this complex query into my company's arcane scheduling software which requires the entire sql stream to be hardcoded into the script. I am also attempting to minimize the amount of archivelog activity being sent to our standbys - I have also tried global temporary tables (no redo!!), but find that you can't add an index to a GTT after you've added data, and if you put the index on prior to adding data it kills the performance gain. Also, when you're trying to join 2 tables/result sets of 3m rows each without an index, performance goes out the window as well. I may actually have to just throw the idea of minimizing the archivelog activity out the window..... I'm going back to my original approach and see how NOLOGGING on my tables cuts down the archivelog activity to the standby. Thanks for your response!

d***.

[burleson]

Hi d***,

>> but find that you can't add an index to a GTT

I think you may be misunderstanding GTT's.

Indexes are rarely needed with a GTT because of the small rowset sizes.

A GTT is like a "stamp" that allows for shared DDL between tables.

See here for a working example of a GTT:

http://www.dba-oracle.com/t_global_tempora...ble_example.htm

[rastl]

Also, when you're trying to join 2 tables/result sets of 3m rows each without an index, performance goes out the window as well.

maybe you are better off joining such big result sets without indexes/nested loops.

[burleson]

Hi again,

>> One of the issues I was trying to avoid is the generation of massive amounts of archivelog entries to be applied to our standbys

If you goal is to bypass archive logging, see here:

http://dba-oracle.com/googlesearchsite_pro...amp;q=nologging

Check out the nologging hint, that may help you . . . .