matching at multiple levels

[jaxtell]

I'm working on a sql statement used to provide a list of categories and subcategories for a web site. Originally, it displayed all categories and first level subcategories

SELECT par.web_category_id AS GROUP_ID, par.web_category AS group_description,
       CAST
          (MULTISET (SELECT DISTINCT kid.web_category_id AS GROUP_ID,
                                     kid.web_category AS group_description
                                FROM web_categories kid
                               WHERE par.web_category_id =
                                                       kid.parent_web_category_id
                    ) AS item_group_nt_typ
          ) AS subgroups
  FROM web_categories par
 WHERE par.parent_web_category_id IS NULL AND par.web_category_id <> 0
 ORDER BY DECODE(par.web_category_id,23,1,25,2,1,3,26,4,27,5,24,6,9999)

easy enough... then it was decided to only show categories containing specific manufacturers. here is what I did

select * from (SELECT par.web_category_id AS GROUP_ID, par.web_category AS group_description,
       CAST
          (MULTISET (SELECT DISTINCT kid.web_category_id AS GROUP_ID,
                                     kid.web_category AS group_description
                                FROM web_categories kid,item_keywords_extranet cike
                               WHERE par.web_category_id =
                                                       kid.parent_web_category_id
                                                       and kid.web_category_id = cike.item_primary_group_id and cike.mfg_id in (select value_1 from common_lookups where group_name ='EXTRANET' and value_name='MFR_LIMIT')
                    ) AS item_group_nt_typ
          ) AS subgroups
  FROM web_categories par
 WHERE par.parent_web_category_id IS NULL AND par.web_category_id <> 0  
 ORDER BY DECODE(par.web_category_id,23,1,25,2,1,3,26,4,27,5,24,6,9999)) where subgroups is not empty

The problem is that it only considered subcategories containing that mfr id, not subcategories of subcategories (or subcategories of subcategories of subcategories).

Now I have this

select * from (SELECT par.web_category_id AS GROUP_ID, par.web_category AS group_description,
       CAST
          (MULTISET (SELECT DISTINCT par1.web_category_id AS GROUP_ID,
                                     par1.web_category AS group_description
                                     from web_categories par1
                                     where 
                                     par1.parent_web_category_id = par.web_category_id and exists (SELECT     *
              FROM web_categories kid, item_keywords_extranet cike
        START WITH (kid.parent_web_category_id = par1.web_category_id or kid.parent_web_category_id = cike.item_primary_group_id) 
        CONNECT BY NOCYCLE kid.parent_web_category_id =
                                                      PRIOR kid.web_category_id
 and  (kid.web_category_id = cike.item_primary_group_id )
   AND cike.mfg_id IN (
                    SELECT value_1
                      FROM common_lookups
                     WHERE group_name = 'EXTRANET'
                           AND value_name = 'MFR_LIMIT'))) AS item_group_nt_typ
          ) AS subgroups
  FROM web_categories par
 WHERE par.parent_web_category_id IS NULL AND par.web_category_id <> 0  
 ORDER BY DECODE(par.web_category_id,23,1,25,2,1,3,26,4,27,5,24,6,9999)) where subgroups is not empty

It works, but it takes about 30 seconds and the explain plan has 5 or 6 merge join cartesians. Is there a better way to do it? Right now I'm thinking that if I know the number of levels of subcategories then I could just write some sql to handle each specific level. Or maybe I could make a mview, since the results aren't likely to change often. What do you think?

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[jaxtell]

Nevermind. I think I have it figured out...

select * from (SELECT par.web_category_id AS GROUP_ID, par.web_category AS group_description,
       CAST
          (MULTISET (
SELECT DISTINCT par1.web_category_id AS GROUP_ID,
                par1.web_category AS group_description
           FROM web_categories par1
          WHERE EXISTS (
                   SELECT *
                     FROM (SELECT     web_category_id, web_category, parent_web_category_id, connect_by_root(web_category_id) cbr
                                 FROM web_categories kid
                           CONNECT BY NOCYCLE kid.parent_web_category_id =
                                                      PRIOR kid.web_category_id
                                                      ) kid,
                          item_keywords_extranet cike
                    WHERE (kid.web_category_id = cike.item_primary_group_id)
                    and par1.web_category_id = kid.cbr
                      AND cike.mfg_id IN (
                             SELECT value_1
                               FROM common_lookups
                              WHERE group_name = 'EXTRANET'
                                AND value_name = 'MFR_LIMIT')
                                and par1.parent_web_category_id = par.web_category_id)) AS item_group_nt_typ
          ) AS subgroups
  FROM web_categories par
 WHERE par.parent_web_category_id IS NULL AND par.web_category_id <> 0  
 ORDER BY DECODE(par.web_category_id,23,1,25,2,1,3,26,4,27,5,24,6,9999)) where subgroups is not empty

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/