Drilling down on the data

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.



--
http://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.
--
http://www.freelists.org/webpage/oracle-l

Other related posts: