Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DELETE records from table having foreign key reference

668031Oct 30 2008 — edited Nov 12 2008
Hi,

I'm with this problem:

There is a history table that has a column that should be a foreign key from a action table. But for performance issues it isn't a foreign key.
The size of the history table is really large and the action table is tiny.

The history table grows very fast, so I can't compromise how fast rows can be inserted into it (because of batch processing).
To remove a action it can take a long time, because it needs to test if the action had been used on the history table.

So I'm searching for a fast way to test if a action had been used in the history table.

First I suggested to create a index to the table that would keep a uniq id of action for the table history, so it wouldn't grow as fast as the history but I've been told that wold compromise the insertion time. I couldn't check the information that is onerous . As I'm new to DB, is it possible?

Just a second I thought on create a new row into the table action a new column named isUsed, and every time a new history row is inserted a it's action is altered to set this to 1. Which option is better?

Is there a better solution?

Any help is really appreciated.
Thanks
Sampaio, Diogo

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 10 2008
Added on Oct 30 2008
2 comments
355 views