We have 3 normalized tables. All are parent and their child tables. These tables have millions of rows.
If any one of the tables gets updated then we need to join with latest rows from all parent and child tables. Finally Pull only the changed rows.
This sql will be executed on daily basis to identify those changes and load into the target table incrementally.
Current table have millions of rows which is degrading the performance by doing entire table scan and cross join to identify the changes if any.
Is there any better way to do this kind of join?. This should be done incrementally every day once.
Please point if we already have discussion on this.
Currently we are cross joining all 3 tables using primary key and filter the rows using OR clause with the load date.
Thank you