Home » SQL & PL/SQL » SQL & PL/SQL » Optimized query does not uses same plan if used to create a table (Oracle 10G)
Optimized query does not uses same plan if used to create a table [message #357484] Wed, 05 November 2008 10:32 Go to next message
khanra60
Messages: 4
Registered: November 2008
Junior Member
I have a query with a hint FIRST_ROWS. It works fine and explain plan shows that it is using proper indexes and the result start to come back fast. However, if I use it to create a table (create table as select ....) the explain plan chages and it does not use indexes, rather full table scans is selected and the outcome takes days to complete. Even if I use this query to insert rows into a table, same result. My problem is that I need to create a table out the result of this query so that I can export it and share data. Anybody has any work around to it?
Re: Optimized query does not uses same plan if used to create a table [message #357487 is a reply to message #357484] Wed, 05 November 2008 10:43 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
When you create the new table, it doesn't have any indexes (and doesn't have any stats gathered) so it's not equivalent to the original table and cant use any indexes if there aren't any...

When you say export - do you mean the Oracle "exp" utility?
Re: Optimized query does not uses same plan if used to create a table [message #357488 is a reply to message #357484] Wed, 05 November 2008 10:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think it is time for you to read the oracle reference manual to find out what is the use of FIRST_ROWS hint and where it is appropriate. A famous quote "Full Table Scan are not always evil and Index are not always efficient".

Regards

Raj
Re: Optimized query does not uses same plan if used to create a table [message #357493 is a reply to message #357487] Wed, 05 November 2008 10:56 Go to previous messageGo to next message
khanra60
Messages: 4
Registered: November 2008
Junior Member
Yes I will use export utility of Oracle. Is there a work around? Full table scan is not an option it takes 5 days to return all the rows, which is pathetic...
Re: Optimized query does not uses same plan if used to create a table [message #357494 is a reply to message #357488] Wed, 05 November 2008 10:59 Go to previous messageGo to next message
khanra60
Messages: 4
Registered: November 2008
Junior Member
The query is dealing with rows of arounf 100 million in one table and 50 million in other two tables. No index utilization is out of question, it takes 5 day to return the result without indexes as oposed to 5 hours with the indexes. So not using indexes is not an options
Re: Optimized query does not uses same plan if used to create a table [message #357496 is a reply to message #357494] Wed, 05 November 2008 11:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Post the query, and the explain plan when you run it , and the explain plan when you create a table from it.


The fact that your query starts to return rows quickly does not mean that it will run to completion quickly - the FIRST_ROWS hint choses plans that will start to return data quickly. ALL_ROWS chooses plans that will return the entire data set more quickly.

Re: Optimized query does not uses same plan if used to create a table [message #357501 is a reply to message #357496] Wed, 05 November 2008 12:08 Go to previous messageGo to next message
khanra60
Messages: 4
Registered: November 2008
Junior Member
I tried the ALL_ROWS and LAST_ROWS hint it does not use any index rather full table scans off all the three tables. I tested the result the FIRST_ROW does work and gives me full data. I will not be able to send you the query and the explain plan because it is on the secure Government site and I am not allowed to copy/transfer data from red to green network, which is also a serious limitation. I also tested with another query which is well optimized and runs fast, as soon I use it to create a table or insert rows into a table or if I try to count rows with count(*) it changes the plan and no indexes are used, full table scan is followed. Other option for me is to create a flat file and then reload the data by using SQLLDR into a table and export the table. Which sounds goofy and not very optimized way of data transfer. If I had to run a query only and run report it would not be a problem. Data transfer is a issue
Re: Optimized query does not uses same plan if used to create a table [message #357507 is a reply to message #357501] Wed, 05 November 2008 12:27 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I think we all may be reading this wrong.

I think the OP is saying the CREATE TABLE is taking a long time, and rightly so. You cannot create a table quicker than it takes to transfer all that data across a network or even in the same database. There is a big difference between "displaying" data on the screen and "creating" data into a table. Lots of overhead with rollback, etc.
Previous Topic: oracle view - urgent
Next Topic: Package is in use?
Goto Forum:
  


Current Time: Sat Dec 03 06:20:55 CST 2016

Total time taken to generate the page: 0.08196 seconds