This chapter discusses indexes, which are schema objects that have the right to speed access to table rows, and index-organized tables, which space tables save on computer in an table of contents structure.
You are watching: A(n) ______ is a number(s) or character(s) appended to or inserted in a primary key value.
This chapter consists of the adhering to sections:
Overview of Indexes
An index is one optional structure, associated with a table or table cluster, that can sometimes rate data access. By producing an table of contents on one or more columns of a table, you gain the capacity in some situations to retrieve a small collection of randomly dispersed rows from the table. Indexes are one of many means of reducing disk I/O.
If a heap-organized table has actually no indexes, then the database must perform a complete table scan to find a value. Because that example, without an index, a questions of ar 2700 in the hr.departments table requires the database to find every heat in every table block because that this value. This approach does not range well together data volumes increase.
For one analogy, expect an HR manager has actually a shelf the cardboard boxes. Folders containing employee details are placed randomly in the boxes. The folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, conversely, the folder because that King (ID 100) is at the bottom of crate 3. To locate a folder, the manager looks at every folder in box 1 from bottom come top, and then move from box to box till the folder is found. To speed access, the manager might create an index that sequentially list every employee ID v its folder location:
ID 100: crate 3, position 1 (bottom)ID 101: crate 7, place 8 id 200: box 1, place 10...Similarly, the manager could create separate indexes because that employee critical names, room IDs, and also so on.
In general, consider creating an index on a shaft in any kind of of the complying with situations:
The indexed columns room queried frequently and also return a little percentage of the total number of rows in the table.
A unique key constraint will certainly be put on the table and you desire to manually specify the index and also all index options.
See Also:Chapter 5, "Data Integrity"
Indexes room schema objects that space logically and physically live independence of the data in the objects with which they space associated. Thus, one index can be reduce or produced without physically influence the table for the index.
Note:If you drop an index, climate applications tho work. However, access of previously indexed data deserve to be slower.
The absence or visibility of an index does not call for a change in the wording of any SQL statement. An table of contents is a fast access path come a single row that data. The affects only the rate of execution. Provided a data worth that has actually been indexed, the index points directly to the ar of the rows containing that value.
The database instantly maintains and uses indexes ~ they space created. The database also automatically reflects transforms to data, such together adding, updating, and deleting rows, in all appropriate indexes v no added actions compelled by users. Retrieval power of indexed data remains almost constant, also as rows room inserted. However, the presence of many indexes ~ above a table degradation DML performance since the database must likewise update the indexes.
Indexes have actually the complying with properties:
Keys and Columns
A key is a collection of columns or expression on which girlfriend can construct an index. Return the terms are frequently used interchangeably, indexes and keys are different. Indexes room structures stored in the database the users manage using SQL statements. Secrets are strictly a reasonable concept.
The adhering to statement creates an index on the customer_id pillar of the sample table oe.orders:
CREATE table of contents ord_customer_ix ON orders (customer_id);In the preceding statement, the customer_id column is the index key. The table of contents itself is named ord_customer_ix.
Note:Primary and unique keys instantly have indexes, however you could want to create an table of contents on a foreign key.
See Also:thedesigningfairy.com Database SQL Language Reference create INDEX syntax and semantics
A composite index, also called a concatenated index, is an index on lot of columns in a table. Columns in a composite table of contents should show up in the order that renders the most sense because that the queries that will certainly retrieve data and need not be adjacent in the table.
Composite indexes have the right to speed retrieval the data for select statements in which the where clause recommendations all or the leading section of the columns in the composite index. Therefore, the bespeak of the columns supplied in the an interpretation is important. In general, the most frequently accessed columns go first.
For example, mean an application generally queries the last_name, job_id, and also salary columns in the employees table. Likewise assume the last_name has actually high cardinality, which method that the variety of distinct worths is large compared come the number of table rows. You create an index with the following column order:
CREATE table of contents employees_ix ON employees (last_name, job_id, salary);Queries that access all 3 columns, just the last_name column, or only the last_name and job_id columns use this index. In this example, queries that carry out not accessibility the last_name tower do not use the index.
Note:In part cases, together as as soon as the leading obelisk has really low cardinality, the database might use a skip scan the this index (see "Index Skip Scan").
Multiple indexes deserve to exist for the same table if the permutation that columns differs for each index. You can produce multiple indexes utilizing the same columns if you specify distinctly different permutations that the columns. Because that example, the following SQL explanation specify precious permutations:
CREATE index employee_idx1 ON employee (last_name, job_id);CREATE table of contents employee_idx2 ON employees (job_id, last_name);
Unique and Nonunique Indexes
Indexes deserve to be distinct or nonunique. Unique indexes guarantee that no two rows the a table have duplicate values in the vital column or columns. Because that example, no 2 employees deserve to have the exact same employee ID. Thus, in a unique index, one rowid exists because that each data value. The data in the leaf blocks is sorted just by key.
Nonunique indexes permit duplicates worths in the indexed shaft or columns. Because that example, the first_name column of the employee table might contain lot of Mike values. For a nonunique index, the rowid is had in the crucial in sorted order, so nonunique indexes are sorted by the index an essential and rowid (ascending).
thedesigningfairy.com Database does no index table rows in which all crucial columns are null, other than for bitmap indexes or once the cluster vital column worth is null.
Types that Indexes
thedesigningfairy.com Database offers several indexing schemes, which carry out complementary power functionality. The indexes have the right to be categorized as follows:
These indexes space the traditional index type. Castle are terrific for primary crucial and highly-selective indexes. Supplied as concatenated indexes, B-tree indexes have the right to retrieve data sorted through the indexed columns. B-tree indexes have actually the following subtypes:
See Also:thedesigningfairy.com Database performance Tuning guide to learn around different index types
Figure 3-1 internal Structure that a B-tree Index
Branch Blocks and Leaf Blocks
A 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 0-40, i m sorry points come the leftmost block in the following branch level. This branch block consists of entries such together 0-10 and 11-19. Every of this entries points come a leaf block that contains key values that fall in the range.
A B-tree table of contents is well balanced because all sheet blocks instantly stay at the exact same depth. Thus, retrieval of any type of record from everywhere in the table of contents takes roughly the same amount the time. The height of the table of contents is the number of blocks forced to walk from the root block to a leaf block. The branch level is the elevation minus 1. In number 3-1, the index has a elevation of 3 and also a branch level the 2.
Branch blocks store the minimum crucial prefix needed to do a branching decision between two keys. This technique enables the database to fit as lot data as possible on every branch block. The branch blocks contain a pointer to the boy block include the key. The variety of keys and pointers is minimal by the block size.
The leaf blocks save on computer every indexed data value and also a corresponding rowid provided to situate the yes, really row. Each entry is sorted through (key, rowid). Within a leaf block, a vital and rowid is attached to its left and right sibling entries. The leaf blocks themselves are additionally doubly linked. In number 3-1 the leftmost leaf block (0-10) is attached to the second leaf block (11-19).
Note:Indexes in columns v character data are based on the binary values of the characters in the database character set.
In an index scan, the database retrieves a row by traversing the index, utilizing the indexed column values specified by the statement. If the database scans the index for a value, then it will discover this value in n I/Os whereby n is the height of the B-tree index. This is the simple principle behind thedesigningfairy.com Database indexes.
If a SQL explain accesses just indexed columns, then the database reads values directly from the index quite than from the table. If the statement accesses columns in enhancement to the indexing columns, climate the database supplies rowids to discover the rows in the table. Typically, the database retrieves table data by alternately reading an table of contents block and also then a table block.
See Also:thedesigningfairy.com Database performance Tuning overview for thorough information about index scans
Full index Scan
In a complete index scan, the database reads the entire index in order. A complete index scan is accessible if a predicate (WHERE clause) in the SQL statement recommendations a column in the index, and also in part circumstances when no property is specified. A full scan can remove sorting since the data is bespeak by table of contents key.
Suppose that an application runs the following query:
SELECT department_id, last_name, value FROM employeesWHERE value > 5000 order BY department_id, last_name;Also assume the department_id, last_name, and also salary space a composite vital in an index. thedesigningfairy.com Database performs a full scan the the index, reading it in sorted bespeak (ordered by department ID and last name) and filtering top top the value attribute. In this way, the database scans a set of data smaller than the employees table, i m sorry contains more columns than are contained in the query, and also avoids sorting the data.
For example, the complete scan could read the index entries together follows:
Fast full Index ScanA rapid full index scan is a full index scan in which the database accesses the data in the table of contents itself there is no accessing the table, and the database reads the table of contents blocks in no particular order.
Fast complete index scans room an alternative to a complete table scan as soon as both that the following problems are met:
The index need to contain all columns essential for the query.
A heat containing every nulls should not show up in the query an outcome set. Because that this an outcome to it is in guaranteed, at least one tower in the table of contents must have actually either:
A not NULL constraint
A predicate applied to it that stays clear of nulls native being taken into consideration in the query an outcome set
For example, an application concerns the adhering to query, which walk not encompass an order BY clause:
SELECT last_name, salaryFROM employees;The last_name column has a no null constraint. If the last name and also salary room a composite vital in one index, then a rapid full index scan have the right to read the table of contents entries to attain the asked for information:
Index range ScanAn index selection scan is an bespeak scan of an table of contents that has the complying with characteristics:
0, 1, or much more values are feasible for an index key.
The database generally uses an index selection scan to access selective data. The selectivity is the percentage of rows in the table that the query selects, through 0 meaning no rows and also 1 meaning all rows. Selectivity is tied to a query predicate, such as wherein last_name favor "A%", or a mix of predicates. A predicate becomes more selective together the value viewpoints 0 and less selective (or an ext unselective) as the value philosophies 1.
For example, a user queries employee whose last names start with A. Assume the the last_name pillar is indexed, v entries together follows:
Abel,rowidAnde,rowidAtkinson,rowidAustin,rowidAustin,rowidBaer,rowid...The database can use a range scan since the last_name pillar is mentioned in the predicate and also multiples rowids are possible for each index key. Because that example, two employees are called Austin, so two rowids are linked with the key Austin.
An index range scan have the right to be bounded top top both sides, as in a query for departments through IDs in between 10 and 40, or bounded on just one side, together in a query for IDs over 40. To scan the index, the database moves backward or forward v the sheet blocks. For example, a scan because that IDs in between 10 and 40 locates the an initial index leaf block that consists of the lowest key value the is 10 or greater. The scan then proceeds horizontally v the attached list of leaf nodes till it locates a value better than 40.
Index distinct Scan
In contrast to one index range scan, an index distinct scan must have actually either 0 or 1 rowid linked with an index key. The database performs a distinct scan once a property references all of the columns in a unique index crucial using an equality operator. One index unique scan stops handling as quickly as it find the first record due to the fact that no 2nd record is possible.
As one illustration, intend that a user runs the following query:
SELECT *FROM employeesWHERE employee_id = 5;Assume the the employee_id shaft is the primary crucial and is indexed through entries together follows:
1,rowid2,rowid4,rowid5,rowid6,rowid...In this case, the database can use one index distinctive scan to locate the rowid because that the employee who ID is 5.
Index Skip Scan
An table of contents skip scan provides logical subindexes of a composite index. The database "skips" with a solitary index as if that were searching separate indexes. Skip scanning is beneficial if there are couple of distinct worths in the leading column of a composite index and many distinctive values in the nonleading crucial of the index.
The database may choose an table of contents skip scan when the leading pillar of the composite table of contents is not specified in a query predicate. For example, assume the you operation the complying with query because that a client in the sh.customers table:
SELECT * from sh.customers whereby cust_email = "Abbey
company.com";The client table has actually a column cust_gender whose values are either M or F. Assume the a composite table of contents exists ~ above the columns (cust_gender, cust_email). Instance 3-1 reflects a part of the index entries.
Example 3-1 Composite index Entries
The database deserve to use a skip scan the this index also though cust_gender is not mentioned in the whereby clause.
In a skip scan, the number of logical subindexes is determined by the variety of distinct values in the top column. In instance 3-1, the top column has actually two feasible values. The database logically splits the index right into one subindex through the crucial F and a second subindex with the an essential M.
When looking for the record for the client whose email is Abbey
company.com, the database searches the subindex v the value F an initial and climate searches the subindex v the value M. Conceptually, the database processes the query as follows:
SELECT * native sh.customers where cust_gender = "F" and also cust_email = "Abbey
company.com"UNION ALLSELECT * native sh.customers where cust_gender = "M" and cust_email = "Abbey
Index Clustering Factor
The index clustering variable measures heat order in relationship to one indexed value such as employee critical name. The more order the exists in heat storage because that this value, the lower the clustering factor.
The clustering variable is advantageous as a turbulent measure the the number of I/Os forced to read whole table by means of one index:
If the clustering element is high, climate thedesigningfairy.com Database performs a relatively high variety of I/Os during a huge index selection scan. The table of contents entries suggest to arbitrarily table blocks, for this reason the database may have to read and also reread the very same blocks over and over again come retrieve the data pointed to by the index.
If the clustering variable is low, then thedesigningfairy.com Database performs a reasonably low number of I/Os during a big index selection scan. The index secrets in a selection tend to point to the exact same data block, therefore the database walk not have to read and also reread the exact same blocks over and also over.
The clustering variable is appropriate for table of contents scans since it deserve to show:
Whether the database will use an table of contents for huge range scans
The level of table company in relationship to the table of contents key
Whether you should think about using an index-organized table, partitioning, or table swarm if rows must be bespeak by the table of contents key
For example, assume that the employee table fits into two data blocks. Table 3-1 depicts the rows in the 2 data block (the ellipses show data that is no shown).
Table 3-1 contents of two Data blocks in the employees Table
100 Steven King SKING ... 156 Janette King JKING ...115 Alexander Khoo AKHOO ......116 Shelli Baida SBAIDA ...204 Hermann Baer HBAER ...105 David Austin DAUSTIN ...130 Mozhe Atkinson MATKINSO ...166 Sundar Ande SANDE ...174 Ellen Abel EABEL ...
|149 Eleni Zlotkey EZLOTKEY ...200 Jennifer Whalen JWHALEN ......137 Renske Ladwig RLADWIG ...173 Sundita Kumar SKUMAR ...101 Neena Kochar NKOCHHAR ...|
Rows are stored in the block in bespeak of last name (shown in bold). For example, the bottom heat in data block 1 describes Abel, the following row up explains Ande, and so on alphabetically till the optimal row in block 1 because that Steven King. The bottom heat in block 2 explains Kochar, the following row up explains Kumar, and also so ~ above alphabetically until the last row in the block for Zlotkey.
Assume that an index exists ~ above the last name column. Each name entry coincides to a rowid. Conceptually, the index entries would certainly look together follows:
Abel,block1row1Ande,block1row2Atkinson,block1row3Austin,block1row4Baer,block1row5...Assume that a different index exist on the employee i would column. Conceptually, the table of contents entries can look as follows, with employee IDs spread in virtually random places throughout the two blocks:
100,block1row50101,block2row1102,block1row9103,block2row19104,block2row39105,block1row4...Example 3-2 queries the ALL_INDEXES watch for the clustering factor for these 2 indexes. The clustering element for EMP_NAME_IX is low, which way that adjacent index entries in a solitary leaf block often tend to allude to rows in the same data blocks. The clustering variable for EMP_EMP_ID_PK is high, which way that surrounding index entries in the very same leaf block room much less likely to allude to rows in the same data blocks.
Example 3-2 Clustering Factor
SQL> choose INDEX_NAME, CLUSTERING_FACTOR 2 native ALL_INDEXES 3 where INDEX_NAME IN ("EMP_NAME_IX","EMP_EMP_ID_PK"); INDEX_NAME CLUSTERING_FACTOR-------------------- -----------------EMP_EMP_ID_PK 19EMP_NAME_IX 2
Reverse an essential Indexes
A reverse crucial index is a form of B-tree index that physically reverses the bytes of every index key while maintaining the obelisk order. Because that example, if the index an essential is 20, and also if the two bytes stored for this crucial in hexadecimal are C1,15 in a standard B-tree index, then a reverse crucial index shop the bytes as 15,C1.
Reversing the crucial solves the difficulty of contention for sheet blocks in the right side that a B-tree index. This trouble can be particularly acute in an thedesigningfairy.com actual Application swarm (thedesigningfairy.com RAC) database in which multiple instances repetitively modify the same block. Because that example, in an orders table the primary secrets for orders space sequential. One circumstances in the cluster adds order 20, while an additional adds 21, v each circumstances writing its vital to the very same leaf block ~ above the right-hand side of the index.
In a reverse an essential index, the reversal of the byte bespeak distributes inserts throughout all leaf tricks in the index. For example, keys such as 20 and also 21 the would have been nearby in a standard an essential index are currently stored far apart in different blocks. Thus, I/O because that insertions the sequential tricks is an ext evenly distributed.
Because the data in the table of contents is not sorted through column vital when the is stored, the reverse an essential arrangement eliminates the capability to run an index variety scanning questions in part cases. For example, if a user issues a query because that order IDs greater than 20, then the database cannot begin with the block include this ID and also proceed horizontally v the leaf blocks.
See Also:thedesigningfairy.com Database power Tuning overview to learn about design considerations because that reverse crucial indexes
Ascending and also Descending Indexes
In one ascending index, thedesigningfairy.com Database shop data in ascending order. By default, character data is ordered by the binary values contained in every byte of the value, numeric data from the smallest to biggest number, and date indigenous earliest to latest value.
For an instance of an ascending index, think about the following SQL statement:
CREATE table of contents emp_deptid_ix on hr.employees(department_id); thedesigningfairy.com Database sorts the hr.employees table on the department_id column. It lots the ascending index v the department_id and corresponding rowid values in ascending order, starting with 0. Once it supplies the index, thedesigningfairy.com Database searches the sorted department_id values and also uses the associated rowids to locate rows having the requested department_id value.
By clues the DESC keyword in the develop INDEX statement, girlfriend can develop a diminish index. In this case, the index stores data ~ above a specified obelisk or columns in to decrease order. If the index in figure 3-1 ~ above the employees.department_id pillar were descending, climate the leaf blocking include 250 would be top top the left side of the tree and also block through 0 ~ above the right. The default search through a descending table of contents is from highest possible to shortest value.
Descending indexes are advantageous when a query sorts part columns ascending and others descending. Because that an example, assume the you develop a composite table of contents on the last_name and department_id columns together follows:
CREATE index emp_name_dpt_ix on hr.employees(last_name ASC, department_id DESC); If a user queries hr.employees for last surname in ascending order (A come Z) and also department IDs in descending stimulate (high come low), then the database have the right to use this index to retrieve the data and avoid the extra action of sorting it.
thedesigningfairy.com Database can use key compression come compress portions of the primary crucial column worths in a B-tree index or one index-organized table. Key compression can substantially reduce the room consumed through the index.
In general, index keys have actually two pieces, a group piece and a distinctive piece. An essential compression division the index key into a prefix entry, i beg your pardon is the group piece, and a suffix entry, which is the unique or nearly unique piece. The database achieves compression by sharing the prefix entries amongst the suffix entries in an index block.
Note:If a key is not identified to have actually a distinct piece, climate the database provides one by additional a rowid to the group piece.
By default, the prefix that a unique index is composed of all key columns not included the critical one, whereas the prefix that a nonunique index consists of all an essential columns. Because that example, suppose that you produce a composite index on the oe.orders table as follows:
CREATE table of contents orders_mod_stat_ix ON assignment ( order_mode, order_status );Many repetitive values occur in the order_mode and also order_status columns. An index block may have entries as displayed in instance 3-3.
Example 3-3 table of contents Entries in orders Table
In instance 3-3, the an essential prefix would certainly consist of a concatenation of the order_mode and order_status values. If this table of contents were created with default vital compression, climate duplicate crucial prefixes such together online,0 and also online,2 would be compressed. Conceptually, the database achieves compression as displayed in the complying with example:
online,0AAAPvCAAFAAAAFaAAaAAAPvCAAFAAAAFaAAgAAAPvCAAFAAAAFaAAlonline,2AAAPvCAAFAAAAFaAAmonline,3AAAPvCAAFAAAAFaAAqAAAPvCAAFAAAAFaAAtSuffix entries form the compressed variation of index rows. Each suffix entry referrals a prefix entry, i m sorry is save on computer in the very same index block as the suffix entry.
Alternatively, you can specify a prefix size when creating a compressed index. Because that example, if you stated prefix size 1, climate the prefix would be order_mode and also the suffix would be order_status,rowid. Because that the worths in instance 3-3, the table of contents would aspect out duplicate incidents of digital as follows:
online0,AAAPvCAAFAAAAFaAAa0,AAAPvCAAFAAAAFaAAg0,AAAPvCAAFAAAAFaAAl2,AAAPvCAAFAAAAFaAAm3,AAAPvCAAFAAAAFaAAq3,AAAPvCAAFAAAAFaAAtThe index stores a particular prefix when per leaf block at most. Only keys in the leaf blocks of a B-tree index space compressed. In the branch blocks the an essential suffix have the right to be truncated, yet the vital is not compressed.
In a bitmap index, the database shop a bitmap because that each table of contents key. In a typical B-tree index, one index entry points to a single row. In a bitmap index, each index an essential stores pointers to multiple rows.
Bitmap indexes are mostly designed because that data warehousing or settings in i beg your pardon queries reference plenty of columns in an advertisement hoc fashion. Situations that may call for a bitmap table of contents include:
The indexing table is either read-only or not subject to far-ranging modification through DML statements.
For a data warehouse example, the sh.customers table has actually a cust_gender obelisk with just two feasible values: M and also F. Expect that queries because that the variety of customers of a specific gender room common. In this case, the customers.cust_gender shaft would it is in a candidate for a bitmap index.
Each bit in the bitmap synchronizes to a possible rowid. If the little bit is set, climate the row with the equivalent rowid includes the crucial value. A mapping function converts the little bit position to an yes, really rowid, so the bitmap index gives the very same functionality together a B-tree index although it offers a different internal representation.
If the indexed column in a single row is updated, then the database locks the index vital entry (for example, M or F) and not the individual bit mapped to the updated row. Since a key points to many rows, DML on indexed data commonly locks every one of these rows. Because that this reason, bitmap indexes are not ideal for plenty of OLTP applications.
Bitmap Indexes on a single Table
Example 3-4 reflects a ask of the sh.customers table. Part columns in this table are candidates because that a bitmap index.
Example 3-4 questions of customers Table
SQL> pick cust_id, cust_last_name, cust_marital_status, cust_gender 2 from sh.customers 3 whereby ROWNUM
The cust_marital_status and cust_gender columns have low cardinality, vice versa, cust_id and also cust_last_name do not. Thus, bitmap indexes might be suitable on cust_marital_status and also cust_gender. A bitmap table of contents is probably not useful for the other columns. Instead, a distinctive B-tree table of contents on this columns would likely administer the most effective representation and also retrieval.
Table 3-2 illustrates the bitmap index because that the cust_gender pillar output displayed in instance 3-4. It is composed of two separate bitmaps, one because that each gender.
Table 3-2 Sample Bitmap
A mapping function converts each little in the bitmap come a rowid the the client table. Each bit value counts on the worths of the equivalent row in the table. Because that example, the bitmap because that the M value has a 1 together its an initial bit due to the fact that the sex is M in the very first row the the client table. The bitmap cust_gender="M" has actually a 0 for its the bits in rows 2, 6, and 7 due to the fact that these rows execute not contain M as their value.
Note:Bitmap indexes can incorporate keys the consist entirely of null values, unlike B-tree indexes. Indexing nulls have the right to be helpful for some SQL statements, such together queries with the aggregate role COUNT.
An analyst investigate demographic trends of the customers might ask, "How countless of ours female client are single or divorced?" This question coincides to the complying with SQL query:
SELECT COUNT(*) from customers whereby cust_gender = "F" and also cust_marital_status IN ("single", "divorced"); Bitmap indexes can process this query effectively by counting the variety of 1 worths in the result bitmap, as illustrated in Table 3-3. To determine the client who fulfill the criteria, thedesigningfairy.com Database deserve to use the result bitmap to access the table.
Table 3-3 Sample Bitmap
single or divorced, and F
Bitmap indexing successfully merges indexes that correspond to several problems in a where clause. Rows that accomplish some, but not all, problems are filtered out prior to the table chin is accessed. This an approach improves solution time, often dramatically.
Bitmap join Indexes
A bitmap sign up with index is a bitmap index for the sign up with of two or more tables. For each value in a table column, the table of contents stores the rowid that the matching row in the indexing table. In contrast, a standard bitmap table of contents is developed on a single table.
A bitmap sign up with index is one efficient way of reducing the volume of data that need to be joined by performing limitations in advance. For an instance of when a bitmap sign up with index would certainly be useful, assume the users regularly query the variety of employees through a details job type. A common query might look together follows:
SELECT COUNT(*) from employees, tasks WHERE employees.job_id = jobs.job_id and also jobs.job_title = "Accountant";The coming before query would generally use an index on jobs.job_title to retrieve the rows because that Accountant and also then the job ID, and also an table of contents on employees.job_id to discover the matching rows. To retrieve the data indigenous the table of contents itself rather than from a scan that the tables, you might create a bitmap join index together follows:
CREATE BITMAP index employees_bm_idx ON employees (jobs.job_title) indigenous employees, jobsWHERE employees.job_id = jobs.job_id;As illustrated in number 3-2, the index an essential is jobs.job_title and the indexing table is employees.
Figure 3-2 Bitmap sign up with Index
Conceptually, employees_bm_idx is an index of the jobs.title column in the SQL query shown in example 3-5 (sample calculation included). The job_title vital in the index points come rows in the employees table. A ask of the variety of accountants deserve to use the table of contents to avoid accessing the employees and also jobs tables due to the fact that the table of contents itself contains the asked for information.
Example 3-5 sign up with of employees and also jobs Tables
SELECT jobs.job_title together "jobs.job_title", employees.rowid together "employees.rowid"FROM employees, jobsWHERE employees.job_id = jobs.job_idORDER through job_title; jobs.job_title employees.rowid----------------------------------- ------------------Accountant AAAQNKAAFAAAABSAALAccountant AAAQNKAAFAAAABSAANAccountant AAAQNKAAFAAAABSAAMAccountant AAAQNKAAFAAAABSAAJAccountant AAAQNKAAFAAAABSAAKAccounting Manager AAAQNKAAFAAAABTAAHAdministration Assistant AAAQNKAAFAAAABTAACAdministration Vice chairman AAAQNKAAFAAAABSAACAdministration Vice president AAAQNKAAFAAAABSAAB...
In a data warehouse, the join condition is one equijoin (it supplies the equality operator) between the primary vital columns of the dimension tables and the foreign key columns in the fact table. Bitmap join indexes are periodically much an ext efficient in storage 보다 materialized join views, an alternate for materializing join in advance.
See Also:thedesigningfairy.com Database Data Warehousing overview for much more information ~ above bitmap sign up with indexes
Bitmap warehouse Structure
thedesigningfairy.com Database provides a B-tree index structure to keep bitmaps for each indexing key. For example, if jobs.job_title is the an essential column that a bitmap index, climate the index data is save on computer in one B-tree. The separation, personal, instance bitmaps space stored in the sheet blocks.
Assume that the jobs.job_title obelisk has distinct values Shipping Clerk, stock Clerk, and several others. A bitmap index entry because that this index has actually the complying with components:
The project title as the table of contents key
A short rowid and high rowid because that a variety of rowids
A bitmap for details rowids in the range
Conceptually, an index sheet block in this index can contain entries together follows:
Shipping Clerk,AAAPzRAAFAAAABSABQ,AAAPzRAAFAAAABSABZ,0010000100Shipping Clerk,AAAPzRAAFAAAABSABa,AAAPzRAAFAAAABSABh,010010Stock Clerk,AAAPzRAAFAAAABSAAa,AAAPzRAAFAAAABSAAc,1001001100Stock Clerk,AAAPzRAAFAAAABSAAd,AAAPzRAAFAAAABSAAt,0101001001Stock Clerk,AAAPzRAAFAAAABSAAu,AAAPzRAAFAAAABSABz,100001...The same task title shows up in lot of entries since the rowid variety differs.
Assume the a conference updates the job ID of one employee from Shipping salesman to share Clerk. In this case, the session requires exclusive access to the index key entry for the old value (Shipping Clerk) and also the new value (Stock Clerk). thedesigningfairy.com Database locks the rows pointed come by these two entries—but no the rows pointed to by Accountant or any type of other key—until the update commits.
The data because that a bitmap index is stored in one segment. thedesigningfairy.com Database stores every bitmap in one or more pieces. Each piece occupies component of a solitary data block.
See Also:"User Segments"
You can produce indexes ~ above functions and also expressions that involve one or more columns in the table gift indexed. A function-based index computes the worth of a function or expression entailing one or much more columns and stores that in the index. A function-based index deserve to be one of two people a B-tree or a bitmap index.
The duty used for building the index deserve to be an arithmetic expression or an expression that has a SQL function, user-defined PL/SQL function, package function, or C callout. Because that example, a role could include the values in two columns.
Uses of Function-Based Indexes
Function-based indexes are efficient for evaluating statements that contain attributes in their wherein clauses. The database only supplies the function-based index as soon as the role is consisted of in a query. When the database procedures INSERT and UPDATE statements, however, it need to still advice the role to process the statement.
For example, mean you produce the complying with function-based index:
CREATE table of contents emp_total_sal_idx ON employees (12 * salary * commission_pct, salary, commission_pct);The database have the right to use the coming before index as soon as processing queries together as instance 3-6 (partial sample calculation included).
Example 3-6 questions Containing an Arithmetic Expression
SELECT employee_id, last_name, first_name, 12*salary*commission_pct as "ANNUAL SAL"FROM employeesWHERE (12 * value * commission_pct)
Function-based indexes defined on the SQL attributes UPPER(column_name) or LOWER(column_name) facilitate case-insensitive searches. Because that example, expect that the first_name shaft in employees includes mixed-case characters. You develop the following function-based table of contents on the hr.employees table:
CREATE index emp_fname_uppercase_idx ON employees ( UPPER(first_name) ); The emp_fname_uppercase_idx index can facilitate queries such together the following:
SELECT * native employeesWHERE UPPER(first_name) = "AUDREY";A function-based table of contents is additionally useful because that indexing only specific rows in a table. For example, the cust_valid shaft in the sh.customers table has either i or A as a value. To index only the A rows, you might write a role that returns a null worth for any type of rows various other than the A rows. You could create the index together follows:
CREATE table of contents cust_valid_idxON client ( situation cust_valid once "A" climate "A" finish );
Optimization through Function-Based Indexes
The optimizer can use an index variety scan top top a function-based index because that queries v expressions in where clause. The selection scan accessibility path is especially valuable when the property (WHERE clause) has actually low selectivity. In example 3-6 the optimizer can use one index variety scan if an table of contents is built on the expression 12*salary*commission_pct.
A virtual shaft is advantageous for speeding accessibility to data derived from expressions. Because that example, you can define virtual tower annual_sal together 12*salary*commission_pct and create a function-based table of contents on annual_sal.
The optimizer performs expression equivalent by parsing the expression in a SQL statement and then comparing the expression tree of the statement and also the function-based index. This compare is case-insensitive and ignores blank spaces.
Application Domain Indexes
An applications domain index is a customized index details to an application. thedesigningfairy.com Database provides extensible indexing to execute the following:
Make usage of dedicated indexing techniques
You can encapsulate application-specific index management routines as an indextype schema object and define a domain index on table columns or qualities of things type. Extensible indexing have the right to efficiently procedure application-specific operators.
The applications software, referred to as the cartridge, controls the structure and content the a domain index. The database interacts with the applications to build, maintain, and search the domain index. The index structure itself can be save on computer in the database together an index-organized table or externally as a file.
See Also:thedesigningfairy.com Database Data Cartridge Developer"s guide for information around using data cartridges in ~ the thedesigningfairy.com Database extensibility architecture
thedesigningfairy.com Database stores table of contents 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 because that each value indexed.
The tablespace of an table of contents segment is either the default tablespace of the owner or a tablespace specifically named in the create INDEX statement. For ease of management you can store an table of contents in a different tablespace native its table. For example, friend may pick not to back up tablespaces containing only indexes, which can be rebuilt, and so decrease the time and storage forced for backups.
See Also:Chapter 12, "Logical storage Structures"
Overview that Index-Organized Tables
An index-organized table is a table save in a variation of a B-tree index structure. In a heap-organized table, rows are inserted where lock fit. In an index-organized table, rows are stored in one index characterized on the primary crucial for the table. Every index entrance in the B-tree likewise stores the non-key shaft values. Thus, the index is the data, and the data is the index. Applications manipulate index-organized tables just like heap-organized tables, making use of SQL statements.
For an analogy of an index-organized table, suppose a human resources manager has actually a book case of cardboard boxes. Each box is labeled v a number—1, 2, 3, 4, and also so on—but the boxes execute not sit ~ above the shelves in sequential order. Instead, each box contains a guideline to the shelf location of the following box in the sequence.
Folders comprise employee records are stored in each box. The folders room sorted by employee ID. Employee King has actually ID 100, which is the shortest ID, so his folder is in ~ the bottom of box 1. The folder for employee 101 is on height of 100, 102 is on optimal of 101, and also so on till box 1 is full. The next folder in the sequence is at the bottom of box 2.
In this analogy, notified folders by employee ID provides it possible to search efficiently for folders without having actually to preserve a separate index. Mean a user inquiry the documents for employee 107, 120, and 122. Rather of searching an table of contents in one step and also retrieving the folders in a different step, the manager have the right to search the folders in sequential order and also retrieve every folder as found.
Index-organized tables provide faster access to table rows by primary an essential or a precious prefix the the key. The existence of non-key columns the a row in the sheet block avoids secondary data block I/O. Because that example, the value of employee 100 is save in the index heat itself. Also, since rows are stored in primary key order, variety access by the primary vital or prefix requires minimal block I/Os. One more benefit is the avoidance the the an are overhead the a separate primary vital index.
Index-organized tables are useful when associated pieces the data have to be stored with each other or data should be physically save in a details order. This kind of table is often used for info retrieval, spatial (see "Overview of thedesigningfairy.com Spatial"), and OLAP applications (see "OLAP").
Index-Organized Table Characteristics
The database system performs all operations on index-organized tables through manipulating the B-tree index structure. Table 3-4 summarizes the differences in between index-organized tables and heap-organized tables.
Table 3-4 to compare of Heap-Organized Tables v Index-Organized Tables
The rowid unique identifies a row. Primary key constraint may optionally be defined.
Primary an essential uniquely identifies a row. Primary crucial constraint need to be defined.
Physical rowid in ROWID pseudocolumn permits building second indexes.
Logical rowid in ROWID pseudocolumn allows building secondary indexes.
Individual rows might be accessed straight by rowid.
Access to individual rows may be achieved indirectly by primary key.
Sequential full table scan returns all rows in part order.
A full index scan or rapid full table of contents scan returns every rows in part order.
Can it is in stored in a table cluster with other tables.
Cannot be stored in a table cluster.
Can contain a column of the long data form and columns that LOB data types.
Can contain LOB columns however not long columns.
Can contain online columns (only relational heap tables room supported).
Cannot contain online columns.
Figure 3-3 illustrates the framework of one index-organized departments table. The sheet blocks contain the rows of the table, ordered sequentially by main key. Because that example, the first value in the first leaf block shows a department ID that 20, department surname of Marketing, manager identifier of 201, and location identifier of 1800.
Figure 3-3 Index-Organized Table
An index-organized table stores all data in the very same structure and also does not should store the rowid. As presented in figure 3-3, sheet block 1 in one index-organized table might contain entries as follows, ordered by primary key:
20,Marketing,201,180030,Purchasing,114,1700Leaf block 2 in one index-organized table can contain entries together follows:
50,Shipping,121,150060,IT,103,1400A scan that the index-organized table rows in primary key order reads the blocks in the following sequence:
To comparison data accessibility in a heap-organized table come an index-organized table, expect block 1 the a heap-organized department table segment has rows as follows:
50,Shipping,121,150020,Marketing,201,1800Block 2 has rows for the exact same table as follows:
30,Purchasing,114,170060,IT,103,1400A B-tree index sheet block for this heap-organized table includes the complying with entries, where the very first value is the primary crucial and the second is the rowid:
20,AAAPeXAAFAAAAAyAAD30,AAAPeXAAFAAAAAyAAA50,AAAPeXAAFAAAAAyAAC60,AAAPeXAAFAAAAAyAABA scan the the table rows in primary vital order reads the table segment blocks in the following sequence:
Thus, the variety of block I/Os in this example is twin the number in the index-organized example.
Index-Organized Tables with Row Overflow Area
When producing an index-organized table, you can specify a separate segment together a row overflow area. In index-organized tables, B-tree table of contents entries deserve to be huge because they contain whole row, for this reason a separate segment to contain the entries is useful. In contrast, B-tree entries are usually little because castle consist of the crucial and rowid.
If a heat overflow area is specified, then the database have the right to divide a row in an index-organized table into the adhering to parts:
The index entry
This component contains tower values for all the primary vital columns, a physical rowid the points come the overflow component of the row, and optionally a couple of of the non-key columns. This component is stored in the table of contents segment.
The overflow part
This component contains pillar values because that the continuing to be non-key columns. This component is save in the overflow warehouse area segment.
Secondary Indexes top top Index-Organized Tables
A an additional index is an table of contents on one index-organized table. In a sense, that is an index on one index. The secondary index is an independent schema object and is stored separately from the index-organized table.
As described in "Rowid Data Types", thedesigningfairy.com Database uses row identifiers called logical rowids for index-organized tables. A logical rowid is a base64-encoded depiction of the table major key. The logical rowid size depends top top the primary an essential length.
Rows in index sheet blocks can move in ~ or between blocks due to the fact that of insertions. Rows in index-organized tables perform not migrate as heap-organized rows execute (see "Chained and also Migrated Rows"). Due to the fact that rows in index-organized tables perform not have permanent physics addresses, the database uses logical rowids based upon primary key.
For example, assume the the departments table is index-organized. The location_id tower stores the identifier of every department. The table stores rows as follows, with the last worth as the place ID:
10,Administration,200,170020,Marketing,201,180030,Purchasing,114,170040,Human Resources,203,2400A secondary index on the location_id column might have index entries as follows, whereby the value complying with the comma is the reasonable rowid:
1700,*BAFAJqoCwR/+ 1700,*BAFAJqoCwQv+1800,*BAFAJqoCwRX+2400,*BAFAJqoCwSn+Secondary indexes carry out fast and also efficient access to index-organized tables utilizing columns that are neither the primary crucial nor a prefix the the main key. Because that example, a ask of the names of departments who ID is greater than 1700 could use the second index to speed data access.
Logical Rowids and also Physical Guesses
Secondary indexes use the logical rowids to situate table rows. A logical rowid contains a physics guess, i m sorry is the physics rowid that the table of contents entry when it was first made. thedesigningfairy.com Database can use physical guesses come probe straight into the sheet block of the index-organized table, bypassing the primary an essential 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 requires a scan of the second index and second I/O to fetch the data block include the row. Because that index-organized tables, accessibility by a an additional index varies, depending upon the use and accuracy of physics guesses:
Without physical guesses, access involves two index scans: a scan that the secondary index complied with by a scan the the primary vital index.
With physical guesses, accessibility depends on their accuracy:
With exact physical guesses, access involves a secondary index scan and second I/O come fetch the data block containing the row.
With inaccurate physical guesses, accessibility involves a an additional index scan and also an I/O to fetch the not correct data block (as shown by the guess), adhered to by one index unique scan of the index arranged table by primary vital value.
Bitmap Indexes ~ above Index-Organized Tables
A second index on one index-organized table can be a bitmap index. As explained in "Bitmap Indexes", a bitmap table of contents stores a bitmap because that each table of contents key.
When bitmap indexes exist on one index-organized table, all the bitmap indexes usage a heap-organized mapping table. The mapping table stores the reasonable rowids of the index-organized table. Each mapping table row stores one reasonable rowid for the matching index-organized table row.
The database accesses a bitmap index utilizing a search key. If the database finds the key, then the bitmap entry is convert to a physical rowid. With heap-organized tables, the database offers the physics rowid to accessibility the basic table. V index-organized tables, the database offers the physics rowid to access the mapping table, which subsequently yields a logical rowid the the database uses to accessibility the index-organized table. Figure 3-4 illustrates index accessibility for a questions of the departments_iot table.
See more: Chicago Bulls Vs New York Knicks Vs Chicago Bulls Feb 3, 2021 Game Summary
Figure 3-4 Bitmap table of contents on Index-Organized Table
Note:Movement the rows in one index-organized table does no leave the bitmap indexes developed on the index-organized table unusable.