Oracle Database Discussions

I have a table EMPLOYEES with 3 million records, I need to run a query without doing a full table scan.
The table is structured with a first_name and Last_name column. I have indiv indexes on each column.


Table: Employees
Index: EMP_FIRST_NAME
EMP_LAST_NAME

My Query Request: I have another table with a list of first and last names in a single column. I need to extract
all the records from the Employees table where the First_name or Last_Name is in this other table (ALL_EMP).


Because I'm using a third party tool to run the query, I'm limited to not using a union.

Table: ALL_EMP
Column: NAME


If I do this query, it uses both indexes. (Just not the query I want, I need the either/or)

select *
from employees
where first_name in (select name from all_emp)
and last_name in (select name from all_emp);

Problem is; I need to use the "OR" operator to give me either/or results.


If I do this query, it doesn't use any indexes. I tried doing the hing "/*+ INDEX(employees)*/
that didn't help..

select *
from employees
where first_name in (select name from all_emp)
or last_name in (select name from all_emp);

Because I have over a million records, can't afford to do a full table scan.


How can I get Oracle to use both indexes with one Query read?????