Home » SQL & PL/SQL » SQL & PL/SQL » SQL Oddity
SQL Oddity [message #209930] Mon, 18 December 2006 09:50 Go to next message
Messages: 220
Registered: April 2006
Senior Member
I have come across what I can only call a bit of an oddity. I first came across it in a query that had a whole bunch of tables and I would have just felt guilty posting all that here, but I was able to narrow it down so I can get it to reproduce using just two.

Here's the query:

SELECT ei.expenditure_item_id, cdl.line_type, ei.system_linkage_function
  FROM pa_expenditure_items_all ei,
       pa_cost_distribution_lines_all cdl
 WHERE ei.expenditure_item_id = cdl.expenditure_item_id
   AND cdl.line_type = 'R'
   AND ei.system_linkage_function IN ('ST', 'PJ', 'ER', 'VI') 
   --and ei.expenditure_item_id = nvl(null, ei.expenditure_item_id)
   --and cdl.expenditure_item_id = nvl(null, cdl.expenditure_item_id)
   --and cdl.expenditure_item_id = cdl.expenditure_item_id
   --and ei.expenditure_item_id = ei.expenditure_item_id

Now there are four lines which are commented out in the query, as far as effect on the output goes I think it is obvious these lines don't do a thing. When it comes to execution time and explain plan however those lines are huge. With all the lines commented out The explain plan is a hash join with full table access for each of the two table execution time aprox. 40 seconds. If I include either or both of the last two commented lines I get the same deal, but when I include either or both of the first two commented lines, I get full table access on the table referenced by the commented in line (when both are in full is on the ei table) and index access on the other table, no more hash join, and and execution time of about half a second.

Now I can see why the index access on one of the table makes all this a lot faster, but why do I have to 'trick' the optimizer into taking this route? These tables should have been analyzed last night so that should not be the problem. Now I am keeping in mind that this is speed to first record which is not the same as speed to all records, and when I plug in a sort by on one of the random columns, the advantage does go to the 'normal' execution path, but If I don't have a sort by in there then it seems like the optimizer aught to get the fastest path to do what I have said I want to do not the fastest path to do what I might have wanted (sort or sum or whatever)

Table and Index Creates for the tables, I excluded all the indexes which are never used no matter what, just because the whole thing is long enough already, also excluded a whole bunch of columns to shorten it up as well. If anyone wants the full declarations feel free to let me know and I will be happy to post them, just seemed like it would have been info overload.



  LINE_NUM                     NUMBER(15)       NOT NULL,
  LINE_TYPE                    VARCHAR2(1 BYTE) NOT NULL,


So anyway, not anything that is really a big deal, just an oddity I was curious to see if anyone had some insight into.

Re: SQL Oddity [message #210215 is a reply to message #209930] Tue, 19 December 2006 20:05 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If I read this correctly, without any of the four lines, Oracle chooses the fastest plan to retrieve all rows. This is intended behaviour. If you want the first row fastest, use the FIRST_ROWS hint or Optimizer Goal / Optimizer Mode.

Why do the first two commented lines affect the plan?

Oracle is not tricky enough to realize they do nothing. It believes that they will filter the results, but not by how much (so it guesses). If you look at the explain plan, you will notice a difference in the expected number of rows retirieved.

Reducing the estimated cardinality of a table will often have no effect, but sometimes it does. The most likely effect is to change a hash join into a nested loop, or to use an index range scan in favour of a full table scan.

You can achieve the same functionality in a more transparent manner by TELLING the optimizer how many rows you expect with the CARDINALITY hint.

Why don't the second two commented lines affect the plan?

When the LHS and RHS of a logical equality are syntactically IDENTICAL (not semantically, as was the case above), then the CBO transforms the predicate into cdl.expenditure_item_id IS NOT NULL. The earlier join predicates will filter all NULLs anyway, so this predicate has no effect whatsoever. Oracle does not use it to modify the plan.

If you changed the column to a NULLABLE non-join column, then it could possibly affect the plan - depending on how many NULLs the CBO thinks are in the table.

In summary, nothing odd is going on at all. The CBO cant read your mind - if you know something the optimizer doesn't (eg. how many rows satisfy a predicate, or that you want first rows returned fastest) then use a hint.

Ross Leishman
Re: SQL Oddity [message #210338 is a reply to message #210215] Wed, 20 December 2006 07:27 Go to previous message
Messages: 220
Registered: April 2006
Senior Member
Thank you very much for the reply, and you are right is does turn the hash join into a nested loop.

Curiosity Satisfied,
Previous Topic: how to print output of all days of a month using sql
Next Topic: insert into
Goto Forum:

Current Time: Thu Jun 29 03:58:43 CDT 2017

Total time taken to generate the page: 0.17422 seconds