Although we know that in the case when we do select from a table and one of the predicates is “field=:bind_variable” and :bind_variable is null, we should not get the rows, oracle not always “think” the same and don’t add upper filter predicate “:bind_variable is not null”, so it can do unnecessary work. It is completely depends from a plan, which will be chosen. Of course this applies to other predicates like >, < or != too.

select how_to_write_this_properly
from index_breaking_predictates
where trunc(i_want_all_the_times) =
to_date (:on_this_date, 'dd/mm/yyyy')
and the_numbers + 1 = :one_less_than_this
and instr(text, :starting_with_this) = 1;

HOW_TO_WRITE_THIS_PROPERLY
---------------------------------------------
where i_want_all_the_times >=
to_date (:on_this_date, 'dd/mm/yyy

I had an interesting conversation about table functions in Oracle’s PL/SQL; and the fact that they’re not available in MySQL. When I explained they’re available in Microsoft T-SQL User-Defined Functions (UDFs), my students wanted a small example. One of them said they’d tried to do it but couldn’t get it to work because they found the Microsoft web pages difficult to read and use. Specifically, they didn’t like the sparseness of this one on how to create a function.

create table physically_ordered_data (
can_vastly_reduce_your_io integer,
and_reduce_buffer_cache_use integer,
when_querying varchar2(1)
default 'Y',
constraint on_the_leading_columns_of_the
primary key (
can_vastly_reduce_your_io,
and_reduce_buffer_cache_use
),
just_define_your_table_as integer
)