Indexing In Oracle 12c When Extended String Length is Configured

Monday Jun 12th 2017 by David Fitzjarrell
Share:

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.

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.

See all articles by David Fitzjarrell

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved