Re: Drilling down on the data

Hi Sanjay,
First, it works a lot better if you initiate a new thread (by sending mail to oracle-l_at_freelists.org) rather than to respond to an existing thread that has nothing to do with your question. At least you changed the subject, so people won't be too confused. (Especially those w/ threading mail programs.)

Anyhow, enough about that.

As to your design....well, I'm afraid it's in bad shape. That is, if I understand what it is you want to do. You mention a table, PRODUCT_BATCH, with BATCH_NO, PARENT_BATCH, ATTR1, and ATTR2. Then you have three observation tables, OBERVATION1, OBSERVATION2, and OBSERVATION3. Each of them has a column BATCH. Is this the same as BATCH_NO in PRODUCT_BATCH? I'm assuming it is. The other columns in the observation tables, are they all labeled correctly? Specifically, should OBSERVATION2 and OBSERVATION3 both have columns OBS3 and OBS4?

Finally, you talk about "tests performed on a batch", but I see no reference to anything called "test" in the sample data you show.

So, I'm a little confused as to your data model and what you're really trying to accomplish here.

However, assuming that the observation tables contain the output of the "test", and there's no way to determine what batch is in what table, here are a couple of possibilities I see right off:

1.) Union together the results of querying each of the observation tables. That way, you'll find the row you're looking for, regardless of which table it's in.

2.) Seriously reconsider your design.
  a.) Why are there multiple observation tables? One

        ought to be enough, even for fairly high concurrency,
        if you consider FREELISTS, INITRANS, and how many
        rows you'll have per block.
  b.)  You could partition the table, each operation type could
        go into it's own partition.

Deciding whch of 2a or 2b to go with is going to depend on your appliacation, and how it works.

I don't recommend 1, except as a stop gap measure, because it's not a very nice design.....

I hope that helps. If you still have more questions or concerns, please provide a test case. (Working table creates, small set of test data, and what the sqmple query output you're trying to achieve should look like.)

-Mark

On Thu, 2004-12-30 at 00:50, sanjay.khangarot_at_wipro.com wrote:

> 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
>

--
Mark J. Bobak
mark_at_bobak.net
"Science is the belief in the ignorance of experts."  --Richard P.
Feynman


--
http://www.freelists.org/webpage/oracle-l