Ytukyg

I am trying to display employee properties using C# WPF view. I have data in different oracle tables in my DB.

Though there are a set of multiple tables, each set consists of the same structure. Those tables structure at high-level is...

Tables Structure

Employee table columns            (EMP table)  : ID, Name, Organisation

Employee properties table columns (EMPPR table): ID, PropertyName, PropertyValues

Each employee's ID and Name in EMP table and employee's properties in EMPPR table. Each employee has properties from 40-80 i.e. 40-80 rows per employee in EMPPR table.

Like this each department has one set of tables. i.e. (DEVEMP,DEVEMPPR),
(HREMP,HREMPPR),
(ADMINEMP, ADMINEMPPR),
(FINEMP, FINEMPPR),
(ADMINEMP, ADMINEMPPR) etc...

I need to fetch employee properties of multiple departments at once where I get lists of employee names department wise.

I am using the following query to fetch employee properties for inputted names as follows:

SELECT Pr.PropertyName, Pr.PropertyValue

FROM DEVEMP Emp

JOIN DEVEMPPR Pr ON Emp.ID = Pr.ID

AND Emp.Name IN (<List of DEV Names Inputted>)

Like this, I need to execute for each department.

Is query will be efficient if I use UNION ALL with all department tables query and IN condition with the combined list as below?

VIEW using UNION ALL

CREATE OR REPLACE FORCE VIEW EMPPRVIEW(NAME, PRNAME, PRVALUE) AS



SELECT Emp.Name, Pr.PropertyName, Pr.PropertyValue

FROM DEVEMP Emp

JOIN DEVEMPPR Pr ON Emp.ID = Pr.ID



UNION ALL



SELECT Emp.Name, Pr.PropertyName, Pr.PropertyValue

FROM HREMP Emp

JOIN HREMPPR Pr ON Emp.ID = Pr.ID



UNION ALL



SELECT Emp.Name, Pr.PropertyName, Pr.PropertyValue

FROM ADMINEMP Emp

JOIN ADMINEMPPR Pr ON Emp.ID = Pr.ID



UNION ALL



.

.

.

QUERY FROM VIEW

SELECT NAME, PRNAME, PRVALUE

FROM EMPPRVIEW

WHERE NAME IN (<Combined list of names from each dept>)

Approach #2 is easy in implementing as we execute query at once, but I am thinking it degrades query performance.

Which approach is better single query execution with UNION ALL or separate query execution for each department?

PS. The inputted emp names list may contain 1000 names for each department and each employee has 40-80 properties.

I concur with MatBailie's opinion; this should have been done as a column in a single Employee table. You could change it to that, and create updatable views mirroring your existing table structure so that the application carries on working while you switch it over to using a single unified table for Employees

In terms of your asking about query efficiency and presumably performance, you're going to have to test it. I only foresee performance problems if oracle won't use an index on each table during the union op..

So to answer your question: it'll work, but you should consider a change the structure rather than persist with finding ways of fudging around what is a problematic initial design decision. The only way to know if it will perform well enough for your requirement is to (stress) test it