0

I have a datamart with 5 dimension table and a fact table. I'm trying to clean a dimension table that has few rows (4000 rows). But, the fact table have millions rows (25GB)(Indexes and partitions).

When I try to delete a row in the table dimension, the process becomes very slow. It's just as slow despite the absence of relationship with a row in the fact table (cascade delete).

Is there any way to optimize this?. Thanks in advance.

JohnPortella
  • 1,791
  • 5
  • 21
  • 30
  • Is there a foreign key on the fact table that references the dimension? If so, is the foreign key indexed? If so, about how many rows in the fact table refer to the dimension? – Jon Heller Feb 06 '16 at 02:28

2 Answers2

0

Presumably, there is a cascading delete of some sort between the dimension table and the fact table.

Adding an index on the key column in the fact table may be sufficient. Then Oracle can immediately tell if/where any given value is.

If that doesn't work, drop the foreign key constraint altogether. Delete the unused values and add the constraint back in.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • "Adding an index on the key column in the fact table may be sufficient. Then Oracle can immediately tell if/where any given value is." Is Not option because the insert would be very slow (new index). "If that doesn't work, drop the foreign key constraint altogether. Delete the unused values and add the constraint back in." It's a great alternative. It's only slow when I tried to activated the constraint. – JohnPortella Feb 08 '16 at 17:16
  • @JohnPortella . . . Having an index on a column does slow down inserts, but I wouldn't characterize the difference as "very slow" because an index is present. Is this the experience that you have had with inserts on tables that have indexes? What is the relative performance iwth and without hte index? – Gordon Linoff Feb 08 '16 at 20:37
0

You could try these strategies as well :

create another copy of the fact table but, without the dim foreign key column of the table to be cleaned.

create fact_table_new as select dim1_k, dim2_k, dim3_k, dim4_k, dim5_k (not this column), fact_1, fact_2, ... from fact_table ;

or

update fact_table set dim5_fk_col = null where dim5_fk_col in (select k_col from dim5_table) ;

BengalTiger
  • 11
  • 2
  • Create a new table is not option, because new information inserts each hour, and the size is big. Update the fact table is very slow. – JohnPortella Feb 08 '16 at 17:13