File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes oracle stored proc for tree Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "oracle stored proc for tree" Watch "oracle stored proc for tree" New topic
Author

oracle stored proc for tree

Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29253
    
140

Copied JDBC part of question from the original in Servlets.

The Web App has a functionality by name Bill Of Material(BOM) where the application users can update the information about the structure of a Manufacturing Part. There is no limit on the number of levels in the hierarchy of a Part. The Web App has a report screen where the user can choose an "entity"(well assume that an entity has been mapped to many parts) and ask for the report. The business logic for generating the report for an entity involves identifiying all the parts mapped to the entity and fetching the demand for each part. Fetching the demand for a part involves exploding the entire BOM hierarchy upwards and identifying the dependent demand (In simple terms, identify the parent parts demand and how many child parts make a parent). This process has to be done recursively until the top most parent is reached. This process has to be repeated for all the parts mapped to the entity.

We thought of invoking an Oracle Stored Procedure for exploding the entire BOM tree and identifying the total demand for a part. From our past experience, we guess that the procedure will take more time if the BOM tree has more levels. So, we have decided that to develop a batch job(again an oracle stored proc) which will explode the BOM and populate the data in a temporary table. The report usecase will use this temp table and display the report. But the problem arises while deciding when this batch job should be executed. Ideally the temp tables should be updated whenever the BOM tables are modified. Apart from BOM there are quite a few other scenarios which affect the report data.


If someone keeps updating the BOM/other usecases which affect the report data continously, as per our design the report will not be displayed. Only when the batch job is not running, we will display the report. Is this a good design?


[edited to add second quote]
[ July 29, 2006: Message edited by: Jeanne Boyarsky ]

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29253
    
140

Arvind,
A stored proc is definitely a good approach here because of the tree which creates some complexity.

Ideally the temp tables should be updated whenever the BOM tables are modified.

If the temp tables are updated whenver the BOM tables are updated, they aren't really temp tables - they are another view of your data which could be set with triggers.

If someone keeps updating the BOM/other usecases which affect the report data continously, as per our design the report will not be displayed. Only when the batch job is not running, we will display the report. Is this a good design?

This is a bit awkard. If a user is sitting there waiting for a report (requested from the servlet), he/she wants it now - not after the batch job finished.

As a user, I would rather see the report as of a certain time. Conviently, Oracle provides a snapshot view by default. A user will only see what was in the database at the time of his/her query.

As long as all the updates are done in a transaction, the user will not be able to see an inconsistent report.
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
Thanks for the favor, Jeanne !

Meanwhile I learnt from the asktom Oracle forum that multithreading is not possible in PL/SQL.


Arvind
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
If the temp tables are updated whenver the BOM tables are updated, they aren't really temp tables - they are another view of your data which could be set with triggers.


Yeah, you are right. They are not temp tables. We are looking at Materialised Views.


This is a bit awkard. If a user is sitting there waiting for a report (requested from the servlet), he/she wants it now - not after the batch job finished.

As a user, I would rather see the report as of a certain time. Conviently, Oracle provides a snapshot view by default. A user will only see what was in the database at the time of his/her query.


The problem in our requirement is that the user will not see what is in the database at the time of his/her query. Let me explain a scenario to you.

Suppose the user is entering demand data for a set of parts. Once he is done with it, he might want to have a look at the report. As per our design, the Business Class for updating the demand data will create a new thread after updating the demand and this new thread will execute the batch job. If the batch job is not complete before the user clicks the "Get Report" button, is it not better to tell them whats actually happening than showing him the report which doesnt reflect his change, Jeanne?

By the way, are you from Germany, Jeanne? Your surname resembles my favourite soccer star, Podolskey


Arvind
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Originally posted by Arvind Sampath:


Suppose the user is entering demand data for a set of parts. Once he is done with it, he might want to have a look at the report. As per our design, the Business Class for updating the demand data will create a new thread after updating the demand and this new thread will execute the batch job. If the batch job is not complete before the user clicks the "Get Report" button, is it not better to tell them whats actually happening than showing him the report which doesnt reflect his change, Jeanne?


I think you would want your batch to update a status record someplace in the database when it's complete.
Then, when your user makes a request for the report s/he be either brought to a status page if the batch is not complete or, if complete, deliver the report.


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
I think you would want your batch to update a status record someplace in the database when it's complete.


Yeah, Ben. This is precisely what we've planned.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29253
    
140

Arvind,
I see. So the user is waiting for a specific change - the result of the batch job kicked off by his submission. Let's look at each of the cases separately because I would expect something different to happen. Note that if the isn't a multi-user system, it is a moot point.

1) User queries while no batch job - Easy! The user gets the report right away because there isn't any contention.
2) User queries while someone else's batch job is running - I would expect to get the report back right away. Why should I have to wait just because someone else is processing. This is the case I was thinking of that can be solved with transactions.
3) User queries before his/her kicked off batch job is finished - Hmm. You are correct that the old report isn't useful because it looks like the changes didn't take. Maybe check the timestamp of when the record was last updated and display a message to the users that processing is still occurring?

By the way, are you from Germany, Jeanne? Your surname resembles my favourite soccer star, Podolskey

No, I'm not from Germany. I'm American (a New Yorker), but my last name is a Russian one.
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

We do something like this where I work.
Our database supports 'phantom processes' so the thread branching gets done there but the concept is the same.

When the user enters this component s/he can either select a menu of reports which can be run or go to a status page to see what reports have already been run and are available for download (pdfs)..

If the user chooses to kick off a new report they fill out the required input data an click enter. The report is kicked off and the app imediately returns to the status page.

On the status page, there is a column for each of the reports which shows the stage of completion (submitting, processing, complete, error). As long as any of the reports has a status of submitting, or processing, the page will keep refreshing. Once a report is done, the status icon becomes a hyperlink for downloading.

For us this works well because users can check to see if a copy of the report they're interested in has already been run and when it finished. They can also see if one is still brewing and can opt to wait for that one instead of kicking one off themelves.
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
Originally posted by Jeanne Boyarsky:

2) User queries while someone else's batch job is running - I would expect to get the report back right away. Why should I have to wait just because someone else is processing. This is the case I was thinking of that can be solved with transactions.


Well, Consider this Scenario. The user's boss, for some reason, decides that all the cars that they manufacture from now on will have 5 wheels instead of 4. Now he goes to the system, changes the BOM Tree structure & sends an email to the user to fetch the latest report and send it to the manufacturing department. So, though it is the user's Boss who kicked off the batch job, the user has to wait for the updated report. The key here is that the user should always see a report which reflects all the changes that were made before the report query is fired. This is atleast what we thought to be the best possible solution. Are we not fair to the user here, Jeanne?

Originally posted by Jeanne Boyarsky:

3) User queries before his/her kicked off batch job is finished - Hmm. You are correct that the old report isn't useful because it looks like the changes didn't take. Maybe check the timestamp of when the record was last updated and display a message to the users that processing is still occurring?


Yep, as Ben said, we'll update a record in the DB, which'll tell us the status of the batch job. We'll use it to display a status message to the user.

Originally posted by Ben Souther:


When the user enters this component s/he can either select a menu of reports which can be run or go to a status page to see what reports have already been run and are available for download (pdfs)..

If the user chooses to kick off a new report they fill out the required input data an click enter. The report is kicked off and the app imediately returns to the status page.

On the status page, there is a column for each of the reports which shows the stage of completion (submitting, processing, complete, error). As long as any of the reports has a status of submitting, or processing, the page will keep refreshing. Once a report is done, the status icon becomes a hyperlink for downloading.



Well, Ben, in our case the input for generating the report is nothing but just a kind of a filter criteria. The user will have to choose values from a set of 8 odd dropdowns or leave it to the default value and ask for the report. Though it is possible to archive the reports run by other users somewhere and display it to the user, we have to assume that all those reports might have obselete if the batch has been run recently.


Arvind
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
Originally posted by Abhi V in the Servlets Forum
Hi,

Dont understand why your stored proc takes time. Have you tried Select START WITH queries. Definetely START WITH queries reduce lot of time.

thanks
abhi


I dont have any idea about START WITH queries. Infact, am hearing that term for the first time. I'll google for it and check it out.

Our stored proc would take lots of time because of the large amount of data involved and also because of the complex tree exploding algorithm. Well, even 2 mins is considered a large amount of time in case of a web app. Our Client insists that he should get a response in less than 11 secs after clicking 'Generate Report'.

Arvind
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
Arvind,

I would say forget about START WITH hierarchical queries. It won't meet the 11 sec demand.

If a user is sitting there waiting for a report (requested from the servlet), he/she wants it now - not after the batch job finished.

As a user, I would rather see the report as of a certain time. Conviently, Oracle provides a snapshot view by default. A user will only see what was in the database at the time of his/her query.



I completely agree with this. But the problem with your approach:- How are materialized views refreshed? Is it fast refresh or complete refresh? Complete refresh truncates the whole table. If there is a user request in this time frame (materialized view refresh do take time unlike an insert) then the user won't see anything meaningfull.

The approach has to be something like this
- You will have a table with exploded BOM
- A batch job to explode the BOM and store it in a staging table or materialized view.
- Once the batch job is completed transport the rows to the exploded BOM table (merge or truncate).

Does this approach cover most of the use cases?
- There is no modification in the BOM - user will be able to see the accurate data
- BOM is modified and batch job is kicked on but yet to complete. - User will see the previous version and it's quite acceptable as the batch hasn't completed yet.
- BOM is modified by user X - batch job started but the exploded table is not updated, user Y queries (happens simultaneously or within fraction)- Again it's same here. The underlying table is not truncated or locked for update. So user will see reports of previous batch job.
- User X fired query and the batch job started updating the exploded table - Since the query is fired before the update started oracle will fetch the records from UNDO segment (though it may be a bit of work will work still)
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
Purushothaman,

Thanks for you time, mate.

The approach has to be something like this
- You will have a table with exploded BOM
- A batch job to explode the BOM and store it in a staging table or materialized view.
- Once the batch job is completed transport the rows to the exploded BOM table (merge or truncate).


The difference between our approaches is the 'staging/buffer table' that you use from which the data is finally transported to the main table. To be honest, I didnt know that if the materialized view is queried while it is being refreshed, then the outcome would be inconsistent.

But as per our design, we are not going to show the user a report which doesnt reflect his/her change. So, we will always try and display the updated report, even if it takes time. So, I guess we dont require a 'staging table' because, if we find that the batch job is running we are not going to query the materialized view at all. We'll query it only when the job is done with it.

Just out of curiosity, doesnt oracle use snapshot mechanisms for materialized views too? I mean, if a query is issue while the materialized view is being refreshed, wont it use the snapshot?

Thanks again !

Arvind
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29253
    
140

Originally posted by Arvind Sampath:
Well, Consider this Scenario. The user's boss, for some reason, decides that all the cars that they manufacture from now on will have 5 wheels instead of 4. Now he goes to the system, changes the BOM Tree structure & sends an email to the user to fetch the latest report and send it to the manufacturing department. So, though it is the user's Boss who kicked off the batch job, the user has to wait for the updated report. The key here is that the user should always see a report which reflects all the changes that were made before the report query is fired. This is atleast what we thought to be the best possible solution. Are we not fair to the user here, Jeanne?

I like Ben's approach of giving the user a choice. I would handle it as giving them the old report anyway with a warning that User X has made a change that is not reflectedy yet.
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
I would handle it as giving them the old report anyway with a warning that User X has made a change that is not reflectedy yet.


hmmm...yeah....we'll also keep an eye on this option. We'll get a clear picture once we get to know the execution time of the batch job for the worst/average cases.

Thanks,
Arvind
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425

But as per our design, we are not going to show the user a report which doesnt reflect his/her change. So, we will always try and display the updated report, even if it takes time. So, I guess we dont require a 'staging table' because, if we find that the batch job is running we are not going to query the materialized view at all. We'll query it only when the job is done with it.


It really contradicts the earlier requirement of 11 seconds. If time is not a constraint then you can always do things in many ways.

Snapshots are used if the table contains uncommitted data. But it's really overhead to construct the data from the rollback segment particulary if there are too many blocks.
[ July 30, 2006: Message edited by: Purushothaman Thambu ]
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
It really contradicts the earlier requirement of 11 seconds.


The 11 secs constraint is always there and that is why we are going for the batch job rather than executing it at run time.

The point that i was trying to make was that since we are not going to show outdated reports at any cost, we will ask the user to wait or visit back later. So, we dont require a 'staging table' here i guess.

This is our current design.

If the user is asking for the report when the batch job is not running, he/she'll have to get the report within 11 secs.

If the user is asking for the report when the batch job is running, he/she'll get a status message asking him/her to wait for some time.

Having said that, we also have the option of displaying the old report to the user if the batch job is running. We are considering this option too.


Arvind
Abhi V
Greenhorn

Joined: Jul 29, 2006
Posts: 2
Hi Arvind,

Check this out for Hierarchical queries(START WITH)

http://www.dbazine.com/oracle/or-articles/mishra3

Thanks
abhi
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
Hi Guys,

The usecase has come off really well. Its been a great learning experience. I thank you all for your valuable inputs.

The batch job is doing better than our expectations. Runs in less than 15 seconds. We used recursion to explode the BOM tree. The batch job is invoked as a separated thread from java.

Just when I thought it was all over, few doubts arised in my mind.

The batch job will first truncate a table(say Table_X), expload the BOM and repopulate the correspoding table. In many cases, the batch job will be invoked multiple times parllely. We thought the Oracle Snapshot mechanism is going to take care of that. Assume that the batch job is running parallely in 2 Oracle Sessions. When one session completes, Java will 'commit' the changes. Now will this affect the other running batch job?

Cheers,
Arvind
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29253
    
140

Originally posted by Arvind Sampath:
Assume that the batch job is running parallely in 2 Oracle Sessions. When one session completes, Java will 'commit' the changes. Now will this affect the other running batch job?

It depends. If both batch jobs have already hit the table, the remaining one will not see the changes. Oracle maintains integrity by showing you the data as it was at the beginning of your query/transaction.

If the second batch job was sitting around - maybe due to lack of resources - and didn't manage to get any work done before the commit, it will see the changes.
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
Thanks Jeanne!

I explained the scenario to one of the DB guys out here. Our batch jobs always truncate the table first before inserting data. Came to know that when our batch job is running in one session, the same batch job if invoked in another session will wait as it will not have the lock to delete the table. Once the first job complets and commits(therby releasing the lock), the second job will gain control. Since we are truncating data first, we are not facing any probs.

Cheers,
Arvind
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: oracle stored proc for tree
 
Similar Threads
Composite and java.awt.Component / Container
MVC App which has a LongRunningProcess
First character of a File Becomes "?" on reading
Expert Oracle JDBC Programming - book promotion topic
Performance problem in jdbc