Extended string lengths can be very beneficial but they can also be an impediment to index and constraint creation. Read on to see what the problems are and how to get around them.
Creating indexes and unique constraints in an Oracle 12c database configured to use the extended string length (32767) may result in the following error being thrown:
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
This can occur for single-column indexes and for concatenated (multi-column) indexes and is a function of the overall length of the index key, the nls_length_semantics setting and the block size of the database or tablespace in use. The issue arises when the total length of the VARCHAR2 key columns exceeds a pre-determined length. This length is dependent upon the nls_length_semantics of the database or session and the database characterset in use. The following example illustrates these limits under both length semantics conditions using the AL32UTF8 characterset, a characterset where one character can consume up to four bytes. The example creates the same table with differing definitions and attempts to create a unique constraint on the table. For byte-length semantics the following results were obtained with an 8k block size in use. Multiple tests were run varying the VARCHAR2 lengths of all columns in the constraint/index, which may seem repetitious but it more clearly illustrates the issue:
SQL>
SQL> --
SQL> -- Create tables with varying length columns
SQL> -- to determine the length limit when
SQL> -- VARCHAR2(32767) is configured
SQL> --
SQL> -- Byte-length semantics
SQL> --
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(6347) not null,
4 stringm varchar2(20) ,
5 stringz varchar2(20) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(6348) not null,
4 stringm varchar2(20) ,
5 stringz varchar2(20) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(6327) not null,
4 stringm varchar2(30) ,
5 stringz varchar2(30) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(6328) not null,
4 stringm varchar2(30) ,
5 stringz varchar2(30) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(6307) not null,
4 stringm varchar2(40) ,
5 stringz varchar2(40) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(6308) not null,
4 stringm varchar2(40) ,
5 stringz varchar2(40) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(6287) not null,
4 stringm varchar2(50) ,
5 stringz varchar2(50) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(6288) not null,
4 stringm varchar2(50) ,
5 stringz varchar2(50) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
Notice in all cases the overall key length calculates to 6387 bytes as the maximum; this means that the sum of the declared lengths of all VARCHAR2 columns to be included in a given index cannot exceed 6387 bytes. This changes dramatically when CHAR length semantics are in use:
SQL>
SQL> --
SQL> -- Now set length semantics to CHAR
SQL> --
SQL> -- Using AL32UTF8 character set the VARCHAR2
SQL> -- lengths reduce to 1/4 of those for BYTE
SQL> -- semantics
SQL> --
SQL> alter session set nls_length_semantics=CHAR;
Session altered.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1576) not null,
4 stringm varchar2(10) ,
5 stringz varchar2(10) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1577) not null,
4 stringm varchar2(10) ,
5 stringz varchar2(10) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1556) not null,
4 stringm varchar2(20) ,
5 stringz varchar2(20) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1557) not null,
4 stringm varchar2(20) ,
5 stringz varchar2(20) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1536) not null,
4 stringm varchar2(30) ,
5 stringz varchar2(30) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1537) not null,
4 stringm varchar2(30) ,
5 stringz varchar2(30) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1516) not null,
4 stringm varchar2(40) ,
5 stringz varchar2(40) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1517) not null,
4 stringm varchar2(40) ,
5 stringz varchar2(40) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1496) not null,
4 stringm varchar2(50) ,
5 stringz varchar2(50) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1497) not null,
4 stringm varchar2(50) ,
5 stringz varchar2(50) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1476) not null,
4 stringm varchar2(60) ,
5 stringz varchar2(60) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1477) not null,
4 stringm varchar2(60) ,
5 stringz varchar2(60) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1456) not null,
4 stringm varchar2(70) ,
5 stringz varchar2(70) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1457) not null,
4 stringm varchar2(70) ,
5 stringz varchar2(70) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1436) not null,
4 stringm varchar2(80) ,
5 stringz varchar2(80) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(1437) not null,
4 stringm varchar2(80) ,
5 stringz varchar2(80) not null);
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
[Again, multiple examples were configured to more clearly illustrate the problem.] The maximum length now becomes 1596, and remember that refers to characters; the overall byte length is three bytes shorter than when using BYTE length semantics and this is due to the 1 character = 4 bytes conversion for the AL32UTF8 character set. Such behavior is repeated for any of the UTF-8 family of character sets as they all map one character to up to four bytes, and Oracle defaults to the 1:4 conversion for these character sets regardless of the actual storage a character may consume. These limitations present themselves when the database block size is 8192, or 8K; larger block sizes will result in longer keys and a slightly different error message. The following results are generated using a 16k block size tablespace:
SQL>
SQL> --
SQL> -- Create tables with varying length columns
SQL> -- to determine the length limit when
SQL> -- VARCHAR2(32761) is configured
SQL> --
SQL> -- Byte-length semantics
SQL> --
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(12907) not null,
4 stringm varchar2(20) ,
5 stringz varchar2(20) not null) tablespace yerg_16k;
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz)
3 using index (
4 create unique index hashtest_uq_idx on hashtest(stringtst, stringm, stringz)
5 tablespace yerg_16k);
Table altered.
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
SQL> create table hashtest(
2 id number primary key,
3 stringtst varchar2(12908) not null,
4 stringm varchar2(20) ,
5 stringz varchar2(20) not null) tablespace yerg_16k;
Table created.
SQL>
SQL> alter table hashtest add constraint
2 hashtest_uq unique (stringtst, stringm, stringz)
3 using index (
4 create unique index hashtest_uq_idx on hashtest(stringtst, stringm, stringz)
5 tablespace yerg_16k);
alter table hashtest add constraint
*
ERROR at line 1:
ORA-01450: maximum key length (12958) exceeded
SQL>
SQL> drop table hashtest purge;
Table dropped.
SQL>
To avoid this error without changing the table structure the total VARCHAR2 length when using BYTE semantics must not exceed 6387 for an 8K block size and it must not exceed 12947 for a 16K block size. A way around this dilemma is to modify the table in question to create virtual columns based upon the longer VARCHAR2 columns then using those virtual columns in the unique index/constraint. An example of this technique follows:
SQL>
SQL> --
SQL> -- Create a table with VARCHAR2 columns in the extended
SQL> -- length range
SQL> --
SQL> create table longstrings (id number, text varchar2(32000), moretext varchar2(32000), smalltxt varchar2(20));
Table created.
SQL>
SQL> --
SQL> -- Create virtual columns using the long text columns
SQL> -- as the source
SQL> --
SQL> -- Use the standard_hash function to ensure the key length
SQL> -- will not be excessive
SQL> --
SQL> alter table longstrings add (text_hash as (standard_hash(text)), moretext_hash as (standard_hash(moretext)));
Table altered.
SQL>
SQL> --
SQL> -- Use these virtual columns in the unique key/index structure
SQL> --
SQL> alter table longstrings add constraint longstrings_text_unq unique (text_hash, moretext_hash, smalltxt);
Table altered.
SQL>
SQL> --
SQL> -- Try to populate the table with non-unique data
SQL> --
SQL> -- The constraint works as expected and the inserts
SQL> -- eventually fail
SQL> --
SQL> begin
2 for i in 1..10000 loop
3 insert into longstrings(id, text, moretext, smalltxt)
4 values (i, 'Borscht '||mod(i,9), 'BorschtBorscht '||mod(i,13), 'Hubba');
5 end loop;
6 commit;
7 end;
8 /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (BING.LONGSTRINGS_TEXT_UNQ) violated
ORA-06512: at line 3
SQL>
Describing the table after the virtual columns are created reveals:
SQL> desc longstrings
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
TEXT VARCHAR2(32000)
MORETEXT VARCHAR2(32000)
SMALLTXT VARCHAR2(20)
TEXT_HASH RAW(20)
MORETEXT_HASH RAW(20)
SQL>
Looking at how the virtual columns are defined reveals that no additional triggers or processing will be necessary to populate them when inserts are processed:
SQL> alter table longstrings add (text_hash as (standard_hash(text)), moretext_hash as (standard_hash(moretext)));
The standard_hash function will be applied to all values of text and moretext at the time the insert is processed, populating the index columns ensuring the constraint will function properly. As a consequence of this, inserts will need to specify all but the _hash columns in the insert list:
insert into longstrings(id, text, moretext, smalltxt) ...
Additionally, any forms, code or web pages that return data from tables modified in this manner will also need to list the columns to be returned, which would exclude any _hash named columns. This prevents forms from failing from the following error:
SQL> declare
2 v_id longstrings.id%type;
3 v_text longstrings.text%type;
4 v_mretxt longstrings.moretext%type;
5 v_smlltxt longstrings.smalltxt%type;
6
7 cursor getall is
8 select * From longstrings
9 where rownum <=12;
10 begin
11 open getall;
12 loop
13 fetch getall into v_id, v_text, v_mretxt, v_smlltxt;
14 exit when getall%notfound;
15
16 dbms_output.put_line(v_id||':'||v_text||':'||v_mretxt||':'||v_smlltxt);
17 end loop;
18 end;
19 /
fetch getall into v_id, v_text, v_mretxt, v_smlltxt;
*
ERROR at line 13:
ORA-06550: line 13, column 3:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 13, column 3:
PL/SQL: SQL Statement ignored
SQL>
Implied select lists like those generated by ‘select * from …’ queries can no longer be used in situations illustrated in the above example after such table modifications have been made since the returned list of values is greater than the number of variables declared to hold such values. Of course, a PL/SQL for loop and ‘select * …’ cursors can be used since it’s necessary to append the column name to the cursor variable to return the selected value:
SQL> declare
2 cursor getall is
3 select * from longstrings
4 where rownum <=10;
5 begin
6 for i in 1..10000 loop
7 insert into longstrings(id, text, moretext, smalltxt)
8 values (i, 'Borscht '||i, 'BorschtBorscht '||i, 'Hubba');
9 end loop;
10 commit;
11 for ls in getall loop
12 dbms_output.put_line(ls.id||' '||ls.text||' '||ls.moretext||' '||ls.smalltxt);
13 end loop;
14 end;
15 /
1 Borscht 1 BorschtBorscht 1 Hubba
2 Borscht 2 BorschtBorscht 2 Hubba
3 Borscht 3 BorschtBorscht 3 Hubba
4 Borscht 4 BorschtBorscht 4 Hubba
5 Borscht 5 BorschtBorscht 5 Hubba
6 Borscht 6 BorschtBorscht 6 Hubba
7 Borscht 7 BorschtBorscht 7 Hubba
8 Borscht 8 BorschtBorscht 8 Hubba
9 Borscht 9 BorschtBorscht 9 Hubba
10 Borscht 10 BorschtBorscht 10 Hubba
PL/SQL procedure successfully completed.
SQL>
Another way around this limitation, if constraints are not being created, is to apply the standard_hash() function to the column or columns directly in the index definition:
SQL> --
SQL> -- Create a table with VARCHAR2 columns in the extended
SQL> -- length range
SQL> --
SQL> create table longstrings (id number, text varchar2(32000), moretext varchar2(32000), smalltxt varchar2(20));
Table created.
SQL>
SQL> --
SQL> -- Create index using the extended string length columns
SQL> -- as the source
SQL> --
SQL> create index longstrings_idx on longstrings(standard_hash(text), standard_hash(moretext), smalltxt);
Index created.
SQL>
SQL> --
SQL> -- Populate the table
SQL> --
SQL> begin
2 for i in 1..10000 loop
3 insert into longstrings(id, text, moretext, smalltxt)
4 values (i, 'Borscht '||mod(i,9), 'BorschtBorscht '||mod(i,13), 'Hubba');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Select using the index
SQL> --
SQL> -- Works for equality queries only
SQL> --
SQL> set autotrace on linesize 132
SQL> column text format a45
SQL> column moretext format a45
SQL>
SQL> select *
2 from longstrings
3 where text = 'Borscht 7';
ID TEXT MORETEXT SMALLTXT
---------- --------------------------------------------- --------------------------------------------- --------------------
313 Borscht 7 BorschtBorscht 1 Hubba
79 Borscht 7 BorschtBorscht 1 Hubba
196 Borscht 7 BorschtBorscht 1 Hubba
781 Borscht 7 BorschtBorscht 1 Hubba
898 Borscht 7 BorschtBorscht 1 Hubba
430 Borscht 7 BorschtBorscht 1 Hubba
547 Borscht 7 BorschtBorscht 1 Hubba
664 Borscht 7 BorschtBorscht 1 Hubba
1249 Borscht 7 BorschtBorscht 1 Hubba
1366 Borscht 7 BorschtBorscht 1 Hubba
1015 Borscht 7 BorschtBorscht 1 Hubba
...
6874 Borscht 7 BorschtBorscht 10 Hubba
6991 Borscht 7 BorschtBorscht 10 Hubba
7693 Borscht 7 BorschtBorscht 10 Hubba
7810 Borscht 7 BorschtBorscht 10 Hubba
7342 Borscht 7 BorschtBorscht 10 Hubba
7459 Borscht 7 BorschtBorscht 10 Hubba
7576 Borscht 7 BorschtBorscht 10 Hubba
8278 Borscht 7 BorschtBorscht 10 Hubba
7927 Borscht 7 BorschtBorscht 10 Hubba
8044 Borscht 7 BorschtBorscht 10 Hubba
8161 Borscht 7 BorschtBorscht 10 Hubba
1111 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1813482573
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 829 | 25M| 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LONGSTRINGS | 829 | 25M| 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LONGSTRINGS_IDX | 30 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='Borscht 7' AND
INTERNAL_FUNCTION("LONGSTRINGS"."MORETEXT"))
2 - access(STANDARD_HASH("TEXT")=HEXTORAW('D55F75A7098D2100B35FEF4A344FDC64A4FFF29E'))
SQL>
Range queries unfortunately use a table scan:
SQL> --
SQL> -- Try using a range
SQL> --
SQL> -- Generates tablescan
SQL> --
SQL> select *
2 from longstrings
3 where text between 'Borscht 6' and 'Borscht 8';
ID TEXT MORETEXT SMALLTXT
---------- --------------------------------------------- --------------------------------------------- --------------------
214 Borscht 7 BorschtBorscht 6 Hubba
215 Borscht 8 BorschtBorscht 7 Hubba
222 Borscht 6 BorschtBorscht 1 Hubba
223 Borscht 7 BorschtBorscht 2 Hubba
224 Borscht 8 BorschtBorscht 3 Hubba
231 Borscht 6 BorschtBorscht 10 Hubba
232 Borscht 7 BorschtBorscht 11 Hubba
233 Borscht 8 BorschtBorscht 12 Hubba
240 Borscht 6 BorschtBorscht 6 Hubba
241 Borscht 7 BorschtBorscht 7 Hubba
242 Borscht 8 BorschtBorscht 8 Hubba
...
9484 Borscht 7 BorschtBorscht 7 Hubba
9485 Borscht 8 BorschtBorscht 8 Hubba
9492 Borscht 6 BorschtBorscht 2 Hubba
9493 Borscht 7 BorschtBorscht 3 Hubba
9494 Borscht 8 BorschtBorscht 4 Hubba
9501 Borscht 6 BorschtBorscht 11 Hubba
9502 Borscht 7 BorschtBorscht 12 Hubba
9503 Borscht 8 BorschtBorscht 0 Hubba
9510 Borscht 6 BorschtBorscht 7 Hubba
9511 Borscht 7 BorschtBorscht 8 Hubba
9512 Borscht 8 BorschtBorscht 9 Hubba
3333 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 411405923
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2471 | 75M| 163 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LONGSTRINGS | 2471 | 75M| 163 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT">='Borscht 6' AND
"TEXT"<='Borscht 8' AND INTERNAL_FUNCTION("LONGSTRINGS"."MORETEXT"))
SQL>
The method chosen to address such issues depends on the length of the VARCHAR2 columns involved; VARCHAR2 columns declared, that are longer than 4000 characters (the standard default from Oracle), will need to have virtual columns created using the standard_hash() function to ensure the key length won’t exceed 80% of the usable space based on the block size; for 8K blocks there will be 7998 bytes available , and for 16K blocks there will be 16198 bytes available (the remaining space is the overhead for block management). How the nls_length_semantics parameter is configured for your database will make a difference when deciding how to proceed with index and constraint creation should the extended string length option be chosen.
