search
HomeDatabaseMysql TutorialOracle中bitmap索引问题

Oracle中bitmap索引问题,查询的where子句中的and和or条件直接对位图(bitmap)进行布尔运算,得到一个位图结果集,而无需将位图

1.什么是位图索引

在索引段中只需存储键的值,而无需存储rowid。在位图索引中每个位(bit)相当于可能存在这个键值得一行。

2.创建位图索引的好处

一:能节省存储索引的空间,传统的B*Tree会占用大量的存储空间,有时候可能比表多要大的多

3.建立位图索引的缺点

因为一个位图索引上得键值,可能指向多行记录,所以更新一行的时候,同时会吧位图索引指向的所有行都给锁定了,大大的降低了并发性。

4.位图索引如何在查询中工作

查询的where子句中的and和or条件直接对位图(bitmap)进行布尔运算,得到一个位图结果集,而无需将位图结果集转换为rowid,如果布尔运算后的结果集比较小,那么查询就能迅速的得要结果,,而不需要进行全表扫描。

5.位图索引例子:

先创建一个表

create table t(id int primary key,name varchar2(10),p_flag varchar2(1));

我们在p_flag上创建一个位图索引

create table t(id int primary key,name varchar2(10),p_flag varchar2(1));

插入数据

insert into t values(1,'hua','N');

insert into t values(3,'huang','Y');
insert into t values(2,'zhang','N');

我们尝试在一个session1中修改id为1,p_flag为N的行

update t set p_flag = 'Y' where id = 1;

这时我们并不着急commit,在另外一个session2中尝试修改id为2,的记录

update t set p_flag = 'Y' where id = 2;

我们发现session2被阻塞了。。不应该啊,因为Oracle只有在两个session同时修改同一条记录才会阻塞啊,那这时候为什么又阻塞了呢?答案是位图索引在作怪!!

这个例子说明位图索引,最后还是用在哪些经常要查询,但是很少修改的记录很大的表上,同时应该在低基数的列上建立位图索引(低基数是指:想对于表数据来说,每个列上得不同数据很少,比如性别只有两个不同值(男,女),但是性别这列所在的表中,可能有很多很数据)。

linux

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MySQL BLOB : are there any limits?MySQL BLOB : are there any limits?May 08, 2025 am 12:22 AM

MySQLBLOBshavelimits:TINYBLOB(255bytes),BLOB(65,535bytes),MEDIUMBLOB(16,777,215bytes),andLONGBLOB(4,294,967,295bytes).TouseBLOBseffectively:1)ConsiderperformanceimpactsandstorelargeBLOBsexternally;2)Managebackupsandreplicationcarefully;3)Usepathsinst

MySQL : What are the best tools to automate users creation?MySQL : What are the best tools to automate users creation?May 08, 2025 am 12:22 AM

The best tools and technologies for automating the creation of users in MySQL include: 1. MySQLWorkbench, suitable for small to medium-sized environments, easy to use but high resource consumption; 2. Ansible, suitable for multi-server environments, simple but steep learning curve; 3. Custom Python scripts, flexible but need to ensure script security; 4. Puppet and Chef, suitable for large-scale environments, complex but scalable. Scale, learning curve and integration needs should be considered when choosing.

MySQL: Can I search inside a blob?MySQL: Can I search inside a blob?May 08, 2025 am 12:20 AM

Yes,youcansearchinsideaBLOBinMySQLusingspecifictechniques.1)ConverttheBLOBtoaUTF-8stringwithCONVERTfunctionandsearchusingLIKE.2)ForcompressedBLOBs,useUNCOMPRESSbeforeconversion.3)Considerperformanceimpactsanddataencoding.4)Forcomplexdata,externalproc

MySQL String Data Types: A Comprehensive GuideMySQL String Data Types: A Comprehensive GuideMay 08, 2025 am 12:14 AM

MySQLoffersvariousstringdatatypes:1)CHARforfixed-lengthstrings,idealforconsistentlengthdatalikecountrycodes;2)VARCHARforvariable-lengthstrings,suitableforfieldslikenames;3)TEXTtypesforlargertext,goodforblogpostsbutcanimpactperformance;4)BINARYandVARB

Mastering MySQL BLOBs: A Step-by-Step TutorialMastering MySQL BLOBs: A Step-by-Step TutorialMay 08, 2025 am 12:01 AM

TomasterMySQLBLOBs,followthesesteps:1)ChoosetheappropriateBLOBtype(TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB)basedondatasize.2)InsertdatausingLOAD_FILEforefficiency.3)Storefilereferencesinsteadoffilestoimproveperformance.4)UseDUMPFILEtoretrieveandsaveBLOBsco

BLOB Data Type in MySQL: A Detailed Overview for DevelopersBLOB Data Type in MySQL: A Detailed Overview for DevelopersMay 07, 2025 pm 05:41 PM

BlobdatatypesinmysqlareusedforvoringLargebinarydatalikeImagesoraudio.1) Useblobtypes (tinyblobtolongblob) Basedondatasizeneeds. 2) Storeblobsin Perplate Petooptimize Performance.3) ConsidersxterNal Storage Forel Blob Romana DatabasesizerIndimprovebackupupe

How to Add Users to MySQL from the Command LineHow to Add Users to MySQL from the Command LineMay 07, 2025 pm 05:01 PM

ToadduserstoMySQLfromthecommandline,loginasroot,thenuseCREATEUSER'username'@'host'IDENTIFIEDBY'password';tocreateanewuser.GrantpermissionswithGRANTALLPRIVILEGESONdatabase.*TO'username'@'host';anduseFLUSHPRIVILEGES;toapplychanges.Alwaysusestrongpasswo

What Are the Different String Data Types in MySQL? A Detailed OverviewWhat Are the Different String Data Types in MySQL? A Detailed OverviewMay 07, 2025 pm 03:33 PM

MySQLofferseightstringdatatypes:CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,andSET.1)CHARisfixed-length,idealforconsistentdatalikecountrycodes.2)VARCHARisvariable-length,efficientforvaryingdatalikenames.3)BINARYandVARBINARYstorebinarydata,similartoC

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool