Nested query vs. INTERSECT Operator

From: Yogen Sanghani <yogen_at_astro.ocis.temple.edu>
Date: 1995/12/17
Message-ID: <4b1tud$idv_at_cronkite.ocis.temple.edu>#1/1

I need to write a relatively complex query using PL/SQL, and my options are to use Temporary tables, Nested Queries or UNION/INTERSECT operators. I'm trying to figure out which of these options would yield the best efforts/performance ratio. I am particularly interested in finding out how does the optimizer handles a query with INTERSECT operators.

The relevent table structures are along these lines:

   ITEM ITEM_DESC1 ITEM_DESC2 ITEM_DESC3

ItemCode            ItemCode            ItemCode            ItemCode
Attrib1              wDesc1              wDesc2              wDesc3

Attrib2
Attrib3
Attrib4
........
Attrib10
Desc1 long
Desc2 varchar(2000)
Desc3 varchar(2000)

(#rows=20,000) (#rows=500,000) (#rows=60,000) (#rows=60,000)

Where,

ITEM       = main items table, with various attributes and three large
             description fields.
ITEM_DESC1 = A break down of all words in Desc1 for all Items

ITEM_DESC2 = A break down of all words in Desc2 for all Items ITEM_DESC3 = A break down of all words in Desc3 for all Items

The query involves finding all "ItemCodes" which match ALL OF of the specified "Attribs" AND "Desc1" field contains ALL of the specified words AND "Desc2" contains ALL of the specified works and "Desc3" contains ALL of the specified words.

A sample query using nested queryes looks like this:

Select ItemCode from ITEM_DESC1
where wDesc1 like 'Word1%' and
ItemCode in (Select ItemCode from ITEM_DESC2

             where wDesc2 like 'Word2%' and
             ItemCode in (Select ItemCode from ITEM_DESC3
                          where wDesc3 like 'Word3%' and
                          ItemCode in (Select ItemCode from ITEM
                                       where Attrib1='att1'
                                       and Attrib2='att2'
                                       ......
                                       )
                          )
             )

I've chosen this particular order of nesting with keeping in mind the table size and the type of match (= vs. like) performed against columns.

I would like any opinions on how this type of query would perform compared to a query constructed using intermediate temporary tables and a query constructed using INTERSECT operators.

Thanks for any suggestions/opinions.

---

Yogen. Received on Sun Dec 17 1995 - 00:00:00 CET