Home » SQL & PL/SQL » SQL & PL/SQL » Outer Joins
Outer Joins [message #206574] Thu, 30 November 2006 10:28 Go to next message
gconner1997
Messages: 10
Registered: July 2006
Location: New York
Junior Member
I just read a question from another user about the role of the (+) in Oracle sql that prompted this question.

I have two scripts on the same tables, the join keys are indexed. Performance is near identical for the four scripts below : (each script returns data under 4 seconds, actually more like 2 seconds )

SELECT *
FROM TABLE_A A, TABLE_B B
WHERE A.KEY = B.KEY(+)
AND A.KEY = 'KEY1'

SELECT *
FROM TABLE_A A, TABLE_B B
WHERE A.KEY = B.KEY(+)
AND A.KEY = 'KEY2'

SELECT *
FROM TABLE_A A LEFT OUTER JOIN TABLE_B B ON A.KEY = B.KEY,
WHERE A.KEY = 'KEY1'

SELECT *
FROM TABLE_A A LEFT OUTER JOIN TABLE_B B ON A.KEY = B.KEY,
WHERE A.KEY = 'KEY2'

NOW when I try to use the 'IN' in these two examples:

SELECT *
FROM TABLE_A A, TABLE_B B
WHERE A.KEY = B.KEY(+)
AND A.KEY IN ('KEY1', 'KEY2')

SELECT *
FROM TABLE_A A LEFT OUTER JOIN TABLE_B B ON A.KEY = B.KEY,
WHERE A.KEY IN ('KEY1', 'KEY2')

I get 150 seconds on the (+) and about 4 seconds on the 'LEFT' script.

What I find most interesting is if I run both scripts in succession a few times, eventually the 'LEFT' script goes to about 0.6 seconds and the (+) script either improves slightly or gets worse. Over 4 runs the return range is 181-146 seconds, 154 seconds was the return on the first run.

Now explain_plan shows that the (+) is doing a full table scan on table_a, so I do understand reason for the time difference, I just don't know the reason why Oracle plans are so different. Is it that the (+) is an old method and not optimized as well as the left script? Is the (+) a more generic outer join? Are there tricks I can use to optimize the 'IN' operator in general?

Sorry, I didn't provide a data set, but for small sets, it's hard to see the performance difference, the real tables I'm joining have millions of rows.

(note a hint didn't help the 'IN (+)' script either)

Thanks
--glenn
Re: Outer Joins [message #206715 is a reply to message #206574] Fri, 01 December 2006 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How many distinct values of KEY are there in the tables?

Are the tables indexed, and if so, how.

Are stats gathered?

How many rows do these queries return?

Most importantly - how are you estimating the times for these queries? Are you running this in a GUI like TOAD or SQL*Developer and counting the time until the first set of rows is returned, or are you counting the time for the full dataset to be returned?
Re: Outer Joins [message #206820 is a reply to message #206715] Fri, 01 December 2006 12:00 Go to previous message
gconner1997
Messages: 10
Registered: July 2006
Location: New York
Junior Member
Thanks - it is a statistics issue.

We have both 9i and 10g in-house and 9i is set up as rule-based,
which is what we are used to here. I was in 10g and didn't notice the explain plan had a cost of 0 (a red-flag) for the (+) script. I guess the reason is the big red full-table-scan distracted me from the rest of the data.

After running the analyze table on both tables, the (+) has a cost of 58 and the (Left) has a cost of 59. Both scripts return in about 0.220 seconds.

Thanks for your help.
--glenn

Not sure if you would still be interested in the data of your
questions for me (which reminded me of the statistics) however I
included them below anyway:

Table_a main table
Table_b outer joined table

Table_a 31 indexes (3 unique), having 31 unique fields.
Table_b 2 indexes (1 unique), having 2 unique fields.

The join itself is simple, one field from each table, the field used from table_a is unique and the index was created as a unique index, table_b there are duplicates, therefore the index was created non-unique.

Each query returned 4 rows. (my actual query had 3 values, one is dulplcated in table_b)

The timing of the sql was the return value of a gui. I ran the
scripts in both 'sql navigator' (older version 3.2d11) and 'oracle sql developer'. Navigator returns a value in the 0.22 seconds area, oracle gui returns a result in the 0.08 seconds, all other things being equal.




Previous Topic: Index regarding
Next Topic: Sql - Related data difficult question
Goto Forum:
  


Current Time: Sun Dec 11 06:15:28 CST 2016

Total time taken to generate the page: 0.06013 seconds