Oracle 11g Release (11.1) index underlying data structure

In this article, the B-tree (B-tree) Hash k-d tree (k-d tree) Point Quadtree (Point Quadtree)

This article describes the structure of Oracle indexes. You can understand the underlying data structure of Oracle indexes to better understand the performance of Oracle indexes on addition, deletion, modification, and query.

B-tree)

The non-index structure can meet all requirements, but the self-balancing B-tree index structure can optimize the search performance on large datasets. Each B-Tree node has multiple keys and pointers. The maximum number of keys supported by a specific B-tree is the order of the tree. Each node has a potential order + 1 pointer pointing to a node lower than it.

For example, as shown in 1, the B-tree of order = 2 has three pointers pointing to the child node (leftmost pointer) smaller than its first key ); a subnode (middle pointer) that is larger than the first key and smaller than the second key; a subnode (rightmost pointer) that is larger than the second key ). Therefore, the B-tree algorithm minimizes the read and write operations required for the positioning records, and transmits nodes that are less than the binary tree algorithm, use one key and two subnodes at most (the structure of a binary tree is a key value, two pointers to the left and right, and B-tree is an extension of a binary tree ). The description is Knuth variation. Its Index consists of two parts: a Sequence set, which provides fast and ordered access to data; an Index set provides direct access to the sequence set.

Although the B-Tree nodes generally do not contain the same number of data values and usually contain a certain amount of unused space, the B-tree algorithm ensures that the tree remains balanced, and Leaf nodes at the same level.

Figure 1 B-tree

Hash)

A hash column quickly and directly accesses a specified stored record based on a given field value. The position where each record is placed is calculated based on the function that records some field fields. Insert and update with the same function.

The problem with hash is that the physical order of records has no relationship with their logical order. In addition, the hash will have a large number of unused areas on the disk.

Figure 2 hash

K-d tree)

Data with two dimensions, such as longitude and latitude, can be effectively stored and retrieved by using the k-d tree transformation called the 2-d tree.

In this structure, the Data Type of each node is field information, two coordinates, and the left pointer and right pointer pointing to two subnodes.

Figure 3 2-d tree

This structure facilitates range query. That is to say, if you specify a vertex (xx, xx) and a distance, the query returns a set of all vertices within the specified original vertex distance.

The 2-d tree is easy to implement. However, because a 2-d Tree Containing k nodes has k heights, insertion and query are complex.

Point Quadtree)

The tree is also used to represent the point data in a two-dimensional space, as shown in Figure 4. However, these structures divide the region into four parts, while the 2-D tree is divided into two parts. Fields of the node record type are composed of attribute information, including two coordinates and the azimuth points pointing to the four subnodes, clockwise, such as northwest NW, Southwest SW, Northeast NE, and SE.

Figure 4 Point Quadtree Index Structure

The quad-tree and 2-d tree are also easy to implement. A k-node quad-tree has k height, and the insertion and query are complex. Each comparison must be performed on at least two coordinates. However, in practice, the length from root to leaf is usually shorter in the tree.

Copy the Python code provided in the second link above and make necessary modifications. Because the Code provided by the web page can only run in a lower version of Python. Versions later than Python 3 are significantly different from those earlier. Therefore, download the final source code of this article and run it in the IDLE of Python 3.3. The following output is displayed:

Python 3.3.0 (v3.3.0:bd8afb90ebf2, Sep 29 2012, 10:57:17) [MSC v.1600 64 bit (AMD64)] on win32

Type "copyright", "credits" or "license()" for more information.

>>> ================================ RESTART ================================

>>> 

<?xml version="1.0" encoding="iso-8859-1"?>

<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN"

 "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">

<svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="400pt" height="400pt" viewBox="0 0 400 400">

 <g fill="none" stroke="blue">

 <line x1="1" y1="1" x2="1" y2="399" />

 <line x1="1" y1="399" x2="399" y2="399" />

 <line x1="399" y1="399" x2="399" y2="1" />

 <line x1="399" y1="1" x2="1" y2="1" />

 <line x1="200" y1="1" x2="200" y2="399" />

 <line x1="1" y1="200" x2="399" y2="200" />

 <line x1="100" y1="1" x2="100" y2="200" />

 <line x1="1" y1="100" x2="200" y2="100" />

 <line x1="50" y1="1" x2="50" y2="100" />

……

Copy the output result and name it ".svg,.html". Open it in a browser and the result is displayed as follows:

Figure 5 an 8*8-sized cross-tree Area

The chart starts from the upper left corner and clockwise. You can use it as "whether or not to split one of the four blocks as needed ".

Download Point Qudatree Python demo