Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the difference between ORACLE index and MYSQL INNODB auxiliary index in dealing with NULL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Processing of NULL by ORACLE Index and MYSQL INNODB Auxiliary Index

We know that the b + index in ORACLE stores the NULL of key values, so that we certainly don't need indexes like IS NULL.

Of course, the ORACLE table mentioned here is a heap table, and the index is a single-column B + tree index. (there is an optimization way to set up a composite index such as create index xx on tab (aPhone1').

This ensures the null value of the index record

So DUMP comes out for

.

Row#11 [7886] flag: -, lock: 2, len=12

Col 0; NULL

Col 1; len 1; (1): 31

Col 2; len 6; (6): 01 00 00 d 5 000 a

....

Null value is recorded)

And in some cases, such as

Select count (b) from tab

This kind of declaration as not null attribute is not needed if column b is not displayed. You must add not null or add it to the where condition.

B is not null .

Obviously, these problems are that ORACLE indexes do not store null values.

Mysql innodb is different if is null can be customized to a b + index, then INNODB is a saved null value.

This article will prove through BLOCK DUMP of ORACLE INDEX and internal access to innodb secondary index.

For simplicity, I'll create a table of two columns as follows:

ORACLE:

Create table test (an int,b int,primary key (a))

Create index b_index on test (b)

Mysql innodb:

Create table test (an int,b int,primary key (a), key (b))

Insert some values:

Insert into test values (1 dint 1)

Insert into test values (5thine null)

Insert into test values (3Phone1)

Insert into test values (4Jing 2)

Insert into test values (10ther null)

Insert into test values (7 and 4)

Insert into test values (8 dint 5)

Insert into test values (11 ~ null)

Insert into test values (20pc6)

Insert into test values (21 and 6)

Insert into test values (1951 null)

Insert into test values (16pas 7)

By looking at the implementation plan:

ORACLE:

SQL > select / * + index (test,b_index) * / * from test where b is null

A B

--

five

ten

eleven

nineteen

Execution Plan

Plan hash value: 1357081020

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 4 | 104 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | TEST | 4 | 104 | 3 (0) | 00:00:01 |

Mysql:

Mysql > explain select * from test where b is null

+- -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -+

| | 1 | SIMPLE | test | NULL | ref | b | b | 5 | const | 4 | 100.00 | Using where; Using index |

+- -+

1 row in set, 1 warning (0.00 sec)

In order to force ORACLE to use HINT to specify the index, but because it is not needed at all, ORACLE has ignored it, and MYSQL innodb has been used.

Next, let's analyze its internal structure:

ORACLE:

SQL > SELECT OBJECT_ID FROM DBA_OBJECTS where object_name='B_INDEX'

OBJECT_ID

-

75905

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ home/oracle/diag/rdbms/mytest/mytest/trace/mytest_ora_2996.trc

SQL > alter session set events' immediate trace name treedump level 75905'

Session altered.

View the trace file

Core content:

* 2016-11-16 22 22 purl 4515 55.053

-begin tree dump

Leaf: 0x10000c3 16777411 (0: nrow: 8 rrow: 8)

-end tree dump

Because only one node of the B+ tree is DBA 16777411, we DUMP this block separately.

Perform DBA conversion

SQL > select dbms_utility.data_block_address_file (16777411)

2 dbms_utility.data_block_address_block (16777411) from dual

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (16777411)

-

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (16777411)

four

one hundred and ninety five

BLOCK DUMP:

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ home/oracle/diag/rdbms/mytest/mytest/trace/mytest_ora_3009.trc

SQL > alter system dump datafile 4 block 195

View the TRACE file:

Block data:

Row#0 [8020] flag: -, lock: 2, len=12

Col 0; len 2; (2): C1 02

Col 1; len 6; (6): 01 00 00 b7 00 00

Row#1 [8008] flag: -, lock: 2, len=12

Col 0; len 2; (2): C1 02

Col 1; len 6; (6): 01 00 00 00 b7 00 02

Row#2 [7996] flag: -, lock: 2, len=12

Col 0; len 2; (2): C1 03

Col 1; len 6; (6): 01 00 00 b7 00 03

Row#3 [7984] flag: -, lock: 2, len=12

Col 0; len 2; (2): C1 05

Col 1; len 6; (6): 01 00 00 b7 00 05

Row#4 [7972] flag: -, lock: 2, len=12

Col 0; len 2; (2): C1 06

Col 1; len 6; (6): 01 00 00 00 b7 00 06

Row#5 [7960] flag: -, lock: 2, len=12

Col 0; len 2; (2): C1 07

Col 1; len 6; (6): 01 00 00 00 b7 00 08

Row#6 [7948] flag: -, lock: 2, len=12

Col 0; len 2; (2): C1 07

Col 1; len 6; (6): 01 00 00 b7 00 09

Row#7 [7936] flag: -, lock: 2, len=12

Col 0; len 2; (2): C1 08

Col 1; len 6; (6): 01 00 00 b7 000b

There are 8 records sorted according to the size of column b and COL 0CoL 2 is ROWID

Note that COL is of type number and has its own ORACLE algorithm.

Algorithm reference:

Http://blog.itpub.net/7728585/viewspace-2128563/

In fact, there are no four lines of NULL stored here at all, because we have a total of 12 lines, and there are only eight lines out of dump.

Let's take a look at MYSQL INNODB:

Because I selected a table with 2 columns, my program can run out the index results directly:

Detailed reference:

Http://blog.itpub.net/7728585/viewspace-2126344/

Here, run.

. / mysqlblock test.ibd-d

Current read blocks is: 0-- This Block is file space header blocks!

Current read blocks is: 1-- This Block is insert buffer bitmap blocks!

Current read blocks is: 2-- This Block is inode blocks!

Current read blocks is: 3-This Block is data blocks (index pages)!

Current read blocks is: 4-- This Block is data blocks (index pages)!-- here are the blocks of the secondary index that we need to look at

[root@testmy test] #. / a.out test.ibd 4

Index_no is:117

Find first one record!

Bazaar 5gamma Amuri 2147483616->

BRU 10 ~ (th) A _ Rue ~ 2147483592 ~ (-)

Bazaar 11 ~ (th) A _ Rue ~ 2147483568 ~->

Bazaar 19, Alul, Musi, 2147483544->

Bazaar 1thecontrol ARU 1For->

Bazaar 1, Apura, 3, Mustang->

Bazaar 2, Magna, Avu, 4, Mutual->

BRV 4MAL ARU 7PUR->

Bazaar 5 Magnum Apura 8 muri->

Bazaar 6 ~ (th) A _ (14) 20 ~ (th))

Bazaar 6 ~ (th) A _ (14) 21 ~ (- 1)

Bazaar 7, Apura, 16, murmur->

Bazaar 5 Magi Amuri 2147483616 Mutual-> insert into test values (5Magneol null)

Bvu 10 insert into test values Alav Murray 2147483592 Mui-> Bazaar (10 law null)

Bazaar 11 ~ (th) insert into test values (11 ~ (th) null)

Bazaar 19 ~ (th) A _ Rue ~ 214748 ~ 3544 ~ ~-> insert into test values (19 ~ ~ null)

We can see that INNODB does record null values, but how is this recorded?

The result of our run above shows that it is a large negative number, but the program does not take into account the null value, that is,

Correct in the case of all not null

Don't forget the NULL identification bitmap of the wardrobe:

Nullable field bitmap (1 bit * null field)

Each NULL value occupies a bit (bit), if less than one byte is counted as a byte, if there is no null value

Occupies at least one byte of 00.

Next we still have to look at this bit, the old way to open (speechless tired)

After reading the binary system, it is as follows:

010000180026 actually the first byte of these six bytes is the NULL bitmap and 01

80000005

In fact, MYSQL INNODB does not actually store the null value of the field, but at least stores the primary key value (rowid) of the row.

A bitmap is recorded on the line to represent it (ORACLE has no ROWID information for this line at all)

01 bitmap is actually 0000 0001, which means the first field is NULL.

Then it is easy to use the index, simply scan the relative field bit icon to show bit 1.

That's why the official documents have:

For both BTREE and HASHindexes, comparison of a key part with a constant value is a range condition

When using the =, IN (), IS NULL, or IS NOT NULL operators.

Such a statement, this IS NULL is not valid for ORACLE.

Finally, let's do a test to prove whether the byte of the NULL bitmap corresponds to the field order:

Create three tables for simplicity

Create table test10 (an int,b int,c int,d int,primary key (a), key (bpene cpene d))

Create table test11 (an int,b int,c int,d int,primary key (a), key (bpene cpene d))

Create table test12 (an int,b int,c int,d int,primary key (a), key (bpene cpene d))

Mysql > insert into test10 values (1 null, 1 null)

Query OK, 1 row affected (0.02 sec)

Mysql > insert into test11 values (1 ~ 1 ~ null ~ null)

Query OK, 1 row affected (0.01sec)

Mysql > insert into test12 values (1) Null ~ (1)

Query OK, 1 row affected (0.01sec)

For key (bmeme cpene d)

B is the first field, NULL is 0001, c is the second field, NULL is 0010, and d is the third field, NULL is 0100.

Let's look at this byte. According to our inference, the first should be 0000 0101, the second should be 0000 0110, and the third should be 0000 0011.

That is, 056.06p03,

As expected, the following binary displays are:

05000010fff28000000180000001

06000010fff28000000180000001

03000010fff28000000180000001

It can be seen that the reasoning is correct.

Let's end the difference in the handling of null values between the two database indexes

1. ORACLE B+ so there is no ROWID to store NULL rows at all, and there is no NULL information. Then any query involving NULL cannot use an index

(note that this does not include the combined index at the beginning of the article, which refers to the B+ single-column index, let alone the IOT table.

When I was posting this article today, I don't know why I mentioned IOT. After all, it is commonly used in ORACLE.

HEAP TABLE is a disordered storage method to store data, unlike INNODB, which is an IOT.

Refer to my blog about IOT:

Http://blog.itpub.net/7728585/viewspace-1820365/)

2. MYSQL INNODB stores information about NULL rows, at least for primary keys, but null values are represented by using a BITMAP bitmap byte (not necessarily a byte)

The order of bitmap bytes represents the order of fields, so indexes can be used with is null.

On "what is the difference between ORACLE index and MYSQL INNODB auxiliary index in dealing with NULL?" this article ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.