| Query sub-factoring issue [message #519805] |
Wed, 17 August 2011 02:07  |
 |
a_oracle
Messages: 98
Registered: November 2010
|
Member |
|
|
Hi All,
I am tuning an existing query and I am using query sub-factoring logic to build the new query..
My query is something like:
with T1 as (select col1,col2....from Table1,table 2...Table3),
T2 as(select col1,col2....from Table4,table 5...Table6)
select * from T1 UNION ALL select * from T2
Now, T1 and T2 st runtime hold nearly 115000 and 129000 data.
I have read that the view created by the query sub-factoring part resides in PGA. So just wanted to ask whether
the amount of data that I have specified is going to effect the performance of the any parallel execution of other queries?
And also is there a certain restriction of the volume of data that we should consider while incorporating the query sub-factoring logic.
Honestly, when i applied this logic in my existing code, the execution time did reduce significantly but I am still doubtful if this
logic would be fine enough.
Please advice.
|
|
|
|
|
|
|
|
| Re: Query sub-factoring issue [message #519814 is a reply to message #519810] |
Wed, 17 August 2011 02:32  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:So, is it fine to go ahead with the logic?
Yes, in my opinion, it turns the query clearer to read, understand and maintain, above all if you add some comment to explain each subquery.
Quote:And should I use materialize hint in the with clause select statement
No, unless you know it should be materialized each time for any number of rows and any workload, otherwise let the optimizer do its job and estimate if it has or not to materialize the queries.
Regards
Michel
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
