Partition Pruning in Oracle

I write this article about "Partition Pruning" based on requests from the regular readers ( Vicky, Neil & Chateswar ) of this blog page. This article is specially for them and the ones who is eager to know about Partition Pruning.


Before we go for "Partition Pruning", it is better to go through couple of articles related to Partitioning in Oracle.  Below are the links related to them . 

Table Partitioning in Oracle

Interval Partitioning in Oracle - 11g New Features

What is Partition Pruning?  

In simple English, Pruning means Eliminating or Ignoring or Filtering.

Partition Pruning is just an activity which happened when SQL statement perform to retrieve the data for a partitioned table. 

Pruning allows the database to access only the relevant partitions and ignore all partitions that are not irrelevant or necessary for the SQL statement.

  

In other words, partition pruning is the act of eliminating, or ignoring the partitions that are irrelevant to the SQL statement's selection criteria.

Benefits of Partition Pruning :-

Pruning is DBA's trump card to enhance performance tuning. Partition pruning significantly reduces the amount of data retrieved from disk and reduces the SQL processing and execution time. 

This improves query performance and optimizing resource utilization.

Static Pruning  Vs  Dynamic Pruning :-

Optimizer decides either Static Pruning or Dynamic Pruning based on the way the SQL statement detects the data of the partitioned object. 

Static Pruning occurs at Compile time of the SQL execution.

Dynamic Pruning occurs at Run time of the SQL execution.

Static Pruning happens when the WHERE condition directly filters the data belonging to particular partition. 

Dynamic Pruning happens when the WHERE condition has any functions or complex literals or Joined with another table.

In other words, Static Pruning happens if a SQL statement containing a WHERE condition with the value passed directly on the partition key column. Otherwise, Dynamic Pruning will happen.

Simply, Static Pruning happens if it comes to know the exact partition before the run time. Dynamic Pruning happens if it comes to know the exact partition after the run time.

Performance wise;  Static Pruning is very faster when compare to dynamic Pruning because it filtered out most of the unwanted resources and data at first step. 

Hope this helps.. if yes, then please share this post and comment your feedback.  

Please LIKE this   FB Page    to get more articles like this. 

Interval Partitioning in Oracle - 11g new feature

As we are aware of the concept of Partitioning in Oracle which helps in three ways  a) Performance  b) Manageability  and  c) Availability.

Having basic idea about partition ( especially range partition ) will help you here to understand about interval partitioning easily. 

We have an article already posted in this site in explaining about Partitioning, types and benefits. Here we go with the link 

Article about Oracle Partitioning

Assuming you have gone through the above link already; here we start to know about Interval Partitioning. 

Why Interval Partition?  How it works ?

Interval Partition is one of the key new features of Oracle 11i.  We can highlight interval partition as extended version of Range partition. 

For example:  we have created partitions in a table for the below range criteria of data belongs to a column of a table. 

Partition 1    for the data lesser than Jan-2016

Partition 2    for the data between  Jan-2016 to Jun 2016

Partition 3    for the data between  Jul-2016  to Dec 2016

Partition 4    for the data between  Jan-2017 to Jun 2017

Inserting data lesser than Jun-2017  will be placed into its right partition; but when we insert a data greater than Jun 2017; for example .    15-Aug-2017;  we will end up with an error message saying there is no partition available. 

To fix this growing data scenario, we will have to create or add new partitions as a maintenance activity month on month.  So, Interval Partition helps here.  Interval Partition is automatically created new partitions whenever required as per the new data is getting inserted.

Example code  to create Interval Partitioned table :-

CREATE TABLE interval_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('30-Apr-2017','DD-MON-YYYY'))
);

Adding the yellow marked line specially for interval partitioning orders Oracle to create new partitions for every new month data greater than 30-Apr-2017. 

For example;  if we insert into a column "created date"  with the data lesser than 30-Apr-2017 it will be a part of Partition part_01.  If we inserted a data greater than 30-Apr-2017, it will create new partition and then insert the new data. 

The new partitions will be created for  the new months of May-2017, Jun-2017, Jul-2017 etc.,  and so on..  So there is no maintenance activity required.  This will be managed automatically. 

List down all the partitions created for a particular table:-

Below query will provide the details about the partitions created by user and interval partitions created by Oracle automatically.

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
WHERE table_name = <table_name>;

How to add interval partition into already partitioned table?

We are in a scenario of a table which was partitioned already, but we would like to add interval partitioning to avoid further maintenance activities. We can go with an ALTER command. 

ALTER TABLE TABLE_PARTITIONED1 SET INTERVAL(NUMTOMYINTERVAL (1, 'MONTH'));

As we have possibility of creating Composite partitioning  such as ( Range-Hash Partitioning, Range-List Partitioning, Range -Range, List-List and so on.. );

We can have Composite Partitioning with Interval Partitions as well as mentioned below.

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

Hope this helps.. if yes, then please share this post and comment your feedback.  

Please LIKE this   FB Page    to get more articles like this. 

Table Partitioning in Oracle

Partitioning is nothing but the concept of "Divide & Rule"   or   "Categorize them to handle easily".

In order to handle data in huge tables in a easy and quick way,
Oracle has an effective concept " Partitioning Tables"  and  " Partitioned Indexes"

Lets start knowing about what it is all about, how it works and benefits out of it.


Say.. "Hai" to Partitioning  


Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition.

Each Partition has its own name. To retrieve the data, it can be retrieved by calling a table or by calling its partition unit. 


Pictorial view of Partitioned Table and Non Partitioned Table




When actually we can partition a table ?

Huge tables which has data more than 2 GB size can go for partitioning.

Table which has years of data, but mostly we deal with recent months data and old data in a table are just to read.

Table which has data can be categorized into types like Department wise, Country wise, etc.,  So that we can deal with those particular category data alone by naming them as partition.


Types of Partitioning :-

The three types of  partitioning are List, Range and Hash.





Below are the lines to support in explaining the above pictorial view of different partitioning types. 


List Partitioning :-

List partitioning segregates data by its data  which falls into different categories. 

For example:-
The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.

A query to retrieve data only from Florida can deal with "East Sales Region" Partition alone.

Example code to create list partition of table 

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

Range Partitioning :-

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.  

Example Dates between 2000 to 2012, 2012 to 2014 , 2012 to 2016 and greater than 2016.  

Example: Revenue less than 1 million, less than 2 million, less than 4 million and Default ( other values will fall in default partition always )


Example Code :-

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
    TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 );

Hash Partitioning:-

No criteria involved. Just spread the data among partitions in equal size. 

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. 

The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Example code :- 

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4 
   STORE IN (gear1, gear2, gear3, gear4);

Composite Partitioning:-

We can have composite partitioning with the combination of  different partitions at different levels. 

Region wise(List) .. within region, year wise. (Range). within year  equal distribution ( hash ) partition..







Benefits of Partitioning :- 

There are three key benefits and they are 

Performance :- By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. It is like taking data from 1 million records rather than 1 billion records is always quicker

Manageability:- With partitioning, maintenance operations can be focused on particular portions of tables. For example, a database administrator could back up a single partition of a table, rather than backing up the entire table

Availability :- if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table.



Hope this helps.. if yes, then please share this post and comment your feedback.  

How to identify High volume tables / Empty tables in Oracle

To maintain database and to improve the performance; we need to have an eye at huge volume tables and also we may in need to delete unused tables ( zero record tables ). 

We can identify Huge tables or Empty tables easily using NUM_ROWS


What is NUM_ROWS?

NUM_ROWS is a column available in Oracle inbuilt table  " ALL_TABLES "

The table "ALL_TABLES"  hold the key summary information about all the existing tables at Schema and database level.


Simply, to identify the tables which have more than 1 million records:-

Below is the query to execute.  It will retrieve table name, num of rows and many more information.

 SELECT   * from ALL_TABLES 
 WHERE    OWNER ='GLOGOWNER'
     AND    NUM_ROWS > 1000000;

Top 10 new features of Oracle 12c

To identify top huge volume tables :-

SELECT table_name, tablespace_name, num_rows FROM dba_tables WHERE owner='GLOGOWNER' ORDER BY num_rows DESC;

The above statement will result with table names in the order of high volume records to low number of records. 


To identify list of empty tables :-

 SELECT   * from ALL_TABLES 
 WHERE    OWNER ='GLOGOWNER'
     AND    NUM_ROWS = 0;

Article about SQL Loader

 

NOTE:-  The count of rows what we get out of the above queries are not exactly matching with live number of records; since the record count will be updated when the table has been undergone gather statistics  either by   ANALYZE statement or   DBMS_STATS package.  These statements will be normally executed by DBAs as part of maintenance activity. 

So, the count would not be exactly match with current data count but approximately matches ( 95%)


To get the count exactly match :-

Execute any of the below statement for a particular table to analyze or gather statistics. Once this is done, NUM_ROWS column will be updated with exact row count of the table.
 

 ANALYZE TABLE SHIPMENT; 
                          [OR]
  EXEC DBMS_STATS.gather_table_stats('GLOGOWNER', 'SHIPMENT');

GLOGOWNER =  Schema or User name

SHIPMENT     =  Table name

Click the link and Like this FB page  to get more articles like this.

SQL Loader - Utility to load data from flat files to Oracle tables - Quick & Detailed view

It is very usual requirement to move custom data available in external files to be moved to the particular tables present in Oracle DB.  SQL Loader is one of the option; where we can play along with different requirements in moving the data. 


First of all, what is SQL Loader?  - A Quick view. 

SQL Loader is an utility by which we can move data from external files such as CSV, TXT, XLS etc., to Oracle tables. 


How to achieve this? 

Very simple. We need to create two files first.   1. Control file    and  2. Source data file. 


What is control file?

Control file is similar to txt file but have to be saved with extension  " .ctl " ; where we will hold the specifications about the flat file, data, table names, delimiter etc., 


Sample & Simple control file will be as following one:-

 load data
 infile 'c:\data\item_data.csv'     BADFILE 'myydata.bad'  DISCARDFILE 'mydata.dis'
 into table item
 fields terminated by "," optionally enclosed by '"'    
 ( item_gid, item_desc, item_type, domain_name)

==> infile holds the external source file name and its path details

       
The bad file and discard files both contain rejected rows, but they are rejected for different reasons:  Both are optional.

• Bad file:  The bad file contains rows that were rejected because of errors.  These errors might include bad datatypes or referential integrity constraints.
• Discard file:  The discard file contains rows that were discarded because they were filtered out because of a statement in the SQL*Loader control file.

==> into table clause holds the < table name > 

==> terminated by "," means data are separated by comma.  " # " means data are separated by #.

==> optionally enclosed by  '  " ' means..  values within double quotes with spaces will be considered as single data.

For example : " Logitech Pen Drive " will be considered as data for single column.  

==> last line in the above example holds the list of column names of the table ITEM.

Sample Source File :-

the source file  item_data.csv file may look like this:

10001,"Laptop HP", 'Electronic', 'Sales'
10002,"Logitech Pen drive", 'Electronic', 'Service'

One we created the above said two files, we have to execute the below SQLLDR statement from command promt. Upon execution of any of the below statement; data will be loaded into the table ITEM.

sqlldr username@server/password control=loader.ctl
sqlldr username/password@server control=loader.ctl

==> sqlldr = keyword
==> username, password & server names are related to the DB where we want to connect and load the data. 
==> control = keyword
==> loader.ctl  is the name of control file. 

More options on SQL Loader?  - A Detailed view. 

Load fixed length data ( instead of comma separator ). 

In this case, control file will be as follows.

load data
 infile 'c:\data\item_data.txt'
 into table item
 (  item_gid  position (02:05) char(4),
    item_desc position (08:27) char(20)
 )

item_data.txt will be like this:-

1234   Laptop
1111   Mouse

Can have data in control file itself ( No need of source file ). ? 

Yes we can .. as follows

Please note that;    infile *    and   begindata are the keywords change in this control file than earlier one. 

load data
 infile *
 replace
 into table item_data
 (  item_gid  position (02:05) char(4),
    item_desc position (08:27) char(20)
 )
begindata
1111  APPLE IPHONE 7S
2222  LENOVA LAPTOP
3333  LOGITECH MOUSE V2
4444  NIKON CAMERA 

Can we modify the data.. on the fly .. whilst upload the data.  ?

Yes we can.. by using the below one sample control file.

LOAD DATA
  INFILE *
  INTO TABLE modified_data
  (  rec_no                      "my_db_sequence.nextval",
     region                      CONSTANT '31',
     time_loaded                 "to_char(SYSDATE, 'HH24:MI')",
     data1        POSITION(1:5)  ":data1/100",
     data2        POSITION(6:15) "upper(:data2)",
     data3        POSITION(16:22)"to_date(:data3, 'YYMMDD')"
  )
BEGINDATA
11111AAAAAAAAAA991201

LOAD DATA
  INFILE 'mail_orders.txt'
  BADFILE 'bad_orders.txt'
  APPEND
  INTO TABLE mailing_list
  FIELDS TERMINATED BY ","
  (  addr,
     city,
     state,
     zipcode,
     mailing_addr   "decode(:mailing_addr, null, :addr, :mailing_addr)",
     mailing_city   "decode(:mailing_city, null, :city, :mailing_city)",
     mailing_state,
     move_date      "substr(:move_date, 3, 2) || substr(:move_date, 7, 2)"
  )

Can we upload data from multiple source files ? 

Yes we can . Please refer the below sample control file. 

LOAD DATA
  INFILE file1.dat
  INFILE file2.dat
  INFILE file3.dat
  APPEND
  INTO TABLE emp
  ( empno  POSITION(1:4)   INTEGER EXTERNAL,
    ename  POSITION(6:15)  CHAR,
    deptno POSITION(17:18) CHAR,
    mgr    POSITION(20:23) INTEGER EXTERNAL
  )

Can we upload into multiple tables ? 

Yes we can. please refer the below sample control file.

LOAD DATA INFILE 'mydata.dat' REPLACE INTO TABLE emp WHEN empno != ' ' ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL ) INTO TABLE proj WHEN projno != ' ' ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL )

Can we upload only selected data from source file ?

Yes we can. please refer the below sample control file. 

LOAD DATA
  INFILE  'mydata.dat' BADFILE  'mydata.bad' DISCARDFILE 'mydata.dis'
  APPEND
  INTO TABLE my_selective_table
  WHEN (01) <> 'H' and (01) <> 'T'
  (
     region              CONSTANT '31',
     service_key         POSITION(01:11)   INTEGER EXTERNAL,
     call_b_no           POSITION(12:29)   CHAR
  )
  INTO TABLE my_selective_table
  WHEN (30:37) = '20031217'
  (
     region              CONSTANT '31',
     service_key     POSITION(01:11)   INTEGER EXTERNAL,
     call_b_no         POSITION(12:29)   CHAR
  )

Can we load images, sound clips, blog data and documents ? 

Yes, we can by using the below control file as sample one. 

Consider the following table is created.

CREATE TABLE image_table (
       image_id   NUMBER(5),
       file_name  VARCHAR2(30),
       image_data BLOB);

Control File:

LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
 image_id   INTEGER(5),
 file_name  CHAR(30),
 image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg

We are at the end of the session of SQL Loader. Now you are proudly a SQL Loader Expert.  Try this out to  experience more.. 


Click the link and Like this FB page  to get more articles like this.

FLASHBACK - How to get deleted records after commit & recover dropped tables

Accidentally or by human error, we might delete/commit,  truncate  some records  and drop table which we were not supposed to do.

Have you done this ?  No worries, we can recover them using FLASHBACK feature in Oracle. 

Let us see in detail below. 

How to Recover Deleted Records using AS OF TIMESTAMP :-

Imagine, if you had deleted some records ( and did commit ) from the table SHIPMENT. Here, we go to get back those records with the below query. 

select * from SHIPMENT as of timestamp sysdate-1/24 ;

will give you result of how the table was before 1 hour with the records deleted an hour before.

[or]

select * from SHIPMENT AS OF TIMESTAMP 
      TO_TIMESTAMP('2017-03-07 10:00:00', 'YYYY-MM-DD HH:MI:SS');

will give you the result of how the table was exactly to the particular time.

[or]

insert into SHIPMENT(select * from emp as of timestamp sysdate-1/24);

will insert the deleted records back.

How to recover dropped table:

From Oracle 10g, Recycle bin stores dropped table in other name, which can be recovered later. 

Imagine SHIPMENT_STATUS  table is dropped. Now, the below statement help you to recover the table back as it was before drop.

FLASHBACK table SHIPMENT_STATUS to before drop;

We can even recover the dropped table with new table name. 

FLASHBACK table SHIPMENT_STATUS to before drop rename to SHIPMENT_STATUS_BKP;


Below are the some of the key points to understand about recovering the data and table :-


1. Flashback feature depends upon on how much undo retention time you have specified. If you have set the UNDO_RETENTION parameter to 6 hours then. Oracle will not overwrite the data in undo tablespace even after committing until 6 Hours have passed. Users can recover from their mistakes made since last 6 hours only.

To check for the current value,  the below query will revert you the answer in seconds.

SELECT TUNED_UNDORETENTION FROM V$UNDOSTAT ;

2. Recycle bin space is refreshed by Oracle when it need space to store newly impacted tables. Hence, it is not sure the dropped table long time back will be available in Recycle bin for sure. 

SHOW RECYCLEBIN;    query will help you to list the available tables. 

3. When we recover the dropped table, all the indexes , triggers and constraints associated with the table will also be recovered, but BITMAP index wont. Since BITMAP indexes are not being  stored in recycle bin when we drop the table. 

4. If the table is altered using DDL statements like adding a new column, data type change of a column, then deleted records cannot be retrieved. 

I wish not to use FLASHBACK in real time :)

Top 10 Awesome New features of Oracle 12c

1. Invisible Columns ( Reference purpose ):-

We can have columns created as Invisible; which will not be shown when we do SELECT * FROM ..
These columns can be used to save some data for reference purpose.  No need of separate custom table to secure any data from visibility - Good one.


2. Easy Database Archiving :-

Need to keep full data in a table, as well as to keep performance when data grow?   Prior to 12c, the options are partitions and purging.
  
In 12c, we have amazing option of marking old records as INACTIVE, so that those records will not be considered for fetching, parsing and data scan. No need of backup, history and purging process for performance issues. 


3.Temporary UNDO or Staging UNDO  :-

Prior to 12C, undo records generated by TEMP Table space is stored in the undo table space. With Temp undo feature in 12C, temp undo records can be stored in temporary table instead of UNDO TS. The benefit is ... reduced undo table space and reduced redo log space used. 



4. Online migration of tables ( now partitioned tables also )

Prior to 12c, only non-partition tables can be migrated from one table space to other table space.  From 12c on-wards, we can migrate  any partition or sub partition of the table can be migrated from one table space to other table space.  ( only if the ONLINE clause is specified for the partition )


5. More than one Indexes for a single column  ( Switch option )

Prior to 12c, only one index can be created for a column.  Going forward, we can create multiple indexes ( For Example:  Binary tree  as well as Bit map ).  But only one index can be in active mode at a time. Indexes can be switched to use any one of them in a needy basis. 


6. Auto Increment Primary Key - ( Similar to Sequence )

From 12c, we can now create a column with 'Generated as Identity' clause. This is equivalent to creating a separate sequence and doing a sequence.nextval for each row.   No need to create new object -SEQUENCE just for incremental purpose.  I love this feature most. 


7. Recover a table is easy though RMAN  ( Good one )

Prior to 12c, if we had to restore a particular table, we had to do all sorts of things like restoring a tablespace and or do Export and Import. The new restore command in RMAN simplifies this task. We can simply restore/recover a table.  Lovely feature.


8. Masking Data for specific users ( REDACTION )

REDACTION is nothing but masking.  We can now mask a field/column of a table for a specific schema/user. When we do SELECT * FROM.. it will show that particular column as masked. 

From Sql Developer we can do this by going to the table:  <TableName> ->Right click on Security Policy->click on New->click on Redaction Policy->Enter <ColumnName>


9. Inline Procedure and Function  ( Great feature among all )

The in line feature is extended in Oracle 12C. In addition to Views, we can now have PL/SQL Procedures and Functions as in line code. 

The query can be written as if it is calling a real stored procedure, but however the functions do not actually exist in the database. 

We will not be able to find them in ALL_OBJECTS. I personally feel this will be a very good feature for the PLSQL developers to explore as there is no code that needs to be compiled and manage separately.


10  Top 'N'  Select and Fetch option ( Long pending feature )

I mentioned this feature as Long pending feature, because we have this already in Non-Oracle DBs.

In 12c, we have new SQL syntax to simplify fetching the first few rows. The new sql syntax "Fetch First X Rows only" can be used. No need of inline views, order by etc., and etc.,  Big relief for SQL writers. 

Hope this is useful. If yes, kindly share.

Sathish Chandran