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:
- An effect you might overlook when creating a table as select with derived datatypes.
- A warning about using any unnecessarily long character types “just in case” you need the space
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 |
“…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
Comment by Matthias Rogel — November 14, 2013 @ 4:42 pm GMT 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 GMT 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 GMT 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:
Comment by Jonathan Lewis — November 15, 2013 @ 2:02 am GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 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
>>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 |
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 GMT Nov 29,2013 |
[…] 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 |