Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Undesirable Full Table scan when a OR condition is used.

277530Nov 3 2008 — edited Nov 8 2008
My Oracle DB version is 10.2.0.2.0

I want to code a simple functionality in a query.
My function takes NUM_ARRAY(List of numbers)
I selecting from EMP table and when input parameter i_emp_id(array of number having emp_id's) is passed then row should be returned for passed employees else if i_emp_id is not passed then data for all employee should be returned.

Eg if i pass NUM_ARRAY(1,2) in i_emp_id parameter then data for emp_id 1 and 2 should be returned. If i pass NULL in i_emp_id the data for all the employees should be returned

To acheive this in my function i have writted select like:

CREATE OR REPLACE PROCEDURE (i_emp_id IN NUM_ARRAY) --NUM_ARRAY is array of numbers
l_count_emp NUMBER;
l_emp_id NUMBER;
l_emp_name VARCHAR2(50);
l_emp_dob DATE;
l_emp_salary NUMBER;
BEGIN
IF i_emp_id IS NOT NULL THEN
l_count_emp := i_emp_id.COUNT;
ELSE
l_count_emp := 0
END IF;

SELECT emp_id, emp_name, emp_dob, emp_salary
INTO l_emp_id, l_emp_name, l_emp_dob, l_emp_salary
FROM employee
WHERE ((emp_id IN (SELECT COLUMN_VALUE FROM TABLE(i_emp_id)) AND l_count_emp > 0) OR
(i_count_emp = 0)
);
END;

If list of emp id is passed in i_emp_id then 1st clause -----"((emp_id IN (SELECT COLUMN_VALUE FROM TABLE(i_emp_id)) AND l_count_emp > 0) " would be successfull and return data for passed in emp id's
If list of emp id is not passed then i_count_emp will have 0 and 2nd clause -----(i_count_emp = 0) will be true and return data for all the employees.

Note their is a 'OR' between 2 clauses in sql.

Now the problem is if i pass in single emp id in i_emp_id then the optimizer goes in for a full table scan. which is not acceptable to me. Ideally it should ignore 2nd clause and only fire 1st clause "((emp_id IN (SELECT COLUMN_VALUE FROM TABLE(i_emp_id)) AND l_count_emp > 0) " and use the index on emp id.
AND if i pass i_emp_id as null then it should go in for full table scan. which is acceptable and logocal.

My queries are as follows:

1.) Why is optimizer is going for full table scan even if one instrument id is passes?
2.) is their any better wat to handle such situations? How does you guys handle such situation?

Thanks
Rohit
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 6 2008
Added on Nov 3 2008
7 comments
729 views