• heap表按字符串和数值型排序规则


    SQL> create user scan identified by scan default tablespace users;
    
    User created.
    
    SQL> grant dba to scan;
    
    Grant succeeded.
    
    
    
    create table t1  (id char(10) primary key,a1 char(10),a2 char(10));  
    
    
    begin 
    for i in 1 .. 25
    loop 
    insert into t1 values(i,i,'a'||i); 
    end loop 
    ; 
    commit; 
    end; 
    
    
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCAN',
                                    tabname          => 'T1',
                                    estimate_percent => 100,
                                    method_opt       => 'for all columns size repeat',
                                    no_invalidate    => FALSE,
                                    degree           => 8,
                                    cascade          => TRUE);
    END;
    / 
    
    
    
    
    create table t2 (id int primary key,a1 char(10),a2 char(10))organization index; 
    
    
    begin 
    for i in 1 .. 25 
    loop 
    insert into t2 values(i,i,'a'||i); 
    end loop 
    ; 
    commit; 
    end;
    
    
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCAN',
                                    tabname          => 'T2',
                                    estimate_percent => 100,
                                    method_opt       => 'for all columns size repeat',
                                    no_invalidate    => FALSE,
                                    degree           => 8,
                                    cascade          => TRUE);
    END;
    / 
    
    
    
    先分别dump下t1和t2表:  t1 heap  t2 iot
    
    SQL> select  TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME, IOT_NAME from user_tables;
    
    TABLE_NAME		       TABLESPACE_NAME		      CLUSTER_NAME		     IOT_NAME
    ------------------------------ ------------------------------ ------------------------------ ------------------------------
    T1			       USERS
    T2
    
    
    
    SQL>  select object_name,object_id from user_objects;
    
    OBJECT_NAME			OBJECT_ID
    ------------------------------ ----------
    SYS_C00147516			   260450
    T2				   260451
    T1				   260449
    SYS_IOT_TOP_260451		   260452
    
    
    
    
    SQL>  select index_name,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,table_type from user_indexes;
    
    INDEX_NAME		       INDEX_TYPE		   TABLE_OWNER			  TABLE_NAME			 TABLE_TYPE
    ------------------------------ --------------------------- ------------------------------ ------------------------------ -----------
    SYS_C00147516		       NORMAL			   SCAN 			  T1				 TABLE
    SYS_IOT_TOP_260451	       IOT - TOP		   SCAN 			  T2				 TABLE
    
    
    
    2.将索引dump到trace文件中
    SQL> select value from v$diag_info where name='Default Trace File';
    
    VALUE
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    /u01/app/oracle/diag/rdbms/ncdb/podinndb/trace/podinndb_ora_28524.trc
    
    SQL> alter session set events 'immediate trace name treedump level 260450';
    
    会话已更改。
    
    
    ----- begin tree dump
    leaf: 0x100008b 16777355 (0: nrow: 25 rrow: 25)
    ----- end tree dump
    
    
    SQL> select index_name, PREFIX_LENGTH, BLEVEL, LEAF_BLOCKS from user_indexes where index_name='SYS_C00147516';
    
    INDEX_NAME		       PREFIX_LENGTH	 BLEVEL LEAF_BLOCKS
    ------------------------------ ------------- ---------- -----------
    SYS_C00147516					      0 	  1
    
    
    
    
    SQL> select dbms_utility.data_block_address_file('16777355') FILE_ID,
           dbms_utility.data_block_address_block('16777355') BLOCK_ID
      from dual;   2    3  
    
       FILE_ID   BLOCK_ID
    ---------- ----------
    	 4	  139
    
    
    
    SQL>  select header_file,header_block from dba_segments where segment_name='SYS_C00147516';
    
    HEADER_FILE HEADER_BLOCK
    ----------- ------------
    	  4	     138
    
    
    
    select dbms_utility.data_block_address_file(16777355)fno,
    dbms_utility.data_block_address_block(16777355) bkno from dualSQL>   2  
      3  ;
    
           FNO	 BKNO
    ---------- ----------
    	 4	  139;  
    
    SQL> alter system dump datafile 4 block 139;
    
    
    
    
    row#0[8013] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 00
    col 0; len 10; (10):  31 20 20 20 20 20 20 20 20 20      ---表示1
    row#1[7842] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 09
    col 0; len 10; (10):  31 30 20 20 20 20 20 20 20 20      ---表示10
    row#2[7823] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 0a
    col 0; len 10; (10):  31 31 20 20 20 20 20 20 20 20      --表示11
    row#3[7804] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 0b
    col 0; len 10; (10):  31 32 20 20 20 20 20 20 20 20      --表示12
    row#4[7785] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 0c
    col 0; len 10; (10):  31 33 20 20 20 20 20 20 20 20       --表示13
    row#5[7766] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 0d
    col 0; len 10; (10):  31 34 20 20 20 20 20 20 20 20       --表示14
    row#6[7747] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 0e
    col 0; len 10; (10):  31 35 20 20 20 20 20 20 20 20       --表示15
    row#7[7728] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 0f
    col 0; len 10; (10):  31 36 20 20 20 20 20 20 20 20       --表示16
    row#8[7709] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 10
    col 0; len 10; (10):  31 37 20 20 20 20 20 20 20 20       --表示17
    row#9[7690] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 11
    col 0; len 10; (10):  31 38 20 20 20 20 20 20 20 20       --表示18
    row#10[7671] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 12
    col 0; len 10; (10):  31 39 20 20 20 20 20 20 20 20        --表示19
    row#11[7994] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 01
    col 0; len 10; (10):  32 20 20 20 20 20 20 20 20 20         --表示2
    row#12[7652] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 13
    col 0; len 10; (10):  32 30 20 20 20 20 20 20 20 20          --表示20
    row#13[7633] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 14
    col 0; len 10; (10):  32 31 20 20 20 20 20 20 20 20           --表示21
    row#14[7614] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 15
    col 0; len 10; (10):  32 32 20 20 20 20 20 20 20 20          --表示22
    row#15[7595] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 16
    col 0; len 10; (10):  32 33 20 20 20 20 20 20 20 20         --表示23
    row#16[7576] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 17
    col 0; len 10; (10):  32 34 20 20 20 20 20 20 20 20         --表示24
    row#17[7557] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 18
    col 0; len 10; (10):  32 35 20 20 20 20 20 20 20 20         --表示25
    row#18[7975] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 02
    col 0; len 10; (10):  33 20 20 20 20 20 20 20 20 20         --表示3
    row#19[7956] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 03
    col 0; len 10; (10):  34 20 20 20 20 20 20 20 20 20           --表示4
    row#20[7937] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 04
    col 0; len 10; (10):  35 20 20 20 20 20 20 20 20 20            --表示5
    row#21[7918] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 05
    col 0; len 10; (10):  36 20 20 20 20 20 20 20 20 20           --表示6
    row#22[7899] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 06
    col 0; len 10; (10):  37 20 20 20 20 20 20 20 20 20           --表示7
    row#23[7880] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 07
    col 0; len 10; (10):  38 20 20 20 20 20 20 20 20 20           --表示8
    row#24[7861] flag: ------, lock: 2, len=19, data:(6):  01 00 00 85 00 08
    col 0; len 10; (10):  39 20 20 20 20 20 20 20 20 20           --表示9
    ----- end of leaf block dump -----
    End dump data blocks tsn: 4 file#: 4 minblk 139 maxblk 139
    
    
    DECLARE
       n   VARCHAR2 (2000);
    BEGIN
       DBMS_STATS.convert_raw_value ('35', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
       DBMS_STATS.convert_raw_value ('20', n);
       DBMS_OUTPUT.put_line (n);
    END;
    
    
    结论 heap表主键只有对应的列
    
    heap 表排序:
    SQL> select * from t1 order by t1.id;
    
    ID	   A1	      A2
    ---------- ---------- ----------
    1	   1	      a1
    10	   10	      a10
    11	   11	      a11
    12	   12	      a12
    13	   13	      a13
    14	   14	      a14
    15	   15	      a15
    16	   16	      a16
    17	   17	      a17
    18	   18	      a18
    19	   19	      a19
    
    ID	   A1	      A2
    ---------- ---------- ----------
    2	   2	      a2
    20	   20	      a20
    21	   21	      a21
    22	   22	      a22
    23	   23	      a23
    24	   24	      a24
    25	   25	      a25
    3	   3	      a3
    4	   4	      a4
    5	   5	      a5
    6	   6	      a6
    
    ID	   A1	      A2
    ---------- ---------- ----------
    7	   7	      a7
    8	   8	      a8
    9	   9	      a9
    
    25 rows selected.
    
    
    t1表 id列为字符串,排序是按字符串排序
    
    
    
    
    /***创建t3表改为数值型/
    
    create table t3  (id int primary key,a1 char(10),a2 char(10));  
    
    
    begin 
    for i in 1 .. 25
    loop 
    insert into t3 values(i,i,'a'||i); 
    end loop 
    ; 
    commit; 
    end; 
    
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCAN',
                                    tabname          => 'T3',
                                    estimate_percent => 100,
                                    method_opt       => 'for all columns size repeat',
                                    no_invalidate    => FALSE,
                                    degree           => 8,
                                    cascade          => TRUE);
    END;
    / 
    
    
    
    SQL>  select  TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME, IOT_NAME from user_tables;
    
    TABLE_NAME	     TABLESPACE_NAME		    CLUSTER_NAME		   IOT_NAME
    -------------------- ------------------------------ ------------------------------ ------------------------------
    T1		     USERS
    T3		     USERS
    T2
    
    
    SQL> select object_name,object_id from user_objects;
    
    OBJECT_NAME			OBJECT_ID
    ------------------------------ ----------
    SYS_C00147518			   260454
    T3				   260453
    SYS_IOT_TOP_260451		   260452
    T1				   260449
    T2				   260451
    SYS_C00147516			   260450
    
    6 rows selected.
    
    
    
    SQL>  select index_name,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,table_type from user_indexes;
    
    INDEX_NAME		       INDEX_TYPE		   TABLE_OWNER			  TABLE_NAME	       TABLE_TYPE
    ------------------------------ --------------------------- ------------------------------ -------------------- -----------
    SYS_C00147516		       NORMAL			   SCAN 			  T1		       TABLE
    SYS_IOT_TOP_260451	       IOT - TOP		   SCAN 			  T2		       TABLE
    SYS_C00147518		       NORMAL			   SCAN 			  T3		       TABLE
    
    
    
    2.将索引dump到trace文件中
    SQL> select value from v$diag_info where name='Default Trace File';
    
    VALUE
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    /u01/app/oracle/diag/rdbms/ncdb/podinndb/trace/podinndb_ora_29363.trc
    
    SQL> alter session set events 'immediate trace name treedump level 260454';
    
    会话已更改。
    
    
    ----- begin tree dump
    leaf: 0x10000a3 16777379 (0: nrow: 25 rrow: 25)
    ----- end tree dump
    
    SQL> select index_name, PREFIX_LENGTH, BLEVEL, LEAF_BLOCKS from user_indexes where index_name='SYS_C00147518';
    
    INDEX_NAME		       PREFIX_LENGTH	 BLEVEL LEAF_BLOCKS
    ------------------------------ ------------- ---------- -----------
    SYS_C00147518					      0 	  1
    
    
    
    
    SQL> select dbms_utility.data_block_address_file('16777379') FILE_ID,
           dbms_utility.data_block_address_block('16777379') BLOCK_ID
      from dual;   2    3  
    
       FILE_ID   BLOCK_ID
    ---------- ----------
    	 4	  163
    
    
    SQL>  select header_file,header_block from dba_segments where segment_name='SYS_C00147518';
    
    HEADER_FILE HEADER_BLOCK
    ----------- ------------
    	  4	     162
    
    SQL> select dbms_utility.data_block_address_file(16777379)fno,
    dbms_utility.data_block_address_block(16777379) bkno from dual  2  ;
    
           FNO	 BKNO
    ---------- ----------
    	 4	  163
    
    SQL> alter system dump datafile 4 block 163;
    
    
    
    row#0[8021] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 00
    col 0; len 2; (2):  c1 02   ---表示1
    row#1[8010] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 01
    col 0; len 2; (2):  c1 03   --表示2
    row#2[7999] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 02
    col 0; len 2; (2):  c1 04   --表示3
    row#3[7988] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 03
    col 0; len 2; (2):  c1 05   --表示4
    row#4[7977] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 04
    col 0; len 2; (2):  c1 06   --表示5
    row#5[7966] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 05
    col 0; len 2; (2):  c1 07
    row#6[7955] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 06
    col 0; len 2; (2):  c1 08
    row#7[7944] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 07
    col 0; len 2; (2):  c1 09
    row#8[7933] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 08
    col 0; len 2; (2):  c1 0a
    row#9[7922] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 09
    col 0; len 2; (2):  c1 0b
    row#10[7911] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 0a
    col 0; len 2; (2):  c1 0c
    row#11[7900] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 0b
    col 0; len 2; (2):  c1 0d
    row#12[7889] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 0c
    col 0; len 2; (2):  c1 0e
    row#13[7878] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 0d
    col 0; len 2; (2):  c1 0f
    row#14[7867] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 0e
    col 0; len 2; (2):  c1 10
    row#15[7856] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 0f
    col 0; len 2; (2):  c1 11
    row#16[7845] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 10
    col 0; len 2; (2):  c1 12
    row#17[7834] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 11
    col 0; len 2; (2):  c1 13
    row#18[7823] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 12
    col 0; len 2; (2):  c1 14
    row#19[7812] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 13
    col 0; len 2; (2):  c1 15
    row#20[7801] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 14
    col 0; len 2; (2):  c1 16
    row#21[7790] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 15
    col 0; len 2; (2):  c1 17
    row#22[7779] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 16
    col 0; len 2; (2):  c1 18  --表示23
    row#23[7768] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 17
    col 0; len 2; (2):  c1 19    --表示24
    row#24[7757] flag: ------, lock: 2, len=11, data:(6):  01 00 00 9d 00 18
    col 0; len 2; (2):  c1 1a    --表示25
    ----- end of leaf block dump -----
    End dump data blocks tsn: 4 file#: 4 minblk 163 maxblk 163
    
    
    
    declare    
       n number;    
     begin    
       dbms_stats.convert_raw_value('c102',    
                                    n);    
       dbms_output.put_line(n);    
     end; 
    
    SQL> select * from t3 order by t3.id;
    
    	ID A1	      A2
    ---------- ---------- ----------
    	 1 1	      a1
    	 2 2	      a2
    	 3 3	      a3
    	 4 4	      a4
    	 5 5	      a5
    	 6 6	      a6
    	 7 7	      a7
    	 8 8	      a8
    	 9 9	      a9
    	10 10	      a10
    	11 11	      a11
    
    	ID A1	      A2
    ---------- ---------- ----------
    	12 12	      a12
    	13 13	      a13
    	14 14	      a14
    	15 15	      a15
    	16 16	      a16
    	17 17	      a17
    	18 18	      a18
    	19 19	      a19
    	20 20	      a20
    	21 21	      a21
    	22 22	      a22
    
    	ID A1	      A2
    ---------- ---------- ----------
    	23 23	      a23
    	24 24	      a24
    	25 25	      a25
    
    25 rows selected.
    
    
    
    结论 字符串和数值型 排序方式不同

  • 相关阅读:
    深入了解接口
    深入.NET平台C#编程 测试题分析
    如何设计高级的数据库
    数据库查询的基础技巧
    回顾Spring MVC_01_概述_入门案例
    初学My Batis之入门
    Spring MVC之视图解析器和URL-Pattern的配置方案
    SpringMVC之入门
    WebService入门
    Spring之实现任务调度
  • 原文地址:https://www.cnblogs.com/zhaoyangjian724/p/6199032.html
Copyright © 2020-2023  润新知