Home » SQL & PL/SQL » SQL & PL/SQL » Explain plan changes drastically using CTAS....
Explain plan changes drastically using CTAS.... [message #221055] Fri, 23 February 2007 08:50 Go to next message
yogeshciscoodc
Messages: 2
Registered: February 2007
Junior Member
Hi
I have a SQL which runs in about 20 minutes.
But when I do a CTAS for the same SQL it doesn't come out for 2 hours ....
I am extremly confused as to what is the problem with this particular SQL.
The Explain plan changes drastically during CTAS...

Let me know if someone has any pointers or if someone can help me......

Thanks
Yogi.
Re: Explain plan changes drastically using CTAS.... [message #221058 is a reply to message #221055] Fri, 23 February 2007 09:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Are you using APPEND hint and nologging clause?
If not try it and post the results.
Search this forum/google about it.
First update the stats on base table/index before doing this.
Re: Explain plan changes drastically using CTAS.... [message #221084 is a reply to message #221058] Fri, 23 February 2007 13:21 Go to previous messageGo to next message
yogeshciscoodc
Messages: 2
Registered: February 2007
Junior Member
I am not sure of how to use the append clause in CTAS but yeah I have been using no logging all along.
Also the tables are analyzed today morning before I ran the query...
The olny thing that is annoying is that while using CTAS it goes into an extra HASH JOIN and access three big tables with a FTS .
I also did a ALL_ROWS hint in the original SQL that was run(thinking it was a FIRST_ROWS coming out) but it seemed to come in around 20 minutes.
So I was just wondering if anyone is facing such a situation ....


Re: Explain plan changes drastically using CTAS.... [message #221101 is a reply to message #221084] Fri, 23 February 2007 20:07 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When you say it runs in 20 mins, does it start returning rows after 20 minutes, or finish returning rows after 20 minutes.

See here for a way to efficiently benchmark the original SELECT.

Post both plans here as well if you want some advice on those, plus the number of rows in each table, and the number of rows that the select will return.

Ross Leishman
Previous Topic: to_date() returns year as 0007
Next Topic: table_name wise no. of records
Goto Forum:
  


Current Time: Fri Dec 09 03:41:03 CST 2016

Total time taken to generate the page: 0.40815 seconds