mview in a Production transactional db

[thomgreen]

I have a developer that that would like to put a materialized view in a production db. The UI is built on a proprietary Java platform so the there is not very much code running from the DB. Performance is not the issue. It is more of a data integrity issue with using a particular table, that is built from several other tables, to reference a field in its current records in order to rebuild that set of records when changes are made:
- Table a is built from tables a,b,c
- New user access runs a proc that goes to table a, stores all values in project field, deletes those records and rebuilds the table
:: Developer is concerned about the data integrity of table a... if records are 'accidentally' (love how she believes they ACCIDENTALLY get deleted) deleted or corrupted. it will not rebuild with all of the info needed.

We are going to test with a normal view but would you necessarily tell the dev to just test the code well enough to assure no rec's are 'ACCIDENTALLY' deleted. I am concerned with the 'refreshes' on the mview in the production environment and managing the mview.

Any thoughts would be appreciated.

 

[jaxtell]

If I understand correctly, table a is completely wiped out and replaced by this procedure?

I've done mviews in transactional databases, using multiple tables, refresh complete on demand. The one with >1 Million records had some of the infrastructure guys upset. The 100k ones run without much issue.

I'm not sure an mview is the correct solution here, but I don't think I fully understand the process. Can you provide a bit more detail?

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[thomgreen]

Actually only the records to be reload, will be deleted.
table_a
=======
project_id
emp_id

The problem is that an emp_id will be passed to a procedure that will need to insert a value into table a. The proc will first get all project_id's that exist in table under the indicated emp_id. It will delete those emp_id,project_id records and rebuild the records for that emp_id using the project_id's gathered earlier in the process.

The developer's fear is a record will get deleted at a different point and not reloaded. I would like to tell her: to not delete records from the table and clean up her code.

 

[Thargy]

thom,

I have to say that this sounds like a disaster in the making.
There is no business requirement to make a table from several others - this can be achieved by a standard view.

Dropping and rebuilding tables is reckless. What's going on here? Can you state the requirement of the developer and let us tackle that?

Why are tables being dropped and rebuilt? Why do records have to be deleted to make changes? Has nobody heard of an UPDATE statement?

I know this may come across as a rant, but believe me, this sounds like a kludge in the making. Can you state the requirement please, please, please?

Regards

T

 

[emblem]

thargtheslayer, your words are very wise

Emblem

 

[thomgreen]

Unfortunately my explanation was either unclear or misinterpreted. There are no tables being deleted just records. Due to table sizes and join complexity the stardard view was resulting in an unexceptable response time. Thank you very much for the responses... I have worked the issue out with the developer.