Re: compression of tables

This is a good example of how using sequence numbers for priomary keys can create more problems than it solves. (When will people stop implementing hierarchical and network model databases in relational databases??? oh well,)

sfar_Selim_at_excite.com (slim) wrote in message news:<16e3a9da.0204230728.e56b4d1_at_posting.google.com>...
> Hi,
>
> suppose that we have 4 tables:
 typo?
>
> --> t1 : t1_id (pk), t1_info
> --> t2 : t2_id (pk), t2_info, t2_t1_id (foreign key)
> --> t3 : t3_id (pk), t3_info, t3_t1_id (foreign key)
>
>
> Suppose that we have 1 millions of records in t1,
> 5 millions of records in t2,
> 4 millions of records in t3
> the goal is to compress these data in this case we process like this
>
> If n ( n > 1) records in t1 have the same t1_info, and the same set of
> info in both tables
> t2 and t3 then we will delete cascade (n-1) rows from t1
>
> e.g. :
> t1 : t1_id t1_info
> 1 'car'
> 2 'car'
> 3 'car'
> 4 'van'
> t2 : t2_id t2_info t2_t1_id
> 1 'tyre' 1
> 2 'brake' 1
> 3 'tyre' 2
> 4 'brake' 2
> 5 'tyre' 3
> 6 'brake' 3
> 7 'capacity' 4
>
> t3 : t3_id t3_info t2_t1_id
> 1 'price' 1
> 2 'power' 1
> 3 'price' 2
> 4 'power' 2
> 5 'price 3
> 6 'power' 3
> 7 'power' 4
>
> In this case we delete on cascade only t1_id = 2 and t1_id = 3 from
> t1
>
> I am writing the function and I have a big problem of performance,
> You are helpful for each idea you give to me!
> thanks ..

Still lots of questions of what you want to get rid of. (example if in table t2 record t2_id=6 did not exist, would you still delete t1_id=3???

possibilities:

   write the procedure so that it works on a range of records from t1 (e.g. all records with info like 'c%') and run multiple copies? Downside: loading the production system with too many processes unless run in off hours.

   Identify the records to be deleted off-line (i.e. in a backup copy of production), then run the deletes in production. Downside: could miss some new ones that were entered into production between when you took the backup and when you run the deletes.

   Fix your database design to prevent this from happening and convert from the old database to the new database. Downside: making the new design work with the application OR rewriting parts of the application.

   bite the bullet and run your slow procedure until it finishes and hope data doesn't change under you. downside: snapshot too old errors?

After this scrub is done, what plans do you have for preventing this from happening again? Your data model right now certainly isn't going to stop it and obviously your application doesn't either. (obviously my preference would be the fixed design solution.)

ed

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com