Database Reference
In-Depth Information
The partitioning method has to be both suitable for the kind of data and suitable for the restrictions applied to
the columns making up the partition key. Here, the restrictions related to hash partitioning described in Table
13-1
should be taken into consideration. In addition, note that even though it's technically possible to use range conditions
on list-partitioned tables, it's not uncommon for such conditions to
not
lead to partition pruning. In fact, data in a
list-partitioned table is by nature not sequential.
If regular compression or purging activities take place, you should also take into consideration whether they can
be implemented by taking advantage of partitioning. For example, dropping or truncating a partition is much faster
than deleting the data it contains. Such a strategy is usually possible only when range or list partitioning is used.
Once the partition key has been chosen, it's necessary to decide whether the partition key can be modified. Such
a modification typically means moving the row into another partition (an operation very similar to deleting the old
row and inserting it again in another partition) and, consequently, changing its rowid. Usually, a row never changes its
rowid. So, if it's a possibility, not only must row movement be enabled at the table level to allow the database engine to
make such a critical modification, but the application also needs to use rowids with special care.
One final—but in my honest opinion very important—remark, is to prevent the most common mistake I have
experienced in projects that implement partitioning. The mistake is designing and implementing the database and
the application without implementing partitioning and then, afterward, partitioning it. More often than not, such an
approach is doomed to fail. I strongly recommend planning the use of partitioning from the beginning of a project.
If you think you'll be able to easily introduce it later, if necessary, you may be in for a big surprise.
Full Index Scans
The database engine can use indexes not only to extract lists of rowids and use them as pointers to read the
corresponding rows from the table, but it can also directly read the column values that are part of the index keys, thus
avoiding following the rowid and accessing the table altogether. Thanks to this very important optimization, when
an index contains all the data needed to process a query, a full table scan or a full partition scan might be replaced
by a
full index scan
. And because an index segment is usually much smaller than a table segment, this is useful for
reducing the number of logical reads.
Full index scans are useful in three main situations. The first is when an index stores all the columns used by a
query. For example, because the
n1
column is indexed, the following query can take advantage of a full index scan.
The following execution plan confirms that no table access is performed (note that all examples in this section are
based on the
index_full_scan.sql
script):
SELECT /*+
index
(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 |
INDEX FULL SCAN
| T_N1_I |
-----------------------------------
1 - filter("N1" IS NOT NULL)
It's essential to understand that this execution plan is possible because the condition available in the
WHERE
clause (
n1 IS NOT NULL
) makes sure that the index stores all the data required for the processing (a
NOT NULL
constraint on the
n1
column would have the same effect because it makes sure that no
NULL
values are inserted).
Otherwise, because single-column B-tree indexes don't store
NULL
values, a full table scan must be executed.
The
INDEX FULL SCAN
operation, which can be forced by the
index
hint as shown in the previous example, scans
an index according to its structure. The advantage of this is that the retrieved data is sorted according to the index
