OraFAQ Forum: Performance Tuning » query plan and negative value (-1) in where clause

Home » RDBMS Server » Performance Tuning » query plan and negative value (-1) in where clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query plan and negative value (-1) in where clause [message #292258]

Tue, 08 January 2008 05:25

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: query plan and negative value (-1) in where clause [message #292323 is a reply to message #292258]

Tue, 08 January 2008 09:42

Kevin Meade
Messages: 2103
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

Report message to a moderator