Optimizing Oracle queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Optimizing Oracle queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-24 : 08:49:54

cristina reynolds writes "Hi, I'm still using Oracle 7.3!
I have found some orphaned records in a table. The relationship
between this table and the other table, which would be the parent
table for the orphaned records, is one to many.
What I'm trying to do is simply to list the orphaned records, and
to do that I need a DISTINCT in my SELECT clause. However, it takes a very long time. My SQL script looks like this:

SELECT DISTINCT FK_KEY
FROM CHILD_TABLE
WHERE FK_KEY NOT IN(SELECT PK_KEY FROM PARENT_TABLE)
/

I've tried to change the script, but with no success.
Is there any ways to optimize this script?

Please help.

Many thanks

Cristina"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-24 : 08:54:48

This might work faster:

SELECT * FROM CHILD_TABLE A
WHERE NOT EXISTS(SELECT * FROM PARENT_TABLE B WHERE B.FK_KEY=A.FK_KEY)

I don't know if this works in Oracle. We're mainly a SQL Server site, if we have an Oracle guru that can shed some light on a more efficient way to do it. You may also want to try:

http://dbforums.com/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-24 : 09:42:58

Also not knowing oracle, I would tend to write the code this way:


SELECT A.* 
FROM
( SELECT DISTINCT FK_KEY
  FROM CHILD_TABLE
) A
LEFT OUTER JOIN
 PARENT_TABLE B
ON 
  A.FK_KEY = B.PK_KEY
WHERE 
  B.PK_KEY is NULL

but, i don't even know if Oracle supports JOIN syntax. My reasoning is -- make sure the distinct is calculated BEFORE joining to the parent_table, which in theory will produce far less joins.

- Jeff

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-03-24 : 11:27:03

Oracle 7.3 does not support the ANSI join syntax, so you'll have to do your outer join in the WHERE clause using Oracle syntax if you use Jeff's approach.

Anyway, your SQL is straightforward enough. Have you traced your query to see the explain plan using TKPROF? Maybe you just have an indexing problem. Is there an index on the FK_KEY in the child table?

Also, are you using the Rule based optimizer or the Cost based optimizer? If using the CBO, do your tables and indexes have statistics generated for them?

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources