-
Hi all, I'm transferring an application from access to oracle. As I re-write some of my queries to fix the way joins are done, i'm noticing that my access db queries are almost 2x as fast as my oracle! Can anyone see something wrong with how I'm writing them?
ACCESS:
SELECT distinct a.sceneid
FROM ((z_scenes a LEFT JOIN scenekw ON a.sceneid = scenekw.sceneid) LEFT
JOIN admin_comments ON a.sceneid = admin_comments.sceneid
WHERE ((scenekw.scene_key_word LIKE '%tara%'
OR a.scenename LIKE '%tara%'
OR a.scenedescription LIKE '%tara%')
AND (scenekw.scene_key_word LIKE '%atlanta%'
OR a.scenename LIKE '%atlanta%'
OR a.scenedescription LIKE '%atlanta%'))
ORACLE (indexes and primary/foreign keys defined on all id's):
SELECT distinct a.sceneid
FROM z_scenes a, scenekw b, admin_comments c
WHERE a.sceneid = b.sceneid(+)
AND a.sceneid = c.sceneid(+)
AND ( (b.scene_key_word LIKE '%Tara%'
OR a.scenename LIKE '%Tara%'
OR a.scenedescription LIKE '%Tara%')
AND (b.scene_key_word LIKE '%Atlanta%'
OR a.scenename LIKE '%Atlanta%'
OR a.scenedescription LIKE '%Atlanta%') )
-
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
