際際滷

際際滷Share a Scribd company logo
Partitioning and Archiving




            Bala Prabahar
BAPS Software Consulting Services Inc.

              07/15/2011
1. What is Partitioning?
  2. Types of Partitioning
   3. How to Implement?
4. Global Versus Local Index
       5. Questions




                               2
Partitioning enables tables and indexes to be
  subdivided into smaller manageable pieces


Each of those smaller pieces is called Partition.




                                                    3
10 Partitions with ~10m records
Available Partitioning Types:
 Range Partitioning (introduced in Oracle 8)
 Hash Partitioning (introduced in Oracle 8i)
 Composite Partitioning (introduced in Oracle 8i)
 List Partitioning (introduced in Oracle 9i)
 Interval Partitioning (introduced in Oracle 11g)
 System Partitioning (introduced in Oracle 11g)
 Reference Partitioning (introduced in Oracle 11g)



                                                      5
Range-hash partitioning was introduced in Oracle 8i
    Range-list partitioning was introduced in Oracle 9i
 Range-range partitioning was introduced in Oracle 11g
   List-range partitioning was introduced in Oracle 11g
   List-hash partitioning was introduced in Oracle 11g
    List-list partitioning was introduced in Oracle 11g
Interval-range partitioning was introduced in Oracle 11g
  Interval-list partitioning was introduced in Oracle 11g
 Interval-hash partitioning was introduced in Oracle 11g
Available Partitioning Types:
 Range Partitioning (introduced in Oracle 8)
 Hash Partitioning (introduced in Oracle 8i)
 Composite Partitioning (introduced in Oracle 8i)
 List Partitioning (introduced in Oracle 9i)
 Interval Partitioning (introduced in Oracle 11g)
 System Partitioning (introduced in Oracle 11g)
 Reference Partitioning (introduced in Oracle 11g)



                                                      7
Interval partitioning is a partitioning method introduced in
Oracle 11g. This is a helpful addition to range partitioning where
Oracle automatically creates a partition when the inserted value
              exceeds all other partition ranges.
                The following restrictions apply:

You can only specify one partitioning key column, and it must be
                     of NUMBER or DATE type.
Interval partitioning is NOT supported for index-organized tables.

 You can NOT create a domain index on an interval-partitioned
                          table.
1   Identify Tables
                              5   Determine # of Partitions




2   Discuss with Business
                              6   Run a Script to identify the
                                    Upper Key Limit



                                  Generate CREATE TABLE
3   Determine Partition Key   7     Statement




    Run SQL Statement
4     Group BY &
                              8   Load data into Partitioned Table
      Order BY
 SELECT <KEY_1>,<KEY_2> <KEY_N>, Count(*)
 FROM <TABLE>
 GROUP BY <KEY_1>, <KEY_2>,<KEY_3>
 ORDER BY <KEY_1>, <KEY_2>,<KEY_3>




                                              10
 ODSR_4IN68CVTKN5HWSCTCM8G03ZIQ    541332
 ODSR_4IPWDLN3A0E9ZQL9C715GI9W1     65351
 ODSR_4IQPKCK3V2L00HG22IF07LF2P     86676
 ODSR_4ITGFBK0J0DFFDYVSFXFAN05U     55170
  ODSR_4IV1M2YUQGVGUIQ4KHQ06WXAP    53280
 ODSR_4IVFQZCVDSWY1PPK0MXC06SNL     67820
  ODSR_4IXS9V5HSV12YA22KWXUV0FPU    47570
 ODSR_4J2627MWPQSEMKEN3SCD6BKIA     49048
 ODSR_4J32BJFV6AV8GCQCX6NEUY5JM     18109
 ODSR_4J4JFJQKSZPTDIM9Z2DVE9ZMA     58148
 ODSR_4J9OGSBOLT2YN8BA0V0EVJHJM     67760




                                             11
1   Identify Tables
                              5   Determine # of Partitions




2   Discuss with Business
                              6   Run a Script to identify the
                                    Upper Key Limit



                                  Generate CREATE TABLE
3   Determine Partition Key   7     Statement




    Run SQL Statement
4     Group BY &
                              8   Load data into Partitioned Table
      Order BY
13
14
1   Identify Tables
                              5   Determine # of Partitions




2   Discuss with Business
                              6   Run a Script to identify the
                                    Upper Key Limit



                                  Generate CREATE TABLE
3   Determine Partition Key   7     Statement




    Run SQL Statement
4     Group BY &
                              8   Load data into Partitioned Table
      Order BY
   CREATE TABLE "/BIC/B0000252000_NEW"

 PARTITION BY RANGE
 ("REQUEST") (PARTITION P1 VALUES LESS THAN
    ('ODSR_4IR2HPAGY9GBAE2NNIO2BQJ6A')
 PCTFREE 10 PCTUSED 40 INITRANS 1
 MAXTRANS 255 STORAGE(INITIAL 30M NEXT 1048576 MINEXTENTS 1
 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "PSAPSR3FACT",




                                                               16
1   Identify Tables
                              5   Determine # of Partitions




2   Discuss with Business
                              6   Run a Script to identify the
                                    Upper Key Limit



                                  Generate CREATE TABLE
3   Determine Partition Key   7     Statement




    Run SQL Statement
4     Group BY &
                              8   Load data into Partitioned Table
      Order BY
18
 Global
 Local




           19
 Multi-providers
 Cubes  Further levels of Partitioning




                                           20
 Drop Partition instead of Deleting Records
 Benefits:
    Old records are not mixed with new records.
    Dropping partition will be faster




                                                   21
22

More Related Content

Similar to Big data mgmt bala (20)

Hive: Loading Data
Hive: Loading DataHive: Loading Data
Hive: Loading Data
Benjamin Leonhardi
Postgre sql 10 table partitioning
Postgre sql 10  table partitioningPostgre sql 10  table partitioning
Postgre sql 10 table partitioning
Marek Hudyma
15 Ways to Kill Your Mysql Application Performance
15 Ways to Kill Your Mysql Application Performance15 Ways to Kill Your Mysql Application Performance
15 Ways to Kill Your Mysql Application Performance
guest9912e5
SQLFire Webinar
SQLFire WebinarSQLFire Webinar
SQLFire Webinar
Carter Shanklin
Database index
Database indexDatabase index
Database index
Riteshkiit
MySQL New Features -- Sunshine PHP 2020 Presentation
MySQL New Features -- Sunshine PHP 2020 PresentationMySQL New Features -- Sunshine PHP 2020 Presentation
MySQL New Features -- Sunshine PHP 2020 Presentation
Dave Stokes
Teched03 Index Maint Tony Bain
Teched03 Index Maint Tony BainTeched03 Index Maint Tony Bain
Teched03 Index Maint Tony Bain
Tony Bain
cPanel now supports MySQL 8.0 - My Top Seven Features
cPanel now supports MySQL 8.0 - My Top Seven FeaturescPanel now supports MySQL 8.0 - My Top Seven Features
cPanel now supports MySQL 8.0 - My Top Seven Features
Dave Stokes
Application sql issues_and_tuning
Application sql issues_and_tuningApplication sql issues_and_tuning
Application sql issues_and_tuning
Anil Pandey
Partitioning 20061205
Partitioning 20061205Partitioning 20061205
Partitioning 20061205
Jeff Hammerbacher
SQL Server 2008 Performance Enhancements
SQL Server 2008 Performance EnhancementsSQL Server 2008 Performance Enhancements
SQL Server 2008 Performance Enhancements
infusiondev
Scaling MySQL Strategies for Developers
Scaling MySQL Strategies for DevelopersScaling MySQL Strategies for Developers
Scaling MySQL Strategies for Developers
Jonathan Levin
PostgreSQL Table Partitioning / Sharding
PostgreSQL Table Partitioning / ShardingPostgreSQL Table Partitioning / Sharding
PostgreSQL Table Partitioning / Sharding
Amir Reza Hashemi
Partitioning kendralittle
Partitioning kendralittlePartitioning kendralittle
Partitioning kendralittle
ngupt28
Discard inport exchange table & tablespace
Discard inport exchange table & tablespaceDiscard inport exchange table & tablespace
Discard inport exchange table & tablespace
Marco Tusa
SQL
SQLSQL
SQL
kaushal123
SQL
SQLSQL
SQL
kaushal123
SQL Tutorial
SQL TutorialSQL Tutorial
SQL Tutorial
ziamd
576 oracle-dba-interview-questions
576 oracle-dba-interview-questions576 oracle-dba-interview-questions
576 oracle-dba-interview-questions
Naveen P
Twp partitioning-11gr2-2009-09-130569
Twp partitioning-11gr2-2009-09-130569Twp partitioning-11gr2-2009-09-130569
Twp partitioning-11gr2-2009-09-130569
Naga Mallesh K
Postgre sql 10 table partitioning
Postgre sql 10  table partitioningPostgre sql 10  table partitioning
Postgre sql 10 table partitioning
Marek Hudyma
15 Ways to Kill Your Mysql Application Performance
15 Ways to Kill Your Mysql Application Performance15 Ways to Kill Your Mysql Application Performance
15 Ways to Kill Your Mysql Application Performance
guest9912e5
Database index
Database indexDatabase index
Database index
Riteshkiit
MySQL New Features -- Sunshine PHP 2020 Presentation
MySQL New Features -- Sunshine PHP 2020 PresentationMySQL New Features -- Sunshine PHP 2020 Presentation
MySQL New Features -- Sunshine PHP 2020 Presentation
Dave Stokes
Teched03 Index Maint Tony Bain
Teched03 Index Maint Tony BainTeched03 Index Maint Tony Bain
Teched03 Index Maint Tony Bain
Tony Bain
cPanel now supports MySQL 8.0 - My Top Seven Features
cPanel now supports MySQL 8.0 - My Top Seven FeaturescPanel now supports MySQL 8.0 - My Top Seven Features
cPanel now supports MySQL 8.0 - My Top Seven Features
Dave Stokes
Application sql issues_and_tuning
Application sql issues_and_tuningApplication sql issues_and_tuning
Application sql issues_and_tuning
Anil Pandey
SQL Server 2008 Performance Enhancements
SQL Server 2008 Performance EnhancementsSQL Server 2008 Performance Enhancements
SQL Server 2008 Performance Enhancements
infusiondev
Scaling MySQL Strategies for Developers
Scaling MySQL Strategies for DevelopersScaling MySQL Strategies for Developers
Scaling MySQL Strategies for Developers
Jonathan Levin
PostgreSQL Table Partitioning / Sharding
PostgreSQL Table Partitioning / ShardingPostgreSQL Table Partitioning / Sharding
PostgreSQL Table Partitioning / Sharding
Amir Reza Hashemi
Partitioning kendralittle
Partitioning kendralittlePartitioning kendralittle
Partitioning kendralittle
ngupt28
Discard inport exchange table & tablespace
Discard inport exchange table & tablespaceDiscard inport exchange table & tablespace
Discard inport exchange table & tablespace
Marco Tusa
SQL Tutorial
SQL TutorialSQL Tutorial
SQL Tutorial
ziamd
576 oracle-dba-interview-questions
576 oracle-dba-interview-questions576 oracle-dba-interview-questions
576 oracle-dba-interview-questions
Naveen P
Twp partitioning-11gr2-2009-09-130569
Twp partitioning-11gr2-2009-09-130569Twp partitioning-11gr2-2009-09-130569
Twp partitioning-11gr2-2009-09-130569
Naga Mallesh K

More from Michelle Crapo (11)

Abap objects
Abap objectsAbap objects
Abap objects
Michelle Crapo
Learning & using new technology
Learning & using new technologyLearning & using new technology
Learning & using new technology
Michelle Crapo
Learning & using new technology
Learning & using new technologyLearning & using new technology
Learning & using new technology
Michelle Crapo
2011 sap inside_track_eim_overview
2011 sap inside_track_eim_overview2011 sap inside_track_eim_overview
2011 sap inside_track_eim_overview
Michelle Crapo
SAP OSS note search
SAP OSS note searchSAP OSS note search
SAP OSS note search
Michelle Crapo
2007 SAPTech Ed
2007 SAPTech Ed2007 SAPTech Ed
2007 SAPTech Ed
Michelle Crapo
SAP Technology QUICK overview
SAP Technology QUICK overviewSAP Technology QUICK overview
SAP Technology QUICK overview
Michelle Crapo
Change Documents2
Change Documents2Change Documents2
Change Documents2
Michelle Crapo
Program For Parsing2
Program For Parsing2Program For Parsing2
Program For Parsing2
Michelle Crapo
Zmd Constant
Zmd ConstantZmd Constant
Zmd Constant
Michelle Crapo
Alv Grids
Alv GridsAlv Grids
Alv Grids
Michelle Crapo
Learning & using new technology
Learning & using new technologyLearning & using new technology
Learning & using new technology
Michelle Crapo
Learning & using new technology
Learning & using new technologyLearning & using new technology
Learning & using new technology
Michelle Crapo
2011 sap inside_track_eim_overview
2011 sap inside_track_eim_overview2011 sap inside_track_eim_overview
2011 sap inside_track_eim_overview
Michelle Crapo
SAP OSS note search
SAP OSS note searchSAP OSS note search
SAP OSS note search
Michelle Crapo
SAP Technology QUICK overview
SAP Technology QUICK overviewSAP Technology QUICK overview
SAP Technology QUICK overview
Michelle Crapo
Program For Parsing2
Program For Parsing2Program For Parsing2
Program For Parsing2
Michelle Crapo

Recently uploaded (20)

FESTIVAL: SINULOG & THINGYAN-LESSON 4.pptx
FESTIVAL: SINULOG & THINGYAN-LESSON 4.pptxFESTIVAL: SINULOG & THINGYAN-LESSON 4.pptx
FESTIVAL: SINULOG & THINGYAN-LESSON 4.pptx
DanmarieMuli1
APM People Interest Network Conference - Tim Lyons - The neurological levels ...
APM People Interest Network Conference - Tim Lyons - The neurological levels ...APM People Interest Network Conference - Tim Lyons - The neurological levels ...
APM People Interest Network Conference - Tim Lyons - The neurological levels ...
Association for Project Management
The Broccoli Dog's inner voice (look A)
The Broccoli Dog's inner voice  (look A)The Broccoli Dog's inner voice  (look A)
The Broccoli Dog's inner voice (look A)
merasan
Adventure Activities Final By H R Gohil Sir
Adventure Activities Final By H R Gohil SirAdventure Activities Final By H R Gohil Sir
Adventure Activities Final By H R Gohil Sir
GUJARATCOMMERCECOLLE
Kaun TALHA quiz Finals -- El Dorado 2025
Kaun TALHA quiz Finals -- El Dorado 2025Kaun TALHA quiz Finals -- El Dorado 2025
Kaun TALHA quiz Finals -- El Dorado 2025
Conquiztadors- the Quiz Society of Sri Venkateswara College
TLE 7 - 2nd Topic - Codes and Standards in Industrial Arts Services.pptx
TLE 7 - 2nd Topic - Codes and Standards in Industrial Arts Services.pptxTLE 7 - 2nd Topic - Codes and Standards in Industrial Arts Services.pptx
TLE 7 - 2nd Topic - Codes and Standards in Industrial Arts Services.pptx
RizaBedayo
Digital Tools with AI for e-Content Development.pptx
Digital Tools with AI for e-Content Development.pptxDigital Tools with AI for e-Content Development.pptx
Digital Tools with AI for e-Content Development.pptx
Dr. Sarita Anand
Principle and Practices of Animal Breeding || Boby Basnet
Principle and Practices of Animal Breeding || Boby BasnetPrinciple and Practices of Animal Breeding || Boby Basnet
Principle and Practices of Animal Breeding || Boby Basnet
Boby Basnet
English 4 Quarter 4 Week 4 Classroom Obs
English 4 Quarter 4 Week 4 Classroom ObsEnglish 4 Quarter 4 Week 4 Classroom Obs
English 4 Quarter 4 Week 4 Classroom Obs
NerissaMendez1
Essentials of a Good PMO, presented by Aalok Sonawala
Essentials of a Good PMO, presented by Aalok SonawalaEssentials of a Good PMO, presented by Aalok Sonawala
Essentials of a Good PMO, presented by Aalok Sonawala
Association for Project Management
N.C. DPI's 2023 Language Diversity Briefing
N.C. DPI's 2023 Language Diversity BriefingN.C. DPI's 2023 Language Diversity Briefing
N.C. DPI's 2023 Language Diversity Briefing
Mebane Rash
Computer Network Unit IV - Lecture Notes - Network Layer
Computer Network Unit IV - Lecture Notes - Network LayerComputer Network Unit IV - Lecture Notes - Network Layer
Computer Network Unit IV - Lecture Notes - Network Layer
Murugan146644
cervical spine mobilization manual therapy .pdf
cervical spine mobilization manual therapy .pdfcervical spine mobilization manual therapy .pdf
cervical spine mobilization manual therapy .pdf
SamarHosni3
EDL 290F Week 3 - Mountaintop Views (2025).pdf
EDL 290F Week 3  - Mountaintop Views (2025).pdfEDL 290F Week 3  - Mountaintop Views (2025).pdf
EDL 290F Week 3 - Mountaintop Views (2025).pdf
Liz Walsh-Trevino
APM People Interest Network Conference - Oliver Randall & David Bovis - Own Y...
APM People Interest Network Conference - Oliver Randall & David Bovis - Own Y...APM People Interest Network Conference - Oliver Randall & David Bovis - Own Y...
APM People Interest Network Conference - Oliver Randall & David Bovis - Own Y...
Association for Project Management
The Story Behind the Abney Park Restoration Project by Tom Walker
The Story Behind the Abney Park Restoration Project by Tom WalkerThe Story Behind the Abney Park Restoration Project by Tom Walker
The Story Behind the Abney Park Restoration Project by Tom Walker
History of Stoke Newington
How to use Init Hooks in Odoo 18 - Odoo 際際滷s
How to use Init Hooks in Odoo 18 - Odoo 際際滷sHow to use Init Hooks in Odoo 18 - Odoo 際際滷s
How to use Init Hooks in Odoo 18 - Odoo 際際滷s
Celine George
A PPT Presentation on The Princess and the God: A tale of ancient India by A...
A PPT Presentation on The Princess and the God: A tale of ancient India  by A...A PPT Presentation on The Princess and the God: A tale of ancient India  by A...
A PPT Presentation on The Princess and the God: A tale of ancient India by A...
Beena E S
PUBH1000 Module 3: Public Health Systems
PUBH1000 Module 3: Public Health SystemsPUBH1000 Module 3: Public Health Systems
PUBH1000 Module 3: Public Health Systems
Jonathan Hallett
The Battle of Belgrade Road: A WW1 Street Renaming Saga by Amir Dotan
The Battle of Belgrade Road: A WW1 Street Renaming Saga by Amir DotanThe Battle of Belgrade Road: A WW1 Street Renaming Saga by Amir Dotan
The Battle of Belgrade Road: A WW1 Street Renaming Saga by Amir Dotan
History of Stoke Newington
FESTIVAL: SINULOG & THINGYAN-LESSON 4.pptx
FESTIVAL: SINULOG & THINGYAN-LESSON 4.pptxFESTIVAL: SINULOG & THINGYAN-LESSON 4.pptx
FESTIVAL: SINULOG & THINGYAN-LESSON 4.pptx
DanmarieMuli1
APM People Interest Network Conference - Tim Lyons - The neurological levels ...
APM People Interest Network Conference - Tim Lyons - The neurological levels ...APM People Interest Network Conference - Tim Lyons - The neurological levels ...
APM People Interest Network Conference - Tim Lyons - The neurological levels ...
Association for Project Management
The Broccoli Dog's inner voice (look A)
The Broccoli Dog's inner voice  (look A)The Broccoli Dog's inner voice  (look A)
The Broccoli Dog's inner voice (look A)
merasan
Adventure Activities Final By H R Gohil Sir
Adventure Activities Final By H R Gohil SirAdventure Activities Final By H R Gohil Sir
Adventure Activities Final By H R Gohil Sir
GUJARATCOMMERCECOLLE
TLE 7 - 2nd Topic - Codes and Standards in Industrial Arts Services.pptx
TLE 7 - 2nd Topic - Codes and Standards in Industrial Arts Services.pptxTLE 7 - 2nd Topic - Codes and Standards in Industrial Arts Services.pptx
TLE 7 - 2nd Topic - Codes and Standards in Industrial Arts Services.pptx
RizaBedayo
Digital Tools with AI for e-Content Development.pptx
Digital Tools with AI for e-Content Development.pptxDigital Tools with AI for e-Content Development.pptx
Digital Tools with AI for e-Content Development.pptx
Dr. Sarita Anand
Principle and Practices of Animal Breeding || Boby Basnet
Principle and Practices of Animal Breeding || Boby BasnetPrinciple and Practices of Animal Breeding || Boby Basnet
Principle and Practices of Animal Breeding || Boby Basnet
Boby Basnet
English 4 Quarter 4 Week 4 Classroom Obs
English 4 Quarter 4 Week 4 Classroom ObsEnglish 4 Quarter 4 Week 4 Classroom Obs
English 4 Quarter 4 Week 4 Classroom Obs
NerissaMendez1
N.C. DPI's 2023 Language Diversity Briefing
N.C. DPI's 2023 Language Diversity BriefingN.C. DPI's 2023 Language Diversity Briefing
N.C. DPI's 2023 Language Diversity Briefing
Mebane Rash
Computer Network Unit IV - Lecture Notes - Network Layer
Computer Network Unit IV - Lecture Notes - Network LayerComputer Network Unit IV - Lecture Notes - Network Layer
Computer Network Unit IV - Lecture Notes - Network Layer
Murugan146644
cervical spine mobilization manual therapy .pdf
cervical spine mobilization manual therapy .pdfcervical spine mobilization manual therapy .pdf
cervical spine mobilization manual therapy .pdf
SamarHosni3
EDL 290F Week 3 - Mountaintop Views (2025).pdf
EDL 290F Week 3  - Mountaintop Views (2025).pdfEDL 290F Week 3  - Mountaintop Views (2025).pdf
EDL 290F Week 3 - Mountaintop Views (2025).pdf
Liz Walsh-Trevino
APM People Interest Network Conference - Oliver Randall & David Bovis - Own Y...
APM People Interest Network Conference - Oliver Randall & David Bovis - Own Y...APM People Interest Network Conference - Oliver Randall & David Bovis - Own Y...
APM People Interest Network Conference - Oliver Randall & David Bovis - Own Y...
Association for Project Management
The Story Behind the Abney Park Restoration Project by Tom Walker
The Story Behind the Abney Park Restoration Project by Tom WalkerThe Story Behind the Abney Park Restoration Project by Tom Walker
The Story Behind the Abney Park Restoration Project by Tom Walker
History of Stoke Newington
How to use Init Hooks in Odoo 18 - Odoo 際際滷s
How to use Init Hooks in Odoo 18 - Odoo 際際滷sHow to use Init Hooks in Odoo 18 - Odoo 際際滷s
How to use Init Hooks in Odoo 18 - Odoo 際際滷s
Celine George
A PPT Presentation on The Princess and the God: A tale of ancient India by A...
A PPT Presentation on The Princess and the God: A tale of ancient India  by A...A PPT Presentation on The Princess and the God: A tale of ancient India  by A...
A PPT Presentation on The Princess and the God: A tale of ancient India by A...
Beena E S
PUBH1000 Module 3: Public Health Systems
PUBH1000 Module 3: Public Health SystemsPUBH1000 Module 3: Public Health Systems
PUBH1000 Module 3: Public Health Systems
Jonathan Hallett
The Battle of Belgrade Road: A WW1 Street Renaming Saga by Amir Dotan
The Battle of Belgrade Road: A WW1 Street Renaming Saga by Amir DotanThe Battle of Belgrade Road: A WW1 Street Renaming Saga by Amir Dotan
The Battle of Belgrade Road: A WW1 Street Renaming Saga by Amir Dotan
History of Stoke Newington

Big data mgmt bala

  • 1. Partitioning and Archiving Bala Prabahar BAPS Software Consulting Services Inc. 07/15/2011
  • 2. 1. What is Partitioning? 2. Types of Partitioning 3. How to Implement? 4. Global Versus Local Index 5. Questions 2
  • 3. Partitioning enables tables and indexes to be subdivided into smaller manageable pieces Each of those smaller pieces is called Partition. 3
  • 4. 10 Partitions with ~10m records
  • 5. Available Partitioning Types: Range Partitioning (introduced in Oracle 8) Hash Partitioning (introduced in Oracle 8i) Composite Partitioning (introduced in Oracle 8i) List Partitioning (introduced in Oracle 9i) Interval Partitioning (introduced in Oracle 11g) System Partitioning (introduced in Oracle 11g) Reference Partitioning (introduced in Oracle 11g) 5
  • 6. Range-hash partitioning was introduced in Oracle 8i Range-list partitioning was introduced in Oracle 9i Range-range partitioning was introduced in Oracle 11g List-range partitioning was introduced in Oracle 11g List-hash partitioning was introduced in Oracle 11g List-list partitioning was introduced in Oracle 11g Interval-range partitioning was introduced in Oracle 11g Interval-list partitioning was introduced in Oracle 11g Interval-hash partitioning was introduced in Oracle 11g
  • 7. Available Partitioning Types: Range Partitioning (introduced in Oracle 8) Hash Partitioning (introduced in Oracle 8i) Composite Partitioning (introduced in Oracle 8i) List Partitioning (introduced in Oracle 9i) Interval Partitioning (introduced in Oracle 11g) System Partitioning (introduced in Oracle 11g) Reference Partitioning (introduced in Oracle 11g) 7
  • 8. Interval partitioning is a partitioning method introduced in Oracle 11g. This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges. The following restrictions apply: You can only specify one partitioning key column, and it must be of NUMBER or DATE type. Interval partitioning is NOT supported for index-organized tables. You can NOT create a domain index on an interval-partitioned table.
  • 9. 1 Identify Tables 5 Determine # of Partitions 2 Discuss with Business 6 Run a Script to identify the Upper Key Limit Generate CREATE TABLE 3 Determine Partition Key 7 Statement Run SQL Statement 4 Group BY & 8 Load data into Partitioned Table Order BY
  • 10. SELECT <KEY_1>,<KEY_2> <KEY_N>, Count(*) FROM <TABLE> GROUP BY <KEY_1>, <KEY_2>,<KEY_3> ORDER BY <KEY_1>, <KEY_2>,<KEY_3> 10
  • 11. ODSR_4IN68CVTKN5HWSCTCM8G03ZIQ 541332 ODSR_4IPWDLN3A0E9ZQL9C715GI9W1 65351 ODSR_4IQPKCK3V2L00HG22IF07LF2P 86676 ODSR_4ITGFBK0J0DFFDYVSFXFAN05U 55170 ODSR_4IV1M2YUQGVGUIQ4KHQ06WXAP 53280 ODSR_4IVFQZCVDSWY1PPK0MXC06SNL 67820 ODSR_4IXS9V5HSV12YA22KWXUV0FPU 47570 ODSR_4J2627MWPQSEMKEN3SCD6BKIA 49048 ODSR_4J32BJFV6AV8GCQCX6NEUY5JM 18109 ODSR_4J4JFJQKSZPTDIM9Z2DVE9ZMA 58148 ODSR_4J9OGSBOLT2YN8BA0V0EVJHJM 67760 11
  • 12. 1 Identify Tables 5 Determine # of Partitions 2 Discuss with Business 6 Run a Script to identify the Upper Key Limit Generate CREATE TABLE 3 Determine Partition Key 7 Statement Run SQL Statement 4 Group BY & 8 Load data into Partitioned Table Order BY
  • 13. 13
  • 14. 14
  • 15. 1 Identify Tables 5 Determine # of Partitions 2 Discuss with Business 6 Run a Script to identify the Upper Key Limit Generate CREATE TABLE 3 Determine Partition Key 7 Statement Run SQL Statement 4 Group BY & 8 Load data into Partitioned Table Order BY
  • 16. CREATE TABLE "/BIC/B0000252000_NEW" PARTITION BY RANGE ("REQUEST") (PARTITION P1 VALUES LESS THAN ('ODSR_4IR2HPAGY9GBAE2NNIO2BQJ6A') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 30M NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSAPSR3FACT", 16
  • 17. 1 Identify Tables 5 Determine # of Partitions 2 Discuss with Business 6 Run a Script to identify the Upper Key Limit Generate CREATE TABLE 3 Determine Partition Key 7 Statement Run SQL Statement 4 Group BY & 8 Load data into Partitioned Table Order BY
  • 18. 18
  • 20. Multi-providers Cubes Further levels of Partitioning 20
  • 21. Drop Partition instead of Deleting Records Benefits: Old records are not mixed with new records. Dropping partition will be faster 21
  • 22. 22