Introduction to Access Paths.
関数を使ってselect文をパワーアップさせる さらっと覚えるsql＆t-sql入門（3） 列の合計や平均、日付の計算といった面倒な処理を一気に解決して. SQL Serverというブラックボックスを開いてみる Dr. K's SQL Serverチューニング研修（1） 不可能だと思われていたSQL Serverチューニングに独自ノウハウ.
This sequencing illustrates the linked lists that connect the index entries. However, index blocks need not be stored in order within an index segment. For example, the block could appear anywhere in the segment, including directly before the block. The database must read an index block to determine which index block it must read next. The index block body stores the index entries in a heap, just like table rows.
For example, if the value 10 is inserted first into a table, then the index entry with key 10 might be inserted at the bottom of the index block. If 0 is inserted next into the table, then the index entry for key 0 might be inserted on top of the entry for Thus, the index entries in the block body are not stored in key order.
However, within the index block, the row header stores records in key order. For example, the first record in the header points to the index entry with key 0 , and so on sequentially up to the record that points to the index entry with key Thus, index scans can read the row header to determine where to begin and end range scans, avoiding the necessity of reading every entry in the block.
Oracle Database Concepts to learn about index blocks. In a nonunique index, the database stores the rowid by appending it to the key as an extra column. The entry adds a length byte to make the key unique. For example, the first index key in the nonunique index shown in Figure is the pair 0 ,rowid and not simply 0. The database sorts the data by index key values and then by rowid ascending.
For example, the entries are sorted as follows:. In a unique index, the index key does not include the rowid. The database sorts the data only by the index key values, such as 0 , 1 , 2 , and so on. Oracle Database Concepts for an overview of unique and nonunique indexes.
B-tree indexes never store completely null keys, which is important for how the optimizer chooses access paths. A consequence of this rule is that single-column B-tree indexes never store nulls. An example helps illustrate. The following example shows that the optimizer chooses a full table scan for a query of all department IDs in hr.
The optimizer cannot use the index on employees. The following example shows that the optimizer chooses an index scan when the predicate excludes null values:.
A unique or primary key constraint is insufficient by itself to produce an index unique scan. Consider the following example, which creates a primary key constraint on a column with a non-unique index, resulting in an index range scan rather than an index unique scan:.
Oracle Database Concepts for more details on index structures and for detailed information on how a B-tree is searched. The scan searches the index in order for the specified key. An index unique scan stops processing as soon as it finds the first record because no second record is possible.
The database obtains the rowid from the index entry, and then retrieves the row specified by the rowid. The following figure illustrates an index unique scan. This example uses a unique scan to retrieve a row from the products table.
The following statement queries the record for product 19 in the sh. Because a primary key index exists on the products. The range in the scan can be bounded on both sides, or unbounded on one or both sides. The optimizer typically chooses a range scan for selective queries. By default, the database stores indexes in ascending order, and scans them in the same order. An index range scan descending is identical to an index range scan except that the database returns rows in descending order.
Usually, the database uses a descending scan when ordering data in a descending order, or when seeking a value less than a specified value. Examples of conditions include:. If the optimizer chooses a full table scan or another index, then a hint may be required to force this access path. In some cases, an index scan reads a set of index blocks, sorts the rowids, and then reads a set of table blocks. Thus, to scan the index, the database moves backward or forward through the leaf blocks.
For example, a scan for IDs between 20 and 40 locates the first leaf block that has the lowest key value that is 20 or greater. The scan proceeds horizontally through the linked list of leaf nodes until it finds a value greater than 40, and then stops. The following figure illustrates an index range scan using ascending order.
In this example, 2 index entries for department 20 exist. This example retrieves a set of values from the employees table using an index range scan. The following statement queries the records for employees in department 20 with salaries greater than This example uses an index to retrieve rows from the employees table in sorted order. The following statement queries the records for employees in department 20 in descending order:.
The database locates the first index leaf block that contains the highest key value that is 20 or less. The scan then proceeds horizontally to the left through the linked list of leaf nodes. The database obtains the rowid from each index entry, and then retrieves the row specified by the rowid.
An index full scan reads the entire index in order. An index full scan can eliminate a separate sorting operation because the data in the index is ordered by index key. The database reads the root block, and then navigates down the left hand side of the index or right if doing a descending full scan until it reaches a leaf block. Then the database reaches a leaf block, the scan proceeds across the bottom of the index, one block at a time, in sorted order.
The following graphic illustrates an index full scan. The database locates the first index leaf block, and then proceeds horizontally to the right through the linked list of leaf nodes. For each index entry, the database obtains the rowid from the entry, and then retrieves the table row specified by the rowid. An index fast full scan reads the index blocks in unsorted order, as they exist on disk. This scan does not use the index to probe the table, but reads the index instead of the table, essentially using the index itself as a table.
Unlike a full scan, a fast full scan cannot eliminate a sort operation because it does not read the index in order. The databases ignores the branch and root blocks and reads the index entries on the leaf blocks. An index skip scan occurs when the initial column of a composite index is "skipped" or not specified in the query. Often, skip scanning index blocks is faster than scanning table blocks, and faster than performing full index scans.
Few distinct values exist in the leading column of the composite index, but many distinct values exist in the nonleading key of the index. The number of distinct values in the leading columns of the index determines the number of logical subindexes. The lower the number, the fewer logical subindexes the optimizer must create, and the more efficient the scan becomes. The scan reads each logical index separately, and "skips" index blocks that do not meet the filter condition on the non-leading column.
This example uses an index skip scan to satisfy a query of the customers table. Conceptually, a portion of the index might look as follows, with the gender value of F or M as the leading edge of the index.
You run the following query for a customer in the sh. The database logically splits the index into two. One subindex has the key F , with entries in the following form:. The second subindex has the key M , with entries in the following form:. When searching for the record for the customer whose email is Abbey company. Conceptually, the database processes the query as follows:. Oracle Database Concepts to learn more about skip scans. An index join scan is a hash join of multiple indexes that together return all columns requested by a query.
The database does not need to access the table because all data is retrieved from the indexes. A hash join of multiple indexes retrieves all data requested by the query, without requiring table access. The cost of retrieving rows from the table is higher than reading the indexes without retrieving rows from the table.
An index join is often expensive. For example, when scanning two indexes and joining them, it is often less costly to choose the most selective index, and then probe the table. An index join involves scanning multiple indexes, and then using a hash join on the rowids obtained from these scans to return the rows.
In an index join scan, table access is always avoided. For example, the process for joining two indexes on a single table is as follows:. The following statement queries the last name and email for employees whose last name begins with A , specifying an index join:. The database uses a hash join to search both sets of rowids for matches.
This section explains how bitmap indexes, and describes some of the more common bitmap index access paths:. About Bitmap Index Access. Bitmap Conversion to Rowid. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, the key is the combination of the indexed data and the rowid range.
The database stores at least one bitmap for each index key. Each value in the bitmap, which is a series of 1 and 0 values, points to a row within a rowid range. Thus, in a bitmap index, one index entry points to a set of rows rather than a single row. In an entry of the index on the employees. In an entry of the index on the customers. The database stores a bitmap index in a B-tree structure. The database can search the B-tree quickly on the first part of the key, which is the set of attributes on which the index is defined, and then obtain the corresponding rowid range and bitmap.
Oracle Database Concepts for an overview of bitmap indexes. Oracle Database Data Warehousing Guide for more information about bitmap indexes. Bitmap indexes are suitable for low distinct cardinality data that is infrequently modified. Data has low cardinality when the number of distinct values in a column is low in relation to the total number of rows.
In general, B-tree indexes are suitable for high cardinality data. The optimizer might choose a B-tree index for a query that returns few rows. In contrast, a bitmap index is suitable for low distinct cardinality data.
If the table contains million rows, then a query for all female customers is unselective, and therefore a candidate for bitmap index access.
Bitmap indexes are a useful way to speed ad hoc queries in a data warehouse. Specifically, bitmap indexes are useful access paths in queries that contain the following:. Before the table itself is accessed, the database filters out rows that satisfy some, but not all, conditions. Combining bitmap indexes on low cardinality columns makes these operations more efficient.
The database can merge bitmaps from bitmap indexes very quickly. The database can convert 1 values in the merged bitmap into rowids efficiently. Unlike B-tree indexes, bitmap indexes can contain nulls. Queries that count the number of nulls in a column can use the bitmap index without scanning the table.
Bitmap indexes are not useful for tables that experience heavy DML. The reason is that one index key points to many rows. If a session modifies the indexed data, then the database cannot lock a single bit in the bitmap: For example, if the marital status of a specific customer changes from single to married , then the database must get exclusive access to the single index entry and married index entry in the bitmap. For a particular value in a bitmap, the value is 1 if the row values match the bitmap condition, and 0 if it does not.
Based on these values, the database uses an internal algorithm to map bitmaps onto rowids. The bitmap entry contains the indexed value, the rowid range start and end rowids , and a bitmap. Each 0 or 1 value in the bitmap is an offset into the rowid range, and maps to a potential row in the table, even if the row does not exist. Because the number of possible rows in a block is predetermined, the database can use the range endpoints to determine the rowid of an arbitrary row in the range.
The Hakan factor is an optimization used by the bitmap index algorithms to limit the number of rows that Oracle Database assumes can be stored in a single block. By artificially limiting the number of rows, the database reduces the size of the bitmaps.
Table shows part of a sample bitmap for the sh. The actual index has 12 distinct values. Only 3 are shown in the sample: As shown in Table , bitmap indexes can include keys that consist entirely of null values, unlike B-tree indexes. Oracle Database Concepts to learn about rowid formats.
A bitmap join index is a bitmap index for the join of two or more tables. The optimizer can use a bitmap join index to reduce or eliminate the volume of data that must be joined during plan execution. Bitmap join indexes can be much more efficient in storage than materialized join views. The following example creates a bitmap index on the sh. Each key value in the index represents a possible city in the customers table.
Conceptually, key values for the index might look as follows, with one bitmap associated with each key value:. Each bit in a bitmap corresponds to one row in the sales table. In the Smithville key, the value 1 means that the first row in the sales table corresponds to a product sold to a Smithville customer, whereas the value 0 means that the second row corresponds to a product not sold to a Smithville customer.
The following plan shows that the database reads the Smithville bitmap to derive the number of Smithville sales Step 4 , thereby avoiding a join of the customers and sales tables.
A bitmap index resides in a B-tree structure, using branch blocks and leaf blocks just as in a B-tree. For example, if the customers. Alternatively, a single branch block could point to a leaf block containing all 12 distinct keys. Each indexed column value may have one or more bitmap pieces, each with its own rowid range occupying a contiguous set of rows in one or more extents. The database can use a bitmap piece to break up an index entry that is large relative to the size of a block. For example, the database could break a single index entry into three pieces, with the first two pieces in separate blocks in the same extent, and the last piece in a separate block in a different extent.
To conserve space, Oracle Database can compression consecutive ranges of 0 values. A bitmap conversion translates between an entry in the bitmap and a row in a table. The optimizer uses a conversion whenever it retrieves a row from a table using a bitmap index entry. Each field in Table has the value 1 or 0 , and represents a column value in a row. Conceptually, the bitmap conversion uses an internal algorithm that says, "Field F in the bitmap corresponds to the N th row of the M th block of the table," or "The N th row of the M th block in the table corresponds to field F in the bitmap.
In this example, the optimizer chooses a bitmap conversion operation to satisfy a query using a range predicate. A query of the sh. The following plan shows that the database uses a range scan to find all key values less than Step 3 , converts the 1 values in the bitmap to rowids Step 2 , and then uses the rowids to obtain the rows from the customers table Step The query scans a single bitmap for positions containing a 1 value.
The database converts the 1 values into rowids, and then uses the rowids to find the rows. The database only needs to process a single bitmap. For example, the following table represents the bitmap index in two bitmap pieces for the value widowed in the sh. To satisfy a query of customers with the status widowed , the database can search for each 1 value in the widowed bitmap and find the rowid of the corresponding row.
In this example, the optimizer chooses a bitmap index single value operation to satisfy a query that uses an equality predicate. The following plan shows that the database reads the entry with the Widowed key in the customers bitmap index Step 3 , converts the 1 values in the bitmap to rowids Step 2 , and then uses the rowids to obtain the rows from the customers table Step For example, the following table represents three values in the bitmap index for the sh.
If a query requests all customers born before , then the database can scan this index for values lower than , and then obtain the rowids for rows that have a 1. The optimizer typically uses a bitmap merge to combine bitmaps generated from a bitmap index range scan. A merge uses a Boolean OR operation between two bitmaps. The resulting bitmap selects all rows from the first bitmap, plus all rows from every subsequent bitmap.
A query might select all customers born before The following example shows sample bitmaps for three customers. If any position in any bitmap has a 1 , then the merged bitmap has a 1 in the same position. Otherwise, the merged bitmap has a 0. The 1 values in resulting bitmap correspond to rows that contain the values , , or The result is a single bitmap that contains 1 values for the requested rows Step 3. A table cluster is a group of tables that share common columns and store related data in the same blocks.
When tables are clustered, a single data block can contain rows from multiple tables. Oracle Database Concepts for an overview of table clusters. The cluster index is a B-tree index on the cluster key. A cluster scan retrieves all rows that have the same cluster key value from a table stored in an indexed cluster.
In an indexed cluster, the database stores all rows with the same cluster key value in the same data block. For example, if the hr. The B-tree cluster index associates the cluster key value with the database block address DBA of the block containing the data. For example, the index entry for key 30 shows the address of the block that contains rows for employees in department When a user requests rows in the cluster, the database scans the index to obtain the DBAs of the blocks containing the rows.
Oracle Database then locates the rows based on these DBAs. As user hr , you create a table cluster, cluster index, and tables in the cluster as follows:. To perform the scan, Oracle Database first obtains the rowid of the row describing department 30 by scanning the cluster index Step 2. Oracle Database then locates the rows in employees2 using this rowid Step 1. Oracle Database Concepts to learn about indexed clusters.
A hash cluster is like an indexed cluster, except the index key is replaced with a hash function. No separate cluster index exists. In a hash cluster, the data is the index. The database uses a hash scan to locate rows in a hash cluster based on a hash value. To perform a hash scan of the cluster, Oracle Database first obtains the hash value by applying a hash function to a cluster key value specified by the statement.
Oracle Database then scans the data blocks containing rows with this hash value. To perform a hash scan, Oracle Database first obtains the hash value by applying a hash function to the key value 30 , and then uses this hash value to scan the data blocks and retrieve the rows Step 1. Oracle Database Concepts to learn about hash clusters. This chapter contains the following topics: Introduction to Access Paths A row source is a set of rows returned by a step in an execution plan.
In contrast, a join operation is binary and receives inputs from two row sources The database uses different access paths for different relational data structures. Table Access Paths A table is the basic unit of data organization in an Oracle database. Relational tables have with the following organizational characteristics: This section explains optimizer access paths for heap-organized tables, and contains the following topics: About Heap-Organized Table Access By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order.
Row Storage in Data Blocks and Segments: A Primer The database stores rows in data blocks. Importance of Rowids for Row Access Every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. Full Table Scans A full table scan reads all rows from a table, and then filters out those rows that do not meet the selection criteria.
When the Optimizer Considers a Full Table Scan In general, the optimizer chooses a full table scan when it cannot use a different access path, or another usable access path is higher cost. The following table shows typical reasons for choosing a full table scan. If no index exists, then the optimizer uses a full table scan. Oracle Database Concepts The query predicate applies a function to the indexed column. Oracle Database Reference The table has a high degree of parallelism.
Oracle Database Reference The query uses a full table scan hint. How a Full Table Scan Works In a full table scan, the database sequentially reads every formatted block under the high water mark. Example The following statement queries salaries over in the hr. Table Access by Rowid A rowid is an internal representation of the storage location of data. When the Optimizer Chooses Table Access by Rowid In most cases, the database accesses a table by rowid after a scan of one or more indexes.
How Table Access by Rowid Works To access a table by rowid, the database performs the following steps: Obtains the rowids of the selected rows, either from the statement WHERE clause or through an index scan of one or more indexes Table access may be needed for columns in the statement not present in the index. Locates each selected row in the table based on its rowid. Table Access by Rowid: Example Assume run the following query: Sample Table Scans A sample table scan retrieves a random sample of data from a simple table or a complex SELECT statement, such as a statement involving joins and views.
In-Memory Query Controls The following database initialization parameters affect the in-memory features: Fixed an issue where a malformed UDP login packet could cause the server to crash. This vulnerability could be used in a denial of service attack against Advantage servers.
The loss of data or any form of data corruption is not likely. Execution of malicious code is not possible. Changed a error into a error produced when a differential backup destination table or dictionary is read-only. An example statement that failed to executed was: Fixed a bug that allowed package names to contains spaces. Fixed a bug in the SQL query engine that caused poor query performance when a view or subquery was used as part of a multi-table join.
Fixed a bug that caused poor SQL query performance when the join condition was only partially optimized. Fixed a bug that prevented cartesian joins in SQL from being canceled.
Fixed a bug that caused a error when using ARC to create a User Defined Function that has a Double type as an input parameter or return value. The problem occurred when the select list had only constant or literal values.
Fixed a bug in the SQL engine that caused a failure when a precision numeric value was used as the input parameter for the sqrt , log , log10, and atan2 scalars. Fixed a bug that allowed applications to store invalid IEEE floating point values in fields of type double.
Fixed an issue that could cause a and error when multiple local server threads or processes were logging in as ADSSYS at the same time.
Fixed an issue where a subindex build on a shared master index could return a error if other users modified the master index while the subindex was being built. Updated the server to not log spurious and errors during replication. Fixed an error logging issue that caused the server to log a error in a situation caused by a client communication error. It now logs a class error and continues processing. Fixed an issue that can lead to a server crash after a corrupt index file is opened.
Admin groups to restore the database. Fixed an issue where the server could write a crash dump file when shutting down with active users currently committing a transaction. Fixed a bug that could cause periodic delay on a busy server performing a large number of updates.
This change was made to work around memory leaks in DLLs created with Delphi. Fixed a error when canceling a query that was in the process of building a temporary index. Fixed a error when opening a Visual FoxPro DBF table when the number of fields is greater than the stored information in the data dictionary. Fixed a bug that caused a error to be logged when replicating a DBF table with memo data that had been updated inside a transaction.
Fixed a bug that caused a table-autocreation failure when the table was encrypted and the operation being performed was inside transaction. For example, when a trigger is being fired and it requires an encrypted table to be auto-created. Fixed a bug where a cascading RI update would fail if the primary key was a compound key and one of fields in the compound key was an autoinc field. Addressed an issue in the server that could result in large numbers of errors being logged.
Fixed a bug that could result in an access violation in the server or in the client code or possibly result in a being returned to the client. Fixed a bug in the server that made it possible for an access violation to occur if an invalid authentication request was made for an existing data dictionary connection. Fixed a bug that could result in a server crash if the WHERE clause of a query was slightly less than bytes in length. Additional conditions had to be met for this to occur: Fixed a error when using VFP tables with after insert triggers.
Fixed an error that can cause a to be logged when shutting down a server with an expired evaluation license. Fixed an issue that could lead to an incorrect error when executing certain SQL statements that reference DBF tables with numeric fields.
Modified the server to not generate crash dump when invalid data is received from the connection request listening socket. Fixed an issue where backup and table creation could sometimes create filenames with all uppercase characters, rather than preserving the character case.
The mixed cased file name may cause table open failure on Linux server. Fixed an issue where a reindex operation would lose index conditions on IDX index files. Fixed an issue where running multiple backups at the same time would report blurry snapshots error even when other threads where not active and could not affect the validity of the backup image.
Fixed a bug in the server that caused OEM data in DBF tables to be replicated incorrectly if the table was replicated to more than one target. Fixed a error when cleaning up a failed transaction on an FTS index at startup. Modified replication processing algorithm for improved performance when there are many deleted records in the replication queue.
Fixed a memory leak that can be caused by replicating OEM memo data to multiple targets. The error only exists in v9.
Fixed a rare deadlock situation with referential integrity. Changed the DBF header date to be compatible with 3rd party utilities. Fixed an issue that could cause the server to deadlock and hang when shutting down with active users. Fixed an issue where using a special character in a column alias name could cause the server to hang. Fixed an issue where using a binary parameter or variable inside a user defined function UDF could sometimes result in a native error , "Memory Allocation Error".
Fixed a bug that caused the SQL engine to erroneously short circuit the execution of a user defined function. Fixed a performance issue when executing a query that used a temporary index on a rapidly changing table. Fixed a bug that caused query performance to degrade when the data distribution in the underlying tables changed. The query optimizer was using stale information to generate the execution plan.
Fixed issue where time value comparison behavior in SQL changed from v8. Fixed a bug in the SQL query execution plan retrieval that may cause server crash. Fixed a bug that caused access violation when a user defined function returned character string that is longer than the function's declared return type.
Fixed an issue in the server where replication would not work if permissions checking was turned on and the first connection to the dictionary after a server restart was an anonymous connection a connection without a user name. Modified the configuration utility for Windows to include a checkbox to control the non-exclusive proprietary locking mode option. This option can also be set via the installer's setup. Fixed an issue in the server that made it possible for a error to be logged when the Advantage Database Server service was shutdown.
Fixed an issue in the server that made it possible for an SQL query to return stale data. Fixed an issue that caused a error when zapping a table. Fixed an issue which could cause a error or the server to hang when running a 64 bit server on 64 bit linux. Fixed an issue in the server where it was possible to read invalid memo data.
If one user read a record and another user updated the memo or BLOB data for that record before the first user read the memo, it could result in the first user reading incorrect memo data. Changed the system view and system. Fixed an issue that caused optimized filters to not be optimized when the filter's expression used a scalar function and the table had a field name that partially matched the scalar function name.
Starting with Oracle Database 12 c Release 1
The database performs a unique scan when a predicate references all of the columns in the key of a UNIQUE index using an equality operator.