This chapter discusses indexes, which are schema objects that can speed access to table rows, and index-organized tables, which are tables stored in an index structure. Show
Overview of IndexesAn index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O. If a heap-organized table has no indexes, then the database must perform a
full table scan to find a value. For example, without an index, a query of location For an analogy, suppose an HR manager has a shelf of cardboard boxes. Folders containing employee information are inserted randomly in the boxes. The folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, whereas the folder for King (ID 100) is at the bottom of box 3. To locate a folder, the manager looks at every folder in box 1 from bottom to top, and then moves from box to box until the folder is found. To speed access, the manager could create an index that sequentially lists every employee ID with its folder location: ID 100: Box 3, position 1 (bottom) ID 101: Box 7, position 8 ID 200: Box 1, position 10 . . . Similarly, the manager could create separate indexes for employee last names, department IDs, and so on. In general, consider creating an index on a column in any of the following situations:
Index CharacteristicsIndexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, an index can be dropped or created without physically affecting the table for the index. Note: If you drop an index, then applications still work. However, access of previously indexed data can be slower. The absence or presence of an index does not require a change in the wording of any SQL statement. An index is a fast access path to a single row of data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value. The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades DML performance because the database must also update the indexes. Indexes have the following properties:
See Also:
Keys and ColumnsA key is a set of columns or expressions on which you can build an index. Although the terms are often used interchangeably, indexes and keys are different. Indexes are structures stored in the database that users manage using SQL statements. Keys are strictly a logical concept. The following statement creates an index on the CREATE INDEX ord_customer_ix ON orders (customer_id); In the preceding statement, the Note: Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key. Composite IndexesA composite index, also called a concatenated index, is an index on multiple columns in a table. Columns in a composite index should appear in the order that makes the most sense for the queries that will retrieve data and need not be adjacent in the table. Composite indexes can speed retrieval of data for For example, suppose an application frequently queries the CREATE INDEX employees_ix ON employees (last_name, job_id, salary); Queries that access all
three columns, only the Note: In some cases, such as when the leading column has very low cardinality, the database may use a skip scan of this index (see "Index Skip Scan"). Multiple indexes can exist for the same table if the permutation of columns differs for each index. You can create multiple indexes using the same columns if you specify distinctly different permutations of the columns. For example, the following SQL statements specify valid permutations: CREATE INDEX employee_idx1 ON employees (last_name, job_id); CREATE INDEX employee_idx2 ON employees (job_id, last_name); Unique and Nonunique IndexesIndexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column or columns. For example, no two employees can have the same employee ID. Thus, in a unique index, one rowid exists for each data value. The data in the leaf blocks is sorted only by key. Nonunique indexes permit duplicates values in the indexed column or columns. For example, the
Oracle Database does not index table rows in which all key columns are null, except for bitmap indexes or when the cluster key column value is null. Types of IndexesOracle Database provides several indexing schemes, which provide complementary performance functionality. The indexes can be categorized as follows:
B-Tree IndexesB-trees, short for balanced trees, are the most common type of database index. A B-tree index is an ordered list of values divided into ranges. By associating a key with a row or range of rows, B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches. Figure 3-1 illustrates the structure of a B-tree index. The example shows an index on the Branch Blocks and Leaf BlocksA B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. In Figure 3-1, the root branch block has an entry A B-tree index is balanced because all leaf blocks automatically stay at the same depth. Thus, retrieval of any record from anywhere in the index takes approximately the same amount of time. The height of the index is the number of blocks required to go from the root block to a leaf block. The branch level is the height minus 1. In Figure 3-1, the index has a height of 3 and a branch level of 2. Branch blocks store the minimum key prefix needed to make a branching decision between two keys. This technique enables the database to fit as much data as possible on each branch block. The branch blocks contain a pointer to the child block containing the key. The number of keys and pointers is limited by the block size. The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked. In
Figure 3-1 the leftmost leaf block ( Note: Indexes in columns with character data are based on the binary values of the characters in the database character set. Index ScansIn an index scan, the database retrieves a row by traversing the index, using the indexed column values specified by the statement. If the database scans the index for a value, then it will find this value in n I/Os where n is the height of the B-tree index. This is the basic principle behind Oracle Database indexes. If a SQL statement accesses only indexed columns, then the database reads values directly from the index rather than from the table. If the statement accesses columns in addition to the indexed columns, then the database uses rowids to find the rows in the table. Typically, the database retrieves table data by alternately reading an index block and then a table block. Full Index ScanIn a full index scan, the database reads the entire index in order. A full index scan is available if a
predicate ( Suppose that an application runs the following query: SELECT department_id, last_name, salary FROM employees WHERE salary > 5000 ORDER BY department_id, last_name; Also assume that For example, the full scan could read the index entries as follows: 50,Atkinson,2800,rowid 60,Austin,4800,rowid 70,Baer,10000,rowid 80,Abel,11000,rowid 80,Ande,6400,rowid 110,Austin,7200,rowid . . .
Fast Full Index ScanA fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order. Fast full index scans are an alternative to a full table scan when both of the following conditions are met:
For example, an application issues the following
query, which does not include an SELECT last_name, salary FROM employees; The Baida,2900,rowid Zlotkey,10500,rowid Austin,7200,rowid Baer,10000,rowid Atkinson,2800,rowid Austin,4800,rowid . . . Index Range ScanAn index range scan is an ordered scan of an index that has the following characteristics:
The database commonly uses an index range scan to
access selective data. The selectivity is the percentage of rows in the table that the query selects, with 0 meaning no rows and 1 meaning all rows. Selectivity is tied to a query predicate, such as For example, a user queries employees whose last names begin with Abel,rowid Ande,rowid Atkinson,rowid Austin,rowid Austin,rowid Baer,rowid . . . The database could use a range scan because the An index range scan can be bounded on both sides, as in a query for departments with IDs between 10 and 40, or bounded on only one side, as in a query for IDs over 40. To scan the index, the database moves backward or forward through the leaf blocks. For example, a scan for IDs between 10 and 40 locates the first index leaf block that contains the lowest key value that is 10 or greater. The scan then proceeds horizontally through the linked list of leaf nodes until it locates a value greater than 40. Index Unique ScanIn contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key. The database performs a unique scan when a predicate references all of the columns in a As an illustration, suppose that a user runs the following query: SELECT * FROM employees WHERE employee_id = 5; Assume that the 1,rowid 2,rowid 4,rowid 5,rowid 6,rowid . . . In this case, the database can use an index unique scan to locate the rowid for the employee whose ID is 5. Index Skip ScanAn index skip scan uses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index. The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate. For example, assume that you run the following query for a customer in
the SELECT * FROM sh.customers WHERE cust_email = ''; The Example 3-1 Composite Index Entries F,,rowid F,,rowid F,,rowid F,,rowid F,,rowid F,,rowid M,,rowid M,,rowid The database can use a skip scan of this index
even though In a skip scan, the number of logical subindexes is determined by the number of distinct values in the leading column. In Example 3-1, the leading column has two possible values. The database logically splits the index into one subindex with the key When searching
for the record for the customer whose email is SELECT * FROM sh.customers WHERE cust_gender = 'F' AND cust_email = '' UNION ALL SELECT * FROM sh.customers WHERE cust_gender = 'M' AND cust_email = ''; Index Clustering FactorThe index clustering factor measures row order in relation to an indexed value such as employee last name. The more order that exists in row storage for this value, the lower the clustering factor. The clustering factor is useful as a rough measure of the number of I/Os required to read an entire table by means of an index:
The clustering factor is relevant for index scans because it can show:
For example, assume that the Table 3-1 Contents of Two Data Blocks in the Employees Table
Rows are stored in the blocks in order of last name (shown in bold). For example, the bottom row in data block 1 describes Abel, the next row up describes Ande, and so on alphabetically until the top row in block 1 for Steven King. The bottom row in block 2 describes Kochar, the next row up describes Kumar, and so on alphabetically until the last row in the block for Zlotkey. Assume that an index exists on the last name column. Each name entry corresponds to a rowid. Conceptually, the index entries would look as follows: Abel,block1row1 Ande,block1row2 Atkinson,block1row3 Austin,block1row4 Baer,block1row5 . . . Assume that a separate index exists on the employee ID column. Conceptually, the index entries might look as follows, with employee IDs distributed in almost random locations throughout the two blocks: 100,block1row50 101,block2row1 102,block1row9 103,block2row19 104,block2row39 105,block1row4 . . . Example 3-2 queries the Example 3-2 Clustering Factor SQL> SELECT INDEX_NAME, CLUSTERING_FACTOR 2 FROM ALL_INDEXES 3 WHERE INDEX_NAME IN ('EMP_NAME_IX','EMP_EMP_ID_PK'); INDEX_NAME CLUSTERING_FACTOR -------------------- ----------------- EMP_EMP_ID_PK 19 EMP_NAME_IX 2 Reverse Key IndexesA reverse key
index is a type of B-tree index that physically reverses the bytes of each index key while keeping the column order. For example, if the index key is Reversing the key solves the problem of contention for leaf blocks in the right side of
a B-tree index. This problem can be especially acute in an Oracle Real Application Clusters (Oracle RAC) database in which multiple instances repeatedly modify the same block. For example, in an In a reverse key index, the reversal of the byte order distributes inserts across all leaf keys in the index. For example, keys such as 20 and 21 that would have been adjacent in a standard key index are now stored far apart in separate blocks. Thus, I/O for insertions of sequential keys is more evenly distributed. Because the data in the index is not sorted by column key when it is stored, the reverse key arrangement eliminates the ability to run an index range scanning query in some cases. For example, if a user issues a query for order IDs greater than 20, then the database cannot start with the block containing this ID and proceed horizontally through the leaf blocks. Ascending and Descending IndexesIn an ascending index, Oracle Database stores data in ascending order. By default, character data is ordered by the binary values contained in each byte of the value, numeric data from smallest to largest number, and date from earliest to latest value. For an example of an ascending index, consider the following SQL statement: CREATE INDEX emp_deptid_ix ON hr.employees(department_id); Oracle Database sorts the By specifying the Descending indexes are useful when a query sorts some columns ascending and others descending. For an example, assume that you create a composite index on the CREATE INDEX emp_name_dpt_ix ON hr.employees(last_name ASC, department_id DESC); If a user queries See Also:
Key CompressionOracle Database can use key compression to compress portions of the primary key column values in a B-tree index or an index-organized table. Key compression can greatly reduce the space consumed by the index. In general, index keys have two pieces, a grouping piece and a unique piece. Key compression breaks the index key into a prefix entry, which is the grouping piece, and a suffix entry, which is the unique or nearly unique piece. The database achieves compression by sharing the prefix entries among the suffix entries in an index block. Note: If a key is not defined to have a unique piece, then the database provides one by appending a rowid to the grouping piece. By default, the prefix of a unique index consists of all key columns excluding the last one, whereas the prefix of a nonunique index consists of all key columns. For example, suppose that you create a composite index on the CREATE INDEX orders_mod_stat_ix ON orders ( order_mode, order_status ); Many repeated values occur in the
Example 3-3 Index Entries in Orders Table online,0,AAAPvCAAFAAAAFaAAa online,0,AAAPvCAAFAAAAFaAAg online,0,AAAPvCAAFAAAAFaAAl online,2,AAAPvCAAFAAAAFaAAm online,3,AAAPvCAAFAAAAFaAAq online,3,AAAPvCAAFAAAAFaAAt In Example 3-3, the key prefix would consist of a
concatenation of the online,0 AAAPvCAAFAAAAFaAAa AAAPvCAAFAAAAFaAAg AAAPvCAAFAAAAFaAAl online,2 AAAPvCAAFAAAAFaAAm online,3 AAAPvCAAFAAAAFaAAq AAAPvCAAFAAAAFaAAt Suffix entries form the compressed version of index rows. Each suffix entry references a prefix entry, which is stored in the same index block as the suffix entry. Alternatively,
you could specify a prefix length when creating a compressed index. For example, if you specified prefix length online 0,AAAPvCAAFAAAAFaAAa 0,AAAPvCAAFAAAAFaAAg 0,AAAPvCAAFAAAAFaAAl 2,AAAPvCAAFAAAAFaAAm 3,AAAPvCAAFAAAAFaAAq 3,AAAPvCAAFAAAAFaAAt The index stores a specific prefix once per leaf block at most. Only keys in the leaf blocks of a B-tree index are compressed. In the branch blocks the key suffix can be truncated, but the key is not compressed. See Also:
Bitmap IndexesIn a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows. Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:
For a data warehouse example, the Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index although it uses a different internal representation. If the indexed column in a single row is updated, then the database locks the index key entry (for example, See Also:
Bitmap Indexes on a Single TableExample 3-4 shows a query of the Example 3-4 Query of customers Table SQL> SELECT cust_id, cust_last_name, cust_marital_status, cust_gender 2 FROM sh.customers 3 WHERE ROWNUM < 8 ORDER BY cust_id; CUST_ID CUST_LAST_ CUST_MAR C ---------- ---------- -------- - 1 Kessel M 2 Koch F 3 Emmerson M 4 Hardy M 5 Gowen M 6 Charles single F 7 Ingram single F 7 rows selected. The Table 3-2 illustrates the bitmap index for the Table 3-2 Sample Bitmap
A mapping function converts each bit in the bitmap to a rowid of the Note: Bitmap indexes can include keys that consist entirely of null values, unlike B-tree indexes. Indexing nulls can be useful for some SQL statements, such as queries with the aggregate function An analyst investigating demographic trends of the customers may ask, "How many of our female customers are single or divorced?" This question corresponds to the following SQL query: SELECT COUNT(*) FROM customers WHERE cust_gender = 'F' AND cust_marital_status IN ('single', 'divorced'); Bitmap indexes can process this query efficiently by counting the number of Table 3-3 Sample Bitmap
Bitmap indexing efficiently merges indexes that correspond to several conditions in a Bitmap Join IndexesA bitmap join index is a bitmap index for the join of two or more tables. For each value in a table column, the index stores the rowid of the corresponding row in the indexed table. In contrast, a standard bitmap index is created on a single table. A bitmap join index is an efficient means of reducing the volume of data that must be joined by performing restrictions in advance. For an example of when a bitmap join index would be useful, assume that users often query the number of employees with a particular job type. A typical query might look as follows: SELECT COUNT(*) FROM employees, jobs WHERE employees.job_id = jobs.job_id AND jobs.job_title = 'Accountant'; The preceding query would typically use an index on CREATE BITMAP INDEX employees_bm_idx ON employees (jobs.job_title) FROM employees, jobs WHERE employees.job_id = jobs.job_id; As illustrated in Figure 3-2, the index key is Conceptually, Example 3-5 Join of employees and jobs Tables SELECT jobs.job_title AS "jobs.job_title", employees.rowid AS "employees.rowid" FROM employees, jobs WHERE employees.job_id = jobs.job_id ORDER BY job_title; jobs.job_title employees.rowid ----------------------------------- ------------------ Accountant AAAQNKAAFAAAABSAAL Accountant AAAQNKAAFAAAABSAAN Accountant AAAQNKAAFAAAABSAAM Accountant AAAQNKAAFAAAABSAAJ Accountant AAAQNKAAFAAAABSAAK Accounting Manager AAAQNKAAFAAAABTAAH Administration Assistant AAAQNKAAFAAAABTAAC Administration Vice President AAAQNKAAFAAAABSAAC Administration Vice President AAAQNKAAFAAAABSAAB . . . In a data warehouse, the join condition is an equijoin (it uses the equality operator) between the primary key columns of the dimension tables and the foreign key columns in the fact table. Bitmap join indexes are sometimes much more efficient in storage than materialized join views, an alternative for materializing joins in advance. Bitmap Storage StructureOracle Database uses a B-tree index structure to store bitmaps for each indexed key.
For example, if Assume that the
Conceptually, an index leaf block in this index could contain entries as follows: Shipping Clerk,AAAPzRAAFAAAABSABQ,AAAPzRAAFAAAABSABZ,0010000100 Shipping Clerk,AAAPzRAAFAAAABSABa,AAAPzRAAFAAAABSABh,010010 Stock Clerk,AAAPzRAAFAAAABSAAa,AAAPzRAAFAAAABSAAc,1001001100 Stock Clerk,AAAPzRAAFAAAABSAAd,AAAPzRAAFAAAABSAAt,0101001001 Stock Clerk,AAAPzRAAFAAAABSAAu,AAAPzRAAFAAAABSABz,100001 . . . The same job title appears in multiple entries because the rowid range differs. Assume that a session updates the job ID of one employee from The data for a bitmap index is stored in one segment. Oracle Database stores each bitmap in one or more pieces. Each piece occupies part of a single data block. Function-Based IndexesYou can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of a function or expression involving one or more columns and stores it in the index. A function-based index can be either a B-tree or a bitmap index. The function used for building the index can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL function, package function, or C callout. For example, a function could add the values in two columns. See Also:
Uses of Function-Based IndexesFunction-based indexes are efficient for evaluating statements that contain functions in their For example, suppose you create the following function-based index: CREATE INDEX emp_total_sal_idx
ON employees (12 * salary * commission_pct, salary, commission_pct);
The database can use the preceding index when processing queries such as Example 3-6 (partial sample output included). Example 3-6 Query Containing an Arithmetic Expression SELECT employee_id, last_name, first_name,
12*salary*commission_pct AS "ANNUAL SAL"
FROM employees
WHERE (12 * salary * commission_pct) < 30000
ORDER BY "ANNUAL SAL" DESC;
EMPLOYEE_ID LAST_NAME FIRST_NAME ANNUAL SAL
----------- ------------------------- -------------------- ----------
159 Smith Lindsey 28800
151 Bernstein David 28500
152 Hall Peter 27000
160 Doran Louise 27000
175 Hutton Alyssa 26400
149 Zlotkey Eleni 25200
169 Bloom Harrison 24000
Function-based indexes defined on the SQL functions CREATE INDEX emp_fname_uppercase_idx ON employees ( UPPER(first_name) ); The SELECT * FROM employees WHERE UPPER(first_name) = 'AUDREY'; A function-based index is also useful for indexing only specific rows in a table. For example, the CREATE INDEX cust_valid_idx ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END ); See Also:
Optimization with Function-Based IndexesThe optimizer can use an index range scan on a function-based index for queries with expressions in A virtual column is useful for speeding access to data derived from expressions. For example, you could define virtual column The optimizer performs expression matching by parsing the expression in a SQL statement and then comparing the expression trees of the statement and the function-based index. This comparison is case-insensitive and ignores blank spaces. See Also:
Application Domain IndexesAn application domain index is a customized index specific to an application. Oracle Database provides extensible indexing to do the following:
You can encapsulate application-specific index management routines as an indextype schema object and define a domain index on table columns or attributes of an object type. Extensible indexing can efficiently process application-specific operators. The application software, called the cartridge, controls the structure and content of a domain index. The database interacts with the application to build, maintain, and search the domain index. The index structure itself can be stored in the database as an index-organized table or externally as a file. Index StorageOracle Database stores index data in an index segment. Space available for index data in a data block is the data block size minus block overhead, entry overhead, rowid, and one length byte for each value indexed. The tablespace of an index segment is either the
default tablespace of the owner or a tablespace specifically named in the Overview of Index-Organized TablesAn index-organized table is a table stored in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where they fit. In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, using SQL statements. For an analogy of an index-organized table, suppose a human resources manager has a book case of cardboard boxes. Each box is labeled with a number—1, 2, 3, 4, and so on—but the boxes do not sit on the shelves in sequential order. Instead, each box contains a pointer to the shelf location of the next box in the sequence. Folders containing employee records are stored in each box. The folders are sorted by employee ID. Employee King has ID 100, which is the lowest ID, so his folder is at the bottom of box 1. The folder for employee 101 is on top of 100, 102 is on top of 101, and so on until box 1 is full. The next folder in the sequence is at the bottom of box 2. In this analogy, ordering folders by employee ID makes it possible to search efficiently for folders without having to maintain a separate index. Suppose a user requests the records for employees 107, 120, and 122. Instead of searching an index in one step and retrieving the folders in a separate step, the manager can search the folders in sequential order and retrieve each folder as found. Index-organized tables provide faster access to table rows by primary key or a valid prefix of the key. The presence of non-key columns of a row in the leaf block avoids an additional data block I/O. For example, the salary of employee 100 is stored in the index row itself. Also, because rows are stored in primary key order, range access by the primary key or prefix involves minimal block I/Os. Another benefit is the avoidance of the space overhead of a separate primary key index. Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order. This type of table is often used for information retrieval, spatial (see "Overview of Oracle Spatial"), and OLAP applications (see "OLAP"). See Also:
Index-Organized Table CharacteristicsThe database system performs all operations on index-organized tables by manipulating the B-tree index structure. Table 3-4 summarizes the differences between index-organized tables and heap-organized tables. Table 3-4 Comparison of Heap-Organized Tables with Index-Organized Tables
Figure 3-3 illustrates the structure of an index-organized An index-organized table stores all data in the same structure and does not need to store the rowid. As shown in Figure 3-3, leaf block 1 in an index-organized table might contain entries as follows, ordered by primary key: 20,Marketing,201,1800 30,Purchasing,114,1700 Leaf block 2 in an index-organized table might contain entries as follows: 50,Shipping,121,1500 60,IT,103,1400 A scan of the index-organized table rows in primary key order reads the blocks in the following sequence:
To contrast data access in a heap-organized table to an index-organized table, suppose block 1 of a heap-organized 50,Shipping,121,1500 20,Marketing,201,1800 Block 2 contains rows for the same table as follows: 30,Purchasing,114,1700 60,IT,103,1400 A B-tree index leaf block for this heap-organized table contains the following entries, where the first value is the primary key and the second is the rowid: 20,AAAPeXAAFAAAAAyAAD 30,AAAPeXAAFAAAAAyAAA 50,AAAPeXAAFAAAAAyAAC 60,AAAPeXAAFAAAAAyAAB A scan of the table rows in primary key order reads the table segment blocks in the following sequence:
Thus, the number of block I/Os in this example is double the number in the index-organized example. Index-Organized Tables with Row Overflow AreaWhen creating an index-organized table, you can specify a separate segment as a row overflow area. In index-organized tables, B-tree index entries can be large because they contain an entire row, so a separate segment to contain the entries is useful. In contrast, B-tree entries are usually small because they consist of the key and rowid. If a row overflow area is specified, then the database can divide a row in an index-organized table into the following parts:
See Also:
Secondary Indexes on Index-Organized TablesA secondary index is an index on an index-organized table. In a sense, it is an index on an index. The secondary index is an independent schema object and is stored separately from the index-organized table. As explained in "Rowid Data Types", Oracle Database uses row identifiers called logical rowids for index-organized tables. A logical rowid is a base64-encoded representation of the table primary key. The logical rowid length depends on the primary key length. Rows in index leaf blocks can move within or between blocks because of insertions. Rows in index-organized tables do not migrate as heap-organized rows do (see "Chained and Migrated Rows"). Because rows in index-organized tables do not have permanent physical addresses, the database uses logical rowids based on primary key. For example, assume that the 10,Administration,200,1700 20,Marketing,201,1800 30,Purchasing,114,1700 40,Human Resources,203,2400 A secondary index on the 1700,*BAFAJqoCwR/+ 1700,*BAFAJqoCwQv+ 1800,*BAFAJqoCwRX+ 2400,*BAFAJqoCwSn+ Secondary indexes provide fast and efficient access to index-organized tables using columns that are neither the primary key nor a prefix of the primary key. For example, a query of the names of departments whose ID is greater than 1700 could use the secondary index to speed data access. See Also:
Logical Rowids and Physical GuessesSecondary indexes use the logical rowids to locate table rows. A logical rowid includes a physical guess, which is the physical rowid of the index entry when it was first made. Oracle Database can use physical guesses to probe directly into the leaf block of the index-organized table, bypassing the primary key search. When the physical location of a row changes, the logical rowid remains valid even if it contains a physical guess that is stale. For a heap-organized table, access by a secondary index involves a scan of the secondary index and an additional I/O to fetch the data block containing the row. For index-organized tables, access by a secondary index varies, depending on the use and accuracy of physical guesses:
Bitmap Indexes on Index-Organized TablesA secondary index on an index-organized table can be a bitmap index. As explained in "Bitmap Indexes", a bitmap index stores a bitmap for each index key. When bitmap indexes exist on an index-organized table, all the bitmap indexes use a heap-organized mapping table. The mapping table stores the logical rowids of the index-organized table. Each mapping table row stores one logical rowid for the corresponding index-organized table row. The database accesses a bitmap index using a search key. If the database finds the key, then the bitmap entry is converted to a physical rowid. With heap-organized tables, the database uses the physical rowid to access the base table. With index-organized tables, the database uses the physical rowid to access the mapping table, which in turn yields a logical rowid that the database uses to access the index-organized table.
Figure 3-4 illustrates index access for a query of the Note: Movement of rows in an index-organized table does not leave the bitmap indexes built on that index-organized table unusable. What are the characteristics of a good GUI?Important qualities of User Interface Design are following :. Simplicity : User Interface design should be simple. ... . Consistency : The user interface should have a more consistency. ... . Intuitiveness : ... . Prevention : ... . Forgiveness : ... . Graphical User Interface Design :. Which of the following are essential characteristics of a good interface design?Clarity is one of the most important features in UI design. It signals to your users that you know what you are doing and that the app they are using is trustworthy. Clarity in UI design can come from consistency, simplicity, and truth in messaging. If you are consistent with your design, it will be clear to users.
What is evaluation in user interface design?The goal of user interface evaluations is to make products and services more usable, easy to learn and intuitive for the user. Learners create an evaluation strategy and validate user interfaces using evaluation techniques and usability testing.
What are the three fundamental parts to a user interface design?The user interface includes three parts; the input mechanism, the output mechanism, and the reporting mechanism.
|