-- 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.
Statistics
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 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[collapse]