Need SQL optimization help

From: Ivan K. <ivan_521521_at_yahoo.com>
Date: Tue, 19 Jul 2005 14:56:54 -0500
Message-ID: <42DD5B06.1050208@yahoo.com>

I have been writing SQL scripts for five years, however lately, the database I have been working with is so large that queries I need to run are taking in excess of hours. I have been reading the Oracle literature on DB and SQL optimization, and so far, I find it a bit bewildering.

I will continue to read the literature, but in the meantime, can someone suggest how I might optimize the following query?

Table ItemList has 16,939,870 rows.
Table SubItems has 97,626,282 rows.
Table SubSubItems has > 100,000,000 rows.

Thank you for your help in advance.

select

   item_list.item_id,
   SubItems.sub_item_id,
   SubSubItems.sub_sub_item_id
from

(

     select distinct
       item_epass01.item_id
     from
       (
         select distinct
           item_ipass01.item_id,
           item_ipass01_k.keyword
         from
          ItemList item_ipass01
          join ItemKeyword item_ipass01_nsk on
          item_ipass01.item_id = item_ipass01_nsk.na_sequence_id
          join Keyword item_ipass01_k on
          (
            item_ipass01_nsk.keyword_id = item_ipass01_k.keyword_id and
            (
              item_ipass01_k.keyword = 'FLAG01' or
              item_ipass01_k.keyword = 'FLAG02' or
              item_ipass01_k.keyword = 'FLAG03' or
              item_ipass01_k.keyword = 'FLAG04'
            )
          )
       )
       item_epass01
       left join
       (
         select distinct
           item_ipass02.item_id,
           item_ipass02_k.keyword ep02_keyword
         from
          ItemList item_ipass02
          join ItemKeyword item_ipass02_nsk on
          item_ipass02.item_id = item_ipass02_nsk.na_sequence_id
          join Keyword item_ipass02_k on
          (
            item_ipass02_nsk.keyword_id = item_ipass02_k.keyword_id and
            item_ipass02_k.keyword = 'FLAG05'
          )
       )
       item_epass02 on
       item_epass01.item_id = cDNA_epass02.na_sequence_id
     where
       ep02_keyword is NULL

   )
   item_list
   left join SubItems on
(

       item_list.item_id = SubItems.item_id and
       SubItems.group_id = 146

   )
   left join SubSubItems on
(

       SubItems.sub_item_id = SubSubItems.sub_item_id and
       SubSubItems.score > 40.0

   ); Received on Tue Jul 19 2005 - 14:56:54 CDT