Forum Stats

  • 3,780,660 Users
  • 2,254,421 Discussions


DELETE records from table having foreign key reference

668031 Member Posts: 2
edited Nov 12, 2008 9:10AM in SQL & PL/SQL

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.
Sampaio, Diogo


  • Gurjas
    Gurjas Member Posts: 1,190
    You should create a bitmap or btree index on the action_id of the history table depending on the cardinality of the column.
    Which will fasten the checking of action usage in the history table.

    As per my understanding the isused idea is not good.

    Why don't you go for the foreign key in the table? If you have already implement the foreign key in the table then this problem should not be there.. now you have to check the existense manually in the table. If possible you can also implement the foreign key now.
  • 668031
    668031 Member Posts: 2
    after all they did create a index. The problem of having a foreign key without having a index by that key is that it takes very long to check if it is used.
This discussion has been closed.