0

I want to add optional "todate" parameter to Prepared statement

One way is to add a placeholder for optional parameter(s) and replace it before execute

  where id=? OPTIONAL_SECTION

For example OPTIONAL_SECTION will be set to TO_DATE <=sysdate -1

A better and correct way is to bind optional parameter with handling null

where id=? and TO_DATE <= nvl(?, TO_DATE)

Is there a better way to handle optional date parameter? Specially because condition can be without equal ( TO_DATE < ?)

  • This is a minimal example, Id isn't a primary key (actually it's a foreign key), SQL return multiple records 
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • 1
    Maybe set it twice and use OR: `where id = ? and (? IS NULL OR TO_DATE <= ?)` (and set your parameter on both the last variables) – ernest_k Apr 05 '20 at 04:51
  • @ernest_k but my solution is more concise and maybe have better performance (without or) – Ori Marko Apr 05 '20 at 04:53
  • @user7294900 No, probably not. Check the actual SQL generated by Ernest's suggestion, and most likely the null check won't even be there. – Tim Biegeleisen Apr 05 '20 at 04:55
  • It doesn't do the same thing. It's possible that I do not understand your question. My solution checks "if incoming date parameter is null, ignore this, otherwise the `to_date` value must be `<=` that incoming date parameter". Yours checks "`to_date` must be `<=` the incoming paramter if it's not null, or `<= to_date`". This is a little absurd, though. – ernest_k Apr 05 '20 at 04:56
  • @TimBiegeleisen Ernest's SQL remove the date filter, but there's more rows/byes/cost in explain plan – Ori Marko Apr 05 '20 at 05:21
  • 1
    Just to be sure here, by "optional" you mean "if the parameter value is null, then ignore the `to_date <= ...` condition. Right? That's how I understood it. – ernest_k Apr 05 '20 at 05:24
  • @ernest_k yes, this is a way to view it – Ori Marko Apr 05 '20 at 05:38

2 Answers2

1

A better and correct way is to bind optional parameter with handling null

That is not necessarily better or more correct. It is a different way, but it's very debatable if it is "better and (more) correct".

SQL Query optimizers generally run before any ? argument values are known, so a TO_DATE <= nvl(?, TO_DATE) cannot be optimized and will require a full table scan, barring any other conditions in the where clause.

However with a TO_DATE <= ?, the optimizer may be able to use an index on TO_DATE to do a ranged scan of the index, so I'd argue that the first option is potentially better, depending on available indexes.

condition can be without equal ( TO_DATE < ?)

The correct way to write the second version is:

where id = ?
  and (? is null or TO_DATE < ?)

You will then of course have to specify the value twice for the PreparedStatement.

Community
  • 1
  • 1
Andreas
  • 154,647
  • 11
  • 152
  • 247
  • Although there's no date index and using table access full, there's more rows/byes/cost in explain plan in your (and Ernest's) solution – Ori Marko Apr 05 '20 at 05:44
  • @user7294900 The cost of a SQL access plan has nothing to do with the number of rows/steps in the access plan, but everything to do with the number of data/index pages that needs to be read from disk/cache, how random those accesses are, and how much work with temporary data is needed, e.g. sorting, merging, hashing, ... – Andreas Apr 05 '20 at 05:46
  • @user7294900 *"Although there's no date index"* That may change at any time, if a DBA analyzes the table and queries used, and decides that an index on `TO_DATE` would be helpful. Even if that is not applicable to *you*, it is a thing to consider in general, and this answer it for everybody with a similar problem, not just you. – Andreas Apr 05 '20 at 05:50
  • I understand, but the difference in explain plan is irrelevant in this case? – Ori Marko Apr 05 '20 at 05:50
  • @user7294900 Difference in explain plan is not irrelevant, but you need to read it right. --- Is a school book with more words a better book to learn from? Not necessarily, since it depends heavily on the actually words used, not just the number of words, which is better to learn from. – Andreas Apr 05 '20 at 06:08
  • @user7294900 explaining how a particular vendor arrives at a particular execution plan is a long story. If you really want to avoid the null check in sql, you can use two prepared statements in java and select which to use based on your parameter being null or not (one statement with `where id = ?` and the other with `where id = ? and to_date <= ?`; and pick in Java) – ernest_k Apr 05 '20 at 06:11
  • 1
    @ernest_k Which is option 1 ("OPTIONAL_SECTION") – Andreas Apr 05 '20 at 06:22
0

The first decision for a query with an optional parameter is as follows:

1) is it accepatable to use one statement for both / all options

2) it is prefered to use a separate statament for each option

In your case you have two parameter options:

id

id and date_parameter

From the naming, I'd argue, that ID is a primary key of the table, so it returns one row only and will be the driver in the execution plan - simple index access.

The date_parameter could only cause that the query return optionally no row

In this case you may safely use the decision 1) - one query for both option will be fine

But in other interpretation, the ID is a foreign key with tons of rows and the date_parameter is used to return only a small number of recent rows.

In this case the decision 1) aka OR query will fail badly. The execution plan is optimized on the case returning mass data, so you will wait ages to get few rows.

So in this case only decision 2) provides a performant solution. The short answer is to dynamically generate two queries based on the parameter passed 

where id=? 

where id=? and data_parameter <= ?

The technical problem with this approach is, that the queries have a different number of bind variables which makes the setXXX complicated.

To avoid this problem you can use the 1=1 or trick, that a) makes the number of the bind variable equal in all queries* and b) eliminates the not necessary onces.

Query for ID only is generated as

where id=? and 1=1 or data_parameter <= ?

Query with IDand DATE_PARAM remains the same

where id=? and data_parameter <= ?

Some more examples and credit for popularizing this approach can be foud here and there

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • Id isn't a primary key (foreign key), SQL return multiple records – Ori Marko Apr 05 '20 at 07:06
  • Rule of thumb @user7294900, if the query with and without the optional parameter *returns a highly different number of rows* use the **two different queries** and **do not use one query with OR**. – Marmite Bomber Apr 05 '20 at 08:09
  • My (second) solution doesn't include OR, and making two queries is more error prone – Ori Marko Apr 05 '20 at 08:16
  • Imagine plausible solution for several optional parameters, split to different queries will be exponential – Ori Marko Apr 05 '20 at 08:23
  • The point I'm addressing is, there should not be a *single query* if dependent on parameters it is expected to use *different execution plans*. But without knowing table details and *your actual problem* this is only an academic discussion @user7294900 – Marmite Bomber Apr 05 '20 at 16:18
  • ... and technically the queries are *different* only from the point of view of the Oracle parser. There is only *one query* with optional parts in the code. – Marmite Bomber Apr 13 '20 at 06:58