NOT IN, SQL, Optimisation

From: Francois Baviere <fbaviere_nospam_at_yahoo.com>
Date: Wed, 14 Nov 2001 10:30:10 +0100
Message-ID: <9stdh7$r9j$1@s1.read.news.oleane.net>

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 - 03:30:10 CST