crystal report barcode generator Oracle Index Schemes in Font

Encode QR Code in Font Oracle Index Schemes

Oracle provides several indexing schemes to suit the requirements of different types of applications. During the design phase, you should select the right index type after you conduct a careful analysis of the particular requirements of your application. The B-tree index implementation uses the concept of a balanced (which is what the B stands for) binary search tree as the basis of an index s structure. Oracle uses its own variation on the B-tree called the B*tree for implementing B-tree indexes. These are the regular default indexes created when you use a CREATE INDEX statement in Oracle. The term B*tree index isn t generally used to refer to Oracle regular indexes they are just called indexes.

B-tree indexes are structured in the form of an inverse tree, with top-level blocks called branch blocks and lower-level blocks called leaf blocks. In the hierarchy of nodes, all nodes except the top or root node have one parent node and may have zero or more nodes beneath them called child nodes. If the depth of the tree structure that is, the number of levels is the same from each leaf block to the root node, the tree is called a balanced tree or B-tree. B-trees automatically maintain the necessary level of index for the size of the table. B-trees also ensure that the index blocks are always between half used and full. B-trees permit select, insert, update, and delete operations with very few I/Os per statement. Most B-trees have only three or fewer levels. When you use a B-tree, you need to read only the B-tree blocks, so the number of disk I/Os will be the number of B-tree levels (say, three) plus the I/Os for performing an update or delete (two: one to read and one to write). To search through a B-tree, you would only need three or fewer disk I/Os. Oracle s implementation of the B-tree, the B*tree, always keeps the tree balanced. The leaf blocks contain two items: the indexed column values and the corresponding ROWID for the row that contains the particular column value. The ROWID is a unique Oracle pointer that identifies the physical location of the row in question, and it is the fastest way to access a row in an Oracle database. Scanning the index will quickly get you the ROWID of the row, and from there it s a quick hop to the row itself. If the query just wanted the value of the indexed column itself, of course, the latter step is omitted because you don t have to fetch any more data for the query.

As in the case of tables, you can use the DBMS_SPACE package to estimate the size of a new index. You must provide the DDL statement that creates the index as an attribute to the CREATE_INDEX_COST procedure of the package, as shown in Listing 5-16. Listing 5-16. Using the DBMS_SPACE Package to Estimate a New Index s Space Requirements SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12* SQL> used SET SERVEROUTPUT ON declare l_index_ddl VARCHAR2(1000); l_used_bytes NUMBER; l_allocated_bytes NUMBER; BEGIN DBMS_SPACE.CREATE_INDEX_COST ( ddl => 'create index persons_idx on persons(person_id)', used_bytes => l_used_bytes, alloc_bytes => l_allocated_bytes); DBMS_OUTPUT.PUT_LINE ('used = ' || l_used_bytes || 'bytes' || ' allocated = ' || l_allocated_bytes || 'bytes'); END; / = 154414918bytes allocated = 427720704bytes

PL/SQL procedure successfully completed. SQL> Note the interesting difference between the two size-related attributes of the CREATE_INDEX_COST procedure: used_bytes shows the number of bytes that the index data actually represents. alloc_bytes shows the number of bytes the index will take up in the tablespace when you actually create it.