[20220124]index split level.txt

[20220124]index split level.txt

--//重複測試:

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立2K的表空間:

SYS@book> alter system set db_2k_cache_size=16m scope=spfile;
System altered.

--//重啟資料庫.

CREATE TABLESPACE tea2k DATAFILE
  '/mnt/ramdisk/book/tea2k01.dbf' SIZE 100M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 2K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON
/

3.測試指令碼:
rem Script:  silly_index_3a.sql
rem Author:  Jonathan Lewis
rem Dated:   Mar 2004 / Jan 2022
rem Purpose: Build an index with a large blevel
rem Notes:
rem Uses 2K block size for tablespace holding the index
rem Estimated run-time (for me) with m_blevel = 23 - ca. 1 hour

define m_blevel = 5
define m_rows = power(2,&m_blevel)

drop table t1 purge;
create table t1 (v1 varchar2(8));

create index t1_i1 on t1(substrb(lpad(v1,1469,'0'),1,1469))
tablespace tea2k
;

-- execute snap_my_stats.start_snap
prompt  ===================================================
prompt  Inserting &m_rows (short) rows in reverse order
prompt  ===================================================
begin
   for i in reverse 1..&m_rows loop
           insert into t1 values (i);
--         commit;
   end loop;
end;
/

-- execute snap_my_stats.end_snap

prompt  ================
prompt  Validating index
prompt  ================

validate index t1_i1;

select
        lf_rows, height, height-1 blevel, lf_blks, br_blks
from
        index_stats
;

column  object_id new_value m_object_id

select  object_id
from    user_objects
where   object_name = 'T1_I1'
/

alter session set events 'immediate trace name treedump level &m_object_id';

insert into t1(v1) values('0');


SCOTT@book> @ silly_index_3a.sql

Table dropped.


Table created.


Index created.

===================================================
Inserting power(2,5) (short) rows in reverse order
===================================================

PL/SQL procedure successfully completed.

================
Validating index
================

Index analyzed.

   LF_ROWS     HEIGHT     BLEVEL    LF_BLKS    BR_BLKS
---------- ---------- ---------- ---------- ----------
        32          6          5         32         31

 OBJECT_ID
----------
    331001
Session altered.
1 row created.

SCOTT@book> @ ttt
tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_27138.trc

----- begin tree dump
branch: 0x1c00204 29360644 (0: nrow: 2, level: 5)
   branch: 0x1c0023b 29360699 (-1: nrow: 2, level: 4)
      branch: 0x1c00205 29360645 (-1: nrow: 2, level: 3)
         branch: 0x1c0021d 29360669 (-1: nrow: 2, level: 2)
            branch: 0x1c00218 29360664 (-1: nrow: 2, level: 1)
               leaf: 0x1c00215 29360661 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00259 29360729 (0: nrow: 1 rrow: 1)
            branch: 0x1c00258 29360728 (0: nrow: 2, level: 1)
               leaf: 0x1c00257 29360727 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00256 29360726 (0: nrow: 1 rrow: 1)
         branch: 0x1c00255 29360725 (0: nrow: 2, level: 2)
            branch: 0x1c00228 29360680 (-1: nrow: 2, level: 1)
               leaf: 0x1c00227 29360679 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00226 29360678 (0: nrow: 1 rrow: 1)
            branch: 0x1c00225 29360677 (0: nrow: 2, level: 1)
               leaf: 0x1c00224 29360676 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00223 29360675 (0: nrow: 1 rrow: 1)
      branch: 0x1c00222 29360674 (0: nrow: 2, level: 3)
         branch: 0x1c0022f 29360687 (-1: nrow: 2, level: 2)
            branch: 0x1c0022e 29360686 (-1: nrow: 2, level: 1)
               leaf: 0x1c0022d 29360685 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c0022c 29360684 (0: nrow: 1 rrow: 1)
            branch: 0x1c0022b 29360683 (0: nrow: 2, level: 1)
               leaf: 0x1c0022a 29360682 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00234 29360692 (0: nrow: 1 rrow: 1)
         branch: 0x1c00233 29360691 (0: nrow: 2, level: 2)
            branch: 0x1c00232 29360690 (-1: nrow: 2, level: 1)
               leaf: 0x1c00231 29360689 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00230 29360688 (0: nrow: 1 rrow: 1)
            branch: 0x1c0023f 29360703 (0: nrow: 2, level: 1)
               leaf: 0x1c0023e 29360702 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c0023d 29360701 (0: nrow: 1 rrow: 1)
   branch: 0x1c0023c 29360700 (0: nrow: 2, level: 4)
      branch: 0x1c0023a 29360698 (-1: nrow: 2, level: 3)
         branch: 0x1c00239 29360697 (-1: nrow: 2, level: 2)
            branch: 0x1c00238 29360696 (-1: nrow: 2, level: 1)
               leaf: 0x1c00237 29360695 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00236 29360694 (0: nrow: 1 rrow: 1)
            branch: 0x1c00229 29360681 (0: nrow: 2, level: 1)
               leaf: 0x1c00235 29360693 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c0020e 29360654 (0: nrow: 1 rrow: 1)
         branch: 0x1c0020d 29360653 (0: nrow: 2, level: 2)
            branch: 0x1c0020c 29360652 (-1: nrow: 2, level: 1)
               leaf: 0x1c0020b 29360651 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c0020a 29360650 (0: nrow: 1 rrow: 1)
            branch: 0x1c00209 29360649 (0: nrow: 2, level: 1)
               leaf: 0x1c00208 29360648 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00207 29360647 (0: nrow: 1 rrow: 1)
      branch: 0x1c00206 29360646 (0: nrow: 2, level: 3)
         branch: 0x1c0020f 29360655 (-1: nrow: 2, level: 2)
            branch: 0x1c00214 29360660 (-1: nrow: 2, level: 1)
               leaf: 0x1c00213 29360659 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00212 29360658 (0: nrow: 1 rrow: 1)
            branch: 0x1c00211 29360657 (0: nrow: 2, level: 1)
               leaf: 0x1c00210 29360656 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c0021f 29360671 (0: nrow: 1 rrow: 1)
         branch: 0x1c0021e 29360670 (0: nrow: 2, level: 2)
            branch: 0x1c0021c 29360668 (-1: nrow: 2, level: 1)
               leaf: 0x1c0021b 29360667 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c0021a 29360666 (0: nrow: 1 rrow: 1)
            branch: 0x1c00219 29360665 (0: nrow: 2, level: 1)
               leaf: 0x1c00217 29360663 (-1: nrow: 1 rrow: 1)
               leaf: 0x1c00216 29360662 (0: nrow: 1 rrow: 1)
----- end tree dump

--//作者利用2K資料塊,儲存鍵值1469長度字串,這樣每塊僅僅1個鍵值,快速提高level水平.
--//2^23 = 8388608條,8388608*2/1024/1024 = 16G ,要達到23level測試時間有點長.

CREATE TABLESPACE TEA2Kx DATAFILE
  '/u01/tea2kx01.dbf' SIZE 100M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 2K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--//修改指令碼m_blevel =23,表空間指向tea2kx.重複測試.明天看看.

SCOTT@book> @ silly_index_3a.sql
Table dropped.
Table created.
Index created.
===================================================
Inserting power(2,23) (short) rows in reverse order
===================================================
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
ORA-06512: at line 3
================
Validating index
================

Index analyzed.

   LF_ROWS     HEIGHT     BLEVEL    LF_BLKS    BR_BLKS
---------- ---------- ---------- ---------- ----------
         0         20         19     317024     317030

 OBJECT_ID
----------
    331003

Session altered.
1 row created.

SCOTT@book> commit ;
Commit complete.

--//放棄,磁碟空間不足.