-- it's just for fun:
SQL> alter system set "_scalar_type_lob_storage_threshold"=32000;
System altered.
SQL> create table t_varchar32000(v varchar2(32000 byte));
Table created.
SQL> insert into t_varchar32000
2 select rpad(rownum,31999) || `x' str from dual connect by level<=1000;
1000 rows created.
SQL> commit;
Commit complete.
SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace users;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace users
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace ts_32k
*
ERROR at line 1:
ORA-01450: maximum key length (26510) exceeded
-- tablespace for big varchars:
SQL> alter system set DB_32K_CACHE_SIZE=100M;
System altered.
SQL> CREATE TABLESPACE TS_32K DATAFILE '/u01/app/oracle/oradata/xtsql/pdb1/ts_32k_1.dbf' SIZE 150M
2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
3 BLOCKSIZE 32K;
Tablespace created.
SQL> create table t_varchar16000(v varchar2(16000 byte)) tablespace ts_32k;
Table created.
SQL> insert into t_varchar16000
2 select rpad(rownum,15999,'x' ) || 'y' from dual connect by level<=1000;
1000 rows created.
SQL> create index ix_t_varchar16000 on t_varchar16000(v) tablespace ts_32k;
Index created.
[sourcecode language=”sql”]
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => user
4 ,tabname => ‘T_VARCHAR16000’
5 ,method_opt => ‘for all columns size auto’
6 ,cascade => true
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> @stats/tab t_varchar16000
OWNER TABLE_NAME PARTITION_NAME # ST_LOCK STALE_STA GLOBAL_ST USER_STAT NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN AVG_SPACE LAST_ANALYZED
————— —————————— ——————– —- ——- ——— ——— ——— ———- ———- ———— ———– ———- —————
XTENDER T_VARCHAR16000 NO YES NO 1000 3016 0 16001 0 14-NOV-13
OWNER INDEX_NAME NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CL_FACTOR LAST_ANALYZED GLOBAL_ST USER_STAT
————— —————————— ———- ————- ———- ———– ———- ————— ——— ———
XTENDER IX_T_VARCHAR16000 1000 1000 1 1000 1000 14-NOV-13 YES NO
———————————————————————————————————————————————————————————————
| column_name | num_distinct| low_value | high_value | num_nulls | num_bucket| last_analyzed | sample_size| globa| user_| avg_c| histogram |
———————————————————————————————————————————————————————————————
| V | 1000 | 1000xxxxxxxxxxxxxx| 9xxxxxxxxxxxxxxxxx| 0 | 1 | 2013-11-14 21:11 | 1000 | YES | NO | 16001| NONE |
———————————————————————————————————————————————————————————————
[/sourcecode]