Re: NOT IN, SQL, Optimisation

I can't decipher that query quickly but given your description why not do something like this...

select pkg_id
from gp_package
where not exists (

    select elt_pdt_id
    from gp_element
    where elt_pkg_id = :pkg_id
    minus
    select elt_pdt_id
    from gp_element
    where elt_pkg_id = gp_package.pkg_id) and not exists (

    select elt_pdt_id
    from gp_element
    where elt_pkg_id = gp_package.pkg_id     minus
    select elt_pdt_id
    from gp_element
    where elt_pkg_id = :pkg_id)
/

Richard

Francois Baviere wrote:
>
> Hi,
>
> I worked quite a bit to find out a SQL sentence to look for some specific
> records in my database. I was quite surprise by the way NOT IN is working.
> However I have found something which work.... however it is very long....
>
> Here enclose the schema of my tables, the working SQL and a brief
> description of what I am trying to do....
>
> If somebody have an idea?
>
> Thanks Very Much
>
> François
>
> PS: I tried some programs to optimise automatically the SQL but after a
> couple of hours... it is still running)....
>
> I got three tables: GP_PACKAGE, GP_PRODUCT, GP_ELEMENT... and simply I got
> packages which are composed of products and the n-n relation is store in the
> element table.... What I am trying to do is to find the packages which are
> composed of the same products of the selected package....
> -----
> SELECT pkg_id, pkg_siris_id, pkg_version, sta_label
> FROM gp_package, gp_status
> WHERE gp_package.pkg_sta_id = gp_status.sta_id
> AND pkg_id NOT IN
> (SELECT pkg_id2
> FROM (SELECT pkg_id1, pkg_id2, COUNT (*) counter
> FROM ((SELECT gp_product1.pdt_cisco_id,
> gp_package2.pkg_id pkg_id2,
> gp_package1.pkg_id pkg_id1
> FROM gp_element gp_element1,
> gp_package gp_package1,
> gp_package gp_package2,
> gp_product gp_product1
> WHERE gp_product1.pdt_id =
>
> gp_element1.elt_pdt_id
> AND gp_package1.pkg_id =
>
> gp_element1.elt_pkg_id
> AND gp_product1.pdt_cisco_id NOT IN
> (SELECT gp_product2.pdt_cisco_id
> FROM gp_product gp_product2,
> gp_element gp_element2
> WHERE gp_element2.elt_pdt_id =
>
> gp_product2.pdt_id
> AND gp_element2.elt_pkg_id =
>
> gp_package2.pkg_id))
> UNION
> (SELECT gp_product2.pdt_cisco_id,
> gp_package2.pkg_id pkg_id2,
> gp_package1.pkg_id pkg_id1
> FROM gp_element gp_element2,
> gp_package gp_package2,
> gp_package gp_package1,
> gp_product gp_product2
> WHERE gp_product2.pdt_id =
>
> gp_element2.elt_pdt_id
> AND gp_element2.elt_pkg_id =
>
> gp_package2.pkg_id
> AND gp_product2.pdt_cisco_id NOT IN
> (SELECT gp_product1.pdt_cisco_id
> FROM gp_product gp_product1,
> gp_element gp_element1
> WHERE gp_element1.elt_pdt_id =
>
> gp_product1.pdt_id
> AND gp_element1.elt_pkg_id =
>
> gp_package1.pkg_id)))
> WHERE pkg_id1 = 121
> GROUP BY pkg_id1, pkg_id2
> HAVING COUNT (*) != 0));
> ------------
> create table GP_PACKAGE
> (
> PKG_ID NUMBER(20) not null,
> PKG_SIRIS_ID VARCHAR2(20) not null,
> PKG_VERSION NUMBER(20) not null,
> PKG_STA_ID NUMBER(20) not null,
> constraint PK_GP_PACKAGE primary key (PKG_ID)
> using index
> tablespace GP_INDEX01
> )
> /
> -- ============================================================
> -- Table : GP_PRODUCT
> -- ============================================================
> create table GP_PRODUCT
> (
> PDT_ID NUMBER(20) not null,
> PDT_CAT_ID NUMBER(20) not null,
> PDT_CISCO_ID VARCHAR2(100) not null,
> constraint PK_GP_PRODUCT primary key (PDT_ID)
> using index
> tablespace GP_INDEX01
> )
> /
> -- ============================================================
> -- Table : GP_ELEMENT
> -- ============================================================
> create table GP_ELEMENT
> (
> ELT_PDT_ID NUMBER(20) not null,
> ELT_PKG_ID NUMBER(20) not null,
> ELT_TYP_ID NUMBER(20) not null,
> constraint PK_GP_ELEMENT primary key (ELT_PDT_ID, ELT_PKG_ID,
> ELT_TYP_ID)
> using index
> tablespace GP_INDEX01
> )
> /
Received on Wed Nov 14 2001 - 12:19:00 CST