前几天在一个YashahDB的测试场景中看到，yashanDB的一个分页查询SQL的相应时间比oracle要快很多，对比执行计划发现是列上没有Not null约束时，YashanDB可以走INDEX FULL SCAN,虽然YashanDB是heapTable， 那看来Null值也在index 中存储(pg同样)，这点和oracle不同，下面实际查看一下Yashandb 的block是否有null记录?
测试样例
create table test1(id int,name varchar2(10));
insert into test1 values(1,'anbob');
insert into test1 values(2,null);
insert into test1 values(3,null);
insert into test1 values(4,'weejar');
insert into test1 values(5,'tom');
commit;
SQL> create index idx_test1_name on test1(name);
Succeed.
SQL> select name from test1;
NAME
-------------
anbob
tom
weejar
5 rows fetched.
SQL> explain select name from test1;
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 1656217774
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | INDEX FAST FULL SCAN | IDX_TEST1_NAME | SYS | 100000| 28( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
9 rows fetched.
Note: 这里使用了index fuast full scan，虽然列上有2行null值。
[yashan@yashan-74 trace]$ yasql / as sysdba
YashanDB SQL Enterprise Edition Release 23.5.1.100 x86_64
Connected to:
YashanDB Server Enterprise Edition Release 23.5.1.100 x86_64 - Linux
SQL> select object_id from dba_objects where object_name='IDX_T1_OID';
OBJECT_ID
---------------------
4373
1 row fetched.
SQL> alter session set events 'immediate trace name treedump level 4373';
[1:26]YAS-04115 "=" expected but missing
SQL> select SEGMENT_NAME, EXTENT_ID ,FILE_ID, BLOCK_ID ,BYTES ,BLOCKS
from dba_extents where segment_name='IDX_TEST1_NAME'; 2
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------------------------------------------------------------- ------------ ------------ ------------ --------------------- ------------
IDX_TEST1_NAME 0 0 76512 65536 8
1 row fetched.
SQL> select HEADER_FILE, HEADER_BLOCK , BYTES , BLOCKS , EXTENTS from dba_segments where segment_name='IDX_TEST1_NAME';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
--------------------- ------------ ----------- --------------------- ---------------------
0 76515 65536 8 1
1 row fetched.
SQL> alter system flush buffer_cache;
Succeed.
SQL> alter system dump datafile 0 MINBLOCK 76512 MAXBLOCK 76520;
Succeed.
Note: yashandb暂不支持treedump（我没找到对应的），先dump block方式。
默认当前版本的trace文件在 $YASDB_DATA/diag/trace 路径下，文件名以{dbname}_{yyyymmdd}_{sid}.trc 格式，首次执行dump操作后将生成与会话ID对应的trace文件，会话ID允许重复使用，当相同ID的新会话第一次dump且该ID对应trace文件已存在时，不会创建新文件，只会继续追加写入。
[yashan@yashan-74 ~]$ cd $YASDB_DATA/diag/trace
[yashan@yashan-74 trace]$ ls
yashandb_20251203_72.trc yashandb_20251221_72.trc yashandb_20251221_72_YashanDB_Trace_10046.trc
[yashan@yashan-74 trace]$ vi yashandb_20251221_72.trc
*** 2025-12-21 20:17:04.717
Start dump data blocks TS#: 0 FILE#: 0 minblk 76512 maxblk 76520
Block dump from disk:
DISK BLOCK DUMP
assm
head: id 0-76512, type 32, instance id 0 lsn 772017701030756353, checksum: 1956265271, change num 3, isEncrypted 0, encrypt algo UNKNOWN, isCompressed 0
dataOid: 4375, extent change number: 0 parent block: 0-76513, slot: 0, freeness: 6, capacity: 1024, count: 8, level: 0
freeness[1]: count: 0:
freeness[2]: count: 0:
freeness[3]: count: 0:
freeness[4]: count: 0:
freeness[5]: count: 0:
freeness[6]: count: 0:
unformat: 3, lastActiveTime: 2025-12-21 11:48:40.263995, instanceId: 0 firstBlock: 4, firstFreeBlock: 0, nranges: 1,
range index: 0, range length: 8, start bid: 0-76512
0. block: 0-76512 freeness: 0, xo: -1 1. block: 0-76513 freeness: 0, xo: -1 2. block: 0-76514 freeness: 0, xo: -1 3. block: 0-76515 freeness: 0, xo: -1
4. block: 0-76516 freeness: 0, xo: -1 5. block: 0-76517 freeness: 255, xo: -1 6. block: 0-76518 freeness: 255, xo: -1 7. block: 0-76519 freeness: 255, xo: -1
xid count: 0, xid offset count: 0:
xid list:
0. NULL 1. NULL 2. NULL 3. NULL
4. NULL 5. NULL 6. NULL 7. NULL
8. NULL 9. NULL 10. NULL 11. NULL
12. NULL 13. NULL 14. NULL 15. NULL
16. NULL 17. NULL 18. NULL 19. NULL
20. NULL 21. NULL 22. NULL 23. NULL
24. NULL 25. NULL 26. NULL 27. NULL
28. NULL 29. NULL 30. NULL 31. NULL
32. NULL 33. NULL 34. NULL 35. NULL
36. NULL 37. NULL 38. NULL 39. NULL
40. NULL 41. NULL 42. NULL 43. NULL
44. NULL 45. NULL 46. NULL 47. NULL
48. NULL 49. NULL 50. NULL 51. NULL
52. NULL 53. NULL 54. NULL 55. NULL
56. NULL 57. NULL 58. NULL 59. NULL
60. NULL 61. NULL 62. NULL 63. NULL
tail: change num: 3
DISK BLOCK DUMP END
*** 2025-12-21 20:17:04.718
Block dump from disk:
DISK BLOCK DUMP
assm
head: id 0-76513, type 32, instance id 0 lsn 772017701030756353, checksum: 1424019185, change num 1, isEncrypted 0, encrypt algo UNKNOWN, isCompressed 0
dataOid: 4375, extent change number: 0 parent block: 0-76514, slot: 0, freeness: 6, capacity: 1010, count: 1, level: 1
freeness[1]: count: 1:
freeness[2]: count: 1:
freeness[3]: count: 1:
freeness[4]: count: 1:
freeness[5]: count: 1:
freeness[6]: count: 1:
0. block: 0-76512 freeness: 6, instance id: 0
tail: change num: 1
DISK BLOCK DUMP END
*** 2025-12-21 20:17:04.718
Block dump from disk:
DISK BLOCK DUMP
assm
head: id 0-76514, type 32, instance id 0 lsn 772017701030756353, checksum: 3688343762, change num 1, isEncrypted 0, encrypt algo UNKNOWN, isCompressed 0
dataOid: 4375, extent change number: 0 parent block: 63-67108863, slot: 65535, freeness: 6, capacity: 1024, count: 1, level: 2
freeness[1]: count: 1:
freeness[2]: count: 1:
freeness[3]: count: 1:
freeness[4]: count: 1:
freeness[5]: count: 1:
freeness[6]: count: 1:
0. block: 0-76513 freeness: 6
tail: change num: 1
DISK BLOCK DUMP END
*** 2025-12-21 20:17:04.718
Block dump from disk:
DISK BLOCK DUMP
segment
head: id 0-76515, type 33, instance id 0 lsn 772017988485144577, checksum: 303480758, change num 4, isEncrypted 0, encrypt algo UNKNOWN, isCompressed 0
segType: btree
dataOid: 4375
root: 0-76516, cmpRowId: 1, cols: 1, dataTypes: 26,
extents lhwm: block id: 0-76517, extent idx: 0, map block: 0-76515, offset: 0, blkIdx: 5, used block count: 5. lhwmL1: 0-76512
extent ctrl: extent count: 1, block count: 8, map count: 0 , extent map offset: 436
last map block: 63-67108863, offset: 0, extents change number: 0
extents hwm: block id: 0-76520, extent idx: 0, offset: 0, unformatted block count: 0, used block count: 8, map block: 63-67108863
extents map: extent count: 1, capacity: 484, next map: 63-67108863, map nodes:
id: 0-76512 size: 8
0. L1 block id: 0-76512 data block id: 0-76516
freeBlocks: head: 0-0, tail: 0-0, count: 0
tail: change num: 4
DISK BLOCK DUMP END
*** 2025-12-21 20:17:04.718
Block dump from disk:
DISK BLOCK DUMP
btree data
head: id 0-76516, type 11, instance id 0 lsn 772017701030756353, checksum: 3476040260, change num 3, isEncrypted 0, encrypt algo UNKNOWN, isCompressed 0
left: 63-67108863, right: 63-67108863, keys: 5, deletedKeys: 0, colNum: 1, freeSize: 7975, freeBegin: 155, freeEnd: 8130, xslots: 2, isIot: 0, isSplit: 0, blockScn: 0,dataOid: 4375, entry: 0-76515, level: 0, keyData:
key 0: size: 14, isDel: 0, isIot: 0, isClean: 0, xslotId: 255, heapBlk: 0-76508, heapDir: 0
key 1: size: 12, isDel: 0, isIot: 0, isClean: 0, xslotId: 255, heapBlk: 0-76508, heapDir: 4
key 2: size: 15, isDel: 0, isIot: 0, isClean: 0, xslotId: 255, heapBlk: 0-76508, heapDir: 3
key 3: size: 9, isDel: 0, isIot: 0, isClean: 0, xslotId: 255, heapBlk: 0-76508, heapDir: 1
key 4: size: 9, isDel: 0, isIot: 0, isClean: 0, xslotId: 255, heapBlk: 0-76508, heapDir: 2
xslot 0: xid: (0-0-0)isActive: 0, isOwscn: 0, fastCommit: 0, isCopied: 0, udBid: 0-0, udVersion: 0, udDir: 0, scn: 0, ssn: 0, fsc: 0, mfb: 0
xslot 1: xid: (0-0-0)isActive: 0, isOwscn: 0, fastCommit: 0, isCopied: 0, udBid: 0-0, udVersion: 0, udDir: 0, scn: 0, ssn: 0, fsc: 0, mfb: 0
tail: change num: 3
DISK BLOCK DUMP END
*** 2025-12-21 20:17:04.719
Block dump from disk:
DISK BLOCK DUMP
unknown
head: id 0-76517, type 0, instance id 0 lsn 764903375304618899, checksum: 1157273973, change num 0, isEncrypted 0, encrypt algo UNKNOWN, isCompressed 0
unknown type 0
tail: change num: 0
DISK BLOCK DUMP END
...
Note： 这里我们能看到index entries 中的5条Key值(包含了2条null)， size 9, 指向heaptable 76508 block. 下面我们dump 表的block。
SQL> select owner,segment_name,extent_id,file_id,block_id,blocks from dba_extents where FILE_ID=0 and 76508 between block_id and block_id+BLOCKS-1;
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------------------- -------------------- ------------ ------------ ------------ ------------
SYS TEST1 0 0 76504 8
1 row fetched.
SQL> alter system dump datafile 0 block 76508;
Succeed.
[yashan@yashan-74 trace]$ vi yashandb_20251221_72.trc
*** 2025-12-21 20:22:27.886
Start dump data blocks TS#: 0 FILE#: 0 minblk 76508 maxblk 76508
Block dump from disk:
DISK BLOCK DUMP
heap data
head: id 0-76508, type 4, instance id 0 lsn 772017701030756353, checksum: 961848851, change num 7, isEncrypted 0, encrypt algo UNKNOWN, isCompressed 0
dataOid: 4374, extent change number: 0
block scn: 772017701030756352, map block: 0-76504, slot: 4, freeness: 5
rows: 5, dirs: 5, xslots: 2, si xslots: 0, free size: 7974, free begin: 156, free end: 8130 free dir: 4095
row[0]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0
row[1]: size: 12 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0
row[2]: size: 12 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0
row[3]: size: 20 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0
row[4]: size: 16 xslot id: 0 columns: 2 format/link/migr/deleted/compact:1/0/0/0/0
xslot[0]: xid: 43-2153-1 active: 0 owscn: 0 fastcommit: 1 ura: block: 0-35033, ver: 1, dir: 25 ssn: 1044414464 fsc: 49666 mfb: 2742 scn: 772017701030756352
xslot[1]: xid: 0-0-0 active: 0 owscn: 0 fastcommit: 0 ura: block: 0-0, ver: 0, dir: 0 ssn: 0 fsc: 0 mfb: 0 scn: 0
tail: change num: 7
DISK BLOCK DUMP END
Note：未显示数据值
— over —