Home » RDBMS Server » Performance Tuning » query plan and negative value (-1) in where clause
query plan and negative value (-1) in where clause [message #292258] Tue, 08 January 2008 05:25 Go to next message
maga
Messages: 17
Registered: November 2007
Junior Member
I have a question about this query:

SELECT g.col1,
       g.col2
FROM   Tab1 g,
       Tab2 Part
WHERE  Part.col3 <> 0
       AND g.col4 = 'PRO3'
       AND g.col5 = Part.col5
       AND g.Cod7 = - 1;



This is the execution plan:

SELECT STATEMENT, GOAL = ALL_ROWS
HASH JOIN
INDEX FAST FULL SCAN SYS_C00254422 (index of tab1)
TABLE ACCESS FULL TAB2 


If I change the select in this way:

SELECT g.col1,
       g.col2
FROM   Tab1 g,
       Tab2 Part
WHERE  Part.col3 <> 0
       AND g.col4 = 'PRO3'
       AND g.col5 = Part.col5
       AND - g.Cod7 = 1;



I have a new query plan:

SELECT STATEMENT, GOAL = ALL_ROWS
NESTED LOOPS
INDEX FAST FULL SCAN SYS_C00254422 (TAB1)
TABLE ACCESS BY INDEX ROWID TAB2
INDEX UNIQUE SCAN SYS_C00254336


Oracle use a nested loop and the index of the table TAB1 and doesn't do the hash join.

[Updated on: Tue, 08 January 2008 05:43]

Report message to a moderator

Re: query plan and negative value (-1) in where clause [message #292263 is a reply to message #292258] Tue, 08 January 2008 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query and above all execution plan are non-readable when they are not formated.
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Also post your Oracle version (4 decimals).

Regards
Michel
Re: query plan and negative value (-1) in where clause [message #292323 is a reply to message #292258] Tue, 08 January 2008 09:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am going to guess at this one.

This is a question of Oracle's CBO not computing correctly the number of rows to be returned by your query. When you put the minus sign in front of the column, you change the values you are querying against. Oracle's statistics were collected on the column, not on the - of the column.

Because of this, Oracle has to revert to using default guesses about restrictiveness of results based on that modified column, rather than using stats collected on the column itself, and thus your query plan has changed.

Basically I am saying this:

Statistics were collected on g.Cod7.
Statistics were not collected on (- g.Cod7).

Your first plan was able to use the stats on g.Cod7 to determine that hash join was best way to go.

The second plan had to guess on how restrictive (- g.Cod7) would be and thus decided nested loop against some other index would be a good idea.

I am guessing the second variation is taking forever to finish?

Have I been clear enough? Kevin
Re: query plan and negative value (-1) in where clause [message #292333 is a reply to message #292258] Tue, 08 January 2008 10:01 Go to previous messageGo to next message
maga
Messages: 17
Registered: November 2007
Junior Member
The second version is faster thant the first one.
only 2 seconds.

[Updated on: Tue, 08 January 2008 10:01]

Report message to a moderator

Re: query plan and negative value (-1) in where clause [message #292336 is a reply to message #292258] Tue, 08 January 2008 10:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
well, maybe your stats are out of date?

anyway, good luck, Kevin
Re: query plan and negative value (-1) in where clause [message #292337 is a reply to message #292336] Tue, 08 January 2008 10:14 Go to previous message
maga
Messages: 17
Registered: November 2007
Junior Member
statistics are good.
thanks
Previous Topic: increasing cache hit ratio
Next Topic: Merge join verse Filter. Same code, different database, different explain plan
Goto Forum:
  


Current Time: Sat Dec 10 14:46:16 CST 2016

Total time taken to generate the page: 0.07366 seconds