avenger avenger - 9 months ago 48
SQL Question

how compare data between two large tables in oracle without affecting production environment

i am facing problem in compare data from two huge tables.

scenario :

problem : i have to find out gaps between two set of data which is stored in tables in oracle DB and having live siebel application on it. i can't simply use select statement on whole set of data (8,000,000 rows) which is effecting performance of application.

what i have done it now :

simply put cursor on one set of data and comparing on my logic with other set of data and inserting gaps in other tables acc to logics, but in this solution it comparing one row at a time which is very slow process and getting time out after sometime.

can anyone suggest any better solution than this so that it can speed up the process really appreciate the help.

tdc tdc
Answer Source

There can be several different ways to improve performance depending on the exact use case. Based on the information you had provided, below are few things that might work.

  1. Rewrite the queries using 'minus' or 'not exists'.

  2. Create index on the columns that are used in the where clause. Note that index creation will take time and resources and impact system, so it is advisable to do that when the load on the server is low. If indexes are already there and not being used, try to use hints.

  3. If the data in those tables is static try to duplicate tables in test environment and run appropriate tests.

Using cursor on 8M rows does not sound very efficient unless that is the only way to go.

If you give more details, we might be able to give better suggestions.