0

In SQL Developer (v 4.1) when I try to run the SQL Tuning Advisor on a query with one or more bind variables, if I leave any of them null, the SQL Tuning Advisor doesn't show any info at all. Is this a SQL Developer bug, or have I misunderstood something about null values, binding, and tuning?

Here is a simple example:

select * from dual where :p = 1;

Highlight the query and press Ctrl + F12, and you're prompted for the value of p. Leave the "NULL" checkbox checked and click "Apply":

Enter Binds for p

The SQL Tuning Advisor seems to quit running immediately and displays no detail for the query:

SQL Tuning Advisor showing empty Details

Is this a SQL Developer bug, or have I misunderstood something about null values, binding, and tuning?

hmqcnoesy
  • 4,165
  • 3
  • 31
  • 47
  • I can not reproduce the error with SQL Developer 17.2 and the 12c database. I did notice some bugs associated with the SQL Tuning Advisor associated with binds with the 10g database (e.g. Bug 6854919). – Patrick Bacon Sep 18 '17 at 19:37
  • Thanks @PatrickBacon. I ran on a copy of 17.2 and it works correctly, so the bug has evidently been corrected. – hmqcnoesy Sep 25 '17 at 15:44

1 Answers1

-1
select * from dual where nvl(:p,-1) = 1;

any null in WHERE clause makes all boolean algebra NULL (FALSE)

Ted at ORCL.Pro
  • 1,602
  • 1
  • 7
  • 10