Hi,
I use oracle version : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.
I have a question on Self join.
I am writing a oracle procedure where I have to refer the data from same table. I am using the self join while developing the procedure. This table will have large volume over the period of time. This table is estimated to have 3 to 4 million rows at its maximum capacity before getting purged. So want to be careful since start to avoid any major performance issue later. So Seeking advice!!
Below is data layout in tables used in my query. The problem statement is that I want to populate an intermediate staging table of all inputs needed to calculate salary for 1 and 5 from table A before going ahead with further calculation. In real time problem we used intermediate staging table because it is a complex calculation and want to save the inputs for future debugging.
Need to populate a table AAA with below data from A and B below. Expected data to be inserted into AAA:
--------------------------------------------------------------------------------------------------------------------------------------------------
ID EXID Salary
1 2 2000
1 3 3000
5 3 3000
5 4 4000
My Query using self join:
----------------------------------
Select a1.ID,
b.EXID,
a2.salary
from
A a1, B b, A a2
where a1.ID = b.ID
and b.EXID = a2.ID
and a1.ID in (1,5);
Note: ID is my primary key in the table.
Please advise if using self join in this case is going to be sub optimal. If so what can be best alternative to achieve something like this.
Table A(This table will have 3 to 4 million rows):
---------------------------------------------------------------------
ID Salary
1 ?
2 2000
3 3000
4 4000
5 ?
6 6000
Table B(This table is a static look up table which gives relation ship between A.ID above):
---------------------------------------------------------------------------------------------------------------------------------
ID EXID
1 2
1 3
2 1
3 1
5 3
5 4
6 2
6 3