Hello Everyone,
I have a little different problem and I though anyone might give me any idea that what is going wrong.
I have a view which consists of 3 tables linked by UNION ALL, overall all the three tables contains around 50 million, 2 million & 3 million respectively.
Here is the view looks like:
Create view test as
Select * from tab1 // contains 50 million rows
Union all
Select * from tab2 // contains 50 million rows
Union all
Select * from tab3 // contains 50 million rows
This view is used to get the appropriate ID existing in any of the including table. Now the problem area is that when we query the view, it takes around 4 minutes to execute and in parallel if we try to update any row from tab1, it gives us timeout error:
update tab1 set process_bitmap = process_bitmap where trans_id = 21159815
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001
We cant afford to increase the timeout to 5 minutes. Only 20-40 sec is acceptable to us. Kindly suggest us some solution.
Also let me know that why the row is being locked due to the select operation, because the view actually selects the row & shows it.
So it is very surprising for us that what is going wrong and where? If we need to tune some configurations then kindly suggest.
Thanking you in advance for your anticipation.
Regards,
Omer Saeed Khan
I have a little different problem and I though anyone might give me any idea that what is going wrong.
I have a view which consists of 3 tables linked by UNION ALL, overall all the three tables contains around 50 million, 2 million & 3 million respectively.
Here is the view looks like:
Create view test as
Select * from tab1 // contains 50 million rows
Union all
Select * from tab2 // contains 50 million rows
Union all
Select * from tab3 // contains 50 million rows
This view is used to get the appropriate ID existing in any of the including table. Now the problem area is that when we query the view, it takes around 4 minutes to execute and in parallel if we try to update any row from tab1, it gives us timeout error:
update tab1 set process_bitmap = process_bitmap where trans_id = 21159815
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001
We cant afford to increase the timeout to 5 minutes. Only 20-40 sec is acceptable to us. Kindly suggest us some solution.
Also let me know that why the row is being locked due to the select operation, because the view actually selects the row & shows it.
So it is very surprising for us that what is going wrong and where? If we need to tune some configurations then kindly suggest.
Thanking you in advance for your anticipation.
Regards,
Omer Saeed Khan