Oracle Scratchpad

November 14, 2013

32K Columns

Filed under: 12c,Function based indexes,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 8:06 am GMT Nov 14,2013

Oracle 12c (release 1) has increased the maximum length of character-based columns to 32K bytes – don’t get too excited, they’re stored out of line (so similar in overheads to LOBs) and need some modification to the parameter file (setting max_string_size to extended) and data dictionary (including a step that starts 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 extended character types, 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.

Footnote:

Don’t forget that everything may change with the arrival of 12.2.

Other notes:

I’ve got a couple of notes on the blog about extended character types:

 

 

 

 

15 Comments »

  1. 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 GMT Nov 14,2013 | Reply

  2. “…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 GMT Nov 14,2013 | Reply

    • 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 GMT Nov 14,2013 | Reply

      • 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 GMT Nov 14,2013 | Reply

        • 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 GMT Nov 15,2013

  3. 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 GMT Nov 14,2013 | Reply

    • 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 GMT Nov 14,2013 | Reply

      • btw, why substr and not cast(.. as varchar2(…))? Because of the length only?

        Comment by Sayan Malakshinov — November 14, 2013 @ 11:23 pm GMT Nov 14,2013 | Reply

      • 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 GMT Nov 15,2013 | Reply

        • 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 GMT 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 GMT 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 GMT Nov 29,2013

  4. >>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 GMT Nov 15,2013 | Reply

  5. […] structure that exposes the defining text. (This doesn’t change, by the way, if you have set max_string_size to […]

    Pingback by v$_fixed_view_definition | Oracle Scratchpad — March 21, 2022 @ 7:08 pm GMT Mar 21,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: