Index full scan oracle как избавиться

Методы доступа к данным в Oracle

Не найдя на хабре статьи, объединяющей в удобном для чтения виде информацию о методах доступа к данным, используемых СУБД Oracle, я решил совершить «пробу пера» и написать эту статью.

Общая информация

Не углубляясь в детали, можно утверждать что Oracle хранит данные в таблицах, вместе с которыми могут существовать особые структуры данных – индексы, призванные ускорить запросы к таблицам. При выполнении запросов Oracle по-разному обращается к таблицам и индексам – способы доступа к данным в различных ситуациях и являются предметом этой статьи.

Для примеров мы будем использовать следующую таблицу и данные в ней:

Для анализа плана выполнения запроса будем пользоваться следующими средствами:

После создания индекса и использования его в примерах и перед созданием следующего индекса, он должен быть удален. Это можно сделать с помощью следующего запроса:

TABLE FULL SCAN

Данный метод доступа, как следует из названия, подразумевает перебор всех строк таблицы с исключением тех, которые не удовлетворяют предикату where (если таковой есть). Применяется он либо в случае, когда условия предиката отсутствуют в индексе, либо когда индекса нет в принципе. Примеры:

TABLE ACCESS BY ROWID, он же ROWID

Этот индекс применяется в случаях, когда нам однозначно известен внутренний идентификатор интересующей нас строки таблицы (ROWID). Это происходит в двух случаях:

• Мы указали идентификатор строки в предикате where;
• ROWID запрошенной записи был найден в индексе;

Переходим к методам доступа, используемым Oracle в случае наличия индексов.

INDEX FULL SCAN

Данный метод доступа просматривает все листовые блоки индекса для поиска соответствий условиям предиката. Для того чтобы Oracle мог применить этот метод доступа, хотя бы одно из полей ключа должно иметь ограничение NOT NULL, т.к. только в этом случае соответствующая строка таблицы попадет в индекс. Этот метод обычно быстрее чем TABLE FULL SCAN, но медленнее, чем INDEX RANGE SCAN (см. ниже).

INDEX FAST FULL SCAN

Этот метод доступа применяется, когда выполнены все требования для INDEX FULL SCAN, а также все данные, выбираемые запросом, содержатся в индексе и таким образом доступ к самой таблице не требуется. В отличие от INDEX FULL SCAN этот метод может читать блоки индекса в несколько параллельных потоков и таким образом порядок возвращаемых значений не регламентирован. Oracle также не может использовать этот метод для bitmap-индексов.

Источник

INDEX FULL SCAN и INDEX FAST FULL SCAN

В продолжение темы . Индекс на основе B* — дерева состоит из узловых и листовых блоков. Листовые блоки образуют двусвязный список. То есть блоки индекса последовательно связаны между собой ссылками. Но это совершенно не значит, что в сегменте индекса они размещены упорядочено. Вероятнее только оракл их кладет туда в произвольном порядке.

Теперь рассмотрим два вроде бы похожих вида индексных доступа: INDEX FULL SCAN и INDEX FAST FULL SCAN. Оба эти метода позволяют получать ROWID записей в таблице и по нему обращаться к записям таблицы (в том случае, если в индекс содержит все требуемые запросом колонки, то к таблице оракл вообще не обращается). Оба эти метода требуют, чтобы хотя бы одна колонка ключа имела ограничение not null.

При соблюдении этого условия запись таблицы попадет в индекс. Почему так, читайте здесь. Но на этом их похожесть и заканчивается.

INDEX FULL SCAN — при таком способе доступа оракл просматривает листовые блоки индекса в порядке их вхождения в двусвязный список листовых блоков. Что это дает? Этот метод гарантирует выборку ключей по порядку — обеспечивает отсортированную выборку. Но есть и минус в таком методе. Если индексные записи в списке находятся рядом, это совершенно не значит, что и в сегменте они также являются соседями. А это приводит к одиночному физическому чтению блоков, что не является быстрым способом получения данных с диска.

FULL SCAN доступно, если предикат ссылается на один из ключей индекса. Этот метод также доступен, когда нет предиката, но выполняются оба следующих условия:

— Все колонки, указанные в запросе, включены в индекс.

— По крайней мере, одна из индексных колонок имеет ограничение not null

INDEX FAST FULL SCAN — при таком методе читаются все индексные блоки в сегменте.

Выбираются между них листовые блоки, из которых уже берутся значения индекса.

Так как индексные блоки в сегменте размещены неупорядоченно, следовательно и результат выборки не может быть использован как отсортированная последовательность. Но есть существенный плюс в таком методе выборки. Из сегмента читаются сразу несколько смежных блоков за один заход (multiblock reads). Индекс читается несколькими параллельными процессами. Это позволяет довольно быстро получить данные с диска. FAST FULL SCAN позволяет добраться к данным, находящимся в индексе, без доступа к таблице.

Используется, когда индекс содержит все колонки, необходимые для запроса. А также одна колонна в индексном ключе должна иметь NOT NULL ограничение или хотя бы в конструкции WHERE на ключевую колонку должно быть выставлено условие is not null.

Источник

8 Optimizer Access Paths

An access path is a technique used by a query to retrieve rows from a row source.

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. A row source can be a table, view, or result of a join or grouping operation.

A unary operation such as an access path , which is a technique used by a query to retrieve rows from a row source, accepts a single row source as input. For example, a full table scan is the retrieval of rows of a single row source. 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. The following table summarizes common access paths for the major data structures.

Table 8-1 Data Structures and Access Paths

Bitmap Index Range Scans

The optimizer considers different possible execution plans, and then assigns each plan a cost . The optimizer chooses the plan with the lowest cost. In general, index access paths are more efficient for statements that retrieve a small subset of table rows, whereas full table scans are more efficient when accessing a large portion of a table.

Oracle Database Concepts for an overview of these structures

Table Access Paths

A table is the basic unit of data organization in an Oracle database.

Relational tables are the most common table type. Relational tables have with the following organizational characteristics:

A heap-organized table does not store rows in any particular order.

An index-organized table orders rows according to the primary key values.

An external table is a read-only table whose metadata is stored in the database but whose data is stored outside the database.

This section explains optimizer access paths for heap-organized tables, and contains the following topics:

Oracle Database Concepts for an overview of tables

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.

As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.

Row Storage in Data Blocks and Segments: A Primer

The database stores rows in data blocks. In tables, the database can write a row anywhere in the bottom part of the block. Oracle Database uses the block overhead, which contains the row directory and table directory, to manage the block itself.

An extent is made up of logically contiguous data blocks. The blocks may not be physically contiguous on disk. A segment is a set of extents that contains all the data for a logical storage structure within a tablespace. For example, Oracle Database allocates one or more extents to form the data segment for a table. The database also allocates one or more extents to form the index segment for a table.

By default, the database uses automatic segment space management (ASSM) for permanent, locally managed tablespaces. When a session first inserts data into a table, the database formats a bitmap block. The bitmap tracks the blocks in the segment. The database uses the bitmap to find free blocks and then formats each block before writing to it. ASSM spread out inserts among blocks to avoid concurrency issues.

The high water mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used. Below the HWM, a block may be formatted and written to, formatted and empty, or unformatted. The low high water mark (low HWM) marks the point below which all blocks are known to be formatted because they either contain data or formerly contained data.

During a full table scan, the database reads all blocks up to the low HWM, which are known to be formatted, and then reads the segment bitmap to determine which blocks between the HWM and low HWM are formatted and safe to read. The database knows not to read past the HWM because these blocks are unformatted.

Oracle Database Concepts to learn about data block storage

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. A rowid is a 10-byte physical address of a row.

The rowid points to a specific file, block, and row number. For example, in the rowid AAAPecAAFAAAABSAAA , the final AAA represents the row number. The row number is an index into a row directory entry. The row directory entry contains a pointer to the location of the row on the block.

The database can sometimes move a row in the bottom part of the block. For example, if row movement is enabled, then the row can move because of partition key updates, Flashback Table operations, shrink table operations, and so on. If the database moves a row within a block, then the database updates the row directory entry to modify the pointer. The rowid stays constant.

Oracle Database uses rowids internally for the construction of indexes. For example, each key in a B-tree index is associated with a rowid that points to the address of the associated row. Physical rowids provide the fastest possible access to a table row, enabling the database to retrieve a row in as little as a single I/O.

Direct Path Reads

In a direct path read , the database reads buffers from disk directly into the PGA, bypassing the SGA entirely.

The following figure shows the difference between scattered and sequential reads, which store buffers in the SGA, and direct path reads.

Figure 8-1 Direct Path Reads

Description of «Figure 8-1 Direct Path Reads»

Situations in which Oracle Database may perform direct path reads include:

Execution of a CREATE TABLE AS SELECT statement

Execution of an ALTER REBUILD or ALTER MOVE statement

Reads from a temporary tablespace

Reads from a LOB segment

Oracle Database Performance Tuning Guide to learn about wait events for direct path reads

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.

Table 8-2 Typical Reasons for a Full Table Scan

No index exists.

If no index exists, then the optimizer uses a full table scan.

The query predicate applies a function to the indexed column.

Unless the index is a function-based index, the database indexes the values of the column, not the values of the column with the function applied. A typical application-level mistake is to index a character column, such as char_col , and then query the column using syntax such as WHERE char_col=1 . The database implicitly applies a TO_NUMBER function to the constant number 1 , which prevents use of the index.

A SELECT COUNT(*) query is issued, and an index exists, but the indexed column contains nulls.

The optimizer cannot use the index to count the number of table rows because the index cannot contain null entries.

The query predicate does not use the leading edge of a B-tree index.

For example, an index might exist on employees(first_name,last_name) . If a user issues a query with the predicate WHERE last_name=’KING’ , then the optimizer may not choose an index because column first_name is not in the predicate. However, in this situation the optimizer may choose to use an index skip scan.

If the optimizer determines that the query requires most of the blocks in the table, then it uses a full table scan, even though indexes are available. Full table scans can use larger I/O calls. Making fewer large I/O calls is cheaper than making many smaller calls.

The table statistics are stale.

For example, a table was small, but now has grown large. If the table statistics are stale and do not reflect the current size of the table, then the optimizer does not know that an index is now most efficient than a full table scan.

The table is small.

If a table contains fewer than n blocks under the high water mark, where n equals the setting for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, then a full table scan may be cheaper than an index range scan. The scan may be less expensive regardless of the fraction of tables being accessed or indexes present.

The table has a high degree of parallelism.

A high degree of parallelism for a table skews the optimizer toward full table scans over range scans. Query the value in the ALL_TABLES.DEGREE column to determine the degree of parallelism.

The query uses a full table scan hint.

The hint FULL( table alias ) instructs the optimizer to use a full table scan.

How a Full Table Scan Works

In a full table scan, the database sequentially reads every formatted block under the high water mark. The database reads each block only once.

The following graphic depicts a scan of a table segment, showing how the scan skips unformatted blocks below the high water mark.

Figure 8-2 High Water Mark

Description of «Figure 8-2 High Water Mark»

Because the blocks are adjacent, the database can speed up the scan by making I/O calls larger than a single block, known as a multiblock read . The size of a read call ranges from one block to the number of blocks specified by the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter. For example, setting this parameter to 4 instructs the database to read up to 4 blocks in a single call.

The algorithms for caching blocks during full table scans are complex. For example, the database caches blocks differently depending on whether tables are small or large.

Oracle Database Concepts for an overview of the default caching mode

Oracle Database Reference to learn about the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter

Full Table Scan: Example

The following statement queries salaries over 4000 in the hr.employees table:

Example 8-1 Full Table Scan

The following plan was retrieved using the DBMS_XPLAN.DISPLAY_CURSOR function. Because no index exists on the salary column, the optimizer cannot use an index range scan, and so uses a full table scan.

Table Access by Rowid

A rowid is an internal representation of the storage location of data.

The rowid of a row specifies the data file and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row because it specifies the exact location of the row in the database.

Rowids can change between versions. Accessing data based on position is not recommended because rows can move.

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. However, table access by rowid need not follow every index scan. If the index contains all needed columns, then access by rowid might not occur (see «Index Fast Full Scans» ).

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:

Step 2 of the following plan shows a range scan of the emp_emp_id_pk index on the hr.employees table. The database uses the rowids obtained from the index to find the corresponding rows from the employees table, and then retrieve them. The BATCHED access shown in Step 1 means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.

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.

When the Optimizer Chooses a Sample Table Scan

The database uses a sample table scan when a statement FROM clause includes the SAMPLE keyword.

The SAMPLE clause has the following forms:

The database reads a specified percentage of rows in the table to perform a sample table scan.

SAMPLE BLOCK ( sample_percent )

The database reads a specified percentage of table blocks to perform a sample table scan.

The sample_percent specifies the percentage of the total row or block count to include in the sample. The value must be in the range .000001 up to, but not including, 100 . This percentage indicates the probability of each row, or each cluster of rows in block sampling, being selected for the sample. It does not mean that the database retrieves exactly sample_percent of the rows.

Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then the database does not sample blocks. To guarantee block sampling for a specific table or index, use the FULL or INDEX_FFS hint.

Источник