RE: Drilling down on the data

  • From: <sanjay.khangarot@xxxxxxxxx>
  • To: <mark@xxxxxxxxx>
  • Date: Thu, 30 Dec 2004 12:31:32 +0530

Thanks mark for your concern regarding the thread, my mistake as I have deleted 
the content in it..

Will take care of this issue in future ..

Now coming to the design..
1.. When a batch passes through a observation, certain characteristics are 
observed and the batch name is changed after the observation ( this is how the 
system is ) these observation are called tests..

So suppose in the first observation table  I am observing 2 characteristics 
Mean Position and Fitness..

After this test it will go to a different observation, and will be observed for 
3 other characteristics which have no resembalence with the observations 
recorded in the first table... that is why I can not go for a single table 
which will observe all the tests.. in one test I have 18 characteristics to 
observe and record

The data and the results:
batch_data
 batch_id          parent_id      batch_No        
1                        Null                 B234
2                           1                  B345
3                            2                 C567

in these tables batch_id references the batch_data(batch_id)

Now the observations ( are  facts)
     
      batch_id          prc_stg_id              setup_id          Day_Time      
 MPS_value        finess_value
            1                     1                                             
 01/02/04         15                       23

Observation2
 batch_id        prc_stg_id         setup_id            Day_Time        pss_x10 
       pss_x50         pss_x90             
   2                        2                                               
03/04/04     99.9              99.6               99.4

Now the results wanted is
for the batch 2 what was the test perform and the results
like

Batch Observation value   level ( level from the hierarchy 1 is parent)
2        pss_x10        99.9    2
2        pss_x50        99.6    2
1        MPS_Value  15       1
1        finess_value   23       1

As I have stated earlier there is no relationship in the observations so they 
are different 'facts' and the batch is working as a 'confirmed' dimension. one 
fact table may have millions of records.

Thanks and Regards

Sanjay

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Mark J. Bobak
Sent: Thu 12/30/2004 11:56 AM
To: Sanjay Khangarot (WT01 - ES- eEnabling)
Cc: Oracle-L Mailing List
Subject: 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@xxxxxxxxxxxxx) 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@xxxxxxxxx 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@xxxxxxxxx
"Science is the belief in the ignorance of experts."  --Richard P.
Feynman


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

Other related posts: