Oracle 12c has increased the maximum length of character-based columns to 32K bytes – don’t get too excited, they’re stored out of lines (so similar in cost to LOBs) and need some modification to the parameter file and data dictionary (starting the database in upgrade mode) before you can use them.
Richard Foote has a pair of articles on indexing such columns:
Be cautious about enabling this option and test carefully – there are going to be a number of side effects, and some of them may require a significant investment in time to resolve. The first one that came to my mind was that if you’ve created a function-based index on a pl/sql function that returns a varchar2() type and haven’t explicitly created the index on a substr() of the return value then the data type of the function’s return value will change from the current default of varchar2(4000) to varchar2(32767) – which means the index will become invalid and can’t be rebuilt or recreated.
Obviously you can redefine the index to include an explicit substr() call – but then you have to find all the code that was supposed to use the index and modify it accordingly.
Jonathan, i’ve tested that extended varchars will be stored as “out of lines” clobs only if length more than value of the new hidden parameter – “_scalar_type_lob_storage_threshold”: http://orasql.org/2013/07/13/oracle-12c-extended-varchars/
But I’ve not tested it with indexes.
Comment by Sayan Malakshinov — November 14, 2013 @ 8:59 am BST Nov 14,2013 |
“…Obviously you can redefine the index to include an explicit substr() call – but then you have to find all the code that was supposed to use the index and modify it accordingly. …”
No, you don’t have to do that ?
See
sokrates@12.1 > create function fa( va in varchar2 ) return varchar2 deterministic is begin return 'a' || substr(va, 2); end fa; / 2 3 4 5 Function created. sokrates@12.1 > create table t ( a varchar2(10000)); Table created. sokrates@12.1 > insert into t select dbms_random.string('a', 10000) from dual connect by level <= 1000; 2 3 4 1000 rows created. sokrates@12.1 > commit; Commit complete. sokrates@12.1 > create index ti on t(substr(fa(a),1,1500)); Index created. sokrates@12.1 > exec dbms_stats.gather_table_stats(null, 't', cascade=> true) PL/SQL procedure successfully completed. sokrates@12.1 > variable v varchar2(10000) sokrates@12.1 > exec select dbms_random.string('a', 10000) into :v from dual; PL/SQL procedure successfully completed. sokrates@12.1 > set autotr on timi on sokrates@12.1 > select * from t where fa(a) = :v; no rows selected Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1488935846 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 55020 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 10 | 55020 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TI | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(INTERNAL_FUNCTION("A") AND "FA"("A")=:V) 2 - access(SUBSTR("SOKRATES"."FA"("A"),1,1500)=:V) REM no need to modify REM select * from t where fa(a) = :v; REM into REM select * from t where substr(fa(a),1,1500) = :v;Comment by Matthias Rogel — November 14, 2013 @ 4:42 pm BST Nov 14,2013 |
Mathias,
Your example shows an index created with an explicit substr() call built in after modifying the database to allow extended data types.
My point was that if you had an index defined pre-upgrade without a substr() it would be valid because a single key can be about 6,400 bytes long; but post-upgrade that index would become invalid and you would have to replace it with one that used an explicit substr().
Comment by Jonathan Lewis — November 14, 2013 @ 4:54 pm BST Nov 14,2013 |
Jonathan,
I got that point “that index would become invalid and you would have to replace it with one that used an explicit substr()” and agree.
What I didn’t get is “…but then you have to find all the code that was supposed to use the index and modify it accordingly …”
I don’t see that. Can you give an example ?
Thanks
Matthias
Comment by Matthias Rogel — November 14, 2013 @ 6:24 pm BST Nov 14,2013 |
Matthias,
A little awkward at the moment since I’d really need a 12c with extended datatypes and a 12c without, and I don’t have access to both at the moment, but here’s an “in-principle” demo that I’ve just run on 11.2.0.4. Note how execution plans change when the index definition changes to include the substr() that would become necessary to allow the index to be created:
create or replace function my_upper(i_v1 in varchar2) return varchar2 deterministic as begin return upper(i_v1); end; / drop table t1; create table t1 ( id number not null, v1 varchar2(100), v2 varchar2(100) ); create index t1_i1 on t1(id, my_upper(v1)); create index t1_i2 on t1(my_upper(v1)); insert into t1 values (1,'abc','def'); commit; set autotrace traceonly explain select /*+ index(t1) */ my_upper(v1) from t1 where id = 1 ; select /*+ index(t1) */ v1 from t1 where my_upper(v1) = 'ABC' ; set autotrace off drop index t1_i1; drop index t1_i2; create index t1_i1 on t1(id, substr(my_upper(v1),1,100)); create index t1_i2 on t1(substr(my_upper(v1),1,100)); set autotrace traceonly explain select /*+ index(t1) */ my_upper(v1) from t1 where id = 1 ; select /*+ index(t1) */ v1 from t1 where my_upper(v1) = 'ABC' ; set autotrace off Original Execution Plans -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2015 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| T1_I1 | 1 | 2015 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ID"=1) ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2054 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 2054 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_I2 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TEST_USER"."MY_UPPER"("V1")='ABC') Execution Plan after change of index definition ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 65 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 65 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_I1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1) ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 104 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 104 | 2 (0)| 00:00:01 | |* 2 | INDEX FULL SCAN | T1_I1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("MY_UPPER"("V1")='ABC') 2 - access(SUBSTR("TEST_USER"."MY_UPPER"("V1"),1,100)='ABC') filter(SUBSTR("TEST_USER"."MY_UPPER"("V1"),1,100)='ABC')Comment by Jonathan Lewis — November 15, 2013 @ 2:02 am BST Nov 15,2013
Sayan,
Thanks for the link, and the link to the article by Tim Hall that it references.
I agree with Tim’s comments about migration and compatibility – this is a feature that you should not use for a new Oracle project, it’s best to view it as a mechanism for minimising the recoding to move a project from some other database. (And even then I wonder if it’s a false economy that should be viewed only as a temporary measure.)
Comment by Jonathan Lewis — November 14, 2013 @ 4:44 pm BST Nov 14,2013 |
I agree that as all new features/versions it’s not stable yet, but hardware continues to become more and more powerful, data – bigger, and 4000 bytes becomes too small. And when(or if…) max blocksize will become more than 32k, it would be great to have such features like extended types on production servers.
Comment by Sayan Malakshinov — November 14, 2013 @ 10:28 pm BST Nov 14,2013 |
btw, why substr and not cast(.. as varchar2(…))? Because of the length only?
Comment by Sayan Malakshinov — November 14, 2013 @ 11:23 pm BST Nov 14,2013 |
Sayan,
when I see how Oracle’ miserable implementated tables with number of columns bigger then 255 (introducing extra row piece for each 255 columns and sometimes extra LIO for each fetch), I could hardly believe someone could even try to use this 12c functionality
Comment by Pavol Babel — November 15, 2013 @ 12:07 am BST Nov 15,2013 |
Pavol,
maybe i’m too optimistics, but i hope that finally extended types will be stored as usual scalar types by default (or _scalar_type_lob_storage_threshold will be documented) and this will allow to avoid use of small clobs.
Comment by Sayan Malakshinov — November 15, 2013 @ 12:16 am BST Nov 15,2013
Sayan,
Well, i do not know. They would have to increase block size, change row piece limitation. Would it be reasonable for OLTP applications to have 128kB block size? As far I know EMC DMAX storage arrays used to use 64kB block size in array cache (so there was huge space wastage when operating with 8kB block size), maybe they would be happy they could return to old concept :)
Comment by Pavol Babel — November 16, 2013 @ 5:06 pm BST Nov 16,2013
Pavol,
I agree, the possible consequences could lead to much more abuse and instability.
I see this as Oracle making a concession to make it easier to migrate INTO Oracle, but I don’t see them taking any risks that could make existing code limitations (e.g. the maximum size of the row directory, or the undo block row directory) introducing funny side effects. ASSM managed to introduce enough oddities even when it was a long term, and important project for Oracle; extended datatypes is too low value to be worth any risk.
Comment by Jonathan Lewis — November 29, 2013 @ 9:28 am BST Nov 29,2013
>>I’d really need a 12c with extended datatypes and a 12c without
It was convenient for me to create one pdb with extended datattypes for tests, so i have 1 pdb normal and 1 with extended within one cdb
Comment by Sayan Malakshinov — November 15, 2013 @ 6:23 am BST Nov 15,2013 |
Sayan,
How very sensible – and one of those ideas that is so obvious AFTER someone else has suggested it.
Comment by Jonathan Lewis — November 29, 2013 @ 8:58 am BST Nov 29,2013 |