Home » SQL & PL/SQL » SQL & PL/SQL » Bad Left Outer Join Optimisation
icon9.gif  Bad Left Outer Join Optimisation [message #194443] Fri, 22 September 2006 04:43 Go to next message
Messages: 7
Registered: September 2006
Junior Member
(I originally posted this in beginners forum, but I'm not sure is the right place since optimisation is a bit technical so I'm reposting here. Aplogies for double post)

I'm very new to Oracle, but used SQL before. I've got the following situation

Table Person (Theres about 9,000,000 different people in my db)
- id (int)
- name (char(255))
- job_id (int)
id is indexed,
( id, job_id ) is indexed

Table Job (Theres only about 50 differnt jobs)
- job_id (int)
- job_name (char(255))
job_id is indexed

all fields are non-null (ints are actually bigint's)

When I execute the query:
select p.Name,

       Person p
         left outer join
       Job j
         on p.job_id = j.job_id

       rownum < 100

This query takes over 2 minutes!...

if I use a normal inner join instead of left outer join (which is fair, since job is non-null) - it is a matter of miliseconds.

I would have expected oracle to notice that on a 'left', the person p must always be present, and so pull back the first 100 records of Person, and join them to Job if applicable

unfortunately, the time its taking, it must be joining the entirity of Person (9 million records) to Job, *then* getting the first 100 results. The explain plan backs this up :-

explain plan:

Unfortunately I do *not* have the choice to not use left outer join, since it is generated from third party software (hibernate).

SQL server copes with the same query with no problem, (1sec response or something). (changing db is not an option either!!)

Is there anything I can do short of changing the script? (this is not an available option). I would have expected the optimiser to get this - is there some hint / index / something else I can do to this thing? I feel certain that oracle can do this, but I'm very new to it and don't know why it isn';t picking up this optimisation

Re: Bad Left Outer Join Optimisation [message #194549 is a reply to message #194443] Fri, 22 September 2006 10:56 Go to previous messageGo to next message
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
I think a hint /*+ FIRST_ROWS */ should help.
I created your test case and optimizer used NLs with above hint. It used hash join without one.

select /*+ FIRST_ROWS */ p.Name,
Person p,
Job j
where p.job_id = j.job_id(+)
and rownum < 100

Alternatively you can issue:

alter session set optimizer_mode=FIRST_ROWS;

somewhere before your query.

If you can not put the hint into SQL or issue "alter session", because of using Hibernate, you can set it globally for whole instance, or create an "after login" trigger that will set it on a user's session level.
Another option is a "query stability" option but I don't go there.

[Updated on: Fri, 22 September 2006 10:58]

Report message to a moderator

Re: Bad Left Outer Join Optimisation [message #194608 is a reply to message #194549] Sat, 23 September 2006 00:04 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I wouldn't go altering the optimizer mode for the entire database - or even the entire session - as it could make untold changes to other queries.

If you are using vendor software, Plan Stability is absolutely the right way to go. I dare say that if you look closer, you will find several more queries that could be tuned the same way.

Ross Leishman
Previous Topic: Joining 2 rows into 1
Next Topic: Idea pls
Goto Forum:

Current Time: Sun Aug 20 04:58:56 CDT 2017

Total time taken to generate the page: 0.14431 seconds