16 December, 2018

Partioning -- 13a : Relocating a Partition

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.


13 December, 2018

Partitioning -- 12 : Data Dictionary Queries

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.



02 December, 2018

Partitioning -- 11 : Composite Partitioning

Oracle allows Composite Partitioning where a Partition can, itself, be Sub-Partitioned.  Each SubPartition is a distinct segment (allocation of physical Oracle Data 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.



28 November, 2018

Partitioning -- 10 : Virtual Column Based Partitioning

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.


16 November, 2018

SQL Slowdown ? A short list of potential reasons

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.



13 November, 2018

Partitioning -- 9 : System Partitioning

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.
.
.
.

12 November, 2018

Partitioning -- 8 : Reference Partitioning

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.

.
.
.

27 October, 2018

Partitioning - 7 : Interval Partitioning

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.



30 September, 2018

Partitioning -- 6 : Hash Partitioning

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".



16 September, 2018

Partitioning -- 5 : List Partitioning

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)



09 September, 2018

Partitioning -- 4 : Row Movement

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

Edit  14-Sep-18:
Also see two earlier posts :
"Enable Row Movement"
and
"Enable Row Movement with MSSM"

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).



08 September, 2018

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

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.



31 August, 2018

Some Statistics on this Blog

This blog now has 630 posts (including this one), 1000 comments and 1.82million pageviews to date.



26 August, 2018

Partitioning -- 3c : Unique Index[es] on a Partitioned Table

Let's explore what sort of Unique Indexes you can create on a Partitioned Table.

There are three types of partitioning for Indexes :

a  Global (Non-Partitioned)

b  Global Partitioned

c  Local Partitioned

Can a Unique Index be created using either type ?

Let me start with another table, SALES_DATA_2  which has the same structure and Partition Key as SALES_DATA, except that it doesn't have the Primary Key definition that builds the Unique Index.

SQL> l
  1  CREATE TABLE SALES_DATA_2
  2 ( SALE_ID NUMBER,
  3   SALE_DATE DATE,
  4   INVOICE_NUMBER VARCHAR2(21),
  5   CUSTOMER_ID NUMBER,
  6   PRODUCT_ID NUMBER,
  7   SALE_VALUE NUMBER
  8 )
  9    TABLESPACE HEMANT
 10    PARTITION BY RANGE (SALE_DATE)
 11   (PARTITION P_2018  VALUES LESS THAN (TO_DATE(' 2019-01-01','YYYY-MM-DD'))
 12    TABLESPACE TBS_YEAR_2018 ,
 13   PARTITION P_2019 VALUES LESS THAN (TO_DATE(' 2020-01-01','YYYY-MM-DD'))
 14    TABLESPACE TBS_YEAR_2019 ,
 15   PARTITION P_2020 VALUES LESS THAN (TO_DATE(' 2021-01-01','YYYY-MM-DD'))
 16    TABLESPACE TBS_YEAR_2020 ,
 17   PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)
 18*   TABLESPACE HEMANT )
SQL> /

Table created.

SQL> 


Next, I try a Global (Non-Partitioned) Unique Index on SALE_ID.  Note that the "GLOBAL" Keyword is optional if it is Non-Partitioned.

SQL> create unique index sales_2_uk
  2  on sales_data_2 (sale_id) global
  3  tablespace hemant
  4  /

Index created.

SQL>
SQL> select partitioned, status
  2  from user_indexes
  3  where index_name = upper('sales_2_uk')
  4  /

PAR STATUS
--- --------
NO  VALID

SQL> drop index sales_2_uk; 

Index dropped.

SQL> 


Effectively, this Global Index is the same as the Primary Key index on SALES_DATA that I built earlier.

Next, I try a Unique Global Partitioned Index on the same column.

SQL> create unique index sales_2_uk
  2  on sales_data_2 (sale_id) global
  3  partition by range (sale_id)
  4  (partition p_1mill values less than (1000001) tablespace new_indexes,
  5   partition p_2mill values less than (2000001) tablespace new_indexes,
  6   partition p_3mill values less than (3000001) tablespace new_indexes,
  7   partition p_maxval values less than (maxvalue) tablespace new_indexes)
  8  /

Index created.

SQL>
SQL> select uniqueness, partitioned, status
  2  from user_indexes
  3  where index_name = upper('sales_2_uk')
  4  /

UNIQUENES PAR STATUS
--------- --- --------
UNIQUE    YES N/A

SQL>
SQL> l
  1  select column_position, column_name
  2  from user_part_key_columns
  3  where name = upper('sales_2_uk')
  4* order by column_position
SQL> /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
              1 SALE_ID

SQL> 
SQL> select partition_name, status
  2  from user_ind_partitions
  3  where index_name = upper('sales_2_uk')
  4  order by partition_position
  5  /

PARTITION_NAME                 STATUS
------------------------------ --------
P_1MILL                        USABLE
P_2MILL                        USABLE
P_3MILL                        USABLE
P_MAXVAL                       USABLE

SQL> 


So, that is a valid Unique Global Partitioned Index.

The next attempt is a Unique Local Partitioned Index -- i.e. partitioned by the same key as the Table.

SQL> create unique index sales_2_uk
  2  on sales_data_2 (sale_id) local
  3  /
on sales_data_2 (sale_id) local
   *
ERROR at line 2:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index


SQL> !oerr ora 14039              
14039, 00000, "partitioning columns must form a subset of key columns of a UNIQUE index"
// *Cause:  User attempted to create a UNIQUE partitioned index whose
//          partitioning columns do not form a subset of its key columns
//          which is illegal
// *Action: If the user, indeed, desired to create an index whose
//          partitioning columns do not form a subset of its key columns,
//          it must be created as non-UNIQUE; otherwise, correct the
//          list of key and/or partitioning columns to ensure that the index'
//          partitioning columns form a subset of its key columns

SQL>
SQL> create unique index sales_2_uk
  2  on sales_data_2 (sale_id, sale_date) local
  3  /

Index created.

SQL> 
SQL> select uniqueness, partitioned, status
  2  from user_indexes
  3  where index_name = upper('sales_2_uk')
  4  /

UNIQUENES PAR STATUS
--------- --- --------
UNIQUE    YES N/A

SQL> select column_position, column_name
  2  from user_part_key_columns
  3  where name = upper('sales_2_uk')
  4  order by column_position
  5  /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
              1 SALE_DATE

SQL> select column_position, column_name 
  2  from user_ind_columns
  3  where index_name = upper('sales_2_uk')
  4  order by column_position
  5  /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
              1 SALE_ID
              2 SALE_DATE

SQL> 
SQL> select partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name = upper('sales_2_uk')
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
P_2018                         TBS_YEAR_2018                  USABLE
P_2019                         TBS_YEAR_2019                  USABLE
P_2020                         TBS_YEAR_2020                  USABLE
P_MAXVALUE                     HEMANT                         USABLE

SQL> 


So, a Unique Local Partitioned Index must include the Table Partition Key as a subset of the Index Key columns.  This is something you must consider when Partitioning the Table and Index both.
(Also, note how USER_PART_KEY_COLUMNS doesn't show SALE_ID as a Partition Key.  This is in 11.2.0.4)



18 August, 2018

Partitioning -- 3b : More Indexes on a Partitioned Table

In the previous blog post, I had demonstrated a Global (Non-Partitioned) Index and a Local (Partitioned) Index.  A Global Index itself may be partitioned.  A Local Index is actually Equi-Partitioned with the Tablee.

Let me demonstrate a Global Partitioned Index on the same table now.

Given the SALES_DATA table which is partitioned by SALE_DATE, I might want to support queries by CUSTOMER_ID.  What if I have millions of customers?  My CUSTOMERS table might be partitioned by CUSTOMER_ID.  Can I have an Index on SALES_DATA that is also partitioned by CUSTOMER_ID ?

SQL> l
  1  create index sales_data_glbl_part_ndx_2
  2  on sales_data (customer_id)
  3  global
  4  partition by range (customer_id)
  5  (
  6   partition p_1 values less than (501000) tablespace new_indexes,
  7   partition p_2 values less than (1000001) tablespace new_indexes,
  8   partition p_3 values less than (1500001) tablespace new_indexes,
  9   partition p_4 values less than (maxvalue)
 10* )
SQL> /

Index created.

SQL> 


This is a Global Partitioned Index which is prefixed on a left prefix (CUSTOMER_ID) of the Index Column(s). Oracle does not support Global Partitioned Indexes that are non-prefixed.  Let me demonstrated the difference between the two.

SQL> drop index sales_data_glbl_part_ndx_2;

Index dropped.

SQL> 
SQL> l
  1  create index sales_data_glbl_part_ndx_2
  2  on sales_data (customer_id, sale_date)
  3  global
  4  partition by range (customer_id)
  5  (
  6   partition p_1 values less than (501000) tablespace new_indexes,
  7   partition p_2 values less than (1000001) tablespace new_indexes,
  8   partition p_3 values less than (1500001) tablespace new_indexes,
  9   partition p_4 values less than (maxvalue)
 10* )
SQL> /

Index created.

SQL> 
SQL> 
SQL> l
  1  create index sales_data_glbl_part_ndx_3
  2  on sales_data (sale_date)
  3  global
  4  partition by range (customer_id)
  5  (
  6   partition p_1 values less than (501000) tablespace new_indexes,
  7   partition p_2 values less than (1000001) tablespace new_indexes,
  8   partition p_3 values less than (1500001) tablespace new_indexes,
  9   partition p_4 values less than (maxvalue)
 10* )
SQL> /
partition by range (customer_id)
                               *
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL> 
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause:  User attempted to create a GLOBAL non-prefixed partitioned index
//          which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
//          index, it must be created as LOCAL; otherwise, correct the list 
//          of key and/or partitioning columns to ensure that the index is 
//          prefixed

SQL> 


My first attempt above is valid because the Index is partitioned on CUSTOMER_ID although it is defined on (CUSTOMER_ID, SALE_DATE).  Thus, it is partitioned on a left prefix. The second index attempt fails because the Partition Key (CUSTOMER_ID) is not a left prefix (part of) the Index key (SALE_DATE alone).

Remember : The Index I've created is  partitioned on CUSTOMER_ID while the Table itself is partitioned on SALE_DATE.
This Index is a Global Index so any Partition Maintenance operation (TRUNCATE, DROP, SPLIT, MERGE) may result in the *entire* Index becoming UNUSABLE unless the UPDATE INDEXES clause is used in the Partition Maintenance DDL statement.  This was demonstrated with the Primary Key index being a Global Index in my earlier blog post.


What about Bitmap Indexes ?  Can a Bitmap Index on a partitioned Table be either Global or Local ?  Oracle does not support Global Bitmap Indexes.  A Bitmap Index on a Partitioned Table has to be Local.

SQL> create bitmap index sales_data_glbl_ndx_4
  2  on sales_data(product_id)
  3  /
on sales_data(product_id)
   *
ERROR at line 2:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables


SQL> !oerr ora 25122
25122, 00000, "Only LOCAL bitmap indexes are permitted on partitioned tables"
// *Cause: An attempt was made to create a global bitmap index on a partioned
//         table.
// *Action: create a local bitmap index instead.

SQL> 
SQL> create bitmap index sales_data_lcl_ndx_2
  2  on sales_data(product_id) local
  3  /

Index created.

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_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2018                         TBS_YEAR_2018
P_2019                         TBS_YEAR_2019
P_2020                         TBS_YEAR_2020
P_MAXVALUE                     USERS

SQL> select partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name = 'SALES_DATA_LCL_NDX_2'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
P_2018                         TBS_YEAR_2018                  USABLE
P_2019                         TBS_YEAR_2019                  USABLE
P_2020                         TBS_YEAR_2020                  USABLE
P_MAXVALUE                     USERS                          USABLE

SQL> 


As demonstrated earlier, a Local Index automatically inherits the Partition Name and Tablespace Name from the Table Partitions.

To summarise, these are the Indexes on my SALES_DATA table :

SQL> l        
  1  select index_name, index_type, partitioned, tablespace_name, status
  2  from user_indexes
  3* where table_name = 'SALES_DATA'
SQL> /

INDEX_NAME                     INDEX_TYPE                  PAR
------------------------------ --------------------------- ---
TABLESPACE_NAME                STATUS
------------------------------ --------
SYS_C0017514                   NORMAL                      NO
HEMANT                         VALID

SALES_DATA_LCL_NDX_1           NORMAL                      YES
                               N/A

SALES_DATA_GLBL_PART_NDX_2     NORMAL                      YES
                               N/A

SALES_DATA_LCL_NDX_2           BITMAP                      YES
                               N/A


SQL>


Remember that for a Partitioned Index (either Global or Local), the Tablespace Name and Status are attributes at the Partition definition level not at the Index definition.


17 August, 2018

Oracle Database Configurations using Docker and Vagrant

Oracle now makes available configurations for the Database (and other products) on both Docker and Vagrant via GitHub.

Good time to familiarize oneself with GitHub, Docker and/or Vagrant.


For the Docker configuration see : https://github.com/oracle/docker-images/tree/master/OracleDatabase

Helpful guide to installation at http://oracle-help.com/articles/prebuilt-oracle-database-18c-with-docker/


For the Vagrant configuration see : https://github.com/oracle/vagrant-boxes/tree/master/OracleDatabase

Helpful guide to installation at http://dbaparadise.com/2018/08/18c-up-and-running-in-30-minutes/  and  http://oracle-help.com/oracle-18c/oracle-database-18c-installation-with-vagrant/




(Note : The Examples have been available on GitHub for quite some time at https://github.com/oracle/oracle-db-examples )



12 August, 2018

Partitioning -- 3a : Indexes on a Partitioned Table

Building on the case study of the Range Partitioned Table from the previous Blog Post, here are some Indexes.

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

INDEX_NAME                     TABLESPACE_NAME                PAR UNIQUENES
------------------------------ ------------------------------ --- ---------
SYS_C0017514                   HEMANT                         NO  UNIQUE

SQL> 
SQL> select column_name, column_position
  2  from user_ind_columns
  3  where index_name = 'SYS_C0017514'
  4  /

COLUMN_NAME                    COLUMN_POSITION
------------------------------ ---------------
SALE_ID                                      1

SQL> 


We have an Index automatically built for the Primary Key constraint (note that the Index was created in the user's DEFAULT Tablespace).  This Index is a Global, Non-Partitioned Index.   Therefore, any Partition Maintenance operation (for a non-empty Partition) on the table may set the Index UNUSABLE unless the UPDATE INDEXES clause is used.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> alter table sales_data drop partition P_2016;

Table altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> insert into sales_data
  2  values (1001,to_date('01-FEB-2017','DD-MON-RR'),'ABC1001',1,10,1000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table sales_data drop partition P_2017;

Table altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
UNUSABLE

SQL> 
SQL> alter index SYS_C0017514 rebuild;

Index altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> 


When I dropped the *empty* P_2016 Partition, the Index remained valid.  However, when I dropped the *non-empty*  P_2017 Partition (even if it has a single row and irrespective of whether Table/Partition statistics have been gathered) without the UPDATE INDEXES clause, the Index became UNUSABLE.  An UNUSABLE Unique Index will not allow fresh inserts (into *any* Partition of the table).

Next, I attempt to create a Local Partitioned Index.  Such an Index has a Partition corresponding to each Table Partition.

SQL> create index sales_data_lcl_ndx_1
  2  on sales_data (sale_date, invoice_number) local
  3  /

Index created.

SQL> select partitioned      
  2  from user_indexes
  3  where index_name = 'SALES_DATA_LCL_NDX_1'
  4  /

PAR
---
YES

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

PAR TABLESPACE_NAME                STATUS
--- ------------------------------ --------
YES                                N/A

SQL> 
SQL> select partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name = 'SALES_DATA_LCL_NDX_1'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
P_2018                         TBS_YEAR_2018                  USABLE
P_2019                         TBS_YEAR_2019                  USABLE
P_2020                         TBS_YEAR_2020                  USABLE
P_MAXVALUE                     USERS                          USABLE

SQL> 


A Local Partitioned Index is created with the LOCAL keyword in the CREATE INDEX statement.
For a Partitioned Index, the TABLESPACE_NAME and STATUS attributes carry no meaning at the Index level --- these have values for each Partition.

Note how the Index Partitions were created with the same Partition Name and Tablespace Name as the Table Partitions. Similarly, any Partition Maintenance operations (DROP, MERGE, SPLIT) at the Table level will automatically be applied to the Index, dropping/creating the corresponding Index Partition(s).

In my next post, I will look at two other types of Index definitions on a Partitioned Table  (Global Partitioned Index and Bitmap Index).




09 August, 2018

Partitioning -- 2 : Simple Range Partitioning -- by DATE

Range Partitioning allows you to separate a logical table into a number of distinct physical segments, each segment holding data that maps to a range of values.
(I encourage you to read the Introduction in the first post in this series)

The simplest and most common implementation is Range Partitioning by a DATE column.

SQL> l
  1  create table sales_data
  2  (sale_id number primary key,
  3   sale_date date,
  4   invoice_number varchar2(21),
  5   customer_id number,
  6   product_id number,
  7   sale_value number)
  8  partition by range (sale_date)
  9  (partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY'))
 10   tablespace TBS_YEAR_2015,
 11  partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY'))
 12   tablespace TBS_YEAR_2016,
 13  partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY'))
 14   tablespace TBS_YEAR_2017,
 15  partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
 16   tablespace TBS_YEAR_2018,
 17  partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY'))
 18   tablespace TBS_YEAR_2019,
 19  partition P_MAXVALUE values less than (MAXVALUE)
 20   tablespace USERS
 21* )
SQL> /

Table created.

SQL> 


Here, I have created each Partition in a separate tablespace.  Note that the Partition Key (SALE_DATE) does not have to be the same as the Primary Key (SALE_ID)

I have also created a MAXVALUE Partition  (Some DBAs/Developers may mistakenly assume this to be a *default* partition.  Range Partitioning, unlike List Partitioning, does not have the concept of a "default" partition.  This simply is the Partition for incoming rows that have Partition Key value that is higher than the last (highest) defined Partition Key Upper Bound (31-Dec-2019 23:59:59 in this case)).

I can look up the data dictionary for these partitions in this manner :

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_2015          TBS_YEAR_2015
P_2016          TBS_YEAR_2016
P_2017          TBS_YEAR_2017
P_2018          TBS_YEAR_2018
P_2019          TBS_YEAR_2019
P_MAXVALUE         USERS

6 rows selected.

SQL> 


Partitions are ordered by Partition *Position*  not Name.

How do I add a new partition for data for the year 2020 ?  By "splitting" the MAXVALUE partition.

SQL> alter table sales_data                 
  2  split partition P_MAXVALUE
  3  at (to_date('01-JAN-2021','DD-MON-YYYY'))
  4  into
  5  (partition P_2020 tablespace TBS_YEAR_2020, partition P_MAXVALUE)
  6  /

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:00', 'SYYYY-MM-DD
P_2016          TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD
P_2017          TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD
P_2018          TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD
P_2019          TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD
P_2020          TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD
P_MAXVALUE         MAXVALUE

7 rows selected.

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

PARTITION_NAME         TABLESPACE_NAME
------------------------------ ------------------------------
P_2015          TBS_YEAR_2015
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

7 rows selected.

SQL> 


Note that, irrespective of the data format I specify in the CREATE or SPLIT commands, Oracle presents the Upper Bound Date (HIGH_VALUE) in it's own format, using a Gregorian Calendar.

How do I remove an older partition ?

SQL> alter table sales_data
  2  drop partition P_2015
  3  /

Table altered.

SQL> 


A DROP command is very simple.

In my next post, I will add Indexes to this table.



Partitioning -- 1 : Introduction

I am beginning a new series of Blog Posts on Partitioning in Oracle.  I plan to cover 11g and 12c.   I might add posts on changes in 18c  (which is really 12.2.0.2 currently)

First, this is my presentation at AIOUG Sangam 11
and this the corresponding article

This series of posts will have new examples, from the simple to the complex, not present in the above presentation / article.

27 July, 2018

18c (18.3) Installation On Premises

Documentation by @oraclebase  (Tim Hall) on installing 18c (18.3) On Premises on OEL :

Oracle Database 18c Installation On Oracle Linux 6 (OL6) and 7 (OL7)

To understand Patch Numbering in 18c, see Oracle Support Document IDs 2337415.1 and 2369376.1

.
.
.




 

27 June, 2018

Global Temporary Table -- revisited

Revisiting the previous case in a 12.2 PDB ....

(This time, the two sessions by "HEMANT" and "SYSTEM" have the Username as the SQL prompt)

In the previous blog post, I demonstrated how to check space allocation for a GTT.   But how does Oracle determine how much space to allocate ?

HEMANT>create global temporary table my_gtt_2
  2  (id_number number, object_name varchar2(128))
  3  on commit preserve rows
  4  /

Table created.

HEMANT>
SYSTEM>select sid,serial# from v$session where username = 'HEMANT';

       SID    SERIAL#
---------- ----------
       300      11923

SYSTEM>
SYSTEM>select username, session_num, sql_id, tablespace,  contents, segtype, con_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  /      

no rows selected

SYSTEM>


So, the creation of a GTT does not allocate any space. A GTT definition is a logical definition and does not allocate space unless and until rows are inserted.

Let me insert a row and check the space.

HEMANT>insert into my_gtt_2 values (1, 'First Object');

1 row created.

HEMANT>commit;

Commit complete.

HEMANT>
SYSTEM>select username, session_num, sql_id, tablespace,  contents, segtype, con_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
SESSION_NUM SQL_ID        TABLESPACE                     CONTENTS  SEGTYPE       CON_ID SQL_ID_TEMPSE
----------- ------------- ------------------------------ --------- --------- ---------- -------------
HEMANT
      11923 54zdzm1mrqpy9 TEMP                           TEMPORARY DATA               6 54zdzm1mrqpy9


SYSTEM>
SYSTEM>select sql_id, sql_text
  2  from v$sql
  3  where sql_id in ('54zdzm1mrqpy9') 
  4  /

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
54zdzm1mrqpy9
insert into my_gtt_2 values (1, 'First Object')


SYSTEM>
SYSTEM>select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=11923
  4  /

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
         1        128 54zdzm1mrqpy9 54zdzm1mrqpy9

SYSTEM>


So, that is 1MB (128 blocks of 8KB each) for the initial extent.  Why is it so ?

SYSTEM>select extent_management, allocation_type, initial_extent, next_extent
  2  from dba_tablespaces
  3  where tablespace_name = 'TEMP'
  4  /

EXTENT_MAN ALLOCATIO INITIAL_EXTENT NEXT_EXTENT
---------- --------- -------------- -----------
LOCAL      UNIFORM          1048576     1048576

SYSTEM>


Because, by default, a TEMPORARY TABLESPACE is created with 1MB Uniform Extents.

Can I change this ?

SYSTEM>create temporary tablespace small_temp
  2  tempfile '/usr/tmp/small_temp.dbf' size 100M
  3  extent management local uniform size 64K;

Tablespace created.

SYSTEM>select extent_management, allocation_type, initial_extent, next_extent
  2  from dba_tablespaces
  3  where tablespace_name = 'SMALL_TEMP'
  4  /

EXTENT_MAN ALLOCATIO INITIAL_EXTENT NEXT_EXTENT
---------- --------- -------------- -----------
LOCAL        UNIFORM          65536       65536

SYSTEM>
HEMANT>create global temporary table small_gtt
  2  (id_number number, object_name varchar2(128))
  3  on commit preserve rows
  4  tablespace small_temp
  5  /

Table created.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=11923
  4  /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE                     BLOCKS     SQL_ID        SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
TEMP                                  128 fd8qcczn6avw6 54zdzm1mrqpy9


SYSTEM>
HEMANT>insert into small_gtt
  2  values (1, 'First Object');

1 row created.

HEMANT>commit;

Commit complete.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=11923
  4  /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE                     BLOCKS     SQL_ID        SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
TEMP                                  128 2j3pja8qjx1sd 54zdzm1mrqpy9

HEMANT
SMALL_TEMP                              8 2j3pja8qjx1sd 2j3pja8qjx1sd


SYSTEM>
SYSTEM>select sql_id, sql_text
  2  from v$sql
  3  where sql_id in ('54zdzm1mrqpy9','fd8qcczn6avw6','2j3pja8qjx1sd')
  4  /

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
2j3pja8qjx1sd
insert into small_gtt values (1, 'First Object')


SYSTEM>


(The previous two SQLs no longer present in the cache but we can see that "54zdzm1mrqpy9" is for the first GTT and "fd8qcczn6avw6" is for the second GTT)

Thus, my existing HEMANT session has two different Temporary Segment usages being reported. That in the TEMP tablespace is 1MB for the 1 row in MY_GTT_2 and that in the SMALL_TEMP tablespace is 64KB for the 1 row in SMALL_GTT.

If I TRUNCATE a GTT (or exit the session) space is released.

HEMANT>truncate table my_gtt_2;

Table truncated.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=11923
  4  /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE                     BLOCKS     SQL_ID        SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
SMALL_TEMP                              8 0zwdmqw9fpkjv 2j3pja8qjx1sd


SYSTEM>


Thus you can have
(a) multiple GTTs (with different definitions, e.g. one for SALES data processing and one for HR data processing)
(b) in multiple TEMPORARY TABLESPACES

The GTT definitions are visible across all sessions that are in the same USER login or have been granted privileges but the data in one session is *not* visible to another session, even if COMMITTed (unlike normal "Permanent" Tables)
.
.
.


17 June, 2018

Global Temporary Table in a PDB

Where and how is the space consumption for a Global Temporary Table when created in a Pluggable Database ?

In a 12c MultiTenant Database, each Pluggable Database (PDB) has its own Temporary Tablespace. So, a GTT (Global Temporary Table) in a PDB is local to the associated Temporary Tablespace.

Let me be clear.  The "Global" does *not* mean that the table is
(a) available across all PDBs   (it is restricted to that PDB alone)
(b) available to all schemas (it is restricted to the owner schema alone, unless privileges are granted to other database users as well)
(c) data is visible to other sessions (data in a GTT is visible only to that session that populated it)

The "global" really means that the definition is created once and available across multiple sessions, each session having a "private" copy of the data.
The "temporary" means that the data does not persist.  If the table is defined as "on commit delete rows", rows are not visible after a COMMIT is issued.  If the table is defined as "on commit preserve rows", rows remain only for the life of the session.  In either case, a TRUNCATE can also be used to purge rows.


Here, I connect to a particular PDB and create a GTT and then populate it

$sqlplus hemant/hemant@PDBHKC

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 17 23:06:28 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Jun 17 2018 23:02:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create global temporary table my_gtt                                               
  2  (id_number number, object_name varchar2(128))
  3  on commit preserve rows
  4  /

Table created.

SQL> 
$sqlplus hemant/hemant@PDBHKC

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 17 23:06:28 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Jun 17 2018 23:02:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create global temporary table my_gtt                                               
  2  (id_number number, object_name varchar2(128))
  3  on commit preserve rows
  4  /

Table created.

SQL> 
SQL> select distinct sid from v$mystat;

       SID
----------
        36

SQL> 
SQL> select serial# from v$session where sid=36;

   SERIAL#
----------
      4882

SQL> 


Another session can see that the table exists (without any corresponding "permanent" tablespace) but not see any data in it.

SQL> select temporary, tablespace_name
  2  from user_tables
  3  where table_name = 'MY_GTT'
  4  /

T TABLESPACE_NAME
- ------------------------------
Y

SQL> select count(*) from my_gtt;

  COUNT(*)
----------
         0


Let's look for information on the Temporary Tablespace / Segment usage(querying from the second session)

SQL> select sid, serial#, sql_id    
  2  from v$session
  3  where username = 'HEMANT';

       SID    SERIAL# SQL_ID
---------- ---------- -------------
        36       4882
       300      34315 739nwj7sjgaxp

SQL> select username, session_num, sql_id, tablespace,  contents, segtype, con_id, sql_id_tempseg
  2  from v$tempseg_usage;

USERNAME SESSION_NUM SQL_ID        TABLESPA CONTENTS  SEGTYPE       CON_ID SQL_ID_TEMPSE
-------- ----------- ------------- -------- --------- --------- ---------- -------------
HEMANT          4882 92ac4hmu9qgw3 TEMP     TEMPORARY DATA               6 3t82sphjrt73h

SQL> select sql_id, sql_text
  2  from v$sql
  3  where sql_id in ('92ac4hmu9qgw3','3t82sphjrt73h');

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
92ac4hmu9qgw3
select serial# from v$session where sid=36


SQL> 


So, SID 36 is the session that populated the GTT and identified it's own SID (36) and SERIAL# (4882), which we can see as the user of the Temporary Segment when querying from the second session (SID 300).

What about the size of the temporary segment populated by SESSION_NUM (i..e SERIAL#)=4882 ?
Again, querying from the second session.

SQL> select extents, blocks, sql_id, sql_id_tempseg 
  2  from v$tempseg_usage
  3  where session_num=4882;

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
         4        512 92ac4hmu9qgw3 3t82sphjrt73h

SQL> 


Now, let's "grow" the GTT with more rows (and then query from the other session).

SQL> insert into my_gtt select * from my_gtt;

72638 rows created.

SQL> 
SQL> l
  1  select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3* where session_num=4882
SQL> /

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
         8       1024 gfkbdvpdb3qvf 3t82sphjrt73h

SQL> select sql_text from v$sql where sql_id = 'gfkbdvpdb3qvf';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert into my_gtt select * from my_gtt

SQL> 


So, the increased space allocation in the Temporary Segment is from the growth of the GTT. Let's grow it further.

SQL> INSERT INTO MY_GTT select * from MY_GTT;

145276 rows created.

SQL> /

290552 rows created.

SQL> 
SQL> select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=4882
  4  /

   EXTENTS     BLOCKS SQL_ID        SQL_ID_TEMPSE
---------- ---------- ------------- -------------
        29       3712 2c3sccf0pj5g1 3t82sphjrt73h

SQL> select sql_text, executions from v$sql where sql_id = '2c3sccf0pj5g1';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
EXECUTIONS
----------
INSERT INTO MY_GTT select * from MY_GTT
         2


SQL> 


So, the growth of the GTT results in increased space allocation in the Temporary Segment.

What happens if I truncate the GTT ?

SQL> truncate table my_gtt;

Table truncated.

SQL> 
SQL> select extents, blocks, sql_id, sql_id_tempseg
  2  from v$tempseg_usage
  3  where session_num=4882;

no rows selected

SQL> 
SQL> select * from v$tempseg_usage;

no rows selected

SQL> 


Temp Space is released by the TRUNCATE of the GTT.

I invite you to try this with a GTT created with ON COMMIT DELETE ROWS and see what happens before and after the COMMIT.

.
.
.

23 April, 2018

Domain Indexes -- 4 : CTXRULE Index

I have earlier provided simple demonstrations of CONTEXT and CTXCAT Indexes.

A CTXRULE Index can be used to build a Document Classification application.  This involves indexing a table of "queries" that define the classification.  Queries use the MATCHES clause.
(Note : Like the CONTEXT Index, a call to SYNC_INDEX is required before the rows are indexed).

SQL> create table common_query_classes
  2  (classification varchar2(64),
  3   query_text varchar2(4000));

Table created.

SQL> create index query_class_index
  2  on common_query_classes (query_text)
  3  indextype is ctxsys.ctxrule
  4  /

Index created.

SQL> 
SQL> insert into common_query_classes
  2  values ('Players','Gavaskar OR Tendulkar OR Chappell OR Imran OR Botham');

1 row created.

SQL> insert into common_query_classes
  2  values ('Grounds','Brabourne OR Wankhede OR Lords');

1 row created.

SQL> commit;

Commit complete.

SQL> 


Note that the query predicates are divided by the OR.  They are NOT listed in Alphabetical order.

Now, I test a few queries :

SQL> exec ctx_ddl.sync_index('QUERY_CLASS_INDEX');

PL/SQL procedure successfully completed.

SQL> 
SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text,'Tendulkar is a Player at Brabourne') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Grounds
Players

SQL> 
SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text,'Botham') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Players

SQL> select classification
  2  from common_query_classes
  3  where MATCHES (query_text, 'Kohli is a Player at Wankhede') > 0
  4  /

CLASSIFICATION
----------------------------------------------------------------
Grounds

SQL> 


Note that, since Kohli is not in the Players list, the last query doesn't return the Classification "Players".
.
.
.

22 April, 2018

Domain Indexes -- 3 : CTXCAT Index

In previous posts in December 2017, I had demonstrated a CONTEXT Index.

A CONTEXT Index is used for full-text retrieval from large pieces of text (or document formats stored in LOBs)

A CTXCAT Index is best suited for small fragments of text that are to be indexed with other relational data.

Before I begin with the CTXCAT index, in addition to the CTXAPP role (that I had granted during the earlier demonstration), the account also needs the CREATE TRIGGER privilege.

SQL> grant ctxapp to ctxuser;

Grant succeeded.

SQL> grant create trigger to ctxuser;

Grant succeeded.

SQL> 


I can now proceed with the CTXUSER demonstration.

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table books
  2  (book_id  integer primary key,
  3   book_title varchar2(250) not null,
  4   book_author varchar2(80),
  5   book_subject varchar2(25),
  6   shelf_id  integer)
  7  /

Table created.

SQL> 
SQL> insert into books values
  2  (1,'A Study In Scarlet','Arthur Conan Doyle','Mystery',1);

1 row created.

SQL> insert into books values
  2  (2,'The Sign Of Four','Arthur Conan Doyle','Mystery',1);

1 row created.

SQL> insert into books values
  2  (3,'Murder On The Orient Express','Agatha Christie','Mystery',1);

1 row created.

SQL> insert into books values
  2  (4,'A Brief History of Time','Stephen Hawking','Science - Physics',2);

1 row created.

SQL> 
SQL> insert into books values
  2  (5,'2001: A Space Odyssey','Arthur C Clarke','Science Fiction',3);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> 


Next, I specify what is called an Index Set -- which specifies the structured columns that are to be included in the CTXCAT Index.  I then define the CTXCAT Index on the BOOK_TITLE column.

SQL> begin
  2  ctx_ddl.create_index_set('books_set');
  3  ctx_ddl.add_index('books_set','book_subject');
  4  ctx_ddl.add_index('books_set','shelf_id');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> create index books_title_index
  2  on books (book_title)
  3  indextype is ctxsys.ctxcat
  4  parameters ('index set books_set')
  5  /

Index created.

SQL> 


Now, I can use the Index to query the table, using the CATSEARCH clause instead of the CONTAINS clause. My query includes both BOOK_TITLE and SHELF_ID

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'History','shelf_id=1') > 0
  4  /

no rows selected

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'History','shelf_id>1') > 0
  4  /

BOOK_TITLE
--------------------------------------------------------------------------------
BOOK_AUTHOR
--------------------------------------------------------------------------------
BOOK_SUBJECT      SHELF_ID
------------------------- ----------
A Brief History of Time
Stephen Hawking
Science - Physics     2


SQL> 


The CTXCAT Index that I built on BOOK_TITLE also includes BOOK_SUBJECT and SHELF_ID as indexed columns by virtue of the INDEX_SET called "BOOKS_SET".

Now, I add another row and verify if I need to Sync the index (as I had to do with the CONTEXT Index earlier).

SQL> insert into books
  2  values 
  3  (6,'The Selfish Gene','Richard Dawkins','Evolution',2);

1 row created.

SQL> commit;
SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'Gene','book_subject > ''S'' ') > 0
  4  /

no rows selected

SQL> select book_title,book_author,book_subject,shelf_id
  2  from books
  3  where catsearch (book_title,'Gene','book_subject > ''E'' ') > 0
  4  /

BOOK_TITLE
--------------------------------------------------------------------------------
BOOK_AUTHOR
--------------------------------------------------------------------------------
BOOK_SUBJECT      SHELF_ID
------------------------- ----------
The Selfish Gene
Richard Dawkins
Evolution      2


SQL> 


Note, specifically, how I could use the BOOK_SUBJECT in the query as if looking up a separate index on BOOK_SUBJECT.
The new book was included in the index without a call to CTX_DDL.SYNC_INDEX as would be required for the CONTEXT IndexType.

The portion of the query that is on the BOOK_TITLE column does a Text search on this column but the portions on BOOK_SUBJECT an SHELF_ID behave as with regular indexes.


(I know  that some readers will dispute the subject categorization "Evolution"  but I deliberately threw that in so that I  could show a query that uses a predicate filter not on "Science").

.
.
.