Re: need help with query

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: 2000/02/15
Message-ID: <950642162.5264.0.nnrp-03.9e984b29@news.demon.co.uk>

in theory your cartesian product minus the set of combinations from employees should be about as efficiency as it gets, unless the cartesian product is huge.

Can you tell us the sizes of the three
tables to give us a hint about the scale of the problem.

Could you also tell use the execution path used in the MINUS solution.

How long does it take to run the MINUS compared to just: select count(*) from jobs, dept;

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

    pascal wrote in message <38A99DF0.AFA14DD9_at_ie.oracle.com>...     Hello,
    I am trying to construct an efficient query for 8.1.5 that reads from a table using a composite key coming from two other tables and returns all rows that fail to match for both keys. For example all employees where (job no, dept no) from DEPT, JOBS are not in EMPLOYEE table.

    DEPT
    Dept no NUMBER
    Dept name VARCHAR2

    JOBS

    Job no       NUMBER 
    Job name     VARCHAR2 

    EMPLOYEE 
    Job no       NUMBER 
    Dept no      NUMBER 

    Employee no NUMBER
    ...

    I have several approaches but they are all highly inefficient performing full table scans across all tables, ignoring the composite index on employee(job no,dept no), and using enormous amounts of temporary table space.

      select jobs.job no, dept.dept no 
      from jobs, dept 
    minus 
      select job no, dept no 
      from employee 

    and

      select jobs.job no,dept.dept no 
      from jobs, dept 
      where jobs.jobid||dept.dept no not in ( 
        select job no||dept no from employee) 
      

    If anyone know how to do this efficiently it would be much appreciated.

--
Received on Tue Feb 15 2000 - 00:00:00 CST