Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore.
EU visitors : Please note that this site uses cookies.

Updated: 2 weeks 1 day ago

Partitioning -- 16 : Hybrid Partitioning

Tue, 2019-05-07 02:55
Oracle 19c introduces Hybrid Partitioning whereby you can have external and internal Partitions co-existing.  External Partitions are on storage (filesystem) outside the database.

Let's say we have a List Partitioned table for the widgets that we manufacture. The table is Partitioned by WIDGET_CLASS_ID, based on an ISO standard.  So all companies that manufacture widgets adopt the same WIDGET_CLASS_ID:

SQL> desc widgets_list
Name Null? Type
----------------------------------------- -------- ----------------------------
WIDGET_CLASS_ID VARCHAR2(5)
WIDGET_ID VARCHAR2(32)
WIDGET_NAME VARCHAR2(32)
WIDGET_DESCRIPTION VARCHAR2(128)

SQL>
SQL> l
1 select table_name, partitioning_type, partition_count
2 from user_part_tables
3* where table_name = 'WIDGETS_LIST'
SQL> /

TABLE_NAME PARTITION PARTITION_COUNT
-------------------------------- --------- ---------------
WIDGETS_LIST LIST 3

SQL>
SQL> l
1 select partition_name,high_value, num_rows
2 from user_tab_partitions
3* where table_name = 'WIDGETS_LIST'
SQL> /

PARTITION_NAME HIGH_VALUE NUM_ROWS
---------------- ---------------- ----------
P_A 'A' 1520
P_B 'B' 520
P_C 'C' 119

SQL>


Later, another widget manufacturer that manufactures widgets of CLASS_ID 'X' is acquired.  The WIDGETS_LIST table is in a non-Oracle database and is received as a CSV file.  We accept the CSV file onto a filesystem location :

sh-4.2$ pwd
/home/oracle/ACQUIRED_COMPANY
sh-4.2$ cat AC_Widgets_List.CSV
'X','ABCXX2','The1','cddfdaxx'
'X','XXD2','The2','dda3'
'X','XRC34','The3','ff33355312'
sh-4.2$


So, we have a CSV file "AC_Widgets_List.CSV" listing the widgets manufactured by this company. We want to add it to our WIDGETS_LIST table.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> create directory acquired_company as '/home/oracle/ACQUIRED_COMPANY';

Directory created.

SQL> grant read, write on directory acquired_company to hemant;

Grant succeeded.

SQL>
SQL> connect hemant/hemant@ORCLPDB1
Connected.
SQL>
SQL> l
1 alter table widgets_list
2 add external partition attributes (
3 type oracle_loader
4 default directory acquired_company
5 access parameters (
6 fields terminated by ','
7 (widget_class_id, widget_id, widget_name, widget_description)
8 )
9* )
SQL> /

Table altered.

SQL>
SQL> l
1 alter table widgets_list
2 add partition P_ACQ_CO values ('X')
3* external location ('AC_Widgets_List.CSV')
SQL> /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','WIDGETS_LIST');

PL/SQL procedure successfully completed.

SQL>
SQL> l
1 select partition_name, high_value, num_rows
2 from user_tab_partitions
3 where table_name = 'WIDGETS_LIST'
4* order by partition_position
SQL> /

PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------------------- ---------------- ----------
P_A 'A' 1520
P_B 'B' 520
P_C 'C' 119
P_ACQ_CO 'X' 3

SQL>
SQL> l
1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS WIDGET_ID WIDGET_NAME
------------ -------------------------------- --------------------------------
WIDGET_DESCRIPTION
--------------------------------------------------------------------------------
'X' 'ABCXX2' 'The1'
'cddfdaxx'

'X' 'XXD2' 'The2'
'dda3'

'X' 'XRC34' 'The3'
'ff33355312'


SQL>


The rows in the "AC_Widgets_List.CSV" file are now visible as rows in a *Partition* in our Oracle Table WIDGETS_LIST.
Of course, these being external, cannot be modified by INSERT/UPDATE/DELETE DML.

The External Attribute Type that I used is ORACLE_LOADER to use the SQL Loader libraries on a filesystem file.  Oracle 19c also supports ORACLE_DATAPUMP, ORACLE_HDFS and ORACLE_HIVE to reference files stored in other types of storage.

Hybrid Partitions are supported with single-level Range and List partitioning methods.  ALTER TABLE to ADD, DROP and RENAME Partitions is supported.

An External Partition can be Exchanged with an External Non-Partitioned Table only.
.
.
UPDATE :  Later, if I update the CSV file (using an external editor) to remove the quotation mark :

sh-4.2$ cat AC_Widgets_List.CSV
X,ABCXX2,The1,cddfdaxx
X,XXD2,The2,dda3
X,XRC34,The3,ff33355312
sh-4.2$

SQL> l
1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS_ID WIDGET_ID WIDGET_NAME
---------------- -------------------------------- --------------------------------
WIDGET_DESCRIPTION
------------------------------------------------------------------------------------
X ABCXX2 The1
cddfdaxx

X XXD2 The2
dda3

X XRC34 The3
ff33355312


SQL>


So, it is possible to edit the External Partition using other methods (here I used "vi" on Linux)
.
.
.

Categories: DBA Blogs

Partitioning -- 15 : Online Modification of Partitioning Type (Strategy)

Mon, 2019-04-08 03:59
Oracle 18c introduces the ability to convert a Partitioned Table from one Type to another -- e.g. from Hash Partitioning to Range Partitioning.  This is effectively a change of the Partitioning strategy for a table without actually having to manually rebuild the table.

I start with a Hash Partitioned Table.

SQL> create table customers(customer_id number, customer_name varchar2(200), customer_city_code number)
2 partition by hash (customer_id) partitions 4;

Table created.

SQL> select partitioning_type from user_part_tables
2 where table_name = 'CUSTOMERS'
3 /

PARTITION
---------
HASH

SQL> select partition_name from user_tab_partitions
2 where table_name = 'CUSTOMERS'
3 /

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P221
SYS_P222
SYS_P223
SYS_P224

SQL>
SQL> insert into customers
2 select dbms_random.value(1,1000001), dbms_random.string('X',25), mod(rownum,5)
3 from dual
4 connect by level < 1000001
5 /

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'CUSTOMERS'
4 /

PARTITION_NAME NUM_ROWS
---------------- ----------
SYS_P221 250090
SYS_P222 249563
SYS_P223 250018
SYS_P224 250329

SQL>


I now want to convert this Hash Partitioned Table to a Range Partitioned Table online.

SQL> alter table customers
2 modify
3 partition by range (customer_id)
4 (partition P_100K values less than (100001),
5 partition P_200K values less than (200001),
6 partition P_300K values less than (300001),
7 partition P_400K values less than (400001),
8 partition P_500K values less than (500001),
9 partition P_600K values less than (600001),
10 partition P_700K values less than (700001),
11 partition P_800K values less than (800001),
12 partition P_900K values less than (900001),
13 partition P_1MIL values less than (1000001),
14 partition P_2MIL values less than (2000001),
15 partition P_MAXVALUE values less than (MAXVALUE))
16 online;

Table altered.

SQL>
SQL> select partitioning_type
2 from user_part_tables
3 where table_name = 'CUSTOMERS'
4 /

PARTITION
---------
RANGE

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> col high_value format a12
SQL> select partition_name, high_value, num_rows
2 from user_tab_partitions
3 where table_name = 'CUSTOMERS'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE NUM_ROWS
---------------- ------------ ----------
P_100K 100001 100116
P_200K 200001 99604
P_300K 300001 99941
P_400K 400001 100048
P_500K 500001 99841
P_600K 600001 99920
P_700K 700001 100081
P_800K 800001 100024
P_900K 900001 100123
P_1MIL 1000001 100302
P_2MIL 2000001 0
P_MAXVALUE MAXVALUE 0

12 rows selected.

SQL>


The Hash Partitioned Table is now converted to a Range Partitioned Table.  The number of Partitions has been changed.  And the operation was performed online with the ONLINE keyword added to the ALTER TABLE ... statement.  The UPDATE INDEXES clauses can also be used to update existing Indexes on the Table.





Categories: DBA Blogs

Everyone should read this

Thu, 2019-04-04 02:10
An excellent article that anyone promising, developing, maintaining or using any system that is non-trivial should read :

https://embeddedartistry.com/blog/2019/4/1/what-can-software-organizations-learn-from-the-boeing-737-max-saga




Categories: DBA Blogs

Partitioning -- 14 : Converting a non-Partitioned Table to a Partitioned Table

Mon, 2019-03-25 09:59
Pre-12cRelease2, there were only three methods to convert a non-Partitioned Table to a Partitioned Table

(a) Create a new, empty, Partitioned Table and copy (using INSERT .... AS SELECT ... ) all the data from the non-Partitioned Table to the new, Partitioned Table (and subsequently rename the new Partitioned Table after renaming or dropping the old non-Partitioned Table)

(b) Create a new, empty, Partitioned Table and use EXCHANGE PARTITION to switch the non-Partitioned Table into the Partitioned Table (and then run subsequent SPLIT PARTITION or ADD PARTITION commands as needed to create the additional Partitions)

(c) Create an interim Partitioned Table and use DBMS_REDEFINITION to do an online copy of the data to the interim Partitioned Table and automatically switch the name at the end


12.2 introduced the ability to use ALTER TABLE  ... MODIFY PARTITION ... to convert a non-Partitioned Table to a Partitioned Table

I start with a non-Partitioned Table :

SQL> select table_name, partitioned
2 from user_tables
3 where table_name = 'SALES_DATA_NONPARTITIONED'
4 /

TABLE_NAME PAR
------------------------------ ---
SALES_DATA_NONPARTITIONED NO

SQL> select index_name, uniqueness, partitioned
2 from user_indexes
3 where table_name = 'SALES_DATA_NONPARTITIONED'
4 /

INDEX_NAME UNIQUENES PAR
------------------------------ --------- ---
SALES_DATA_UK UNIQUE NO

SQL>


I then convert it to a Range-Partitioned Table.

SQL> alter table sales_data_nonpartitioned
2 modify
3 partition by range (sale_date)
4 (
5 partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
6 partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY')),
7 partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
8 partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')),
9 partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY')),
10 partition p_MAXVALUE values less than (MAXVALUE)
11 )
12 online
13 update indexes
14 /

Table altered.

SQL>
SQL> alter table sales_data_nonpartitioned rename to sales_data;

Table altered.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE
---------------- --------------------------
P_2015 TO_DATE(' 2016-01-01 00:00
P_2016 TO_DATE(' 2017-01-01 00:00
P_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TO_DATE(' 2019-01-01 00:00
P_2019 TO_DATE(' 2020-01-01 00:00
P_MAXVALUE MAXVALUE

6 rows selected.

SQL>
SQL> select index_name, partitioned, uniqueness, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 /

INDEX_NAME PAR UNIQUENES STATUS
------------------------------ --- --------- --------
SALES_DATA_UK NO UNIQUE VALID

SQL>


The SALES_DATA_NONPARTITIONED was converted to a Range Partitioned Table.  If I didn't have to rename the table (e.g. if the table name was actually, properly SALES_DATA only), then there would be no need to lock the table as the RENAME command does.



Categories: DBA Blogs

Partitioning -- 13d : TRUNCATE and DROP Partitions and Global Indexes

Wed, 2019-03-20 07:11
A TRUNCATE or DROP Partition makes Global Indexes on a Partitioned Table UNUSABLE.

You may be lucky if the target partition was empty, resulting in Oracle maintaining Global Indexes as valid.  However, the accepted rule is that you either (a) use the UPDATE INDEXES clause [resulting in the TRUNCATE or DROP taking longer to run, effectively locking the table partitions] OR  (b) do a REBUILD of the Indexes that become UNUSABLE after the TRUNCATE or DROP.

12c has introduced what it calls Asynchronous Global Index Maintenance.  With this feature present, the TRUNCATE or DROP runs much faster as a DDL without actually removing the target rows from the Global Indexes [but still requires the UPDATE INDEXES clause to be specified]

So, now in my 12.2 database I have these two Indexes on SALES_DATA :

SQL> select index_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

INDEX_NAME PAR STATUS
------------------------------ --- --------
SALES_DATA_PK NO VALID
SALES_DATA_LCL_NDX_1 YES N/A

SQL>


I then TRUNCATE a non-empty Partition and check the Indexes

SQL> alter table sales_data truncate partition P_2015 update indexes;

Table truncated.

SQL>
SQL> select index_name, partitioned, status, orphaned_entries
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

INDEX_NAME PAR STATUS ORP
------------------------------ --- -------- ---
SALES_DATA_PK NO VALID YES
SALES_DATA_LCL_NDX_1 YES N/A NO

SQL>


The ORPHANED_ENTRIES column indicates that SALES_DATA_PK is subject to Asynchronous Index Maintenance.

This is the job that will do the Index Maintenance at 2am  :

SQL> l
1 select owner, job_name, last_start_date, next_run_Date
2 from dba_scheduler_jobs
3* where job_name = 'PMO_DEFERRED_GIDX_MAINT_JOB'
SQL> /

OWNER
---------------------------------------------------------------------------
JOB_NAME
---------------------------------------------------------------------------
LAST_START_DATE
---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
SYS
PMO_DEFERRED_GIDX_MAINT_JOB
20-MAR-19 10.18.51.215433 AM UTC
21-MAR-19 02.00.00.223589 AM UTC


SQL> !date
Wed Mar 20 20:05:24 SGT 2019

SQL>


So, I could
(1) wait for the next run of the job OR
(2) manually trigger the job (which will scan the entire database for all indexes that require such maintenance) OR
(3) Execute  DBMS_PART.CLEANUP_GIDX  to initiate the maintenance for the specific index OR
(4) Execute an ALTER INDEX REBUILD to make the Index USABLE again.

SQL> execute dbms_part.cleanup_gidx('HEMANT','SALES_DATA');

PL/SQL procedure successfully completed.

SQL> select index_name, partitioned, status, orphaned_entries
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

INDEX_NAME PAR STATUS ORP
------------------------------ --- -------- ---
SALES_DATA_PK NO VALID NO
SALES_DATA_LCL_NDX_1 YES N/A NO

SQL>


Note that the argument to CLEANUP_GIDX is the *Table Name*, not an Index Name.


Here I have demonstrated a TRUNCATE Partition, but the same method would be usable for a DROP Partition.




Categories: DBA Blogs

Partitioning -- 13c : Merging Partitions

Tue, 2019-03-12 07:18
The reverse of SPLITting a Partition is to MERGE two adjacent partitions.

I reverse the SPLIT that I did in the previous blog post.

SQL> l
1 select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4* order by partition_position
SQL> /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL>
SQL> alter table sales_data
2 merge partitions P_2019_H1, P_2019_H2
3 into partition P_2019
4 update indexes
5 /

Table altered.

SQL>
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019 HEMANT TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

6 rows selected.

SQL>


But, we find that the new Partition was created in the default "HEMANT"  tablespace !  So, we have to be careful about specifying target tablespace(s).

Let me reverse the action and try again.

SQL> alter table sales_data
2 split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
3 into (partition P_2019_H1 tablespace TBS_YEAR_2019, partition P_2019_H2 tablespace TBS_YEAR_2019)
4 update indexes
5 /

Table altered.

SQL>
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL> alter table sales_data
2 merge partitions P_2019_H1, P_2019_H2
3 into partition P_2019 tablespace TBS_YEAR_2019
4 update indexes
5 /

Table altered.

SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

6 rows selected.

SQL>


So, when running Table Partition DDL, be careful about the intended and actual target Tablespace(s).  (What about Index Partitions ?  The UPDATE INDEXES clause can specify target tablespaces for each Index Partition of each Index as well ... something like : (this is from the documentation on the ALTER TABLE command)
UPDATE INDEXES (cost_ix (PARTITION c_p1 tablespace tbs_02, 
PARTITION c_p2 tablespace tbs_03))
Categories: DBA Blogs

Partitioning -- 13b : Splitting a Partition

Sun, 2019-01-13 05:46
Let's say the business anticipates growing sales volume in 2019 and new reporting requirements.  IT analyses the requirements and decides that the SALES_DATA Table that is currently Partitioned by YEAR, needs to be Partitioned by HALF-YEAR from 2019 onwards.

SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2016 ARCHIVE_SALES_DATA
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

6 rows selected.

SQL>
SQL> alter table sales_data
2 split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
3 into (partition P_2019_H1, partition P_2019_H2)
4 update indexes
5 /

Table altered.

SQL>
SQL> col high_value format a26 trunc
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL>


I used the UPDATE INDEXES clause to ensure that all (specifically Global) Indexes affected by the SPLIT are updated so that they don't go into an UNUSABLE state.

I could have optionally used a TABLESPACE clause for each of the two new Partitions P_2019_H1 and P_2019_H2


(Also see a previous BlogPost on using SPLIT PARTITION to add a new Partition at the "end" of the table by splitting the last Partition)



Categories: DBA Blogs

Partioning -- 13a : Relocating a Partition

Sun, 2018-12-16 05:19
When you want to / need to move a Partition to a different Tablespace (e.g. as part of a LifeCycle Management Policy), you may need downtime to relocate the Partition.  However, version 12cRelease1 allows Online Relocation of a Partition.

Let's say I have a SALES_DATA table and I need to move the Year 2016 data to a tablespace with datafiles on "cheaper" (lesss-performant) storage :

SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2016 TBS_YEAR_2016
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

6 rows selected.

SQL>
SQL> select index_name, tablespace_name, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 /

INDEX_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ --------
SYS_C0017514 HEMANT VALID
SALES_DATA_LCL_NDX_1 N/A
SALES_DATA_LCL_NDX_2 N/A

SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /

INDEX_NAME PARTITION_NA TABLESPACE_NAM STATUS
------------------------------ ------------ -------------- --------
SALES_DATA_LCL_NDX_1 P_2016 TBS_YEAR_2016 USABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 TBS_YEAR_2016 USABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE

12 rows selected.

SQL>


I then want to move the Year 2016 data to the Tablespace ARCHIVE_SALES_DATA :

SQL> alter table SALES_DATA
2 move partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /

Table altered.

SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NA TABLESPACE_NAME
------------ ------------------
P_2016 ARCHIVE_SALES_DATA
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

6 rows selected.

SQL>
SQL> select index_name, tablespace_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by index_name
5 /

INDEX_NAME TABLESPACE_NAME PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1 YES N/A
SALES_DATA_LCL_NDX_2 YES N/A
SYS_C0017514 HEMANT NO UNUSABLE

SQL> alter index SYS_C0017514 rebuild ;

Index altered.

SQL> select index_name, tablespace_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by index_name
5 /

INDEX_NAME TABLESPACE_NAME PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1 YES N/A
SALES_DATA_LCL_NDX_2 YES N/A
SYS_C0017514 HEMANT NO VALID

SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /

INDEX_NAME PARTITION_NA TABLESPACE_NAME STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1 P_2016 TBS_YEAR_2016 UNUSABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 TBS_YEAR_2016 UNUSABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE

12 rows selected.

SQL>
SQL> alter index SALES_DATA_LCL_NDX_1
2 rebuild partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /

Index altered.

SQL>
SQL> alter index SALES_DATA_LCL_NDX_2
2 rebuild partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /

Index altered.

SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /

INDEX_NAME PARTITION_NA TABLESPACE_NAME STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1 P_2016 ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE

12 rows selected.

SQL>


When I relocated the P_2016 Partition (to the ARCHIVE_SALES_DATA Tablespace), the ROWIDs for rows in that Partition changed.  So the Non-Partitioned Index SYS_C0017514 and the corresponding Local Partitions of the two Partitioned Indexes became "UNUSABLE".  These had to be rebuilt. Alternatively, I could have added the UPDATE INDEXES clause to to the ALTER TABLE ... MOVE PARTITION .. statement to reset the Indexes to Usable but this would not have relocated the Local Partitions for those two Indexes to the new Tablespace.

Note that for Table Partitions, the MOVE clause relocates the Partition but for Index Partition the REBUILD clause is used to relocate (as well as make Usable) the Partition.

I would encourage you to view documentation and examples of the MOVE ONLINE facility in 12c to relocate a Table Partition without downtime.


Categories: DBA Blogs

Partitioning -- 12 : Data Dictionary Queries

Wed, 2018-12-12 19:47
Here's a compilation of some useful data dictionary queries on the implementation of Partitioning.

REM  List all Partitioned Tables in the database (or filter by OWNER in the WHERE clause)
REM Note that the last column is the *defined* default subpartition count
select owner, table_name, partitioning_type, subpartitioning_type, partition_count,
def_subpartition_count as default_subpart_count
from dba_part_tables
order by owner, table_name


REM List all Partitioned Indexes in the database (or filter by OWNER in the WHERE clause)
REM Note that the last column is the *defined* default subpartition count
select owner, index_name, table_name, partitioning_type, subpartitioning_type, partition_count,
def_subpartition_count as default_subpart_count
from dba_part_indexes
order by owner, index_name


REM List Partition Key Columns for all Partitioned Tables
REM (or filter by OWNER or NAME (NAME is TABLE_NAME when object_type='TABLE'))
REM Need to order by column_position as Partition Key may consist of multiple columns
select owner, name, column_name
from dba_part_key_columns
where object_type = 'TABLE'
order by owner, name, column_position


REM List Partition Key Columns for Table SubPartitions
REM (or filter by OWNER or NAME (NAME is TABLE_NAME when object_type='TABLE'))
REM Need to order by column_position as SubPartition Key may consist of multiple columns
select owner, name, column_name
from dba_subpart_key_columns
where object_type = 'TABLE'
order by owner, name, column_position


REM List Partition Key Columns for Index SubPartitions
REM (or filter by OWNER or NAME (NAME is INDEX_NAME when object_type='INDEX'))
REM Need to order by column_position as SubPartition may consist of multiple columns
select owner, name, column_name
from dba_subpart_key_columns
where object_type = 'INDEX'
order by owner, name, column_position


REM List all Table Partitions (or filter by TABLE_OWNER or TABLE_NAME in the WHERE clause)
REM Need to order by partition_position
select table_owner, table_name, partition_name, high_value, tablespace_name, num_rows, last_analyzed
from dba_tab_partitions
order by table_owner, table_name, partition_position


REM List all Table SubPartitions (or filter by TABLE_OWNER or TABLE_NAME in the WHERE clause)
select table_owner, table_name, partition_name, subpartition_name, high_value, tablespace_name, num_rows, last_analyzed
from dba_tab_subpartitions
order by table_owner, table_name, subpartition_position


REM List all Index Partitions (or filter by INDEX_OWNER or INDEX_NAME in the WHERE clause)
REM Need to order by partition_position
REM Note : For Table Names, you have to join back to dba_indexes
select index_owner, index_name, partition_name, tablespace_name, num_rows, last_analyzed
from dba_ind_partitions
order by index_owner, index_name, partition_position


REM List all Index SubPartitions (or filter by INDEX_OWNER or INDEX_NAME in the WHERE clause)
select index_owner, index_name, partition_name, subpartition_name, tablespace_name, num_rows, last_analyzed
from dba_ind_subpartitions
order by index_owner, index_name, subpartition_position


I have listed only a few columns from the data dictionary views of interest.  You may extract more information by referencing other columns or joining to other data dictionary views.



Categories: DBA Blogs

Partitioning -- 11 : Composite Partitioning

Sun, 2018-12-02 09:53
Oracle allows Composite Partitioning where a Partition can, itself, be Sub-Partitioned.  Each SubPartition is a distinct segment (allocation of physical blocks) while the Partition itself remains a logical definition without a segment.

Composite Partitioning can comprise of :


  • Range-Hash  
  • Range-List  
  • Range-Range
  • List-Range
  • List-Hash
  • List-List
  • Interval-Hash
  • Interval-List
  • Interval-Range

Here is one example of Range-List Partitioning :

SQL> drop table my_sales_table; Table dropped. SQL>
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL>
SQL> l
1 create table my_sales_table
2 (invoice_id number(16) primary key,
3 invoice_date date,
4 region_code varchar2(5),
5 invoice_amount number)
6 partition by range (invoice_date)
7 subpartition by list (region_code)
8 subpartition template
9 (
10 subpartition US values ('US') tablespace tbs_US,
11 subpartition EMEA values ('EMEA') tablespace tbs_EMEA,
12 subpartition ASIA values ('ASIA') tablespace tbs_ASIA,
13 subpartition OTHERS values (DEFAULT) tablespace tbs_OTHERS)
14 (
15 partition p_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')),
16 partition p_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY'))
17* )
SQL> /

Table created.

SQL>
SQL> select table_name, partition_name, subpartition_name
2 from user_tab_subpartitions
3 where table_name = 'MY_SALES_TABLE'
4 /

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ --------------- ------------------------------
MY_SALES_TABLE P_2018 P_2018_US
MY_SALES_TABLE P_2018 P_2018_EMEA
MY_SALES_TABLE P_2018 P_2018_ASIA
MY_SALES_TABLE P_2018 P_2018_OTHERS
MY_SALES_TABLE P_2019 P_2019_US
MY_SALES_TABLE P_2019 P_2019_EMEA
MY_SALES_TABLE P_2019 P_2019_ASIA
MY_SALES_TABLE P_2019 P_2019_OTHERS

8 rows selected.

SQL>
SQL> l
1 select segment_name, segment_type, partition_name, tablespace_name
2 from user_segments
3 where segment_name = 'MY_SALES_TABLE'
4* order by 1,2,3
SQL> /

SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_N
--------------- ------------------ --------------- ------------
MY_SALES_TABLE TABLE SUBPARTITION P_2018_ASIA TBS_ASIA
MY_SALES_TABLE TABLE SUBPARTITION P_2018_EMEA TBS_EMEA
MY_SALES_TABLE TABLE SUBPARTITION P_2018_OTHERS TBS_OTHERS
MY_SALES_TABLE TABLE SUBPARTITION P_2018_US TBS_US
MY_SALES_TABLE TABLE SUBPARTITION P_2019_ASIA TBS_ASIA
MY_SALES_TABLE TABLE SUBPARTITION P_2019_EMEA TBS_EMEA
MY_SALES_TABLE TABLE SUBPARTITION P_2019_OTHERS TBS_OTHERS
MY_SALES_TABLE TABLE SUBPARTITION P_2019_US TBS_US

8 rows selected.

SQL>


Note how the actual SubPartition Names are auto-created by Oracle using the composite of the Partition Name  (P_2018, P_2019) and the SubPartition Name (from the SubPartition Template).

In this case, the names that are SubPartition in USER_TAB_SUBPARTITIONS appear as PARTITION_NAME in USER_SEGMENTS because each of the two logical Partitions (P_2018, P_2019) don't actually have their own Segments.


Note :  I set "deferred_segment_creation" to FALSE so that all the Segments would be created upfront even if they are not populated. "deferred_segment_creation" is an 11g feature.



Categories: DBA Blogs

Partitioning -- 10 : Virtual Column Based Partitioning

Wed, 2018-11-28 03:44
Oracle 11g supports specifying a Virtual Column as the Partition Key.

A Virtual Column is a column where the value is derived on the basis of an expression on other columns or sql/plsql functions.  The actual value is not stored in the block holding the row but is computed when the row is retrieved.

For example :

create table my_sales_table
(invoice_id number primary key,
invoice_date date,
sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
customer_id number,
sale_value number
)
/

insert into my_sales_table
(invoice_id, invoice_date, customer_id, sale_value)
values
(1,sysdate,100,10200)
/

select invoice_id, invoice_date, sale_year
from my_sales_table
/

INVOICE_ID INVOICE_DATE SALE_YEAR
1 28-NOV-18 2018


The MY_SALES_TABLE can be List, Range or Hash Partitioned on SALE_YEAR.

drop table my_sales_table;

create table my_sales_table
(invoice_id number primary key,
invoice_date date,
sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
customer_id number,
sale_value number
)
partition by list(sale_year)
(partition p_2018 values (2018),
partition p_2019 values (2019),
partition p_2020 values (2020)
)
/

insert into my_sales_table
(invoice_id, invoice_date, customer_id, sale_value)
values
(1,sysdate,100,10200)
/

select invoice_date, sale_year from my_sales_table partition (p_2018)
/

INVOICE_DATE SALE_YEAR
28-NOV-18 2018


Thus, the SALE_YEAR value is not actually stored on disk, yet each partition has rows based on the SALE_YEAR (derived) value.


Categories: DBA Blogs

SQL Slowdown ? A short list of potential reasons

Thu, 2018-11-15 20:14
Jonathan Lewis has published a short list of potential reasons why you might see a slowdown in SQL execution.  With newer releases 12.2, 18c and 19c, the list may have to be expanded.



Categories: DBA Blogs

Partitioning -- 9 : System Partitioning

Tue, 2018-11-13 08:59
System Partitioning, introduced in 11g, unlike all the traditional Partitioning methods, requires that all DML specify the Target Partition.  For a System Partitioned Table, the RDBMS does not use a "high value" rule to determine the Target Partition but leaves it to (actually requires) the application code (user) to specify the Partition.

In my opinion, this seems like the precursor to Oracle Database Sharding.

SQL> create table sys_part_table
2 (id_column number,
3 data_element_1 varchar2(50),
4 data_element_2 varchar2(50),
5 entry_date date)
6 partition by SYSTEM
7 (partition PART_A tablespace PART_TBS_A,
8 partition PART_B tablespace PART_TBS_B,
9 partition PART_C tablespace PART_TBS_C)
10 /

Table created.

SQL>


Notice that I did not specify a Partition Key (column).  The Partitions are not mapped to specific values / range of values in a Key column.

Any DML must specify the Target Partition.

SQL> insert into sys_part_table
2 values (1, 'First Row','A New Beginning',sysdate)
3 /
insert into sys_part_table
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method


SQL>
SQL> !oerr ora 14701
14701, 00000, "partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method"
// *Cause: User attempted not to use partition-extended syntax
// for a table partitioned by the System method
// *Action: Must use of partition-extended syntax in contexts mentioned above.

SQL>
SQL> insert into sys_part_table partition (PART_A)
2 values (1, 'First Row','A New Beginning',sysdate)
3 /

1 row created.

SQL> insert into sys_part_table partition (PART_B)
2 values (2,'Second Row','And So It Continues',sysdate)
3 /

1 row created.

SQL>


I have to specify the Target Partition for my INSERT statement. This, obviously, also applies to DELETE and UPDATE statements.   However, I can run a SELECT statement without filtering (pruning) to any Target Partition(s) -- i.e. a SELECT statement that does not use the PARTITION clause will span across all the Partitions.

SQL> select * from sys_part_table;

ID_COLUMN DATA_ELEMENT_1
---------- --------------------------------------------------
DATA_ELEMENT_2 ENTRY_DAT
-------------------------------------------------- ---------
1 First Row
A New Beginning 13-NOV-18

2 Second Row
And So It Continues 13-NOV-18


SQL>


With Tablespaces assigned to the Partitions (see the CREATE table statement above),  I  can have each Partition mapped to a different underlying Disk / Disk Group.
.
.
.
Categories: DBA Blogs

Partitioning -- 8 : Reference Partitioning

Mon, 2018-11-12 08:43
Like Interval Partitioning, another enhancement in 11g is Reference Partitioning.

Reference Partitioning allows you to use a Referential Integrity Constraint to equi-partition a "Child" Table with a "Parent" Table.

Here is a quick demonstration :

SQL> l
1 create table orders
2 (order_id number primary key,
3 order_date date not null,
4 customer_id number)
5 partition by range (order_date)
6 (partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
7 partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
8* )
SQL> /

Table created.

SQL>
SQL> l
1 create table order_lines
2 (line_unique_id number primary key,
3 order_id number not null,
4 order_line_id number,
5 product_id number,
6 product_quantity number,
7 constraint order_lines_fk foreign key (order_id)
8 references orders(order_id)
9 )
10* partition by reference (order_lines_fk)
SQL> /

Table created.

SQL>
SQL> col high_value format a28 trunc
SQL> col table_name format a16
SQL> col partition_name format a8
SQL> select table_name, partition_name, high_value
2 from user_tab_partitions
3 where table_name in ('ORDERS','ORDER_LINES')
4 order by table_name, partition_position
5 /

TABLE_NAME PARTITIO HIGH_VALUE
---------------- -------- ----------------------------
ORDERS P_2017 TO_DATE(' 2018-01-01 00:00:0
ORDERS P_2018 TO_DATE(' 2019-01-01 00:00:0
ORDER_LINES P_2017
ORDER_LINES P_2018

SQL>


Notice the "automatically" created Partitions for the ORDER_LINES ("Child") Table that match those for the ORDERS ("Parent") Table.

.
.
.

Categories: DBA Blogs

Partitioning - 7 : Interval Partitioning

Sat, 2018-10-27 01:32
Interval Partitioning was introduced in 11g as an enhancement to Range Partitioning, but supporting only DATE and NUMBER datatypes.  This allows you to define the interval for each Partition and leave it to the database engine to automatically create new Partitions as required when data is inserted.  Thus, you do not have to pre-create Partitions for future data.

Here is a demo with Monthly Date Intervals.

 2  (manufacture_date date,
3 item_code varchar2(32),
4 item_quantity number(8,0))
5 partition by range (manufacture_date)
6 interval (numtoyminterval(1,'MONTH'))
7 (partition P_1 values less than (to_date('01-JUL-2018','DD-MON-YYYY')))
8 /

Table created.

SQL> set long 32
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 /

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------
P_1 TO_DATE(' 2018-07-01 00:00:00',

SQL>


The INTERVAL clause specifies how the upper bounds for new Partitions are to be defined.  I only need to name the boundary for the first (lowest) Partition and name the Partition.  All subsequent Partitions are automatically created with names assigned by Oracle and high values based on the INTERVAL clause.

Let me insert a few rows.

SQL> insert into manufacturing_summary
2 (manufacture_date, item_code, item_quantity)
3 values
4 (to_date('29-JUN-2018','DD-MON-YYYY'), 'ABC123',4000)
5 /

1 row created.

SQL> insert into manufacturing_summary
2 values (to_date('01-JUL-2018','DD-MON-YYYY'),'ABC123',3000)
3 /

1 row created.

SQL> insert into manufacturing_summary
2 values (to_date('01-JUL-2018','DD-MON-YYYY'),'FGH422',1000)
3 /

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------
P_1 TO_DATE(' 2018-07-01 00:00:00',
SYS_P519 TO_DATE(' 2018-08-01 00:00:00',

SQL>


Oracle automatically created Partition S_P519 for July data.

What happens if there manufactuing daa is not available from 02-Jul-2018 to, say, 04-Sep-2018 ?  And availability of data resumes only on 05-Sep-2018 ?

SQL> insert into manufacturing_summary
2 values (to_date('05-SEP-2018','DD-MON-YYYY'),'ABC123',3000)
3 /

1 row created.

SQL> commit;

Commit complete.

SQL> select partition_position, partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 order by partition_position
5 /

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
HIGH_VALUE
--------------------------------
1 P_1
TO_DATE(' 2018-07-01 00:00:00',

2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

3 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',


SQL>


The third Partition, SYS_P520 is created with the Upper Bound (HIGH_VALUE) of 01-Oct for the September data.

What if August data becomes available subsequently and is inserted ?

SQL> insert into manufacturing_summary
2 values (to_date('10-AUG-2018','DD-MON-YYYY'),'ABC123',1500)
3 /

1 row created.

SQL> commit;

Commit complete.

SQL> select partition_position, partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 order by partition_position
5 /

PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
HIGH_VALUE
--------------------------------
1 P_1
TO_DATE(' 2018-07-01 00:00:00',

2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

3 SYS_P521
TO_DATE(' 2018-09-01 00:00:00',

4 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',


SQL>


A new Partition with the HIGH_VALUE of 01-Sept did get created as SYS_P521 and inserted into the ordered position 3.  While the previously created Partition S_P520 (HIGH_VALUE 01-Oct) got renumbered to 4.  We can verify this by actually querying the Partitions.

SQL> select * from manufacturing_summary partition (SYS_P521);

MANUFACTU ITEM_CODE ITEM_QUANTITY
--------- -------------------------------- -------------
10-AUG-18 ABC123 1500

SQL>
SQL> select * from manufacturing_summary partition (SYS_P520);

MANUFACTU ITEM_CODE ITEM_QUANTITY
--------- -------------------------------- -------------
05-SEP-18 ABC123 3000

SQL>


SYS_P520 was created first for September data although no August data existed.  SYS_P521 was created subsequently for August data which was inserted later.

Remember this : NEVER rely on Partition Names to attempt to identify what data is in a Partition.  Always use PARTITION_POSITION and HIGH_VALUE to identify the logical position (rank) and the data that is present in the Partition.

Where do the Partition names SYS_P519, SYS_P520, SYS_P521 come from ?  They are from a system defined sequence, self-managed by Oracle.

Let me demonstrate this with another example.

SQL> l
1 create table dummy_intvl_tbl
2 (id_col number,
3 data_col varchar2(15))
4 partition by range(id_col)
5 interval (100)
6* (partition P_1 values less than (101))
SQL> /

Table created.

SQL> insert into dummy_intvl_tbl
2 values (50,'data1');

1 row created.

SQL>
SQL> insert into dummy_intvl_tbl
2 values (150,'data3');

1 row created.

SQL>
SQL> insert into manufacturing_summary
2 values (to_date('25-OCT-2018','DD-MON-YYYY'),'FGH422',500);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select table_name, partition_position, partition_name, high_value
2 from user_tab_partitions
3 where table_name in ('MANUFACTURING_SUMMARY','DUMMY_INTVL_TBL')
4 order by 1,2
5 /

TABLE_NAME PARTITION_POSITION PARTITION_NAME
------------------------------ ------------------ ------------------------------
HIGH_VALUE
--------------------------------
DUMMY_INTVL_TBL 1 P_1
101

DUMMY_INTVL_TBL 2 SYS_P525
201

MANUFACTURING_SUMMARY 1 P_1
TO_DATE(' 2018-07-01 00:00:00',

MANUFACTURING_SUMMARY 2 SYS_P519
TO_DATE(' 2018-08-01 00:00:00',

MANUFACTURING_SUMMARY 3 SYS_P521
TO_DATE(' 2018-09-01 00:00:00',

MANUFACTURING_SUMMARY 4 SYS_P520
TO_DATE(' 2018-10-01 00:00:00',

MANUFACTURING_SUMMARY 5 SYS_P526
TO_DATE(' 2018-11-01 00:00:00',


7 rows selected.

SQL>


Note how Partition Name SYS_P525 was allocated to DUMMY_INTVL_TBL and then P_526 to MANUFACTURING_SUMMARY.
These System Defined Partition names use a *global* sequence, not tied to a specific table.

Can you rename the System Defined Partition after it has been automatically created ?

SQL> alter table manufacturing_summary
2 rename partition SYS_P519 to Y18M07
3 /

Table altered.

SQL> alter table manufacturing_summary
2 rename partition SYS_P520 to Y18M09
3 /

Table altered.

SQL> alter table manufacturing_summary
2 rename partition SYS_P521 to Y18M08
3 /

Table altered.

SQL> alter table manufacturing_summary
2 rename partition SYS_P526 to Y18M10
3 /

Table altered.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'MANUFACTURING_SUMMARY'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------
P_1 TO_DATE(' 2018-07-01 00:00:00',
Y18M07 TO_DATE(' 2018-08-01 00:00:00',
Y18M08 TO_DATE(' 2018-09-01 00:00:00',
Y18M09 TO_DATE(' 2018-10-01 00:00:00',
Y18M10 TO_DATE(' 2018-11-01 00:00:00',

SQL>


Yes, fortunately, you CAN rename the Partitions *after* they are automatically created.



Categories: DBA Blogs

Partitioning -- 6 : Hash Partitioning

Sun, 2018-09-30 06:25
Unlike Range or List Partitioning where you define the rule which identifies which Partition a row will be inserted into (based on the value in the Partition Key Column(s)),  Hash Partitioning relies on Oracle applying a "hashing formula (algorithm)" to "randomly" distribute incoming rows across the available Partitions in the table.
This would be useful when you want to break up a table into smaller physical segments (maybe into even separate Tablespaces on different disks) without considering grouping of data.  In Date based Range Partitioning data is grouped into different Partitions -- i.e. physical segments on disk --  based on the Date value (e.g. by Month or Year).  In List Partitioning, data is grouped based on the value in the Partition Key Column.

Here is a small example on Hash Partitioning :

SQL> create table iot_incoming_data
2 (data_item_number number,
3 data_item_key varchar2(32),
4 data_item_value varchar2(64),
5 data_item_timestamp timestamp)
6 partition by hash (data_item_number)
7 (partition p1 tablespace hash_ptn_1,
8 partition p2 tablespace hash_ptn_2,
9 partition p3 tablespace hash_ptn_3,
10 partition p4 tablespace hash_ptn_4)
11 /

Table created.

SQL>


In this definition of the table, I have "randomly" distributed incoming rows across 4 Partitions in 4 different Tablespaces.  Given the incoming "data_item_number" values (either machine generated or from a sequence), each of the 4 Partitions would be equally loaded.
(In contrast, in Date based Range Partitioning of, say, a SALES table, you might have fewer rows in older Partitions and an increasing number of rows in new Partitions as your business and Sales Volume grow over time !).

Unlike Range Partitioning, Hash Partitioning will not perform well for a "range based query"  (e.g. a range of sales dates or a range of data item numbers).  It is suitable for "equality" or "in-list" predicates.  If you do need a range based query, you would need a Global Index.

Note that it is advised that you should use a Power of 2 for the number of Hash Partitions.

Let me demonstrate insertion of data :

SQL> insert into iot_incoming_data
2 select rownum,
3 dbms_random.string('X',16),
4 dbms_random.string('X',32),
5 systimestamp
6 from dual
7 connect by level < 10001;

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('HEMANT','IOT_INCOMING_DATA');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'IOT_INCOMING_DATA'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 2471
P2 2527
P3 2521
P4 2481

SQL>


Note that I have inserted the 10,000 rows from a single session.  In the real world, you would have multiple sessions concurrently inserting rows into the table.
Based on the Hashing algorithm that Oracle used (note : this is internal to Oracle and we cannot use any custom algorithm), Oracle has more or less evenly distributed the incoming rows across the 4 Partitions.

Let me select some random rows from the Partitions :

SQL> select data_item_number  
2 from iot_incoming_data partition (P1)
3 where rownum < 6
4 order by 1;

DATA_ITEM_NUMBER
----------------
8361
8362
8369
8379
8380

SQL>
SQL> select data_item_number
2 from iot_incoming_data partition (P2)
3 where rownum < 6
4 order by 1
5 /

DATA_ITEM_NUMBER
----------------
8087
8099
8101
8105
8109

SQL>
SQL> select data_item_number
2 from iot_incoming_data partition (P3)
3 where rownum < 6
4 and data_item_number < 100
5 order by 1
6 /

DATA_ITEM_NUMBER
----------------
2
5
8
18
20

SQL>
SQL> select data_item_number
2 from iot_incoming_data partition (P4)
3 where rownum < 6
4 and data_item_number between 1000 and 1100
5 order by 1
6 /

DATA_ITEM_NUMBER
----------------
1001
1002
1005
1008
1009

SQL>


(The first two queries returned rows with values greater than 8000 simply because I didn't specify a range of values as a filter and those rows came from the first few blocks that Oracle read from the buffer cache).
Note how the DATA_ITEM_NUMBER values indicate "near-random" distribution of rows across the Partitions.  It is likely that if I had created multiple sessions concurrently running inserts into the table, distribution of the rows would have been even more "random".



Categories: DBA Blogs

Partitioning -- 5 : List Partitioning

Sun, 2018-09-16 10:14
List Partitioning allows you to specify a value (or a set of values) for the Partition Key to map to each Partition.

This example shows List Partitioning.

SQL> create table request_queue
2 (request_id number primary key,
3 request_submision_time timestamp,
4 requestor number,
5 request_arg_1 varchar2(255),
6 request_arg_2 varchar2(255),
7 request_arg_3 varchar2(255),
8 request_status varchar2(10),
9 request_completion_time timestamp)
10 partition by list (request_status)
11 (partition p_submitted values ('SUBMITTED'),
12 partition p_running values ('RUNNING'),
13 partition p_errored values ('ERRORED'),
14 partition p_completed values ('COMPLETED'),
15 partition p_miscell values ('RECHECK','FLAGGED','UNKNOWN'),
16 partition p_default values (DEFAULT)
17 )
18 /

Table created.

SQL>


Note how the P_MISCELL Partition can host multiple values for the REQUEST_STATUS column.
The last Partition, has is specified as a DEFAULT Partition (note that DEFAULT is a keyword, not a value like the others) to hold rows for REQUEST_STATUS for values not mapped to any of the other Partitions.  With List Partitioning, you should always have a DEFAULT Partition (it can have any name, e.g. P_UNKNOWN) so that unmapped rows can be captured.

If you go back to my previous post on Row Movement, you should realise the danger of capturing changing values (e.g. from "SUBMITTED" to "RUNNING" to "COMPLETED") in different Partitions.  What is the impact of updating a Request from the "SUBMITTED" status to the "RUNNING" status and then to the "COMPLETED" status ?  It is not simply an update of the REQUEST_STATUS column alone but a physical reinsertion of the entire row (with the consequent update to all indexes) at each change of status.

SQL> insert into request_queue
2 values (request_id_seq.nextval,systimestamp,101,
3 'FAC1','NOTE',null,'SUBMITTED',null)
4 /

1 row created.

SQL>
SQL> commit;

Commit complete.

.... sometime later ....

SQL> update request_queue
2 set request_status = 'RUNNING'
3 where request_id=1001
4 /
update request_queue
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL>


So, although now we know that we must ENABLE ROW MOVEMENT, we must suffer the impact of the physical reinsertion of the entire row into a new Partition.

SQL> alter table request_queue enable row movement;

Table altered.

SQL> update request_queue
2 set request_status = 'RUNNING'
3 where request_id=1001
4 /

1 row updated.

SQL> commit;

Commit complete.

SQL>
.... sometime later ....

SQL> update request_queue
2 set request_status = 'COMPLETED',
3 request_completion_time=systimestamp
4 where request_id=1001
5 /

1 row updated.

SQL> commit;

Commit complete.

SQL>


(Note that all the previous "Partitioning 3a to 3d" posts about Indexing apply to List Partitioning as well)



Categories: DBA Blogs

Partitioning -- 4 : Row Movement

Sun, 2018-09-09 10:06
Do you expect Primary Keys to be updatable ?  Some argue that Primary Key values should be immutable.  The argument is that a Primary Key should not be modified.

What about Partition Keys ?  Would you allow a Partition Key to be updated ?

Let me take the SALES_DATA table again :

SQL> desc sales_data
Name Null? Type
----------------------------------------- -------- ----------------------------
SALE_ID NOT NULL NUMBER
SALE_DATE DATE
INVOICE_NUMBER VARCHAR2(21)
CUSTOMER_ID NUMBER
PRODUCT_ID NUMBER
SALE_VALUE NUMBER

SQL> insert into sales_data
2 values (sales_data_seq.nextval,
3 to_date('09-SEP-2019','DD-MON-YYYY'),
4 'INV320001X',
5 45,
6 52,
7 10000)
8 /

1 row created.

SQL> commit;

Commit complete.

SQL>


After the INSERT, I realise that the year in the SALE_DATE is wrong -- it is 2019 instead of 2018.  I need to update the row to set the year to 2018.
(Since the SALES_DATA table is partitioned to have a separate Partition for each year, this row has gone into the P_2019 Partition).

SQL> select * from sales_data
2 where invoice_number='INV320001X' and customer_id=45;

SALE_ID SALE_DATE INVOICE_NUMBER CUSTOMER_ID PRODUCT_ID SALE_VALUE
---------- --------- --------------------- ----------- ---------- ----------
320001 09-SEP-19 INV320001X 45 52 10000

SQL> select * from sales_data partition (P_2019);

SALE_ID SALE_DATE INVOICE_NUMBER CUSTOMER_ID PRODUCT_ID SALE_VALUE
---------- --------- --------------------- ----------- ---------- ----------
320001 09-SEP-19 INV320001X 45 52 10000

SQL>
SQL> update sales_data
2 set sale_date = to_date('09-SEP-2018','DD-MON-YYYY')
3 where sale_id=320001
4 /
update sales_data
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL>


I encounter an error.  Oracle does not like updating a Partition Key value such that the row would have to move to a different Partition --- from the P_2019 Partition to the P_2018 Partition.

How would I allow updates that result in a row moving to a different Partition ?

SQL> alter table sales_data enable row movement;

Table altered.

SQL> update sales_data
2 set sale_date = to_date('09-SEP-2018','DD-MON-YYYY')
3 where sale_id=320001
4 /

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from sales_data partition (P_2019);

no rows selected

SQL> select * from sales_data partition (P_2018)
2 where sale_id=320001
3 /

SALE_ID SALE_DATE INVOICE_NUMBER CUSTOMER_ID PRODUCT_ID SALE_VALUE
---------- --------- --------------------- ----------- ---------- ----------
320001 09-SEP-18 INV320001X 45 52 10000

SQL>


The ALTER TABLE ... ENABLE ROW MOVEMENT is a DDL command (needs to be issued only once to allow any number of subsequent updates to the tables rows) that allows a row to move from one Partition to another Partition.  In this case, the row moved from P_2019 to P_2018.

Moving rows from one Partition to another Partition is expensive.  Each row moved in such a manner results in
(a) marking deletion of the row from the original Partition
(b) physically inserting the *entire* rows (irrespective of length of the row) into the new Partition -- not just the SALE_DATE value but every column has to be written into a block in the new Partition
(c) updating *every* index (Global or Local) on the Table

That is why it is not a good design to have frequently updated Partition Keys resulting in a row moving from one Partition to another.  You may have to reconsider the Partitioning definition or data and transaction flow in the application.

(Do you know where else ENABLE ROW MOVEMENT is required ?  There are other cases, not related to Partitioning, where you may have to ENABLE ROW MOVEMENT for a table.  By default when you CREATE a Table, ROW MOVEMENT is not enabled unless you explicitly enable it).



Categories: DBA Blogs

Partitioning -- 3d : Partial Indexing (in 11g)

Sat, 2018-09-08 10:24
Oracle 12c has introduced a new feature called "Partial Index" whereby selective partitions of a Table are indexed.  This is useful, for example, where you have a large historical table and you know that older Partitions are infrequently accessed and no longer need to be indexed.  For such tables, you can afford to "lose" the index for these older Partitions.

How would you do this in 11.2 ?

Let me go back to the SALES_DATA table with data from 2016 to 2018 populated.  This is the status of the index partition segments :

SQL> l
1 select segment_name, partition_name, bytes/1024
2 from user_segments
3 where segment_type = 'INDEX PARTITION'
4 and segment_name in
5 (select index_name
6 from user_indexes
7 where table_name = 'SALES_DATA')
8* order by 1,2
SQL> /

SEGMENT_NAME PARTITION_NA BYTES/1024
------------------------------ ------------ ----------
SALES_DATA_LCL_NDX_1 P_2016 3072
SALES_DATA_LCL_NDX_1 P_2017 3072
SALES_DATA_LCL_NDX_1 P_2018 3072
SALES_DATA_LCL_NDX_2 P_2016 64
SALES_DATA_LCL_NDX_2 P_2017 64
SALES_DATA_LCL_NDX_2 P_2018 64

6 rows selected.

SQL>


So, if I now want to "unindex" the year 2016 partition (P_2016) of the SALES_DATA table, I can :

SQL> show parameter deferred_segment_creation

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE

SQL> alter index SALES_DATA_LCL_NDX_1 modify partition P_2016 unusable;

Index altered.

SQL> alter index SALES_DATA_LCL_NDX_2 modify partition P_2016 unusable;

Index altered.

SQL>
SQL> l
1 select segment_name, partition_name, bytes/1024
2 from user_segments
3 where segment_type = 'INDEX PARTITION'
4 and segment_name in
5 (select index_name
6 from user_indexes
7 where table_name = 'SALES_DATA')
8* order by 1,2
SQL> /

SEGMENT_NAME PARTITION_NA BYTES/1024
------------------------------ ------------ ----------
SALES_DATA_LCL_NDX_1 P_2017 3072
SALES_DATA_LCL_NDX_1 P_2018 3072
SALES_DATA_LCL_NDX_2 P_2017 64
SALES_DATA_LCL_NDX_2 P_2018 64

SQL>
SQL> select count(*) from sales_data partition (P_2016);

COUNT(*)
----------
100000

SQL>



You will notice that although the P_2016 Partition in the Table has data, the corresponding Index Partition no longer has a segment -- no space is allocated to it  (although the logical definition of the index exists).  This is possible with the "deferred_segment_creation" parameter set to TRUE in 11g.

In fact, you will notice that although the table has Partitions for 2019 and 2020 and MAXVALUE, corresponding Index Partition Segments do not exist (because no data has been inserted into those Table Partitions yet) !

SQL> select partition_name           
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NA
------------
P_2016
P_2017
P_2018
P_2019
P_2020
P_MAXVALUE

6 rows selected.

SQL>
SQL> select index_name, partition_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7 order by index_name, partition_position
8 /

INDEX_NAME PARTITION_NA STATUS
------------------------------ ------------ --------
SALES_DATA_LCL_NDX_1 P_2016 UNUSABLE
SALES_DATA_LCL_NDX_1 P_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USABLE
SALES_DATA_LCL_NDX_2 P_2016 UNUSABLE
SALES_DATA_LCL_NDX_2 P_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USABLE

12 rows selected.

SQL>


This behaviour is a consequence of "deferred_segment_creation".

Note : If a Partitioned Index is a Unique / Primary Key Index, do NOT attempt to set an Index Partition to UNUSABLE.  UNUSABLE status would prevent INSERTs into the table.



Categories: DBA Blogs

Some Statistics on this Blog

Fri, 2018-08-31 00:04
This blog now has 630 posts (including this one), 1000 comments and 1.82million pageviews to date.



Categories: DBA Blogs

Pages