When Does A ROWID Change? Part IV (“Mass Production”) December 21, 2022
Posted by Richard Foote in Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Changing ROWID, Clustering Factor, Data Clustering, Flashback, Move Partitions, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Partitioning, Richard's Blog, ROWID.1 comment so far
In Part II in this series, I discussed how the update of the partitioned key column of a row that results in the row being moved to a different partition, will result in the ROWID of such rows changing.
However, there a quite a number of other user initiated actions in which ROWIDs can easily change (as indeed discussed in Connor McDonald’s video on this subject).
Some of these include:
- Moving a table or partition, as this results in the segment being reorganised, with all associated rows being physically relocated and their associated ROWIDs changing
- Altering a non-partitioned table such that it be now be partitioned, which again results in the physical relocation of all rows and their ROWIDs changing (which BTW, can potentially occur on a Autonomous Database without any user intervention)
- Altering the partitioning strategy of a partitioned table, again changes the physical location of all rows
- Hybrid Columnar Compression (HCC), which by packing rows more tightly, can more likely result in the physical relocation of a row during subsequent DML statements
- Altering a table to Shrink Space, which attempts to move rows between table blocks to pack rows more tightly, again potentially resulting in rows physically moving and the changing of their associated ROWIDs
- Flashback of a table, which results in rows being deleted and inserted and hence the change of their associated ROWIDs
I’ll illustrate an example of all this, with one of the key reasons why you may want to re-organise a table (and implicitly change all the ROWIDs of a table).
I’ll start by creating and populating a simple little table, with a CODE column that has very poorly clustered data:
SQL> create table bowie (id number, code number, name varchar2(42)); Table created. SQL> insert into bowie select rownum, mod(rownum, 500), 'DAVID BOWIE' from dual connect by level <=1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE'); PL/SQL procedure successfully completed.
Let’s now create an index on this CODE column:
SQL> create index bowie_code_i on bowie(code); Index created.
We take note of the ROWIDs of a few random rows:
SQL> select id, rowid from bowie where id in (42, 4242, 424242) order by id;
ID ROWID
---------- ------------------
42 AAASn1AAMAAAgB2AAp
4242 AAASn1AAMAAAgCHACL
424242 AAASn1AAMAAAgbtAAJ
If we run a simple query with a predicate based on the CODE column:
SQL> select * from bowie where code=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 42000 | 1004 (2)| 00:00:01 |
| * 1 | TABLE ACCESS FULL | BOWIE | 2000 | 42000 | 1004 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3596 consistent gets
0 physical reads
0 redo size
20757 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
We notice the CBO has chosen to ignore the index and use a FTS instead, even though only 2000 rows in a 1M row table (just 0.2%) are returned.
Why?
Because the clustering of the CODE data is terrible, with the required values littered throughout the table. If we look at the Clustering Factor of the index:
SQL> select index_name, leaf_blocks, clustering_factor from user_indexes where index_name='BOWIE_CODE_I'; INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR ------------ ----------- ----------------- BOWIE_CODE_I 2063 1000000
We notice the index has the worst possible Clustering Factor value of 1000000.
So to improve the performance of this (say critical) query, we can add a Clustering Attribute to this table based on the CODE column and then reorganise the table:
SQL> alter table bowie add clustering by linear order (code); Table altered. SQL> alter table bowie move online; Table altered.
If we now look at the Clustering Factor of the index:
SQL> select index_name, leaf_blocks, clustering_factor from user_indexes where index_name='BOWIE_CODE_I'; INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR ------------ ----------- ----------------- BOWIE_CODE_I 2063 3568
We can see it has substantially improved, down to just 3568 from the previous 1000000 value, as the data is now perfectly clustered based on the CODE column.
If we now re-run the query:
SQL> select * from bowie where code=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 42000 | 15 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE | 2000 | 42000 | 15 (0)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | BOWIE_CODE_I | 2000 | | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
50735 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
The CBO now choses to use the index and the query is much more efficient as a result (consistent gets down to just 17 from the previous 3596).
So all is now much better, except for any application that was reliant on using ROWIDs to fetch the data, as all ROWIDs have now changed:
SQL> select id, rowid from bowie where id in (42, 4242, 424242) order by id;
ID ROWID
---------- ------------------
42 AAASn6AAMAAAACvAEf
4242 AAASn6AAMAAAiRaAA4
424242 AAASn6AAMAAAiRWAEQ
So there are many ways in which the ROWID of a row can potentially change.
And now there’s another key manner in which a ROWID can very easily change in Oracle Autonomous Database environments, as I’ll next discuss…
When Does A ROWID Change? Part III (“Arriving Somewhere But Not Here”) December 13, 2022
Posted by Richard Foote in Autonomous Database, Changing ROWID, Index Internals, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Partitioning, Performance Tuning, Richard's Blog, ROWID, Secondary Indexes.2 comments
In Part II of this series, I discussed how updating the Partitioned Key of a row from a Partitioned table will result in the row physically moving and the associated ROWID changing.
One of the reasons why changing the ROWID has historically has not been the default behaviour and requires the explicit setting of the ENABLE ROW MOVEMENT clause for Partitioned tables is because changing a ROWID comes at a cost. The cost being not only having to delete and re-insert the row within the table, but also delete and re-insert the associated index entry for each corresponding index on the table.
To illustrate, I’m going to create and populate another simple little Partitioned Table:
SQL> CREATE TABLE big_bowie2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE)) ENABLE ROW MOVEMENT;
Table created.
SQL> INSERT INTO big_bowie2 SELECT rownum, mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,100
ownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), sysdate-
m,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE2');
PL/SQL procedure successfully completed.
I’m now going to create a number of basic Global indexes on this table:
SQL> create index big_bowie2_id_i on big_bowie2(id); Index created. SQL> create index big_bowie2_code1_i on big_bowie2(code1); Index created. SQL> create index big_bowie2_code2_i on big_bowie2(code2); Index created. SQL> create index big_bowie2_code3_i on big_bowie2(code3); Index created. SQL> create index big_bowie2_code4_i on big_bowie2(code4); Index created. SQL> create index big_bowie2_code5_i on big_bowie2(code5); Index created. SQL> create index big_bowie2_code6_i on big_bowie2(code6); Index created. SQL> create index big_bowie2_code7_i on big_bowie2(code7); Index created. SQL> create index big_bowie2_code8_i on big_bowie2(code8); Index created. SQL> create index big_bowie2_code9_i on big_bowie2(code9); Index created. SQL> create index big_bowie2_code10_i on big_bowie2(code10); Index created.
If I run a simple single row UPDATE that updates a non-indexed, non-partitioned key column:
SQL> update big_bowie2 set name='ZIGGY STARDUST' where id=424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 16 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 16 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
2 consistent gets
0 physical reads
328 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We can see that the number of db block gets is just 1 and consistent gets just 2, as only the one table block needs to be updated and easily accessed via the index on the ID column.
If we now run a single row update of an indexed column:
SQL> update big_bowie2 set code1=42 where id = 424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 8 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 8 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
2 consistent gets
1 physical reads
948 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
We can see that the number of db block gets increases to 5, as not only does the table block have to be updated but so also do the associated index blocks.
If we now finally run a single row update on the partitioned table’s partition key column that results in the row having to physically move to another partition:
SQL> update big_bowie2 set release_date='06-DEC-22' where id = 424;
1 row updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 64 | 2 (0) | 00:00:01 |
| 1 | UPDATE | BIG_BOWIE2 | | | | |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE2_ID_I | 1 | 64 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=424)
Statistics
----------------------------------------------------------
1 recursive calls
49 db block gets
3 consistent gets
0 physical reads
5996 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
We see that the number of db block gets jumps significantly to 49, as all the corresponding indexes require their associated index entries to be likewise deleted/re-inserted in order to change all their ROWIDs.
So this additional cost of updating the indexes has been a cost that Oracle has traditionally attempted to avoid, by generally not changing the ROWID when performing an update of a row.
Of course, the update of a Partitioned Key column is not the only manner in which ROWIDs have previously easily changed as we’ll see in Part IV…
UPDATE: As my buddie Martin Widlake makes in this comment. it’s also well worth mentioning the increase in associated redo (as redo is an excellent measurement of “work” the Oracle Database has to perform), if Oracle has to change the ROWID of a row and make the necessary changes to all its corresponding indexes. In the example above, the redo increases significantly from 328 bytes to 5996 bytes, when Oracle has to move the row to another partition and so update the ROWID on the 11 indexes. More on all this when I discuss the changes implemented with the current Autonomous Databases…
When Does A ROWID Change? Part II (“You’ve Got A Habit Of Leaving”) December 9, 2022
Posted by Richard Foote in Autonomous Database, CBO, Changing ROWID, Global Indexes, Multiple Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Partitioning, Performance Tuning, Richard's Blog, ROWID.3 comments
In my previous post, I discussed how a row is generally “migrated”, but the ROWID remains unchanged, when a row is updated such that it can no longer fit into its current block. Hence the general rule has always been that the ROWID of a row does not change.
However, even before the changes now present with Oracle Autonomous Databases (to be discussed in future posts), there has always been (since Oracle 8) one classic scenario when this “ROWID never changes after an update” rule has not been true.
To illustrate, I’m going to create and populate a basic little Range-based Partitioned table, with the RELEASE_DATE column being the partitioned column:
SQL> CREATE TABLE big_bowie(id number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_bowie SELECT rownum, sysdate-mod(rownum,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');
PL/SQL procedure successfully completed.
Let’s look at the current ROWIDs of a few random rows:
SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;
ID RELEASE_D ROWID
---------- --------- ------------------
424 08-OCT-21 AAASe9AAMAAAAj7ABU
444 18-SEP-21 AAASe9AAMAAAAj7ABo
482 11-AUG-21 AAASe9AAMAAAAj7ACO
I’m now going to try and update for these rows, the partitioned column value, such that they would now logically belong in the other partition:
SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482); update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482) * ERROR at line 1: ORA-14402: updating partition key column would cause a partition change
I now get a very key and important error. By default, Oracle does not allow you to update a row if it results in the row having to physically move to a different partition.
I suspect there are at least 3 good reasons for this default restriction.
One is to protect the business integrity of the data, where it might just not make any business sense for a row to be updated in this manner.
The second is that it protects any applications out there that explicitly uses ROWIDs and relies on the ROWIDs not suddenly changing behind the scenes.
And finally, it protects perhaps valuable database resources and ensures that the database does not have to incur any additional workloads, that would be necessary if such an operation were to proceed.
But we have the ability and control to override this default behaviour in the following manner with the ENABLE ROW MOVEMENT clause:
SQL> alter table big_bowie enable row movement; Table altered.
If we now try and update these rows again:
SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482); 3 rows updated. SQL> commit; Commit complete.
The updates are now successful.
As these rows no longer logically belong in the previous partition, they have to be physically moved to its new partition. This is effectively implemented by deleting the rows from the previous partition and then re-inserting them in the new partition segment.
If we now look the ROWIDs of these updated rows:
SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;
ID RELEASE_D ROWID
---------- --------- ------------------
424 06-DEC-22 AAASe+AAMAAAATQABR
444 06-DEC-22 AAASe+AAMAAAATQABS
482 06-DEC-22 AAASe+AAMAAAATQABT
We notice that they now all have different ROWIDs, because they indeed now all exist in a different physical location.
In my next post, I’ll highlight but one obvious disadvantage and consequence of allowing rows to be physically moved in this manner…
Automatic Indexes: Automatically Rebuild Unusable Indexes Part II (“I Wish You Would”) May 11, 2022
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning, Performance Tuning, Rebuild Unusable Indexes.1 comment so far
Within a few hours of publishing my last blog piece on how Automatic Indexing (AI) can automatically rebuild indexes that have been placed in an UNUSABLE state, I was asked by a couple of readers a similar question: “Does this also work if just a single partition of an partitioned index becomes unusable”?
My answer to them both is that I’ve provided them the basic framework in the demo to check out the answer to that question for themselves (Note: a fantastic aspect of working with the Oracle Database is that it’s available for free to play around with, including the Autonomous Database environments).
But based on the principle that for every time someone asks a question, there’s probably a 100 others who potentially might be wondering the same thing, thought I’ll quickly whip up a demo to answer this for all.
I’ll begin with the same table format and data as my previous blog:
SQL> CREATE TABLE big_ziggy(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_ziggy SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;
10000000 rows created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY');
PL/SQL procedure successfully completed.
But this time, I’ll run a number of queries similar to the following, that also has a predicate based on the partitioned key (RELEASE_DATE) of the table:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1051 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 1051 (2) | 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | BIG_ZIGGY | 1 | 26 | 1051 (2) | 00:00:01 | 3 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5618 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
If we wait for the next AI task to kick in:
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 11-MAY-2022 10:55:43 Activity end : 11-MAY-2022 10:56:27 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 192.94 MB (192.94 MB / 0 B) Indexes dropped : 0 SQL statements verified : 6 SQL statements improved (improvement factor) : 3 (6670.1x) SQL plan baselines created : 0 Overall improvement factor : 2x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------------------------- | BOWIE | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL | --------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------
We can see that AI has indeed automatically created a LOCAL, partitioned index (on columns RELEASE_DATE, TOTAL_SALES) in this scenario, as we have an equality predicate based on the partitioned key (RELEASE_DATE).
Currently, all is well with the index, with all partitions in a USABLE state:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_ZIGGY';
INDEX_NAME PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_6wv99zdbsy8ar YES YES VISIBLE N/A
SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2017 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
SQL> select index_name, column_name, column_position from user_ind_columns
where index_name='SYS_AI_6wv99zdbsy8ar';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_6wv99zdbsy8ar RELEASE_DATE 1
SYS_AI_6wv99zdbsy8ar TOTAL_SALES 2
But if we now do an offline reorg of a specific table partition:
SQL> alter table big_ziggy move partition albums_2017; Table altered. SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ -------------------- -------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2017 UNUSABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
We can see we’ve now made the associated Local Index partition UNUSABLE.
If we run the following query:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 986 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 986 (2) | 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | BIG_ZIGGY | 1 | 26 | 986 (2) | 00:00:01 | 3 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Statistics
----------------------------------------------------------
3 recursive calls
4 db block gets
5578 consistent gets
5571 physical reads
924 redo size
676 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
The CBO has no choice here but to do a full partition table scan.
If now wait again for the next AI task to strut its stuff:
SQL> select dbms_auto_index.report_last_activity() from dual; DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 11-MAY-2022 11:42:42 Activity end : 11-MAY-2022 11:43:13 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 192.94 MB (192.94 MB / 0 B) Indexes dropped : 0 SQL statements verified : 4 SQL statements improved (improvement factor) : 1 (5573x) SQL plan baselines created : 0 Overall improvement factor : 1.1x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------------------------- | BOWIE | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL | --------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ -------------------- -------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2017 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
The index partition is now automatically in a USABLE state again.
If we look at the index object data:
SQL> select object_name, subobject_name, to_char(created, 'dd-Mon-yy hh24:mi:ss') created, to_char(last_ddl_time, 'dd-Mon-yy hh24:mi:ss’) last_ddl_time from dba_objects where object_name='SYS_AI_6wv99zdbsy8ar'; OBJECT_NAME SUBOBJECT_NAME CREATED LAST_DDL_TIME ------------------------------ -------------------- --------------------------- --------------------------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 11-May-22 10:41:33 11-May-22 10:56:14 SYS_AI_6wv99zdbsy8ar ALBUMS_2016 11-May-22 10:41:33 11-May-22 10:56:15 SYS_AI_6wv99zdbsy8ar ALBUMS_2017 11-May-22 10:41:33 11-May-22 11:42:42 SYS_AI_6wv99zdbsy8ar ALBUMS_2018 11-May-22 10:41:33 11-May-22 10:56:18 SYS_AI_6wv99zdbsy8ar ALBUMS_2019 11-May-22 10:41:33 11-May-22 10:56:19 SYS_AI_6wv99zdbsy8ar ALBUMS_2020 11-May-22 10:41:33 11-May-22 10:56:20 SYS_AI_6wv99zdbsy8ar ALBUMS_2021 11-May-22 10:41:33 11-May-22 10:56:22 SYS_AI_6wv99zdbsy8ar ALBUMS_2022 11-May-22 10:41:33 11-May-22 10:56:22 SYS_AI_6wv99zdbsy8ar 11-May-22 10:41:33 11-May-22 11:43:13
We can see that just the impacted index partition has been rebuilt.
The CBO can now successfully use the index to avoid the full partition table scan:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3640710173
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 4 (0) | 00:00:01 | 3 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_ZIGGY | 1 | 26 | 4 (0) | 00:00:01 | 3 | 3 |
|* 3 | INDEX RANGE SCAN | SYS_AI_6wv99zdbsy8ar | 1 | | 3 (0) | 00:00:01 | 3 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TOTAL_SALES"=123456)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
I’ll leave it to the discernible reader to determine if this also works in the scenario where the partitioned index were to be global… 🙂
Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part II (Neighbourhood Threat) January 13, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Transaction Processing, CBO, Exadata, Local Indexes, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning.4 comments
In my first post on Automatic Indexing on Partitioned Tables, I discussed how Automatic Indexing (AI) can now create a Non-Partitioned index if deemed the most effective indexing structure (this wasn’t supported when AI was initially released). A Non-Partitioned index is indeed likely the most efficient indexing structure if the underlying table has many partitions and associated SQL equality predicates only reference non-partition key columns. A Non-Partitioned index ensure Oracle only needs to scan the single index structure and not all the partitions of a Local index.
But what if SQLs do reference the column by which the underlying table is partitioned?
The following SQL has an equality filtering predicate on the RELEASE_DATE column, the column by which the BIG_BOWIE1 table is partitioned:
SQL> SELECT * FROM big_bowie1 where release_date = to_date('2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss');
no rows selected
If we look at the subsequent AI report:
INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: *: invisible ------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------ | Owner | Table | Index | Key | Type | Properties | ------------------------------------------------------------------------------------------------ | BOWIE | BIG_BOWIE1 | SYS_AI_14gpurjp8m76s | RELEASE_DATE | B-TREE | LOCAL | ------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------
We notice that Automatic Indexing has in this instance created a Local Index.
If we look further down the AI report:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 4mm3mbkk38pa8
SQL Text : SELECT * FROM big_bowie1 where release_date = to_date('2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss')
Improvement Factor : 8339x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 146957 71
CPU Time (s): 146124 71
Buffer Gets: 16678 3
Optimizer Cost: 162 4
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 2 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 4031749531
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 162 | |
| 1 | PARTITION RANGE SINGLE | | 3602 | 93652 | 162 | 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL | BIG_BOWIE1 | 3602 | 93652 | 162 | 00:00:01 |
-----------------------------------------------------------------------------------
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value : 4049653350
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 4 | 00:00:01 |
| 1 | PARTITION RANGE SINGLE | | 3 | 78 | 4 | 00:00:01 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE1 | 3 | 78 | 4 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | SYS_AI_14gpurjp8m76s | 1 | | 3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("RELEASE_DATE"=TO_DATE(' 2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss'))
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
We can see Automatic Indexing has created the index because it provides an average Improvement Factor of 8339x. As the necessary indexed column(s) matches the table partitioning key, it makes sense for the associated index be a Local index as Oracle is certain which specific index partition to visit based on the value of the equality predicate.
If we look at the details of this new AI:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BIG_BOWIE1'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_14gpurjp8m76s YES NO VISIBLE ADVANCED LOW N/A 20000000 30742 19941449 SYS_AI_8armv0hqq73fa YES NO VISIBLE ADVANCED LOW VALID 20000000 42697 19995451 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BIG_BOWIE1' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ --------------- --------------- SYS_AI_14gpurjp8m76s RELEASE_DATE 1 SYS_AI_8armv0hqq73fa TOTAL_SALES 1 SQL> SELECT index_name, partitioning_type, partition_count, locality FROM user_part_indexes WHERE table_name = 'BIG_BOWIE1'; INDEX_NAME PARTITION PARTITION_COUNT LOCALI ------------------------------ --------- --------------- ------ SYS_AI_14gpurjp8m76s RANGE 8 LOCAL SQL> select index_name, partition_name, status, compression from user_ind_partitions where index_name in (select index_name from user_indexes where table_name='BIG_BOWIE1') order by partition_position; INDEX_NAME PARTITION_NAME STATUS COMPRESSION -------------------- -------------------- -------- ------------- SYS_AI_14gpurjp8m76s ALBUMS_2013 USABLE ADVANCED LOW SYS_AI_14gpurjp8m76s ALBUMS_2014 USABLE ADVANCED LOW SYS_AI_14gpurjp8m76s ALBUMS_2015 USABLE ADVANCED LOW SYS_AI_14gpurjp8m76s ALBUMS_2016 USABLE ADVANCED LOW SYS_AI_14gpurjp8m76s ALBUMS_2017 USABLE ADVANCED LOW SYS_AI_14gpurjp8m76s ALBUMS_2018 USABLE ADVANCED LOW SYS_AI_14gpurjp8m76s ALBUMS_2019 USABLE ADVANCED LOW SYS_AI_14gpurjp8m76s ALBUMS_2020 USABLE ADVANCED LOW
We can see that indeed, a Visible, Usable, Local index was created by Automatic Indexing.
So depending on the column(s) within the index, Automatic Indexing can potentially create either a Local or Non-Partitioned index when indexing a partitioned table.
Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part I (Conversation Piece) October 14, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Index Access Path, Local Indexes, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning, Performance Tuning.2 comments
In this little series, I’m going to discuss how Automatic Indexing works in relation to Partitioning.
I’ve discussed Indexing and Partitioning many times previously and how Oracle has various options when indexing a partitioned table:
- Non-Partitioned Index
- Globally Partitioned Index
- Locally Partitioned Index
So the question(s) are how does Automatic Indexing handle scenarios with partitioned objects?
A very important point to make at the start is that based on my research, the answer has already changed significantly since Automatic Indexing was first released. So it’s important to understand that Automatic Indexing is an ever evolving capability, that will advance and improve as time goes on.
I’ll focus on how the feature currently works (as of Oracle Database 19.5), but will mention previously identified behaviour as a reference on how things can easily change.
In my first simple little example, I’m just going to create a range-partitioned table, partitioned based on RELEASE_DATE, with a partition for each year’s worth of data:
SQL> CREATE TABLE big_bowie1(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
PARTITION ALBUMS_2020 VALUES LESS THAN (MAXVALUE));
Table created.
I’ll now add about 8 years worth of data:
SQL> INSERT INTO big_bowie1 SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800), ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000; 10000000 rows created. SQL> COMMIT; Commit complete.
As discussed previously, I’ll importantly collect statistics:
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE1'); PL/SQL procedure successfully completed.
I’ll now run the following very selective query based the TOTAL_SALES column that is NOT part of the partitioning key:
SQL> SELECT * FROM big_bowie1 WHERE total_sales = 42;
19 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2468051548
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 643 (15)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 20 | 520 | 643 (15)| 00:00:01 | 1 | 8 |
|* 2 | TABLE ACCESS STORAGE FULL| BIG_BOWIE1 | 20 | 520 | 643 (15)| 00:00:01 | 1 | 8 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("TOTAL_SALES"=42)
filter("TOTAL_SALES"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44014 consistent gets
9516 physical reads
0 redo size
1107 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed
Without an index in place, the CBO has no choice but to use a FTS. But what will Automatic Indexing make of things?
If we look at the next Automatic Indexing report:
SQL> select dbms_auto_index.report_last_activity() from dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 13-OCT-2020 01:47:48
Activity end : 13-OCT-2020 02:59:48
Executions completed : 1
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 184.55 MB (184.55 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 2
SQL statements improved (improvement factor) : 1 (44119.6x)
SQL plan baselines created : 0
Overall improvement factor : 25135.8x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
---------------------------------------------------------------------------------
| BOWIE | BIG_BOWIE1 | SYS_AI_2zt7rg40mxa4n | TOTAL_SALES | B-TREE | NONE |
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : chwm2gubm8fx9
SQL Text : SELECT * FROM big_bowie1 WHERE total_sales = 42
Improvement Factor : 44119.6x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 4387193 1173
CPU Time (s): 2599423 1037
Buffer Gets: 749507 22
Optimizer Cost: 643 22
Disk Reads: 470976 2
Direct Writes: 0 0
Rows Processed: 323 19
Executions: 17 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 2468051548
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 643 | |
| 1 | PARTITION RANGE ALL | | 20 | 520 | 643 | 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL | BIG_BOWIE1 | 20 | 520 | 643 | 00:00:01 |
-----------------------------------------------------------------------------------
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value : 937174207
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 494 | 22 | 00:00:01 |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE1 | 19 | 494 | 22 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_2zt7rg40mxa4n | 19 | | 3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("TOTAL_SALES"=42)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
We notice a couple of interesting points.
Firstly, yes Automatic Indexing has created an index based on the TOTAL_SALES column (SYS_AI_2zt7rg40mxa4n) as it improves performance by a reported 44119.6x.
Note also that the Automatic Index is a Non-Partitioned (Global) Index. From a performance perspective, this is the most efficient index to create to improve the performance of this query as the CBO only has the one index structure to navigate (vs. a LOCAL index that would require having to navigate down all 8 index structures for each table partition.
If we look at the index details:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE1'; INDEX_NAME PAR AUT VISIBILIT STATUS ------------------------------ --- --- --------- -------- SYS_AI_2zt7rg40mxa4n NO YES VISIBLE VALID
We notice that this is indeed a Non-Partitioned Index, that is both VISIBLE and VALID and so can be potentially used by any database session.
If we now re-run the query:
SQL> SELECT * FROM big_bowie1 WHERE total_sales = 42;
19 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 937174207
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 23 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| BIG_BOWIE1 | 20 | 520 | 23 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | SYS_AI_2zt7rg40mxa4n | 20 | | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TOTAL_SALES"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
1166 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed
We can see the query now uses the newly created Automatic Index and is indeed more efficient, performing now just 23 consistent gets (previously 44014 consistent gets).
However, this was NOT previous behaviour.
The documentation previously mentioned that only LOCAL indexes are used when indexing partitioned tables.
If we run the same demo on Oracle Database 19.3, we get the following report:
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 14-OCT-2020 13:12:07
Activity end : 14-OCT-2020 14:24:07
Executions completed : 1
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 192.94 MB (192.94 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 1
SQL statements improved (improvement factor) : 1 (1950.5x)
SQL plan baselines created : 0
Overall improvement factor : 1950.5x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
---------------------------------------------------------------------------------
| BOWIE | BIG_BOWIE1 | SYS_AI_8armv0hqq73fa | TOTAL_SALES | B-TREE | LOCAL |
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 2pp8ypramw30s
SQL Text : SELECT * FROM big_bowie1 WHERE total_sales = 42
Improvement Factor : 1950.5x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 6996973 27327
CPU Time (s): 6704215 12819
Buffer Gets: 815306 49
Optimizer Cost: 12793 28
Disk Reads: 2 40
Direct Writes: 0 0
Rows Processed: 475 25
Executions: 19 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 4294056405
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12793 | |
| 1 | PARTITION RANGE ALL | | 20 | 520 | 12793 | 00:00:01 |
| 2 | TABLE ACCESS FULL | BIG_BOWIE1 | 20 | 520 | 12793 | 00:00:01 |
-----------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 3781269341
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 650 | 28 | 00:00:01 |
| 1 | PARTITION RANGE ALL | | 25 | 650 | 28 | 00:00:01 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE1 | 25 | 650 | 28 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | SYS_AI_8armv0hqq73fa | 25 | | 17 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("TOTAL_SALES"=42)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
As we can see, in this scenario, the newly created Automatic Index has a “Property” of LOCAL.
If we look at its index details:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE1'; INDEX_NAME PAR AUT VISIBILIT STATUS ------------------------------ --- --- --------- -------- SYS_AI_8armv0hqq73fa YES YES VISIBLE N/A SQL> SELECT index_name, partitioning_type, partition_count, locality FROM user_part_indexes WHERE table_name = 'BIG_BOWIE1'; INDEX_NAME PARTITION PARTITION_COUNT LOCALI ------------------------------ --------- --------------- ------ SYS_AI_8armv0hqq73fa RANGE 8 LOCAL
We can see how a Local Index was previously created.
As such if we re-run an equivalent query:
SQL> SELECT * FROM big_bowie1 WHERE total_sales = 42;
25 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3781269341
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 26 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 20 | 520 | 26 (0)| 00:00:01 | 1 | 8 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| BIG_BOWIE1 | 20 | 520 | 26 (0)| 00:00:01 | 1 | 8 |
|* 3 | INDEX RANGE SCAN | SYS_AI_8armv0hqq73fa | 20 | | 17 (0)| 00:00:01 | 1 | 8 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
50 consistent gets
0 physical reads
0 redo size
1555 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed
Although the query is returning 6 more rows (as with the random number generation, has a slightly different data set), it’s more expensive proportionally now having to perform 50 consistent gets as it now has to read 8 index structures rather than just the one.
So (IMHO), Automatic Indexing has improved here, creating a more efficient index structure than previously. So always bear in mind that Automatic Indexing is an evolving beast, improving and adapting as time moves on.
However, note the compromise here is that by having an effectively Global index structure, there may be some additional issues depending on any subsequent structural changes to the table.
More on Automatic Indexing and Partitioning in my next post…
“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part III” (Ricochet) October 25, 2018
Posted by Richard Foote in Block Dumps, Global Indexes, Local Indexes, Oracle Indexes, Partitioned Indexes, Partitioning.1 comment so far
In Part I and Part II of this series, we looked at how Global Indexes can effectively perform “Partition Pruning” when the partition keys are specified in SQL predicates, by only using those index entries that have a Data Object of interest stored within the index Rowids.
In this piece, I’ll cover the key performance advantage that Global Indexes have over Local Indexes and why I generally recommended Global Indexes from a purely performance perspective.
First, a quick recap of how the Global Index performed. Following is the performance of a query where the table partitioned key is specified in the query:
SQL> SELECT * FROM big_bowie
WHERE total_sales = 42 and
release_date between '01-JAN-2017' and '31-JUL-2017';
ID ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
1000041 42 42 20-JUL-17 42
Execution Plan
----------------------------------------------------------
Plan hash value: 1081241859
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0) | 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE | 1 | 25 | 13 (0) | 00:00:01 | 7 | 7 |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE_TOTAL_SALES_I | 10 | | 3 (0) | 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RELEASE_DATE"=TO_DATE('2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
2 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
885 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As discussed previously, at just 5 consistent gets, it’s very efficient as only the table blocks that reside in possible partitions of interest are only accessed.
The following query selects all TOTAL_SALES values of interest, with no partition key predicate:
SQL> SELECT * FROM big_bowie
WHERE total_sales = 42;
ID ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
400041 42 42 28-JAN-12 42
1800041 42 42 28-JAN-12 42
800041 42 42 03-MAR-13 42
1200041 42 42 07-APR-14 42
1600041 42 42 12-MAY-15 42
200041 42 42 12-MAY-15 42
600041 42 42 15-JUN-16 42
1000041 42 42 20-JUL-17 42
41 42 42 24-AUG-18 42
1400041 42 42 24-AUG-18 42
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1761527485
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 250 | 13 (0) | 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE | 10 | 250 | 13 (0) | 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE_TOTAL_SALES_I | 10 | | 3 (0) | 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1184 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
So the index is scanned (4 consistent gets) and 10 consistent gets for the 10 rows accessed (as the clustering here is poor) for a total of 14 consistent gets.
Let’s now compare this to an equivalent Local Index.
SQL> create index big_bowie_total_sales_local_i on big_bowie(total_sales) local invisible; Index created. SQL> alter index big_bowie_total_sales_i invisible; Index altered. SQL> alter index big_bowie_total_sales_local_i visible; Index altered.
If we compare the size characteristics between the two indexes we notice a couple of important differences:
SQL> select index_name, blevel, leaf_blocks from dba_indexes where table_name='BIG_BOWIE'; INDEX_NAME BLEVEL LEAF_BLOCKS ------------------------------ ---------- ----------- BIG_BOWIE_TOTAL_SALES_I 2 5585 BIG_BOWIE_TOTAL_SALES_LOCAL_I 1 4444 SQL> select index_name, partition_name, blevel, leaf_blocks from dba_ind_partitions where index_name='BIG_BOWIE_TOTAL_SALES_LOCAL_I'; INDEX_NAME PARTITION_NAME BLEVEL LEAF_BLOCKS ------------------------------ -------------------- ---------- ----------- BIG_BOWIE_TOTAL_SALES_LOCAL_I ALBUMS_2011 1 525 BIG_BOWIE_TOTAL_SALES_LOCAL_I ALBUMS_2012 1 581 BIG_BOWIE_TOTAL_SALES_LOCAL_I ALBUMS_2013 1 579 BIG_BOWIE_TOTAL_SALES_LOCAL_I ALBUMS_2014 1 579 BIG_BOWIE_TOTAL_SALES_LOCAL_I ALBUMS_2015 1 579 BIG_BOWIE_TOTAL_SALES_LOCAL_I ALBUMS_2016 1 581 BIG_BOWIE_TOTAL_SALES_LOCAL_I ALBUMS_2017 1 580 BIG_BOWIE_TOTAL_SALES_LOCAL_I ALBUMS_2018 1 440 8 rows selected.
The first difference is that the corresponding Local index segments have a reduced BLEVEL (just 1) when compared to the Global Index (value of 2). A reduction in BLEVEL is quite possible as instead of one “big” index segment, we now have 8 “smaller” index segments.
However, if we look at the overall size of both indexes, we notice that the Local Index (at 4444 leaf blocks) is somewhat smaller than the Global Index (5585 leaf blocks). This is due to the Rowids of Local Indexes not having to be the extended Global Index 10 byte version (which contains the 4 byte Data Object Id), but the standard 6 byte version. Local Indexes can only reference the one table partition and so it’s unnecessary to store the corresponding Data Object Id within the Rowid.
A partial block dump of a Local Index leaf block:
Leaf block dump
===============
header address 924483684=0x371a8064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 483
kdxcofbo 1002=0x3ea
kdxcofeo 1823=0x71f
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 29412237=0x1c0cb8d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 47
col 1; len 6; (6): 01 c0 20 7b 00 a6
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 47
col 1; len 6; (6): 01 c0 22 3a 00 00
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 48
col 1; len 6; (6): 01 c0 20 7b 00 a7
Shows that the Rowids are only 6 bytes.
If we re-run the query that references the partition key in a SQL predicate:
SQL> SELECT * FROM big_bowie
WHERE total_sales = 42 and
release_date between '01-JAN-2017' and '31-JUL-2017';
ID ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
1000041 42 42 20-JUL-17 42
Execution Plan
----------------------------------------------------------
Plan hash value: 3499166408
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 25 | 2 (0) | 00:00:01 | 7 | 7 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE | 1 | 25 | 2 (0) | 00:00:01 | 7 | 7 |
|* 3 | INDEX RANGE SCAN | BIG_BOWIE_TOTAL_SALES_LOCAL_I | 1 | | 1 (0) | 00:00:01 | 7 | 7 |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RELEASE_DATE"<=TO_DATE(' 2017-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
885 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We notice that this is slightly more efficient with only 4 consistent gets, when previously the Global Index required 5 consistent gets. This is directly due to the reduction in the BLEVEL.
So this is a good thing, especially if this query is frequently executed.
If we now run the query without the partition key SQL predicate:
SQL> SELECT * FROM big_bowie WHERE total_sales = 42;
ID ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
400041 42 42 28-JAN-12 42
1800041 42 42 28-JAN-12 42
800041 42 42 03-MAR-13 42
1200041 42 42 07-APR-14 42
1600041 42 42 12-MAY-15 42
200041 42 42 12-MAY-15 42
600041 42 42 15-JUN-16 42
1000041 42 42 20-JUL-17 42
41 42 42 24-AUG-18 42
1400041 42 42 24-AUG-18 42
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3527547124
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 250 | 15 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 10 | 250 | 15 (0) | 00:00:01 | 1 | 8 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE | 10 | 250 | 15 (0) | 00:00:01 | 1 | 8 |
|* 3 | INDEX RANGE SCAN | BIG_BOWIE_TOTAL_SALES_LOCAL_I | 10 | | 9 (0) | 00:00:01 | 1 | 8 |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
27 consistent gets
0 physical reads
0 redo size
1088 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
We notice that consistent gets have increased more significantly, up to 27 consistent gets when it was previously 14 consistent gets.
This is because instead of accessing the one Global Index structure, we are now forced to access all 8 Local index structures, as the required TOTAL_SALES value could potentially be found in any of the table partitions. So that’s a minimum of at least 2 consistent gets per Local Index (with an index of BLEVEL 1) that has to accessed even if there are actually no corresponding rows of interest in the particular table partition.
Imagine if this table had a 1000+ table partitions, you can easily see how the cost of using such Local Indexes can quickly become excessive.
So Local Indexes can be very problematic if the partition key is NOT referenced in the SQL or if the range of possible table partitions is excessive. The advantage of a Non-Partitioned index is that there is only the one index structure that need be accessed, regardless of the number of table partitions.
So what if you want to protect yourself from the possible ramifications of the table partition key not being referenced in SQL predicates, but you want to take advantage of the performance benefits of smaller index structures that might have a reduced index BLEVEL?
That’s the topic of Part IV in this series 🙂
“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane) October 9, 2018
Posted by Richard Foote in Global Indexes, Index Internals, Local Indexes, Oracle Indexes, Partitioned Indexes, Partitioning, ROWID.2 comments
In Part I of this series, I highlighted how a Non-Partitioned Global Index on a Partitioned Table is able to effectively perform “Partition Pruning” by reading only the associated index entries to access just the table blocks of interest from relevant table partitions when the table partitioned keys are specified in an SQL Predicate.
Understanding how Oracle achieves this is key (pun fully intended) in understanding the associated advantages of Global Indexes.
Back in time before Oracle introduced Partitioning (pre-Oracle 8 days), the 6 byte ROWID was safely made up of the following components:
- File Number
- Block Number
- Row Number
to uniquely determine the location of any given row.
If we look at a partial block dump of a leaf block from the index based on the Non-Partitioned table:
Leaf block dump
===============
header address 1881436260=0x70247064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1880=0x758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 29387269=0x1c06a05
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 c0 1d 68 00 18
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 c0 24 c8 00 c1
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 c0 3a 1c 00 96
…
We notice that the ROWID for each index entry is the standard 6 bytes in size.
With the introduction of Oracle 8 and the Partitioning Option, the File Number was no longer unique, with this number of files (approx. 1K) now possible not for the database at large, but for each Tablespace (thus making Oracle able to cater for very large databases with there now being the option for so many more data files in a database).
This means for a Partitioned Table in which each table partition (or sub-partition) could potentially reside in different tablespaces, the associated file number (RELATIVE_FNO) within the ROWID is no longer unique. Therefore, for Global Indexes in which index entries span across all table partitions, the ROWID is extended to include the 4 byte Data Object Id. A specific object can only live in one tablespace and if Oracle knows the tablespace, Oracle can determine which specific file number the ROWID is referencing. So an extended ROWID is consists of:
- Data Object Id
- File Number
- Block Number
- Row Number
If we look at a partial block dump of a leaf block from the index based on the Partitioned table:
Leaf block dump
===============
header address 1881436260=0x70247064
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 399
kdxcofbo 834=0x342
kdxcofeo 1652=0x674
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 29385221=0x1c06205
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8020] flag: ——-, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 01 57 4a 01 c0 5e cf 00 cc
row#1[8004] flag: ——-, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 01 57 4a 01 c0 5f 74 00 e7
row#2[7988] flag: ——-, lock: 0, len=16
col 0; len 2; (2): c1 02
col 1; len 10; (10): 00 01 57 4b 01 c0 5c 32 00 c9
…
We notice that the ROWID for each index entry is now the extended 10 bytes in size as it includes the Data Object Id.
Storing the Data Object Id as part of the ROWID has various advantages, such as being able to asynchronously maintain index entries following table partition operations such as dropping a table partition (as discussed previously here).
However the key advantage of storing the Data Object Id as part of the ROWID is that this enables Oracle when using Global Indexes to automatically perform “Partition Pruning” (the ability to access only those partitions that can possibly contain data of interest), when the table partition key is specified in an SQL predicate.
When the table partition key is specified in an SQL predicate, Oracle can determine which table partitions can only contain such data and then only access the table blocks via the index ROWIDs that have corresponding Data Object Ids of interest. This is how in the example in Part I Oracle was able to only access just the table block that belongs in the table partition of interest, effectively performing predicate filtering at the index level, without unnecessarily having to access the table blocks at all from partitions that are not of interest.
This enables Global Indexes to have almost Local Index like performance in scenarios where the table partition key is specified in SQL predicates. Local Indexes do have the advantage of potentially having a reduced BLEVEL in that if you have say 100 table partitions, each Local Index would only have to be approx. 1/100 the size of the single, Non-Partitioned Index (although Global Indexes can in turn be partitioned if individual index size were problematic, even if the table were not partitioned). Additionally, Local Indexes don’t have to concern themselves with having to read through unnecessary index entries if index entries associated with a specific subset of table partitions were only of interest.
However, Global Indexes have a key performance advantage over Local Indexes which I’ll discussed in Part III.
“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part I (The Jean Genie) October 4, 2018
Posted by Richard Foote in Global Indexes, Local Indexes, Non-Partitioned Indexes, Oracle Indexes, Partitioned Indexes, Partitioning.7 comments
When it comes to indexing a partitioned table, many automatically opt for Local Indexes, as it’s often assumed they’re simply easier to manage and more efficient than a corresponding Global Index.
Having smaller index structures that are aligned to how the table is partitioned certainly has various advantages. The focus in this little series is on the “more efficient” claim.
A key point that many miss is that a Non-Partitioned Index on a Non-Partitioned table is not exactly the same beast as a Non-Partitioned Index on a Partitioned Table. The purpose of this initial post is to illustrate this difference.
Let’s begin by creating a Non-Partitioned table that has a number of years worth of data:
SQL> CREATE TABLE big_ziggy (id number, album_id number, country_id number, release_date date, total_sales number);
Table created.
SQL> INSERT INTO big_ziggy
SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
mod(rownum,200000)+1
FROM dual CONNECT BY LEVEL 2000000;
2000000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY');
PL/SQL procedure successfully completed.
So we have a 2M row table with about 8 years worth of data (based on the RELEASE_DATE column) and a TOTAL_SALES column that has some 200,000 distinct columns throughout this period.
Let’s next create a standard Non-Partitioned index based on the TOTAL_SALES column:
SQL> create index big_ziggy_total_sales_i on big_ziggy(total_sales); Index created.
If we now run a query to access the 10 rows with a value equal to 42:
SQL> SELECT * FROM big_ziggy WHERE total_sales = 42;
ID ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
1400041 42 42 24-AUG-18 42
400041 42 42 28-JAN-12 42
1000041 42 42 20-JUL-17 42
1800041 42 42 28-JAN-12 42
600041 42 42 15-JUN-16 42
800041 42 42 03-MAR-13 42
1200041 42 42 07-APR-14 42
1600041 42 42 12-MAY-15 42
200041 42 42 12-MAY-15 42
41 42 42 24-AUG-18 42
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1252095634
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 250 | 13 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BIG_ZIGGY | 10 | 250 | 13 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | BIG_ZIGGY_TOTAL_SALES_I | 10 | | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1184 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
We notice we need 14 consistent gets to access these 10 rows, 4 gets for index block accesses and 10 gets to access the relevant rows from the table blocks (as we have a terrible clustering due to the relevant data being distributed throughout the table).
If we run a query where we’re only interested in accessing data only within a specific year:
SQL> SELECT * FROM big_ziggy WHERE total_sales = 42 and release_date
between '01-JAN-2017' and '31-JUL-2017';
ID ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
1000041 42 42 20-JUL-17 42
Execution Plan
----------------------------------------------------------
Plan hash value: 1252095634
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0) | 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BIG_ZIGGY | 1 | 25 | 13 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | BIG_ZIGGY_TOTAL_SALES_I | 10 | | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RELEASE_DATE">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "RELEASE_DATE"<=TO_DATE(' 2017-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
2 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
885 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Even though we now only return the one row, notice we still have to perform the same 14 consistent gets. That’s because the RELEASE_DATE column is NOT part of the index, so we still need to fetch all 10 matching rows with TOTAL_SALES=42 and then filter out those that don’t have a RELEASE_DATE of interest. The note above in the predicate information shows we now have this additional filtering taking place.
Let’s run the same queries on a table with identical data, but this time on a table that is partitioned based on the RELEASE_DATE column, with a partition for each years worth of data:
SQL> CREATE TABLE big_bowie(id number, album_id number, country_id number, release_date date, total_sales number)
2 PARTITION BY RANGE (release_date)
3 (PARTITION ALBUMS_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
4 PARTITION ALBUMS_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')),
5 PARTITION ALBUMS_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')),
6 PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
7 PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
8 PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
9 PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
10 PARTITION ALBUMS_2018 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_bowie
SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
mod(rownum,200000)+1
FROM dual CONNECT BY LEVEL 2000000;
2000000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');
PL/SQL procedure successfully completed.
Again, we create a standard, Non-Partitioned Index:
SQL> create index big_bowie_total_sales_i on big_bowie(total_sales); Index created.
If we now run the equivalent of the first query:
SQL> SELECT * FROM big_bowie WHERE total_sales = 42;
ID ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
400041 42 42 28-JAN-12 42
1800041 42 42 28-JAN-12 42
800041 42 42 03-MAR-13 42
1200041 42 42 07-APR-14 42
1600041 42 42 12-MAY-15 42
200041 42 42 12-MAY-15 42
600041 42 42 15-JUN-16 42
1000041 42 42 20-JUL-17 42
41 42 42 24-AUG-18 42
1400041 42 42 24-AUG-18 42
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1761527485
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 250 | 13 (0) | 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE | 10 | 250 | 13 (0) | 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE_TOTAL_SALES_I | 10 | | 3 (0) | 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1184 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
We get the exact same performance, with the same 14 consistent gets necessary to access the 10 rows of interest.
If we now run the equivalent of the second query:
SQL> SELECT * FROM big_bowie WHERE total_sales = 42 and release_date between '01-JAN-2017' and '31-JUL-2017';
ID ALBUM_ID COUNTRY_ID RELEASE_D TOTAL_SALES
---------- ---------- ---------- --------- -----------
1000041 42 42 20-JUL-17 42
Execution Plan
----------------------------------------------------------
Plan hash value: 1081241859
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0) | 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE | 1 | 25 | 13 (0) | 00:00:01 | 7 | 7 |
|* 2 | INDEX RANGE SCAN | BIG_BOWIE_TOTAL_SALES_I | 10 | | 3 (0) | 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RELEASE_DATE"=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
2 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
885 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We notice a key difference. Even though it’s equivalent to the same index as in the previous Non-Partitioned table and even though the index only contains just the TOTAL_SALES column, the number of consistent gets has dropped from 14 to just 5 consistent gets.
In this example, Oracle has clearly not had to fetch the rows from the table that do not match the RELEASE_DATE of interest. Even though the predicate information is listing the requirement for filtering to take place, this filtering has clearly been performed within the index, without having to actually fetch any of the rows that aren’t of interest.
The index is able to only access the row(s) of interest from the Partitioned Table…
This is the little “hidden efficiency” of Global Indexes on Partitioned Tables, which is what we effectively have here.
In Part II, I’ll discuss how Oracle does this additional filtering within the index and why understanding this is important in deciding which type of index to deploy, as from a “performance” perspective, Global Indexes are often the preferred option.
12.2 Some Cool Partitioning New Features (Big Wheels) April 5, 2017
Posted by Richard Foote in 12c Rel 2, Oracle Indexes, Partitioning.2 comments
I previously discussed just how easy it is to convert online a non-partitioned table to be partitioned with Oracle Database 12.2.
Thought I might run through a number of really cool new partitioning features and capabilities that were also introduced in 12.2.
To start, I’m just going to create a basic range-partitioning table and populate it with a year’s worth of data:
SQL> create table ziggy
2 (prod_id NUMBER,
3 cust_id NUMBER,
4 time_id DATE,
5 quantity NUMBER)
6 PARTITION BY RANGE (time_id)
7 (PARTITION z_2016_q1 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy')),
8 PARTITION z_2016_q2 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy')),
9 PARTITION z_2016_q3 VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')),
10 PARTITION z_2016_q4 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')));
Table created.
SQL> insert into ziggy select mod(rownum,10), mod(rownum,100), sysdate-dbms_random.value(94, 454), 100
from dual connect by level <= 100000;
100000 rows created.
SQL> commit;
Commit complete.
I’ll then create both a global, non-partitioned index and a locally partitioned index:
SQL> create index ziggy_prod_id_i on ziggy(prod_id); Index created. SQL> select index_name, num_rows, status from dba_indexes where index_name='ZIGGY_PROD_ID_I'; INDEX_NAME NUM_ROWS STATUS -------------------- ---------- -------- ZIGGY_PROD_ID_I 100000 VALID SQL> create index ziggy_time_id_i on ziggy(time_id) local; Index created. SQL> select index_name, partition_name, num_rows, status from dba_ind_partitions where index_name='ZIGGY_TIME_ID_I'; INDEX_NAME PARTITION_NAME NUM_ROWS STATUS -------------------- -------------------- ---------- -------- ZIGGY_TIME_ID_I Z_2016_Q1 23941 USABLE ZIGGY_TIME_ID_I Z_2016_Q2 25276 USABLE ZIGGY_TIME_ID_I Z_2016_Q3 25522 USABLE ZIGGY_TIME_ID_I Z_2016_Q4 25261 USABLE
OK, the first 12.2 new feature is the capability to now “Split” a partition online (previously this was an offline only operation that resulted in invalid global indexes and invalid corresponding local indexes):
SQL> alter table ziggy
2 split PARTITION z_2016_q4 into
3 (PARTITION z_2016_oct VALUES LESS THAN (TO_DATE('01-NOV-2016','dd-MON-yyyy')),
4 PARTITION z_2016_nov VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy')),
5 PARTITION z_2016_dec) online;
Table altered.
SQL> select index_name, num_rows, status from dba_indexes
where index_name='ZIGGY_PROD_ID_I';
INDEX_NAME NUM_ROWS STATUS
-------------------- ---------- --------
ZIGGY_PROD_ID_I 100000 VALID
SQL> select index_name, partition_name, num_rows, status from dba_ind_partitions where index_name='ZIGGY_TIME_ID_I';
INDEX_NAME PARTITION_NAME NUM_ROWS STATUS
-------------------- -------------------- ---------- --------
ZIGGY_TIME_ID_I Z_2016_DEC 8276 USABLE
ZIGGY_TIME_ID_I Z_2016_NOV 8298 USABLE
ZIGGY_TIME_ID_I Z_2016_OCT 8687 USABLE
ZIGGY_TIME_ID_I Z_2016_Q1 23941 USABLE
ZIGGY_TIME_ID_I Z_2016_Q2 25276 USABLE
ZIGGY_TIME_ID_I Z_2016_Q3 25522 USABLE
6 rows selected.
Nice !!
OK, let’s quickly check how many rows I have for each PROD_ID value that belongs within the Q1 partition:
SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;
PROD_ID COUNT(*)
---------- ----------
0 2391
1 2334
2 2324
3 2372
4 2284
5 2462
6 2348
7 2399
8 2380
9 2388
So we have a PROD_ID with values between 0 and 9 that have roughly the same number of rows.
Let’s now check the size of each table partition in blocks:
SQL> select partition_name, blocks from dba_tab_partitions where table_name='ZIGGY'; PARTITION_NAME BLOCKS -------------------- ---------- Z_2016_DEC 44 Z_2016_NOV 44 Z_2016_OCT 45 Z_2016_Q1 1006 Z_2016_Q2 1006 Z_2016_Q3 1006
Note that the Q1 partition current has 1006 blocks allocated.
OK, the next cool new feature is to select which rows we make want to keep during a subsequent re-org operation. In the following example, I’m going to re-org the Q1 partition and compress the data, but I’m only going to keep those rows where the PROD_ID is between 1 and 8 (hence getting rid of all rows with PROD_ID that’s 0 or 9):
SQL> ALTER TABLE ziggy 2 MOVE PARTITION z_2016_q1 TABLESPACE users COMPRESS ONLINE 3 INCLUDING ROWS WHERE prod_id between 1 and 8; Table altered.
The new INCLUDING ROWS clause explicitly states that I’m only going to include those rows where the PROD_ID is between 1 and 8.
If we now check the size of the partition and its contents:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY');
PL/SQL procedure successfully completed.
SQL> select partition_name, blocks from dba_tab_partitions
where table_name='ZIGGY';
PARTITION_NAME BLOCKS
-------------------- ----------
Z_2016_DEC 44
Z_2016_NOV 44
Z_2016_OCT 45
Z_2016_Q1 57
Z_2016_Q2 1006
Z_2016_Q3 1006
SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;
PROD_ID COUNT(*)
---------- ----------
1 2334
2 2324
3 2372
4 2284
5 2462
6 2348
7 2399
8 2380
We see the Q1 partition has indeed decreased in size (down to just 57 blocks from 1006 blocks) because it has now been compressed AND because it now only has rows where the PROD_ID is between 1 and 8.
Nice !!
The next cool new feature is that we now have new syntax (FOR EXCHANGE WITH TABLE) to more easily create a table by which we wish to subsequently perform a partition exchange. This ensures that the new table is entirely compatible for such an exchange, although note that associated index are NOT created as part of this process:
SQL> CREATE TABLE ziggy_exchange 2 TABLESPACE users 3 FOR EXCHANGE WITH TABLE ziggy; Table created. SQL> ALTER TABLE ziggy 2 EXCHANGE PARTITION z_2016_q1 WITH TABLE ziggy_exchange; Table altered.
If we look at the contents of each object, we can see the partition exchange has been successful:
SQL> select prod_id, count(*) from ziggy where time_id
between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;
no rows selected
SQL> select prod_id, count(*) from ziggy_exchange
where time_id between '01-JAN-2016' and '31-MAR-2016'
group by prod_id order by prod_id;
PROD_ID COUNT(*)
---------- ----------
1 2334
2 2324
3 2372
4 2284
5 2462
6 2348
7 2399
8 2380
Nice !!
The final new 12.2 partitioning feature I want to introduce is the ability now to make a particular partition (or sub-partition) read only:
SQL> alter table ziggy modify partition z_2016_q1 read only; Table altered. SQL> select table_name, partition_name, read_only from dba_tab_partitions where table_name='ZIGGY'; TABLE_NAME PARTITION_NAME READ -------------------- -------------------- ---- ZIGGY Z_2016_DEC NO ZIGGY Z_2016_NOV NO ZIGGY Z_2016_OCT NO ZIGGY Z_2016_Q1 YES ZIGGY Z_2016_Q2 NO ZIGGY Z_2016_Q3 NO SQL> insert into ziggy values (1,1,'13-JAN-2016', 1); insert into ziggy values (1,1,'13-JAN-2016', 1) * ERROR at line 1: ORA-14466: Data in a read-only partition or subpartition cannot be modified.
Nice !!
There are lots of great new features introduced with Oracle Database 12c R2, but sometimes it’s these lesser know features that can so terribly useful.
12.2 Online Conversion of a Non-Partitioned Table to a Partitioned Table (A Small Plot Of Land) March 27, 2017
Posted by Richard Foote in 12c Release 2 New Features, Attribute Clustering, Clustering Factor, Online DDL, Oracle, Oracle Indexes, Partitioning.4 comments
In my previous post, I discussed how you can now move heap tables online with Oracle Database 12.2 and how this can be very beneficial in helping to address issues with the Clustering Factor of key indexes.
A problem with this technique is that is requires the entire table to be effectively reorganised when most of the data might already be well clustered. It would be much more efficient if we could somehow only move and reorganise just the portion of a table that has poorly clustered data introduced to the table since the last reorg.
Partitioning the table appropriately would help to address this disadvantage but converting a non-partitioned table to be partitioned can be a pain. To do this online with as little complication as possible one could use the dbms_redefintion package which has improved with latter releases.
However, with Oracle Database 12.2, there is now an even easier, more flexible method of performing such a conversion.
Using the same table definition and data as from my previous post, I’m going to first create a couple of additional indexes (on the ID column and on the DATE_CREATED column) :
SQL> create unique index ziggy_id_i on ziggy(id); Index created. SQL> create index ziggy_date_created_i on ziggy(date_created); Index created.
To convert a non-partitioned table to a partitioned table online, we can now use this new extension to the ALTER TABLE syntax:
SQL> alter table ziggy
2 modify partition by range (date_created)
3 (partition p1 values less than (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
4 partition p2 values less than (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
5 partition p3 values less than (maxvalue)) online;
Table altered.
How simple is that !! We now have a table that is range partitioned based on the DATE_CREATED column and this conversion was performed online.
We notice not only is the table now partitioned with all the indexes remaining Valid, but the index based on the partitioning key (DATE_CREATED) has also been implicitly converted to be a Local partitioned index:
SQL> select table_name, status, partitioned from dba_tables
where table_name='ZIGGY';
TABLE_NAME STATUS PAR
------------ -------- ---
ZIGGY VALID YES
SQL> select index_name, status, partitioned, num_rows
from dba_indexes where table_name='ZIGGY';
INDEX_NAME STATUS PAR NUM_ROWS
-------------------- -------- --- ----------
ZIGGY_DATE_CREATED_I N/A YES 2000000
ZIGGY_CODE_I VALID NO 2000000
ZIGGY_ID_I VALID NO 2000000
SQL> select index_name, partition_name, status, leaf_blocks from dba_ind_partitions
where index_name like 'ZIGGY%';
INDEX_NAME PARTITION_NAME STATUS LEAF_BLOCKS
-------------------- --------------- -------- -----------
ZIGGY_DATE_CREATED_I P1 USABLE 865
ZIGGY_DATE_CREATED_I P2 USABLE 1123
ZIGGY_DATE_CREATED_I P3 USABLE 1089
SQL> select index_name, partitioning_type, partition_count, locality
from dba_part_indexes where table_name='ZIGGY';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI
-------------------- --------- --------------- ------
ZIGGY_DATE_CREATED_I RANGE 3 LOCAL
As part of the table conversion syntax, we have the option to also update all the associated indexes and partition them in any manner we may want. For example:
SQL> alter table ziggy
2 modify partition by range (date_created)
3 (partition p1 values less than (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
4 partition p2 values less than (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
5 partition p3 values less than (maxvalue)) online
6 update indexes
7 (ziggy_code_i local,
8 ziggy_id_i global partition by range (id)
9 (partition ip1 values less than (maxvalue)));
Table altered.
In this example, not only are we converting the non-partitioned table to be partitioned, but we’re also explicitly converting the index on the CODE column to be a Locally partitioned index and the index on the ID column to be Globally partitioned in its own manner.
If we look at the definition of these indexes, we see that they also have all been converted to partitioned indexes online along with the table:
SQL> select table_name, status, partitioned from dba_tables where table_name='ZIGGY'; TABLE_NAME STATUS PAR ------------ -------- --- ZIGGY VALID YES SQL> select index_name, status, partitioned from dba_indexes where table_name = 'ZIGGY'; INDEX_NAME STATUS PAR -------------------- -------- --- ZIGGY_CODE_I N/A YES ZIGGY_ID_I N/A YES ZIGGY_DATE_CREATED_I N/A YES SQL> select index_name, partitioning_type, partition_count, locality from dba_part_indexes where table_name='ZIGGY'; INDEX_NAME PARTITION PARTITION_COUNT LOCALI -------------------- --------- --------------- ------ ZIGGY_CODE_I RANGE 3 LOCAL ZIGGY_ID_I RANGE 1 GLOBAL ZIGGY_DATE_CREATED_I RANGE 3 LOCAL
If we look at the Clustering Factor of the important CODE column index, we see that all partitions have an excellent Clustering Factor as all partitions have just been created.
SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions where index_name='ZIGGY_CODE_I'; PARTITION_NAME NUM_ROWS CLUSTERING_FACTOR -------------------- ---------- ----------------- P1 490000 2275 P2 730000 3388 P3 780000 3620
However, if we now add new rows to the table as would occur with a real application, the data from the “current” partition results in the Clustering Factor “eroding” over time for this partition.
SQL> insert into ziggy select 2000000+rownum, mod(rownum,100), sysdate, 'DAVID BOWIE'
from dual connect by level <= 500000; 500000 rows created. SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_index_stats(ownname=>null,indname=>'ZIGGY_CODE_I');
PL/SQL procedure successfully completed.
SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions
where index_name='ZIGGY_CODE_I';
PARTITION_NAME NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- -----------------
P1 490000 2275
P2 730000 3388
P3 1280000 238505
As discussed previously, the Clustering Attribute has no effect with standard DML operations. Therefore, the efficiency of the CODE index reduces over time in the partition where new data is being introduced. The Clustering Factor has now substantially increased from 3620 to 238505. Note for all the other partitions where there are no modifications to the data, the Clustering Factor remains excellent.
Having the table/index partitioned means we can therefore periodically reorg just the problematic partition:
SQL> alter table ziggy move partition p3 update indexes online;
Table altered.
SQL> select partition_name, num_rows, clustering_factor from dba_ind_partitions
where index_name='ZIGGY_CODE_I';
PARTITION_NAME NUM_ROWS CLUSTERING_FACTOR
-------------------- ---------- -----------------
P1 490000 2275
P2 730000 3388
P3 1280000 5978
The Clustering Factor for this partition has now reduced substantially from 238505 to just 5978.
For those of you with the Partitioning database option, the ability in 12.2 to now so easily convert a non-partitioned table to be partitioned, along with its associated indexes is just brilliant 🙂
12c Online Partitioned Table Reorganisation Part I (Prelude) January 7, 2014
Posted by Richard Foote in 12c, Oracle Indexes, Partitioning, Unusable Indexes, Update Indexes, Update Indexes Online.2 comments
First post for 2014 !!
Although it’s generally not an overly common activity with Oracle databases, reorganising a table can be somewhat painful, primarily because of the associated locking implications and the impact it has on indexes.
If we look at the following example:
SQL> create table muse2 (id number, status varchar2(6), name varchar2(30));
Table created.
SQL> insert into muse2 select rownum, 'CLOSED', 'DAVID BOWIE'
from dual connect by level <= 3000000;
3000000 rows created.
SQL> commit;
Commit complete.
SQL> create index muse2_id_pk on muse2(id);
Index created.
SQL> alter table muse2 add constraint muse2_id_pk primary key(id);
Table altered.
SQL> create index muse2_status_i on muse2(status);
Index created.
So we have a table with a couple of indexes. We can’t move the table using the ONLINE option as it’s only applicable for Index Organized Tables:
SQL> alter table muse2 move online; alter table muse2 move online * ERROR at line 1: ORA-01735: invalid ALTER TABLE option
If in one session, we have a current transaction on the table (i.e. not committed):
SQL> insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST'); 1 row created.
An attempt to MOVE the table in another session will fail with locking issues:
SQL> alter table muse2 move; alter table muse2 move * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
On the other hand, if the table MOVE command proceeds:
SQL> alter table muse2 move; Table altered.
It in turn locks other transactions out during the duration and leaves all indexes in an UNUSABLE state:
SQL> insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST');
insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST')
*
ERROR at line 1:
ORA-01502: index 'BOWIE.MUSE2_ID_PK' or partition of such index is in unusable state
SQL> select index_name, status from dba_indexes
where table_name='MUSE2';
INDEX_NAME STATUS
--------------- --------
MUSE2_ID_PK UNUSABLE
MUSE2_STATUS_I UNUSABLE
If we now look at a similar Partitioned Table example:
SQL> create table muse (id number, status varchar2(6), name varchar2(30)) 2 partition by range (id) 3 (partition p1 values less than (1000001), 4 partition p2 values less than (2000001), 5 partition p3 values less than (maxvalue)); Table created. SQL> insert into muse select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 3000000; 3000000 rows created. SQL> commit; Commit complete. SQL> create index muse_id_pk on muse(id); Index created. SQL> alter table muse add constraint muse_id_pk primary key(id); Table altered. SQL> create index muse_status_i on muse(status) local; Index created.
Similar locking and indexing issues occur if we try and reorganise a partition, even if we UPDATE INDEXES on the fly. For example, if we have an active transaction in one session:
SQL> insert into muse values (3000001, 'OPEN', 'ZIGGY STARDUST'); 1 row created.
While in another session:
SQL> alter table muse move partition p3 tablespace users update indexes; alter table muse move partition p3 tablespace users update indexes * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
We get the same old resource busy error. If we tried things the other way around, so in one session we first attempt to move a table partition:
SQL> alter table muse move partition p3 tablespace users update indexes; alter table muse move partition p3 tablespace users update indexes * ERROR at line 1: ORA-14327: Some index [sub]partitions could not be rebuilt
We can eventually get the above error if in another session we then attempt to insert a new row into this partition:
SQL> insert into muse values (3000002, 'OPEN', 'ZIGGY STARDUST'); 1 row created.
which in turn hangs for the period of time until the above error is generated.
The associated local index is now not a happy chappy:
SQL> select index_name, partition_name, status from dba_ind_partitions where index_name='MUSE_STATUS_I' union select index_name, null, status from dba_indexes where index_name='MUSE_ID_PK'; INDEX_NAME PARTITION_NAME STATUS --------------- --------------- -------- MUSE_ID_PK VALID MUSE_STATUS_I P1 USABLE MUSE_STATUS_I P2 USABLE MUSE_STATUS_I P3 UNUSABLE
So Moving tables and table partitions around can all get a bit messy, especially if high availability is required.
The DBMS_REDEFINITION package is designed specifically to enable the online redefinition of objects, but it has its own issues and is not as clean and simplistic as a simple MOVE operation when we just want to perform a table re-org.
So what was has changed in Oracle Database 12c ?
We’ll see in the next post although the title here does rather give it away 🙂
12c Asynchronous Global Index Maintenance Part III (Re-Make/Re-Model) August 7, 2013
Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Global Indexes, Oracle Indexes, Partitioning, Unique Indexes.6 comments
As I discussed previously in Part I, the space occupied by orphaned row entries associated with asynchronously maintained global indexes is not automatically reclaimed by subsequent DML operations within the index. Hence the need to clean out these orphaned index entries via the various options discussed in Part II.
However, a good question by Jason Bucata asked what about Unique indexes. “If the index entries aren’t marked deleted but are truly still “there” in the structure, does that mean you can’t use this feature if any global indexes are unique” ?
So now I need a Part III to answer this question 🙂
So same demo setup as before but this time with a Unique index on the ID column:
SQL> create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue)); Table created. SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 3000000; 3000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed. SQL> create unique index muse_id_i on muse(id); Index created.
Let’s now drop a table partition and confirm we indeed do have orphaned unique index entries:
SQL> alter table muse drop partition muse1 update global indexes; Table altered. SQL> select index_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE'; INDEX_NAME NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ORP -------------- ---------- ---------- ----------- -------- --- MUSE_ID_I 3000000 11264 8216 VALID YES SQL> analyze index muse_id_i validate structure; Index analyzed. SQL> select name, blocks, lf_rows, del_lf_rows from index_stats; NAME BLOCKS LF_ROWS DEL_LF_ROWS -------------------- ---------- ---------- ----------- MUSE_ID_I 9216 3000000 1000000
If we take a look at a partial block dump of the first (left-most) index leaf block at this stage:
Block header dump: 0x018076dc
Object id on Block? Y
seg/obj: 0x16c11 csc: 0x00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0029239b
Leaf block dump
===============
header address 360728164=0x15804664
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 404
kdxcofbo 844=0x34c
kdxcofeo 1675=0x68b
kdxcoavs 831
kdxlespl 0
kdxlende 0
kdxlenxt 25196253=0x18076dd
kdxleprv 0=0x0
kdxledsz 10
kdxlebksz 8036
row#0[8021] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 00
col 0; len 2; (2): c1 02
row#1[8006] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 01
col 0; len 2; (2): c1 03
row#2[7991] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 02
col 0; len 2; (2): c1 04
row#3[7976] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 03
col 0; len 2; (2): c1 05
row#4[7961] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 04
col 0; len 2; (2): c1 06
row#5[7946] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 05
col 0; len 2; (2): c1 07
row#6[7931] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 06
col 0; len 2; (2): c1 08
row#7[7916] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 07
col 0; len 2; (2): c1 09
row#8[7901] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 08
col 0; len 2; (2): c1 0a
row#9[7886] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 09
col 0; len 2; (2): c1 0b
row#10[7871] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0a
col 0; len 2; (2): c1 0c
row#11[7856] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0b
col 0; len 2; (2): c1 0d
row#12[7841] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0c
col 0; len 2; (2): c1 0e
row#13[7826] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0d
col 0; len 2; (2): c1 0f
row#14[7811] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0e
col 0; len 2; (2): c1 10
row#15[7796] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0f
col 0; len 2; (2): c1 11
As discussed previously, the index leaf block remains “untouched” after the drop table partition operation and has no index entries actually marked as “deleted”. However, just take a note of the rowid values of the first 10 rows. I’m now going to reinsert new rows with an ID between 1 and 10 that were previously deleted as part of dropping the table partition …
SQL> insert into muse select rownum, 42, 'ZIGGY STARDUST' from dual connect by level <= 10; 10 rows created. SQL> commit; Commit complete. SQL> analyze index muse_id_i validate structure; Index analyzed. SQL> select name, blocks, lf_rows, del_lf_rows from index_stats; NAME BLOCKS LF_ROWS DEL_LF_ROWS -------------------- ---------- ---------- ----------- MUSE_ID_I 9216 3000000 999990
We can see that the number of so-called deleted leaf rows is now only 999990 and has decreased by the 10 rows we’ve inserted.
If we take a look now at the first index leaf block again:
Block header dump: 0x018076dc
Object id on Block? Y
seg/obj: 0x16c11 csc: 0x00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0004.012.0000079d 0x014045c7.0122.44 –U- 10 fsc 0x0000.00292503
Leaf block dump
===============
header address 360612452=0x157e8264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 404
kdxcofbo 844=0x34c
kdxcofeo 1675=0x68b
kdxcoavs 831
kdxlespl 0
kdxlende 0
kdxlenxt 25196253=0x18076dd
kdxleprv 0=0x0
kdxledsz 10
kdxlebksz 8036
row#0[8021] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 86
col 0; len 2; (2): c1 02
row#1[8006] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 87
col 0; len 2; (2): c1 03
row#2[7991] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 88
col 0; len 2; (2): c1 04
row#3[7976] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 89
col 0; len 2; (2): c1 05
row#4[7961] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8a
col 0; len 2; (2): c1 06
row#5[7946] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8b
col 0; len 2; (2): c1 07
row#6[7931] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8c
col 0; len 2; (2): c1 08
row#7[7916] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8d
col 0; len 2; (2): c1 09
row#8[7901] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8e
col 0; len 2; (2): c1 0a
row#9[7886] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8f
col 0; len 2; (2): c1 0b
row#10[7871] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0a
col 0; len 2; (2): c1 0c
row#11[7856] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0b
col 0; len 2; (2): c1 0d
row#12[7841] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0c
col 0; len 2; (2): c1 0e
row#13[7826] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0d
col 0; len 2; (2): c1 0f
row#14[7811] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0e
col 0; len 2; (2): c1 10
row#15[7796] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0f
col 0; len 2; (2): c1 11
We notice that the first 10 index entries now have different rowids from the previous block dump.
So this is an exception to the rule. With a Unique index, Oracle will indeed reuse the storage occupied by the orphaned Unique index entry if the same unique value as an orphaned value is subsequently re-inserted. This is not the case with Non-Unique indexes, even if such Non-Unique indexes are used to police either a PK or Unique Key constraint.
So the new valid index entries and any existing orphaned entries can be read and/or ignored as necessary:
SQL> select * from muse where id between 1 and 100;
ID CODE NAME
---------- ---------- --------------------
1 42 ZIGGY STARDUST
2 42 ZIGGY STARDUST
3 42 ZIGGY STARDUST
4 42 ZIGGY STARDUST
5 42 ZIGGY STARDUST
6 42 ZIGGY STARDUST
7 42 ZIGGY STARDUST
8 42 ZIGGY STARDUST
9 42 ZIGGY STARDUST
10 42 ZIGGY STARDUST
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2515419874
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE | 1 | 23 | 4 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | MUSE_ID_I | 100 | | 3 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"<=100)
filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
974 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
However, if new unique values are inserted into the table but with ID values that didn’t previously exist:
SQL> insert into muse select rownum+3000000, 42, 'ZIGGY STARDUST' from dual connect by level <= 1000000; 1000000 rows created. SQL> commit; Commit complete. SQL> analyze index muse_id_i validate structure; Index analyzed. SQL> select name, blocks, lf_rows, del_lf_rows from index_stats; NAME BLOCKS LF_ROWS DEL_LF_ROWS -------------------- ---------- ---------- ----------- MUSE_ID_I 11264 4000000 999990
We notice that the number of so-called deleted leaf entries remains the same after inserting the 1M new rows.
So in this scenario, the effectively “empty” leaf blocks containing nothing but orphaned unique index entries are not re-cycled and reused by subsequent index block splits as they would have been had they contained nothing but deleted index entries.
So Unique indexes in the unlikely event that such unique values are subsequently reinserted are an exception to the general rule of orphaned global index entries having to be “cleaned out”.
12c Asynchronous Global Index Maintenance Part II (The Space Between) August 6, 2013
Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Coalesce Cleanup, dbms_part.cleanup_gidx, Index Coalesce, Oracle Indexes, Partitioning.9 comments
In Part I, I discussed how global indexes can now be asynchronously maintained in Oracle 12c when a table partition is dropped or truncated. Basically, when a table partition is dropped/truncated with the UPDATE GLOBAL INDEXES clause, Oracle simply keeps track of the object numbers of those table partitions and ignores any corresponding rowids within the index during subsequent index scans. As such, these table partition operations are very fast and efficient as the global indexes are not actually maintained during the partition operation, but importantly, continue to remain in a usable state.
If we look at a partial 11g global index block dump after dropping a table partition (eg. the MUSE_ID_I in the previous demo):
Block header dump: 0x01028750
Object id on Block? Y
seg/obj: 0x130ac csc: 0x00.3c7323 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x1028748 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0006.001.00000f91 0x00c03e16.0177.02 —- 378 fsc 0x1c0b.00000000
Leaf block dump
===============
header address 130573412=0x7c86464
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0x318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 378
kdxlenxt 16942929=0x1028751
kdxleprv 16942927=0x102874f
kdxledsz 0
kdxlebksz 8036
row#0[8019] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 13
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3b
row#1[8002] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 14
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3c
row#2[7985] flag: —D--, lock: 2, len=17
col 0; len 3; (3): c2 10 15
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3d
row#3[7968] flag: —D--, lock: 2, len=17
col 0; len 3; (3): c2 10 16
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3e
row#4[7951] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 17
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3f
….
We notice that all index entries that reference the dropped table partition are marked as deleted. They all have a D (deleted) flag set and have been locked by the drop table partition transaction in ITL slot 2. So prior to Oracle 12c, to update global indexes on the fly was a relatively expensive operation as it required all the associated index entries to be deleted from the global indexes.
However, if we look at a block dump of the same index in an Oracle 12c database following a table partition being dropped:
Block header dump: 0x018000e0
Object id on Block? Y
seg/obj: 0x16bbe csc: 0x00.26ae40 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x18000d8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0026ae40
Leaf block dump
===============
header address 364741220=0x15bd8264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0x318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 25166049=0x18000e1
kdxleprv 25166047=0x18000df
kdxledsz 0
kdxlebksz 8036
row#0[8019] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 13
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3b
row#1[8002] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 14
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3c
row#2[7985] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 15
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3d
row#3[7968] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 16
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3e
row#4[7951] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 17
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3f
…
We notice there are no deleted index entries, the index remains totally untouched by the drop table partition operation. So the good news is that dropping/truncating a table partition while updating global indexes is extremely fast and efficient while the indexes remain hunky dory as subsequent index range scans can ignore any rowids that don’t reference existing table partitions of interest.
However, the bad news is that during subsequent index DML operations, Oracle does not know which index entries are valid and which are not and so the space used by these “orphaned” index entries can not be automatically reclaimed and reused as it can with conventionally deleted index entries. Therefore, we need some other way to clean out the orphaned index entries.
There are a number of possible ways to do this. One way is to simply rebuild the global index (or index partition):
SQL> alter index muse_code_i rebuild partition code_p1; Index altered. SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO' union select index_name, i.partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME ORP NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ------------ --------------- ------------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 NO 1000000 2944 2758 USABLE MUSE_CODE_I CODE_P2 YES 1000000 4352 4177 USABLE MUSE_ID_I YES 2000000 9216 5849 VALID
Effective, but relatively expensive as this requires the entire index structure to be rebuilt from scratch. Depending on the scale and distribution of the orphaned index entries, another possibly cheaper alternative is to use the new CLEANUP coalesce clause:
SQL> alter index muse_id_i coalesce cleanup; Index altered. SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'MUSE_ID_I', estimate_percent=>null); PL/SQL procedure successfully completed. SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO' union select index_name, i.partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME ORP NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ------------ --------------- --- --------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 NO 1000000 4224 4137 USABLE MUSE_CODE_I CODE_P2 YES 1000000 4352 4177 USABLE MUSE_ID_I NO 2000000 9216 5849 VALID SQL> analyze index muse_id_i validate structure; Index analyzed. SQL> select name, lf_rows, del_lf_rows from index_stats; NAME LF_ROWS DEL_LF_ROWS ------------ ---------- ----------- MUSE_ID_I 2000000 0
This visits each index leak block and removes all the orphaned index entries as part of the coalesce process. Note this is a more “powerful” version of coalesce as a standard coalesce is not aware of orphaned index entries and will only coalesce the index without actually removing the orphaned index.
Yet another possible option is to simply wait for the PMO_DEFERRED_GIDX_MAINT_JOB job to run (scheduled by default during the 2am maintenance window) to clean out orphaned index entries from all currently impacted global indexes. Yet another alternative is to manually run the dbms_part.cleanup_gidx procedure which is in turn called by this job:
SQL> exec dbms_part.cleanup_gidx; PL/SQL procedure successfully completed. SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO' union select index_name, i.partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME ORP NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ------------ --------------- --- ---------- ---------- ----------- -------- MUSE_CODE_I CODE_P1 NO 1000000 2944 2758 USABLE MUSE_CODE_I CODE_P2 NO 1000000 4352 4177 USABLE MUSE_ID_I NO 2000000 9216 5849 VALID
We notice the last index partition has now been cleaned out and no longer has orphaned index entries.
So with the new asynchronous global index maintenance capabilities of the Oracle 12c database, we can perform a much faster and more efficient drop/truncate table partition operation while keeping our global indexes in a usable state and leave the tidying up of the resultant orphaned index entries to another time and method of our convenience.
12c Asynchronous Global Index Maintenance Part I (Where Are We Now ?) August 2, 2013
Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Oracle Indexes, Partitioning.8 comments
I previously looked at how global index maintenance was performed when dropping a table partition prior to Oracle Database 12c. Let’s see how things have now changed since the introduction of 12c.
Let’s start by creating the same partitioned table and global indexes as previously:
SQL> create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue)); Table created. SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 3000000; 3000000 rows created. SQL> commit; Commit complete. SQL> create index muse_id_i on muse(id); Index created. SQL> create index muse_code_i on muse(code) global partition by range(code)(partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue)); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.
If we look at the current state of affairs, all is currently hunky dory:
SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO' union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ORP ------------ --------------- ---------- ---------- ----------- -------- --- MUSE_CODE_I CODE_P1 1500000 4224 4137 USABLE NO MUSE_CODE_I CODE_P2 1500000 4352 4177 USABLE NO MUSE_ID_I 3000000 9216 8633 VALID NO
However, a difference to note here is a new data dictionary column called ORPHANED_ENTRIES which denotes whether the index currently has any orphaned index entries. What are these ? We shall see …
Let’s see how expensive it is to now drop the same table partition while updating the global indexes:
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=7; NAME VALUE ---------------------------------------------------------------- ---------- db block gets 129249 redo size 105069544 SQL> alter table muse drop partition muse1 update global indexes; Table altered. Elapsed: 00:00:00.76 SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=7; NAME VALUE ---------------------------------------------------------------- ---------- db block gets 129314 redo size 105083724
As we can see, this is significantly different than before when this was a relatively slow and expensive exercise. At just 65 block gets and only 14180 bytes of redo, this is now about the same cost as dropping the partition without updating the global indexes. How can this be ?
If we now look at the status of our global indexes:
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed. SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO' union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%'; INDEX_NAME PARTITION_NAME NUM_ROWS BLOCKS LEAF_BLOCKS STATUS ORP ------------ --------------- ---------- ---------- ----------- -------- --- MUSE_CODE_I CODE_P1 1000000 4224 4137 USABLE YES MUSE_CODE_I CODE_P2 1000000 4352 4177 USABLE YES MUSE_ID_I 2000000 9216 5849 VALID YES
We notice that indeed, the index entries have been reduced (for example, only 2M index entries now in MUSE_ID_I instead of 3M) as if the indexes have been updated. However, we also notice that although the indexes are both marked as now having orphaned entries, they’re still in a USABLE state.
Basically, when dropping (or truncating) a table partition, Oracle in 12c now “postpones” the actual removal of the global index entries associated with the dropped/truncated partition. This can now be performed asynchronously at a time of our choosing. So it’s therefore now very quick and very cheap to update these global indexes on the fly.
However, most importantly, the indexes are still usable and can be guaranteed to return the correct results, ignoring any orphaned entires as required. These can be easily ignored as they all have an object number in the index entry rowids associated with the dropped table partition object and not the table partition(s) of interest as required by the queries.
So if we now select values via the ID column index that only spans data in the dropped table partition:
SQL> select * from muse where id between 42 and 420;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2515419874
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE | 1 | 23 | 4 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | MUSE_ID_I | 1 | | 3 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=42 AND "ID"<=420)
filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
470 bytes sent via SQL*Net to client
532 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
We notice that quite correctly, no rows are now returned.
The partitioned global index on the CODE column likewise only returns valid data when accessed:
SQL> select * from muse where code=42;
ID CODE NAME
---------- ---------- ------------------------------
1000042 42 DAVID BOWIE
1100042 42 DAVID BOWIE
1200042 42 DAVID BOWIE
1300042 42 DAVID BOWIE
1400042 42 DAVID BOWIE
1500042 42 DAVID BOWIE
1700042 42 DAVID BOWIE
1600042 42 DAVID BOWIE
1900042 42 DAVID BOWIE
1800042 42 DAVID BOWIE
2000042 42 DAVID BOWIE
2100042 42 DAVID BOWIE
2200042 42 DAVID BOWIE
2300042 42 DAVID BOWIE
2400042 42 DAVID BOWIE
2500042 42 DAVID BOWIE
2600042 42 DAVID BOWIE
2700042 42 DAVID BOWIE
2900042 42 DAVID BOWIE
2800042 42 DAVID BOWIE
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4070098220
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 460 | 24 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 20 | 460 | 24 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE | 20 | 460 | 24 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | MUSE_CODE_I | 20 | | 3 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CODE"=42)
filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
25 consistent gets
2 physical reads
0 redo size
1147 bytes sent via SQL*Net to client
554 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
As we can see, only valid data belonging to the non-dropped partitions are returned via the index, even though the index has orphaned index entries that reference the dropped table partition.
If we look at the INDEX_STATS of these indexes, we notice at one level that the orphaned index entries are counted as if they’re deleted entries:
SQL> analyze index muse_id_i validate structure; Index analyzed. SQL> select name, lf_rows, del_lf_rows from index_stats; NAME LF_ROWS DEL_LF_ROWS ------------ ---------- ----------- MUSE_ID_I 3000000 1000000
We see that the index statistics is indicating that there are 1M so-called deleted index entries. The validation process is ensuring that the orphaned index entries only reference partitions that indeed no longer exist and counts such entries as deleted ones.
So it currently looks we’ve got the best of both worlds here. We effectively get the same performance during the drop table partition operation as if we don’t maintain the global indexes but get the same index availability and subsequent query performance as if we do. So what’s the catch ?
Well, very importantly, unlike actual deleted index entries, they are not readily removed and their space reused by subsequent DML activities within the leaf blocks. In fact, these orphaned index entries can even “get in the way” as we see here when we attempt to reinsert the same data back into table:
SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 1000000; 1000000 rows created. Elapsed: 00:03:56.52 Execution Plan ---------------------------------------------------------- Plan hash value: 1731520519 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | MUSE | | | | | 2 | COUNT | | | | | |* 3 | CONNECT BY WITHOUT FILTERING| | | | | | 4 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(LEVEL<=1000000) Statistics ---------------------------------------------------------- 700 recursive calls 758362 db block gets 53062 consistent gets 5069 physical reads 355165692 redo size 869 bytes sent via SQL*Net to client 903 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 75 sorts (memory) 0 sorts (disk) 1000000 rows processed SQL> commit; Commit complete.
This is notably slower and more expensive than if the index entries had actually been deleted because Oracle is not able to simply identify and overwrite the orphaned index entries during DML operations as they’re not physically marked as deleted. If we look at the INDEX_STATS after inserting these new rows:
SQL> analyze index muse_id_i validate structure; Index analyzed. SQL> select name, blocks, lf_rows, del_lf_rows from index_stats; NAME BLOCKS LF_ROWS DEL_LF_ROWS --------------- ---------- ---------- ----------- MUSE_ID_I 12288 4000000 1000000 SQL> analyze index muse_code_i validate structure; Index analyzed. SQL> select name, blocks, lf_rows, del_lf_rows from index_stats; NAME BLOCKS LF_ROWS DEL_LF_ROWS --------------- ---------- ---------- ----------- MUSE_CODE_I 9216 2000000 500000
We notice that unlike actual deleted index entries in which all the deleted space would have simply have been reused, we see instead that none of the space occupied by the orphaned rows has been reused. This in the end means accessing more index blocks, potentially performing more block splits, more newer blocks having to be generated and overall more work having to be performed than would have been necessary if they had just been plain deleted index entries.
So how we actually get rid of these orphaned index entries ? I look at a number of different techniques we can use in Part II. And yes, good old block dumps are on their way as well 🙂
Richard Foote's Oracle Blog 