Optimized query does not uses same plan if used to create a table [message #357484] |
Wed, 05 November 2008 10:32  |
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 #357496 is a reply to message #357494] |
Wed, 05 November 2008 11:07   |
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   |
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  |
joy_division
Messages: 4963 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.
|
|
|