Funny that oracle can easily cast ‘nan’,'inf’,'infinity’,'-inf’,'-infinity’ to corresponding binary_float_infinity,binary_double_nan, but there is no any format models for to_char(binary_float_infinity,format) or to_binary_***(text_expr,format) that can output the same as to_char(binary_float_infinity)/to_binary_float(‘inf’) without format parameter:
If a BINARY_FLOAT or BINARY_DOUBLE value is converted to CHAR or NCHAR, and the input is either infinity or NaN (not a number), then Oracle always returns the pound signs to replace the value.
SQL> select to_binary_float('inf') from dual;
In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.
Little example with difference:
SQL> create or replace function f_deterministic(p varchar2)
2 return varchar2
7 return p;
SQL> set (more...)
Craig Shallahamer wrote excellent article “When is v$sesstat really updated?”.
And my today post just a little addition and correction about the difference of updating ‘Db time’ and ‘CPU used by this session’ statistics.
In this test I want to show that the statistics will be updated after every fetch call.
I have set arraysize=2, so sql*plus will fetch by 2 rows:
-- Result will be fetched by (more...)
Everyone knows Tom Kyte’s mantra:
You should do it in a single SQL statement if at all possible.
But we all know that “Every rule has an exception”
There are many different cases when pl/sql with sql can be more efficient than only sql, and i dont want to catalog them. I just want to show a couple examples of such exceptions:
1. Running totals by several dimensions
Simple example from forum:
Valid funny queries
select the join from join join join using(the,some)
select some join from left join join right using(some,the)
select 1 join from join join join join join using(the) on 1=1
select the some from join
where the=some( the(select some from join)
,the(select the from join)
Several months ago I wrote about avoiding inlist iterator, but this post about how to force inlist iterator in queries like that:
from xt_test1 t1
t1.a = :a
and t2.id in (a,b)
i.e. when we need to get rows from big table using index by list of values from another table.
We already know that the CBO transformation engine in 12c can unnest scalar subqueries from select-list.
So it’s not very surprising, that CBO is now able to add scalar subqueries costs to total query cost (even if “_optimizer_unnest_scalar_sq” = false):
Interesting, that SYS_OP_MAP_NONNULL appeared in the Oracle 12c documentation: Choosing Indexes for Materialized Views
Lazy tip: By the way, with length limitations, we can also use documented dump function:
2 t(a,b) as (
3 select *
4 from table(ku$_vcnt(null,'FF','A'))
9 ,case when sys_op_map_nonnull(a) = sys_op_map_nonnull(b) then '=' else '!=' end comp1
10 ,case when dump(a,1017) = dump(b,1017) then '=' else '!=' end comp2
-- it's just for fun:
SQL> alter system set "_scalar_type_lob_storage_threshold"=32000;
SQL> create table t_varchar32000(v varchar2(32000 byte));
SQL> insert into t_varchar32000
2 select rpad(rownum,31999,'x' ) || 'y' from dual connect by level<=1000;
1000 rows created.
SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace users;
Previously i wrote beautifier in perl, but it was not so good, so i decided to write it in java using popular BlancoSQLFormatter library.
So you can download it now: http://orasql.org/scripts/SQLBeautifier.jar
java -jar SQLBeautifier.jar your_file.sql
echo select * from dual | java -jar (more...)