Hi, We have one table which contains the information about the batches and the heirarchy of the batches. On these batches certain operation are performed and as there are no similary in the operations, every operation results are stored in a table, so if we have 9 operations then these 9 operations goes in 9 tables. The table structures are as follows: Product_batch ( table) Batch_no Parent_batch Attri1 Attri2 1 Null Xyx abc 2 1 aaaa saaa 3 2 aaaa 344 Observation1(Table) Batch obs1 obs2 1 123 124 Observation2 (table) batch obs3 obs4 2 345 456 Observation3 batch obs3 obs4 3 result result Now to come to know about the tests performed on a batch and it`s parents how can we proceed? As from the batch I can`t come to know about the table in which the results are lying...(can be 1,2 or 3) eg. If I wanted to know the results of batch 3 and it`s parents(2 and 1) how can I go to the table 3 to pick the observation, then for it`s parent which is 2 go to obseravation2 and get the results and so on and then finally give the result... Does anyone have came across with this kind of problem? TIA Regards Sanjay ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Jonathan Lewis Sent: Mon 12/27/2004 11:11 PM To: oracle-l Subject: Re: Materialize hint In reverse order, the "with" clause names and defines a subquery before its use in a query - a bit like a macro in C. Unlike C macros though, the optimizer can choose to write your subquery in-line and then optimise the expanded statement, or create a temporary table from the definition and use the temporary table for the main query. You can choose to use the 'with' clause simply to make a complex SQL statement tidier, knowing that there should be no performance benefit in creating a temporary table. If you want to control the optimiser, then the 'materialize' hint makes it create a temporary table; the 'inline' hint makes it perform 'macro- substitution'. As far as I know, neither hint is documented. The 'with' clause (known as subquery factoring) is quite flexible - though not yet as flexible as DB2's which can cope with recursive definitions). Here's an example I wrote to answer a fun puzzle that Daniel Morgan put out on cdo.server some months ago. with age_list as ( select rownum age from all_objects where rownum <= 36 ), product_check as ( select age1.age as youngest, age2.age as middle, age3.age as oldest, age1.age + age2.age +age3.age as summed from age_list age1, age_list age2, age_list age3 where age2.age >= age1.age and age3.age >= age2.age and age1.age * age2.age * age3.age = ( select max(age) from age_list ) ), summed_check as ( select youngest, middle, oldest, summed from ( select youngest, middle, oldest, summed, count(*) over(partition by summed) ct from product_check ) where ct > 1 ) select * from summed_check where oldest > middle ; Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004 ----- Original Message ----- From: "Mladen Gogala" <gogala@xxxxxxxxxxxxx> To: "oracle-l" <oracle-l@xxxxxxxxxxxxx> Sent: Saturday, December 25, 2004 5:05 AM Subject: Materialize hint I recently ran accross a SQL by Jonathan that uses "materialize" hint. As was unable to find the hint documented anywhere, and God knows I tried before asking the question, I must ask the folowing two =20 questions: 1) What does "materialize" hint do and where is it documented? I was unable to find it documented in either 10g documentation or 9.2 documentation. 2) The same question for "with generator" clause. This question is, of course, meant for Jonathan but I'd appreciate anybody else's answer as well. -- //www.freelists.org/webpage/oracle-l Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or Mailadmin@xxxxxxxxx immediately and destroy all copies of this message and any attachments. -- //www.freelists.org/webpage/oracle-l