Alternative for slow SQL subquery

Linux and Unix-Like UNIX and Linux Applications
1

Hi -- I have the following SQL query in my UNIX shell script -- but the subquery in the second section is very slow. I know there must be a way to do this with a union or something which would be better. Can anyone offer an alternative to this query? Thanks.

select
        count(*)
from
        employee_tbl
where
        employee_status_cd = 'C'
        or
        (
                employee_type_cd NOT IN ('TMGR', 'DIR')
                or
                (
                        employee_type_cd = 'MGMT'
                        and pay_grade_cd NOT BETWEEN '11' and '29'
                )
        )
        or
        (
                employee_type_cd = 'C'
                and comp_pay_pct = 0
                and employee_id in (select PT.employee_id
                from
                        payroll_tbl PT
                where
                        PT.specl_comp_cd = '21')
        )
;
2

Did you run tkprof? ( alter session set sql_trace true;) then run tkprof on the dump?
if the subselect returns thousands of hits from the db, or PT.specl_comp_cd is not indexed and the table contains millions of rows then you have a problem. Is employee id indexed in the payroll_tbl?

3

I don't have permissions to run sql_trace. Employee_id is the key field but it is not indexed.