Interesting.

The EXPLAIN PLAN might be helpful for this one.

I'm also interested in whether or not you get the same results with both queries, as the outer join logic is different between the 2.

Hmmm...

- 1 thing to look at is the intermedia text / context ( depending on which version of Oracle) stuff if you are going to be doing a lot of in-string searching like this.

- You are not using table c in the example, so why include it?

- As for performance, is everything else comparable between the 2? Is Oracle set up well? I t could just be that your installation is poorly optimized.

- Here is another way to try this:

SELECT
---*
FROM
---(
---SELECT
------a.sceneid
---FROM
------z_scenes a
---WHERE
------(
---------a.scenename ---------LIKE '%Tara%'------OR
---------a.scenedescription---LIKE '%Tara%'
------)---AND
------(
---------a.scenename------------LIKE '%Atlanta%'---OR
---------a.scenedescription---LIKE '%Atlanta%'
------)
---UNION
---SELECT
------ b.sceneid
---FROM
------scenekw b------
---WHERE
------b.scene_key_word LIKE '%Tara%'------AND
------b.scene_key_word LIKE '%Atlanta%'
---)

Of course, this assumes that there are no records in b that are not already in a, which may not be true.

- Another quick thought - you are using a LIKE on a field called key word - this is counter-intuitive as keywords are meant to be small words that are straight-searchable (ie - without need for LIKEs)

Don't know if this helps - just thinking out loud on this one.

- Chris