hi,
I'm using Oracle version 19c. We recently got into a discussion regarding joining transaction tables to lookup (parent) tables. One developer suggested that rather than joining the transaction table to a lookup table, hard code the actual foreign key value in the query rather than join to the lookup table to reduce performance risk. An example is below. Does anyone know of any pros/cons for either method? Only con of option 2 that I can of is that the foreign key ID values might be different between DEV and PROD.
--option 1 join to the department table
select emp.first_name,
emp.last_name
from employee emp
join department dept
on emp.department_id = dept.department_id
where dept.department_name = 'Facilities'
--option 2 hard code id value for "Facilities" department
select emp.first_name,
emp.last_name
from employee emp
where emp.department_id = 2 --id value for "Facilities"