I am using a recursive view which is giving the chain of the managers to whom a particular Employee is reporting at various level.
For Example:-Suppose the manager of Employee A is B and the manager of Employee B is C.So here A is reporting to A at level 0 and A is reporting to B at level 1 and A is reporting to C at level 2.
Likewise Employee B is reporting to Bat level 0 and repporting to C at level 1.
My recursive view is giving me this kind of data.But in my Base table there are 70 thousand Employess and it is taking lot of time to return the data for all the employees.
Is there any other way of doing it.
Its little urgent, so a quick reply is appreciated !!
I agree with the fact that recursive queries are slow as it takes lot of spool but for only 70K employees, it should not take that long. We process 100 of thousands of records with it and the outcome is not bad at all. If possible, can you share your query and the table definition here. The only other way I can think of is to create seperate tables/views for different levels of data and JOIN them to get the desired output.
May be this is not the right way. but just throwing an idea. How about creating a another view with the same table defintions and data? or atleast a temporary table?
So that we could jon these 2 tables.
Sorry, if it doesn't make sense.