Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> SQL WITH Clause
Dick B
post Mar 15 2012, 03:28 PM
Post #1


Advanced Member
***

Group: Members
Posts: 52
Joined: 15-March 12
From: New England
Member No.: 46,936



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.
Go to the top of the page
 
+Quote Post
burleson
post Mar 15 2012, 04:53 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,706
Joined: 26-January 04
Member No.: 13



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.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
SteveC
post Mar 15 2012, 05:48 PM
Post #3


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



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).
Go to the top of the page
 
+Quote Post
burleson
post Mar 16 2012, 06:00 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,706
Joined: 26-January 04
Member No.: 13



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 . . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Dick B
post Mar 16 2012, 07:11 AM
Post #5


Advanced Member
***

Group: Members
Posts: 52
Joined: 15-March 12
From: New England
Member No.: 46,936



QUOTE (SteveC @ Mar 15 2012, 06:48 PM) *
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.

Go to the top of the page
 
+Quote Post
Dick B
post Mar 16 2012, 07:16 AM
Post #6


Advanced Member
***

Group: Members
Posts: 52
Joined: 15-March 12
From: New England
Member No.: 46,936



QUOTE (burleson @ Mar 15 2012, 05:53 PM) *
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***.
Go to the top of the page
 
+Quote Post
rastl
post Mar 19 2012, 04:06 AM
Post #7


Advanced Member
***

Group: Members
Posts: 56
Joined: 12-October 06
Member No.: 5,309



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
Go to the top of the page
 
+Quote Post
Dick B
post Mar 19 2012, 07:14 AM
Post #8


Advanced Member
***

Group: Members
Posts: 52
Joined: 15-March 12
From: New England
Member No.: 46,936



QUOTE (rastl @ Mar 19 2012, 05:06 AM) *
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***.
Go to the top of the page
 
+Quote Post
burleson
post Mar 19 2012, 07:22 AM
Post #9


Advanced Member
***

Group: Members
Posts: 11,706
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
rastl
post Mar 19 2012, 07:40 AM
Post #10


Advanced Member
***

Group: Members
Posts: 56
Joined: 12-October 06
Member No.: 5,309



QUOTE (d*** B @ Mar 19 2012, 01:14 PM) *
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.
Go to the top of the page
 
+Quote Post
burleson
post Mar 19 2012, 07:59 AM
Post #11


Advanced Member
***

Group: Members
Posts: 11,706
Joined: 26-January 04
Member No.: 13



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 . . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd November 2014 - 12:40 PM