Sliding Windows and Indexes

Summary-Partitioning

Posted on by Barbara Raymond

Partitioning is extremely useful in scaling up large database objects in the database. This scaling is visible from the perspective of performance scaling, availability scaling, and administrative scaling. All three are extremely important to different people.

The DBA is concerned with administrative scaling. The owners of the system are concerned with availability, because downtime is lost money, and anything that reduces downtime—or reduces the impact of downtime—boosts the payback for a system.

The end users of the system are concerned with performance scaling. No one likes to use a slow system, after all.

We also looked at the fact that in an OLTP system, partitions may not increase performance, especially if applied improperly. Partitions can increase the performance of certain classes of queries, but those queries are generally not applied in an OLTP system.

This point is important to understand, as many people associate partitioning with “free performance increase.” This does not mean that partitions should not be used in OLTP systems—they do provide many other salient benefits in this environment— just don’t expect a massive increase in throughput.

Expect reduced downtime. Expect the same good performance (partitioning will not slow you down when applied appropriately). Expect easier manageability, which may lead to increased performance due to the fact that some maintenance operations are performed by the DBAs more frequently because they can be.

We investigated the various table partitioning schemes offered by Oracle—range, hash, list, interval, reference, interval reference, virtual column, and composite—and talked about when they are most appropriately used.

We spent the bulk of our time looking at partitioned indexes and examining the differences between prefixed and nonprefixed and local and global indexes. We investigated partition operations in data warehouses combined with global indexes, and the trade-off between resource consumption and availability.

We also looked at the ease of maintenance features such as the ability to perform maintenance operations on multiple partitions at a time, cascade truncate, and cascade exchange. Oracle continues to update and improve partitioning with each new release.

Over time, I see this feature becoming more relevant to a broader audience as the size and scale of database applications grow.

The Internet and its database-hungry nature along with legislation requiring longer retention of audit data are leading to more and more extremely large collections of data, and partitioning is a natural tool to help manage that problem.

Partitioning and Performance, Revisited-Partitioning-1

Posted on by Barbara Raymond

Many times, I hear people say, “I’m very disappointed in partitioning. We partitioned our largest table and it went much slower. So much for partitioning being a performance increasing feature!” Partitioning can do one of the following three things to overall query performance:

•\ Make your queries go faster
•\ Not impact the performance of your queries at all
•\ Make your queries go much slower and use many times the resources as the nonpartitioned implementation

In a data warehouse, with an understanding of the questions being asked of the data, the first bullet point is very much achievable. Partitioning can positively impact queries that frequently full scan large database tables by eliminating large sections of data from consideration.

Suppose you have a table with one billion rows in it. There is a timestamp attribute. Your query is going to retrieve one year’s worth of data from this table (and it has ten years of data). Your query uses a full table scan to retrieve this data. Had it been partitioned by this timestamp entry—say, a partition per month—then you could have full scanned one-tenth the data (assuming a uniform distribution of data over the years).

Partition elimination would have removed the other 90 percent of the data from consideration. Your query would likely run faster.

Now, take a similar table in an OLTP system. You would never retrieve ten percent of a one billion–row table in that type of application.

Therefore, the massive increase in speed seen by the data warehouse just would not be achievable in a transactional system. You are not doing the same sort of work, and the same possible improvements are just not realistic.

Therefore, in general, in your OLTP system the first bullet point is not achievable, and you won’t be applying partitioning predominantly for increased performance. Increased availability—absolutely.

Administrative ease of use—very much so. But in an OLTP system, I say you have to work hard to make sure you achieve the second point: that you do not impact the performance of your queries at all, negatively or positively. Many times, your goal is to apply partitioning without affecting query response time.

On many occasions, I’ve seen that the implementation team will see they have a medium-sized table, say of 100 million rows. Now, 100 million sounds like an incredibly large number (and five or ten years ago, it would have been, but time changes all things).

So the team decides to partition the data. But in looking at the data, there are no logical attributes that make sense for RANGE partitioning. There are no sensible attributes for that. Likewise, LIST partitioning doesn’t make sense.

Nothing pops out of this table as being the right thing to partition by. So, the team opts for hash partitioning on the primary key, which just happens to be populated by an Oracle sequence number. It looks perfect, it is unique and easy to hash, and many queries are of the form SELECT * FROM T WHERE PRIMARY_KEY = :X.

But the problem is there are many other queries against this object that are not of that form. For illustrative purposes, assume the table in question is really the ALL_OBJECTS dictionary view, and while internally many queries would be of the form WHERE OBJECT_ID = :X, the end users frequently have these requests of the application as well:

•\ Show me the details of SCOTT’s EMP table (where owner = 😮 and object_type = :t and object_name = :n).
•\ Show me all of the tables SCOTT owns (where owner = 😮 andobject_type = :t).
•\ Show me all of the objects SCOTT owns (where owner = :o).

In support of those queries, you have an index on (OWNER,OBJECT_TYPE,OBJECT_ NAME). But you also read that local indexes are more available, and you would like to be more available regarding your system, so you implement them. You end up re-creating your table like this, with 16 hash partitions:

However, when you run this with autotrace on and review the output, you notice the following performance characteristics:

You compare that to the same table, only with no partitioning implemented, and discover the following:

You might immediately jump to the (erroneous) conclusion that partitioning causes an increase in I/O. The query gets 7 consistent gets without partitioning and 36 with partitioning. If your system had an issue with high consistent gets (logical I/Os before), it is worse now. If it didn’t have one before, it might well get one.

The root cause? The index partitioning scheme. Look at the following output from an explain plan for the partitioned version of this table:

This query has to look at each and every index partition here. The reason for that is because entries for SCOTT may well be in each and every index partition and probably is.

The index is logically hash partitioned by OBJECT_ID; any query that uses this index and that does not also refer to the OBJECT_ID in the predicate must consider every index partition! So, what is the solution here? You should globally partition your index. Using the previous case as the example, we could choose to hash partition the index:

Note There are considerations to be taken into account with hash partitioned indexes regarding range scans, which we’ll discuss later in this section.

Asynchronous Global Index Maintenance-Partitioning

Posted on by Barbara Raymond

As shown in the prior section, you can maintain global indexes while dropping or truncating partitions via the UPDATE GLOBAL INDEXES clause. However, as shown previously, such operations come at a cost in terms of time and resource consumption.

When dropping or truncating table partitions, Oracle postpones the removal of the global index entries associated with the dropped or truncated partitions. This is known as asynchronous global index maintenance. Oracle postpones the maintenance of the global index to a future time while keeping the global index usable.

The idea is that this improves the performance of dropping/truncating partitions while keeping any global indexes in a usable state. The actual cleanup of the index entries is done later (asynchronously) either by the DBA or by an automatically scheduled Oracle job. It’s not that less work is being done, rather it’s the cleanup of index entries is decoupled from the DROP/TRUNCATE statement.

A small example will demonstrate asynchronous global index maintenance. To set this up, we create a table, populate it with test data, and create a global index:
$ sqlplus eoda/foo@PDB1
SQL> drop table partitioned;
SQL> CREATE TABLE partitioned( timestamp date,id int)PARTITION BY RANGE (timestamp) (PARTITION fy_2020 VALUES LESS THAN (to_date(’01-jan-2021′,’dd-mon-yyyy’)), PARTITION fy_2021 VALUES LESS THAN( to_date(’01-jan-2022′,’dd-mon-yyyy’)));
SQL> insert into partitioned partition(fy_2020)select to_date(’31-dec-2020′,’dd-mon-yyyy’)-mod(rownum,364), rownum from dual connect by level < 100000;99999 rows created. SQL> insert into partitioned partition(fy_2021)select to_date(’31-dec-2021′,’dd-mon-yyyy’)-mod(rownum,364), rownum from dual connect by level < 100000;99999 rows created. SQL> create index partitioned_idx_global on partitioned(timestamp) GLOBAL; Index created.

Next, we’ll run a query to retrieve the current values of redo size and db block gets statistics for the current session:
SQL> col r1 new_value r2
SQL> col b1 new_value b2
SQL> select * from(select b.value r1from v$statname a, v$mystat bwhere a.statistic# = b.statistic#and a.name = ‘redo size’),(select b.value b1from v$statname a, v$mystat bwhere a.statistic# = b.statistic#and a.name = ‘db block gets’);
R1 B1
56928036 80829

Next, a partition is dropped with the UPDATE GLOBAL INDEXES clause specified:
SQL> alter table partitioned drop partition fy_2020 update global indexes; Table altered.

Now we’ll calculate the amount of redo generated and the number of current blocks accessed:
SQL> select * from(select b.value – &r2 redo_genfrom v$statname a, v$mystat bwhere a.statistic# = b.statistic#and a.name = ‘redo size’),(select b.value – &b2 db_block_getsfrom v$statname a, v$mystat bwhere a.statistic# = b.statistic#and a.name = ‘db block gets’);
old 2: (select b.value – &r2 redo_gen
new 2: (select b.value – 4816712 redo_gen
old 6: (select b.value – &b2 db_block_gets
new 6: (select b.value – 4512 db_block_gets
REDO_GEN DB_BLOCK_GETS
16864 103

Only a small amount of the redo is generated, and a small number of blocks are accessed. The reason behind this is that Oracle doesn’t immediately perform the index maintenance of removing the index entries from the dropped partition. Rather, these entries are marked as orphaned and will later be cleaned up by Oracle. The existence of orphaned entries can be verified via the following:
SQL> select index_name, orphaned_entries, status from user_indexes where table_name=’PARTITIONED’;
INDEX_NAME ORP STATUS
PARTITIONED_IDX_GLOBAL YES VALID

How do the orphaned entries get cleaned up? Oracle has an automatically scheduled
PMO_DEFERRED_GIDX_MAINT_JOB, which runs in a nightly maintenance window:
SQL> select job_name from dba_scheduler_jobs where job_name like ‘PMO%’;
JOB_NAME
PMO_DEFERRED_GIDX_MAINT_JOB

If you don’t want to wait for that job, you can manually clean up the entries yourself:
SQL> exec dbms_part.cleanup_gidx; PL/SQL procedure successfully completed.

Now checking for orphaned rows shows there are none:
SQL> select index_name, orphaned_entries, status from user_indexes where table_name=’PARTITIONED’;
INDEX_NAME ORP STATUS
PARTITIONED_IDX_GLOBAL NO VALID

In this way, you can perform operations such as dropping and truncating partitions and still leave your global indexes in a usable state without the immediate overhead of cleaning up the index entries as part of the drop/truncate operation.

Tip See Oracle Support note 1482264.1 for further details on asynchronous global index maintenance.

“Live” Global Index Maintenance-Partitioning

Posted on by Barbara Raymond

Oracle has the ability to maintain the global indexes during the partition operation using the UPDATE GLOBAL INDEXES clause. This means that as you drop a partition, split a partition, perform whatever operation necessary on a partition, Oracle will perform the necessary modifications to the global index to keep it up to date.

Since most partition operations will cause this global index invalidation to occur, this feature can be a boon to systems that need to provide continual access to the data. You’ll find that you sacrifice the raw speed of the partition operation, but with the associated window of unavailability immediately afterward as you rebuild indexes, for a slower overall response time from the partition operation but coupled with 100 percent data availability.

In short, if you have a data warehouse that cannot have downtime, but must support these common data warehouse techniques of sliding data in and out, then this feature is for you—but you must understand the implications.

Revisiting our previous example, if our partition operations had used the UPDATE GLOBAL INDEXES clause when relevant (in this example, it would not be needed on the ADD PARTITION statement since the newly added partition would not have any rows in it), we would have discovered the indexes to be perfectly valid and usable both during and after the operation:

SQL> alter table partitioned exchange partition fy_2020 with table fy_2020 including indexes without validation UPDATE GLOBAL INDEXES;Table altered.

Note in the following output, the N/A status observed for the PARTITIONED_IDX_LOCAL index simply means the statuses are associated with the index partitions associated with that index, not the index itself. It doesn’t make sense to say the locally partitioned index is valid or not; it is just a container that logically holds the index partitions themselves:

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
PARTITIONED_IDX_LOCAL N/A
PARTITIONED_IDX_GLOBAL VALID
FY_2020_IDX VALID
FY_2022_IDX VALID

But there is a trade-off: we are performing the logical equivalent of DELETE and INSERT operations on the global index structures. When we drop a partition, we have to delete all of the global index entries that might be pointing to that partition.

When we did the exchange of a table with a partition, we had to delete all of the global index entries pointing to the original data and then insert all of the new ones that we just slid in there. So the amount of work performed by the ALTER commands was significantly increased.

You should expect with global index maintenance considerations that the approach without index maintenance will consume fewer database resources and therefore ­perform faster but incur a measurable period of downtime.

The second approach, involving maintaining the indexes, will consume more resources and perhaps take longer overall, but will not incur downtime. As far as the end users are concerned, their ability to work never ceased. They might have been processing a bit slower (since we were competing with them for resources), but they were still processing, and they never stopped.

The index rebuild approach will almost certainly run faster, considering both the elapsed time and the CPU time. This fact has caused many a DBA to pause and say, “Hey, I don’t want to use UPDATE GLOBAL INDEXES—it’s slower.”

That is too simplistic of a view, however. What you need to remember is that while the operations overall took longer, processing on your system was not necessarily interrupted. Sure, you as the DBA might be looking at your screen for a longer period of time, but the really important work that takes place on your system was still taking place.

You need to see if this trade-off makes sense for you. If you have an eight-hour maintenance window overnight in which to load new data, then by all means, use the rebuild approach if that makes sense. However, if you have a mandate to be available continuously, then the ability to maintain the global indexes will be crucial.

One more thing to consider is the redo generated by each approach. You will find that the UPDATE GLOBAL INDEXES generates considerably more redo (due to the index maintenance), and you should expect that to only go up as you add more and more global indexes to the table.

The redo generated by the UPDATE GLOBAL INDEXES processing is unavoidable and cannot be turned off via NOLOGGING, since the maintenance of the global indexes is not a complete rebuild of their structure but more of an incremental maintenance.

Additionally, since you are maintaining the live index structure, you must generate undo for that—in the event the partition operation fails, you must be prepared to put the index back the way it was. And remember, undo is protected by redo itself, so some of the redo you see generated is from the index updates and some is from the rollback. Add another global index or two and you would reasonably expect these numbers to increase.

So, UPDATE GLOBAL INDEXES is an option that allows you to trade off availability for resource consumption. If you need to provide continuous availability, it’s the option for you. But you have to understand the ramifications and size other components of your system appropriately. Specifically, many data warehouses have been crafted over time to use bulk direct path operations, bypassing undo generation and, when permitted, redo generation as well. Using UPDATE GLOBAL INDEXES cannot bypass either of those two ­elements. You need to examine the rules you use to size your redo and undo needs before using this feature, so you can assure yourself it can work on your system.

Hash Partition Using Powers of Two-Partitioning

Posted on by Barbara Raymond

I mentioned earlier that the number of partitions should be a power of two. This is easily observed to be true. To demonstrate, we’ll set up a stored procedure to automate the creation of a hash partitioned table with N partitions (N will be a parameter). This procedure will construct a dynamic query to retrieve the counts of rows by partition and then display the counts and a simple histogram of the counts by partition. Lastly, it will open this query and let us see the results. This procedure starts with the hash table creation. We will use a table named T:

SQL> create or replaceprocedure hash_proc( p_nhash in number,p_cursor out sys_refcursor )authid current_userasl_text long;l_template long :=’select $POS$ oc, ”p$POS$” pname, count(*) cnt ‘ || ‘from t partition ( $PNAME$ ) union all ‘;table_or_view_does_not_exist exception;pragma exception_init( table_or_view_does_not_exist, -942 ); beginbegin
execute immediate ‘drop table t’; exception when table_or_view_does_not_existthen null;end;
execute immediate ‘CREATE TABLE t ( id )partition by hash(id)partitions ‘ || p_nhash || ‘asselect rownumfrom all_objects’;

Next, we will dynamically construct a query to retrieve the count of rows by partition. It does this using the template query defined earlier. For each partition, we’ll gather the count using the partition-extended table name and union all of the counts together:
for x in ( select partition_name pname, PARTITION_POSITION pos
from user_tab_partitions where table_name = ‘T’
order by partition_position )loopl_text := l_text ||replace(replace(l_template,’$POS$’, x.pos),’$PNAME$’, x.pname );end loop;

Now, we’ll take that query and select out the partition position (PNAME) and the count of rows in that partition (CNT). Using RPAD, we’ll construct a rather rudimentary but effective histogram:

open p_cursor for’select pname, cnt,

substr( rpad(””,30round( cnt/max(cnt)over(),2),”*”),1,30) hg from (‘ || substr( l_text, 1, length(l_text)-11 ) || ‘) order by oc’;end;/

If we run this with an input of 4, for four hash partitions, we would expect to see output similar to the following:
SQL> variable x refcursor
SQL> set autoprint on
SQL> exec hash_proc( 4, 😡 );
PL/SQL procedure successfully completed.

The simple histogram depicted shows a nice, even distribution of data over each of the four partitions. Each has close to the same number of rows in it. However, if we simply go from four to five hash partitions, we’ll see the following:
SQL> exec hash_proc( 5, 😡 );
PL/SQL procedure successfully completed.
PN CNT HG

This histogram points out that the first and last partitions have just half as many rows as the interior partitions. The data is not very evenly distributed at all. We’ll see the trend continue for six and seven hash partitions:
SQL> exec hash_proc( 6, 😡 );
PL/SQL procedure successfully completed.
PN CNT HG

SQL> exec hash_proc( 7, 😡 );
PL/SQL procedure successfully completed.
PN CNT HG

As soon as we get back to a number of hash partitions that is a power of two, we achieve the goal of even distribution once again:
SQL> exec hash_proc( 8, 😡 );
PL/SQL procedure successfully completed.
PN CNT HG

If we continue this experiment up to 16 partitions, we would see the same effects for the 9th through the 15th partitions—a skewing of the data to the interior partitions, away from the edges—and then upon hitting the 16th partition, you would see a flattening out again. The same would be true again up to 32 partitions, and then 64, and so on. This example just points out the importance of using a power of two as the number of hash partitions.

Global Indexes-Partitioning

Posted on by Barbara Raymond

Global indexes are partitioned using a scheme that is different from that used in the underlying table. The table might be partitioned by a TIMESTAMP column into ten partitions, and a global index on that table could be partitioned into five partitions by the REGION column. Unlike local indexes, there is only one class of global index, and that is a prefixed global index. There is no support for a global index whose index key does not begin with the partitioning key for that index. That implies that whatever attribute(s) you use to partition the index will be on the leading edge of the index key itself.

Building on our previous example, here is a quick example of the use of a global index. It shows that a global partitioned index can be used to enforce uniqueness for a primary key, so you can have partitioned indexes that enforce uniqueness, but do not include the partition key of the table. The following example creates a table partitioned by TIMESTAMP that has an index partitioned by ID:
$ sqlplus eoda/foo@PDB1
SQL> drop table partitioned;
SQL> CREATE TABLE partitioned( timestamp date,id int)
PARTITION BY RANGE (timestamp)
PARTITION part_1 VALUES LESS THAN
( to_date(’01-jan-2021′,’dd-mon-yyyy’) ) , PARTITION part_2 VALUES LESS THAN
( to_date(’01-jan-2022′,’dd-mon-yyyy’) ));Table created.
SQL> create index partitioned_indexon partitioned(id)GLOBALpartition by range(id)
partition part_1 values less than(1000), partition part_2 values less than (MAXVALUE) );Index created.

Note the use of MAXVALUE in this index. MAXVALUE can be used in any range partitioned table as well as in the index. It represents an infinite upper bound on the range. In our examples so far, we’ve used hard upper bounds on the ranges (values less than ). However, a global index has a requirement that the highest partition (the last partition) must have a partition bound whose value is MAXVALUE. This ensures that all rows in the underlying table can be placed in the index.

Now, completing this example, we’ll add our primary key to the table:
SQL> alter table partitioned add constraint partitioned_pk primarykey(id);Table altered.

It is not evident from this code that Oracle is using the index we created to enforce the primary key (it is to me because I know that Oracle is using it), so we can prove it by simply trying to drop that index:
SQL> drop index partitioned_index;drop index partitioned_index
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

To show that Oracle will not allow us to create a nonprefixed global index, we only need try the following:
SQL> create index partitioned_index2on partitioned(timestamp,id)GLOBALpartition by range(id)(partition part_1 values less than(1000), partition part_2 values less than (MAXVALUE) );partition by range(id)
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed

The error message is pretty clear. The global index must be prefixed. So, when would you use a global index? We’ll take a look at two system types, data warehouse and OLTP, and see when they might apply.

Data Warehousing and Global Indexes

Many data warehouses implement a sliding window approach to managing data—that is, drop the oldest partition of a table and add a new partition for the newly loaded data. In the following sections, we’ll take a look at what is meant by a sliding window of data and the potential impact of a global index on it.

Composite Partitioning-Partitioning

Posted on by Barbara Raymond

Lastly, we’ll look at some examples of composite partitioning, which is a mixture of range, hash, and/or list. Table 13-1 lists the various ways you can mix and match types of partitioning. In other words, the table shows there are nine different combinations of composite partitioning currently available.

Table 13-1.  Oracle Database Supported Composite Partitioning Schemes by Version

It is interesting to note that when you use composite partitioning, there will be no partition segments; there will be only subpartition segments.
When using composite partitioning, the partitions themselves do not have segments (much like a partitioned table doesn’t have a segment).
The data is physically stored in subpartition segments, and the partition becomes a logical container, or a container that points to the actual subpartitions.

In our example, we’ll look at a range-hash composite partitioning. Here, we are using a different set of columns for the range partition from those used for the hash partition. This is not mandatory; we could use the same set of columns for both:
$ sqlplus eoda/foo@PDB1
SQL> CREATE TABLE composite_example
( range_key_column date,hash_key_column int,data varchar2(20))PARTITION BY RANGE (range_key_column)subpartition by hash(hash_key_column) subpartitions 2

PARTITION part_1
VALUES LESS THAN(to_date(’01/01/2021′,’dd/mm/yyyy’))
(subpartition part_1_sub_1,subpartition part_1_sub_2),

PARTITION part_2
VALUES LESS THAN(to_date(’01/01/2022′,’dd/mm/yyyy’))
(subpartition part_2_sub_1,subpartition part_2_sub_2));Table created.

In range-hash composite partitioning, Oracle will first apply the range partitioning rules to figure out which range the data falls into.
Then it will apply the hash function to decide into which physical partition the data should finally be placed. This process is described in Figure 13-4.

Figure 13-4.  Range-hash composite partition example

So, composite partitioning gives you the ability to break your data up by range and, when a given range is considered too large or further partition elimination could be useful, to break it up further by hash or list. It is interesting to note that each range partition need not have the same number of subpartitions; for example, suppose you were range partitioning on a date column in support of data purging (to remove all old data rapidly and easily).

In the year 2020 and before, you had equal amounts of data in odd code numbers in the CODE_KEY_COLUMN and in even code numbers. But after that, you knew the number of records associated with the odd code number was more than double, and you wanted to have more subpartitions for the odd code values.

You can achieve that rather easily just by defining more subpartitions:
SQL> CREATE TABLE composite_range_list_example ( range_key_column date,code_key_column int,data varchar2(20))PARTITION BY RANGE (range_key_column)subpartition by list(code_key_column)

PARTITION part_1
VALUES LESS THAN(to_date(’01/01/2021′,’dd/mm/yyyy’)) (subpartition part_1_sub_1 values( 1, 3, 5, 7 ), subpartition part_1_sub_2 values( 2, 4, 6, 8 )),

PARTITION part_2
VALUES LESS THAN(to_date(’01/01/2022′,’dd/mm/yyyy’)) (subpartition part_2_sub_1 values ( 1, 3 ), subpartition part_2_sub_2 values ( 5, 7 ), subpartition part_2_sub_3 values ( 2, 4, 6, 8 )));Table created.

Here, you end up with five partitions altogether: two subpartitions for partition PART_1 and three for partition PART_2.

Local Indexes and Unique Constraints-Partitioning

Posted on by Barbara Raymond

To enforce uniqueness—and that includes a UNIQUE constraint or PRIMARY KEY constraints—your partitioning key must be included in the constraint itself if you want to use a local index to enforce the constraint.
This is the largest limitation of a local index, in my opinion. Oracle enforces uniqueness only within an index partition—never across partitions. What this implies, for example, is that you cannot range partition on a TIMESTAMP field and have a primary key on the ID that is enforced using a locally partitioned index. Oracle will instead utilize a global index to enforce uniqueness.

In the next example, we will create a range partitioned table that is partitioned by a column named TIMESTAMP but has a primary key on the ID column. We can do that by executing the following CREATE TABLE statement in a schema that owns no other objects, so we can easily see exactly what objects are created by looking at every segment this user owns:
SQL> CREATE TABLE partitioned( timestamp date,id int,constraint partitioned_pk primary key(id))
PARTITION BY RANGE (timestamp)
PARTITION part_1 VALUES LESS THAN
( to_date(’01/01/2021′,’dd/mm/yyyy’) ) , PARTITION part_2 VALUES LESS THAN
( to_date(’01/01/2022′,’dd/mm/yyyy’) ));Table created.

And inserting some data so that we get segments created:
SQL> insert into partitioned values(to_date(’01/01/2020′,’dd/mm/yyyy’),1); 1 row created.
SQL> insert into partitioned values(to_date(’01/01/2021′,’dd/mm/yyyy’),2); 1 row created.

Assuming we run this in a schema with no other objects created, we’ll see the following:
SQL > select segment_name, partition_name, segment_type from user_segments;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
PARTITIONED PART_1 TABLE PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED _PK INDEX

The PARTITIONED_PK index is not even partitioned, let alone locally partitioned, and as we’ll see, it cannot be locally partitioned. Even if we try to trick Oracle by realizing that a primary key can be enforced by a nonunique index as well as a unique index, we’ll find that this approach will not work either:
SQL> drop table partitioned;
SQL> CREATE TABLE partitioned( timestamp date,id int
PARTITION BY RANGE (timestamp)
PARTITION part_1 VALUES LESS THAN
( to_date(’01-jan-2021′,’dd-mon-yyyy’) ) , PARTITION part_2 VALUES LESS THAN
( to_date(’01-jan-2022′,’dd-mon-yyyy’) ));Table created.
SQL> create index partitioned_idx on partitioned(id) local; Index created.

And inserting some data so that we get segments created:
SQL> insert into partitioned values(to_date(’01/01/2020′,’dd/mm/yyyy’),1); 1 row created.
SQL> insert into partitioned values(to_date(’01/01/2021′,’dd/mm/yyyy’),2); 1 row created
SQL> select segment_name, partition_name, segment_type from user_segments;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
PARTITIONED PART_1 TABLE PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED_IDX PART_1 INDEX PARTITION
PARTITIONED_IDX PART_2 INDEX PARTITION

Here, Oracle attempts to create a global index on ID, but finds that it cannot since an index already exists. The preceding statements would work if the index we created was not partitioned, as Oracle would have used that index to enforce the constraint.

The reasons why uniqueness cannot be enforced, unless the partition key is part of the constraint, are twofold. First, if Oracle allowed this, it would void most of the advantages of partitions. Availability and scalability would be lost, as each and every partition would always have to be available and scanned to do any inserts and updates.
The more partitions you had, the less available the data would be. The more partitions you had, the more index partitions you would have to scan, and the less scalable partitions would become. Instead of providing availability and scalability, doing this would actually decrease both.

Additionally, Oracle would have to effectively serialize inserts and updates to this table at the transaction level. This is because if we add ID=1 to PART_1, Oracle would have to somehow prevent anyone else from adding ID=1 to PART_2. The only way to do this would be to prevent others from modifying index partition PART_2, since there isn’t anything to really lock in that partition.

In an OLTP system, unique constraints must be system enforced (i.e., enforced by Oracle) to ensure the integrity of data. This implies that the logical model of your application will have an impact on the physical design. Uniqueness constraints will either drive the underlying table partitioning scheme, driving the choice of the partition keys, or point you toward the use of global indexes instead. We’ll take a look at global indexes in more depth next.

Partitioning Indexes-Partitioning

Posted on by Barbara Raymond

Indexes, like tables, may be partitioned. There are two possible methods to partition indexes:

•\ Equipartition the index with the table: This is also known as a local index. For every table partition, there will be an index partition that indexes just that table partition. All of the entries in a given index partition point to a single table partition, and all of the rows in a single table partition are represented in a single index partition.

•\ Partition the index by range or hash: This is also known as a globally partitioned index. Here, the index is partitioned by range, or optionally by hash, and a single index partition may point to any (and all) table partitions.

Figure 13-5 demonstrates the difference between a local and a global index.

Chapter 13   Partitioning

Figure 13-5.  Local and global index partitions

In the case of a globally partitioned index, note that the number of index partitions may be different from the number of table partitions.

Since global indexes may be partitioned by range or hash only, you must use local indexes if you wish to have a list or composite partitioned index. The local index will be partitioned using the same scheme as the underlying table.

Local Indexes vs. Global Indexes

In my experience, most partition implementations in data warehouse systems use local indexes. In an OLTP system, global indexes are much more common, and we’ll see why shortly. It has to do with the need to perform partition elimination on the index structures to maintain the same query response times after partitioning as before partitioning them.

Local indexes have certain properties that make them the best choice for most data warehouse implementations. They support a more available environment (less downtime), since problems will be isolated to one range or hash of data. On the other hand, since it can point to many table partitions, a global index may become a point of failure, rendering all partitions inaccessible to certain queries.

Local indexes are more flexible when it comes to partition maintenance operations. If the DBA decides to move a table partition, only the associated local index partition needs to be rebuilt or maintained. With a global index, all index partitions must be rebuilt or maintained in real time.

The same is true with sliding window implementations, where old data is aged out of the partition and new data is aged in. No local indexes will be in need of a rebuild, but all global indexes will be either rebuilt or maintained during the partition operation. In some cases, Oracle can take advantage of the fact that the index is locally partitioned with the table and will develop optimized query plans based on that. With global indexes, there is no such relationship between the index and table partitions.

Local indexes also facilitate a partition point-in-time recovery operation. If a single partition needs to be recovered to an earlier point in time than the rest of the table for some reason, all locally partitioned indexes can be recovered to that same point in time. All global indexes would need to be rebuilt on this object. This does not mean “avoid global indexes”—in fact, they are vitally important for performance reasons, as you’ll learn shortly—you just need to be aware of the implications of using them.

Row Movement-Partitioning

Posted on by Barbara Raymond

You might wonder what would happen if the value of the column used to determine the partition is modified in any of the preceding partitioning schemes. There are two cases to consider:
•\ The modification would not cause a different partition to be used; therow would still belong in this partition. This is supported in all cases.
•\ The modification would cause the row to move across partitions. Thisis supported if row movement is enabled for the table; otherwise, anerror will be raised.

We can observe these behaviors easily. In the previous example in the “RangePartitioning” section, we inserted a pair of rows into PART_1 of the RANGE_EXAMPLE table:
$ sqlplus eoda/foo@PDB1
SQL> CREATE TABLE range_example
( range_key_column date,data varchar2(20))PARTITION BY RANGE (range_key_column)
( PARTITION part_1 VALUES LESS THAN
(to_date(’01/01/2021′,’dd/mm/yyyy’)), PARTITION part_2 VALUES LESS THAN
(to_date(’01/01/2022′,’dd/mm/yyyy’)));Table created.

SQL> insert into range_example( range_key_column, data )values(to_date( ’15-dec-2020 00:00:00′, ‘dd-mon-yyyy hh24:mi:ss’ ), ‘application data…’ );1 row created.

SQL> insert into range_example( range_key_column, data )values( to_date( ’01-jan-2021 00:00:00′, ‘dd-mon-yyyy hh24:mi:ss’ )-1/24/60/60,
‘application data…’ );1 row created.

SQL> select * from range_example partition(part_1); RANGE_KEY DATA
15-DEC-20 application data…
31-DEC-20 application data…

We take one of the rows and update the value in its RANGE_KEY_COLUMN such that it can remain in PART_1:
SQL> update range_example set range_key_column = trunc(range_key_column) where range_key_column =to_date( ’31-dec-2020 23:59:59′, ‘dd-mon-yyyy hh24:mi:ss’ ); 1 row updated.
As expected, this succeeds: the row remains in partition PART_1. Next, we update the
RANGE_KEY_COLUMN to a value that would cause it to belong in PART_2:
SQL> update range_exampleset range_key_column = to_date(’01-jan-2021′,’dd-mon-yyyy’) where range_key_column = to_date(’31-dec-2020′,’dd-mon-yyyy’);update range_example
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

This immediately raises an error since we did not explicitly enable row movement. We can enable row movement on this table to allow the row to move from partition to partition.
You should be aware of a subtle side effect of doing this, however, namely, that the ROWID of a row will change as the result of the update:
SQL> select rowid from range_examplewhere range_key_column = to_date(’31-dec-2020′,’dd-mon-yyyy’);
SQL> alter table range_example enable row movement; Table altered.
SQL> update range_exampleset range_key_column = to_date(’01-jan-2021′,’dd-mon-yyyy’) where range_key_column = to_date(’31-dec-2020′,’dd-mon-yyyy’);1 row updated.

As long as you understand that the ROWID of the row will change on this update, enabling row movement will allow you to update partition keys.

Note There are other cases where a ROWID can change as a result of an update. It can happen as a result of an update to the primary key of an IOT. The universal ROWID will change for that row, too. The FLASHBACK TABLE command may also change the ROWID of rows, as might the ALTER TABLE SHRINK command.

You need to understand that, internally, row movement is done as if you had, in fact, deleted the row and reinserted it. It will update every single index on this table and delete the old entry and insert a new one. It will do the physical work of a DELETE plus an INSERT.

However, it is considered an update by Oracle even though it physically deletes and inserts the row—therefore, it won’t cause INSERT and DELETE triggers to fire, just the UPDATE triggers. Additionally, child tables that might prevent a DELETE due to a foreign key constraint won’t.
You do have to be prepared, however, for the extra work that will be performed; it is much more expensive than a normal UPDATE. Therefore, it would be a bad design decision to construct a system whereby the partition key was modified frequently and that modification would cause a partition movement.