-- it's just for fun: SQL> alter system set "_scalar_type_lob_storage_threshold"=32000; System altered. SQL> create table t_varchar32000(v varchar2(32000 byte)); Table created. SQL> insert into t_varchar32000 2 select rpad(rownum,31999,'x' ) || 'y' from dual connect by level<=1000; 1000 rows created. SQL> commit; Commit complete. SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace users; (more...)
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...)
Patch for “Bug 16516751 : Suboptimal execution plan for query with join and in-list using composite index” is available now
Bug about which i wrote previously is fixed now in 12.2, and patch 16516751 is available now for 188.8.131.52 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...)
Tim Hall perfectly describes in his excellent post how new extended datatypes are stored on Oracle 12c.
I just found interesting parameter “_scalar_type_lob_storage_threshold“ – “threshold for VARCHAR2, NVARCHAR2, and RAW storage as BLOB” – This parameter is the max size in bytes, at which these data types will (more...)
Previously i showed how we can optimize getting TopN rows sorted by field “B” for each distinct value “A” with undocumented “lateral” in previous versions of Oracle RDBMS.
But now it is documented!
Very simple example:
with t as (select level a from dual connect by level<=10) select * from (more...)
I was hoping that if inline “with” functions are in the query, so their results will be consistent with it (as operators), but unfortunately such functions returns also inconsistent results as standalone pl/sql functions.
SQL> create table t as select 1 a from dual; Table created. SQL> declare 2 j (more...)