关于Oracle位图索引内部浅论
我们都知道ORACLE位图索引适用于字段不同值很少的情况,同时修改DML会导致整个同样的值全部被锁定,这严重影响了并发性,所以不建
我们都知道Oracle位图索引适用于字段不同值很少的情况,同时修改DML会导致整个同样的值全部被锁定,这严重影响了并发性,所以不建议OLTP系统大量使用位图索引。
但是具体位图索引的内部是如何排列和组织的呢?如下将进行探讨,由于水平有限可能有一定错误。
首先认识BITMAP索引的组织方式,首先看一下ORACLE给出的这张图
可以看到本图中实际上有4种颜色蓝色、绿色、红色、黄色,BITMAP 索引也是B-TREE的格式,但是其页节点存储的是键值+ROWID范围+位图键的方式,这样一来可以很明显的感知到BITMAP所以在键值很少的时候其空间比较B-TREE索引是小很多的,而在位图键方面使用一连串的0和1来表示是否存在相应的值,这样一来ORACLE就能快速的进行是否有值的定位。
接下来我们进行DUMP,先建立测试表
建立这样一张表
SQL> select id,count(*) from testt_bit group by id;
ID COUNT(*)
----------- ----------
1 100000
2 100000
3 100000
同时在分布上是连续的,1是1-100000行,2是100001-200000行,3是剩下的
在ID列上建立BIT MAP索引
create bitmap index TEST_BIT_IND on TESTT_BIT (ID)
首先进行BITMAP索引的结构DUMP如下:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/ora11g/diag/rdbms/test/test/trace/test_ora_5604.trc
SQL> alter session set events 'immediate trace name treedump level 76306';
*** 2015-03-18 00:40:35.111
----- begin tree dump
branch: 0x1000333 16778035 (0: nrow: 8, level: 1)
leaf: 0x1000334 16778036 (-1: nrow: 2 rrow: 2)
leaf: 0x1000335 16778037 (0: nrow: 2 rrow: 2)
leaf: 0x1000336 16778038 (1: nrow: 2 rrow: 2)
leaf: 0x1000337 16778039 (2: nrow: 2 rrow: 2)
leaf: 0x1000338 16778040 (3: nrow: 2 rrow: 2)
leaf: 0x1000339 16778041 (4: nrow: 2 rrow: 2)
leaf: 0x100033a 16778042 (5: nrow: 2 rrow: 2)
leaf: 0x100033b 16778043 (6: nrow: 1 rrow: 1)
----- end tree dump
这里清楚的看到了这个位图索引的层次,首先它只有2层,1个根节点8个页节点,每个叶节点包含2个索引条目(最后一个节点除外)
接下来我们DUMP根节点:
根据DBA(block adress)16778035进行换算
SQL> select dbms_utility.data_block_address_file(16778035),
2 dbms_utility.data_block_address_block(16778035) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
4 819
然后alter system dump datafile 4 block 819进行DUMP
header address 47810796042828=0x2b7bd183ba4c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 7
kdxcofbo 42=0x2a
kdxcofeo 7972=0x1f24
kdxcoavs 7930
kdxbrlmc 16778036=0x1000334
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
关于这一部分引用一个文档(作者不详)
其中的kdxcolev表示索引层级号,这里由于我们转储的是根节点,所以其层级号为1。对叶子节点来说该值为0;
kdxcolok表示该索引上是否正在发生修改块结构的事务;kdxcoopc表示内部操作代码;kdxconco表示索引条目中列的数量;
kdxcosdc表示索引结构发生变化的数量,当你修改表里的某个索引键值时,该值增加;kdxconro表示当前索引节点中索引条目的数量,但是注意,不包括kdxbrlmc指针;kdxcofbo表示当前索引节点中可用空间的起始点相对当前块的位移量;
kdxcofeo表示当前索引节点中可用空间的最尾端的相对当前块的位移量;kdxcoavs表示当前索引块中的可用空间总量,也就是用kdxcofeo减去kdxcofbo得到的。kdxbrlmc表示分支节点的地址,该分支节点存放了索引键值小于row#0(在转储文档后半部分显示) 所含有的最小值的所有节点信息;kdxbrsno表示最后一个被修改的索引条目号,这里看到是0,表示该索引是新建的索引;
kdxbrbksz表示可用数据块的空间大小。实际从这里已经可以看到,即便是PCTFREE设置为0,也不能用足8192字节。
row#0[8044] dba: 16778037=0x1000335
col 0; len 2; (2): c1 02
col 1; len 3; (3): 01 00 03
col 2; TERM
row#1[8031] dba: 16778038=0x1000336
col 0; len 2; (2): c1 02
col 1; len 4; (4): 01 00 03 f8
col 2; TERM
row#2[8019] dba: 16778039=0x1000337
col 0; len 2; (2): c1 03
col 1; len 3; (3): 01 00 04
col 2; TERM
row#3[8006] dba: 16778040=0x1000338
col 0; len 2; (2): c1 03
col 1; len 4; (4): 01 00 04 bf
col 2; TERM
row#4[7998] dba: 16778041=0x1000339
col 0; len 2; (2): c1 04
col 1; TERM
row#5[7985] dba: 16778042=0x100033a
col 0; len 2; (2): c1 04
col 1; len 4; (4): 01 00 05 57
col 2; TERM
row#6[7972] dba: 16778043=0x100033b
col 0; len 2; (2): c1 04
col 1; len 4; (4): 01 00 05 f8
col 2; TERM

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

0.What does this article do? We propose DepthFM: a versatile and fast state-of-the-art generative monocular depth estimation model. In addition to traditional depth estimation tasks, DepthFM also demonstrates state-of-the-art capabilities in downstream tasks such as depth inpainting. DepthFM is efficient and can synthesize depth maps within a few inference steps. Let’s read about this work together ~ 1. Paper information title: DepthFM: FastMonocularDepthEstimationwithFlowMatching Author: MingGui, JohannesS.Fischer, UlrichPrestel, PingchuanMa, Dmytr

DDREASE is a tool for recovering data from file or block devices such as hard drives, SSDs, RAM disks, CDs, DVDs and USB storage devices. It copies data from one block device to another, leaving corrupted data blocks behind and moving only good data blocks. ddreasue is a powerful recovery tool that is fully automated as it does not require any interference during recovery operations. Additionally, thanks to the ddasue map file, it can be stopped and resumed at any time. Other key features of DDREASE are as follows: It does not overwrite recovered data but fills the gaps in case of iterative recovery. However, it can be truncated if the tool is instructed to do so explicitly. Recover data from multiple files or blocks to a single

If you need to know how to use filtering with multiple criteria in Excel, the following tutorial will guide you through the steps to ensure you can filter and sort your data effectively. Excel's filtering function is very powerful and can help you extract the information you need from large amounts of data. This function can filter data according to the conditions you set and display only the parts that meet the conditions, making data management more efficient. By using the filter function, you can quickly find target data, saving time in finding and organizing data. This function can not only be applied to simple data lists, but can also be filtered based on multiple conditions to help you locate the information you need more accurately. Overall, Excel’s filtering function is a very practical

The performance of JAX, promoted by Google, has surpassed that of Pytorch and TensorFlow in recent benchmark tests, ranking first in 7 indicators. And the test was not done on the TPU with the best JAX performance. Although among developers, Pytorch is still more popular than Tensorflow. But in the future, perhaps more large models will be trained and run based on the JAX platform. Models Recently, the Keras team benchmarked three backends (TensorFlow, JAX, PyTorch) with the native PyTorch implementation and Keras2 with TensorFlow. First, they select a set of mainstream

I cry to death. The world is madly building big models. The data on the Internet is not enough. It is not enough at all. The training model looks like "The Hunger Games", and AI researchers around the world are worrying about how to feed these data voracious eaters. This problem is particularly prominent in multi-modal tasks. At a time when nothing could be done, a start-up team from the Department of Renmin University of China used its own new model to become the first in China to make "model-generated data feed itself" a reality. Moreover, it is a two-pronged approach on the understanding side and the generation side. Both sides can generate high-quality, multi-modal new data and provide data feedback to the model itself. What is a model? Awaker 1.0, a large multi-modal model that just appeared on the Zhongguancun Forum. Who is the team? Sophon engine. Founded by Gao Yizhao, a doctoral student at Renmin University’s Hillhouse School of Artificial Intelligence.

Facing lag, slow mobile data connection on iPhone? Typically, the strength of cellular internet on your phone depends on several factors such as region, cellular network type, roaming type, etc. There are some things you can do to get a faster, more reliable cellular Internet connection. Fix 1 – Force Restart iPhone Sometimes, force restarting your device just resets a lot of things, including the cellular connection. Step 1 – Just press the volume up key once and release. Next, press the Volume Down key and release it again. Step 2 – The next part of the process is to hold the button on the right side. Let the iPhone finish restarting. Enable cellular data and check network speed. Check again Fix 2 – Change data mode While 5G offers better network speeds, it works better when the signal is weaker

Recently, the military circle has been overwhelmed by the news: US military fighter jets can now complete fully automatic air combat using AI. Yes, just recently, the US military’s AI fighter jet was made public for the first time and the mystery was unveiled. The full name of this fighter is the Variable Stability Simulator Test Aircraft (VISTA). It was personally flown by the Secretary of the US Air Force to simulate a one-on-one air battle. On May 2, U.S. Air Force Secretary Frank Kendall took off in an X-62AVISTA at Edwards Air Force Base. Note that during the one-hour flight, all flight actions were completed autonomously by AI! Kendall said - "For the past few decades, we have been thinking about the unlimited potential of autonomous air-to-air combat, but it has always seemed out of reach." However now,

This week, FigureAI, a robotics company invested by OpenAI, Microsoft, Bezos, and Nvidia, announced that it has received nearly $700 million in financing and plans to develop a humanoid robot that can walk independently within the next year. And Tesla’s Optimus Prime has repeatedly received good news. No one doubts that this year will be the year when humanoid robots explode. SanctuaryAI, a Canadian-based robotics company, recently released a new humanoid robot, Phoenix. Officials claim that it can complete many tasks autonomously at the same speed as humans. Pheonix, the world's first robot that can autonomously complete tasks at human speeds, can gently grab, move and elegantly place each object to its left and right sides. It can autonomously identify objects
