Oracle SQL
  • LICENSE

Monthly Archives: November 2013

Little example of index creation on extended varchars

Posted on November 15, 2013 by Sayan Malakshinov Posted in 12c, oracle, undocumented Leave a comment
-- 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]
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL