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...)
Bug about which i wrote previously is fixed now in 12.2, and patch 16516751 is available now for 184.108.40.206 Solaris64.
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine
Previously i wrote about 2 undocumented cases when oracle invalidates result cache:
1. “select for update” from table with commit;
2. deletion of unrelated rows from parent table if there is unindexed foreign key with “on delete cascade”.
Later I found another case, but forgot to post:
If there are (more...)
I just noticed that os_command.zip from old oracle white paper was lost, so i decided to post link to my old package, which is like os_command but with timeout parameter: http://github.com/xtender/xt_shell