{\rtf1\adeflang1025\ansi\ansicpg1252\uc1\adeff1\deff0\stshfdbch0\stshfloch0\stshfhich0\stshfbi0\deflang1033\deflangfe1042{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f1\fswiss\fcharset0\fprq2{\*\panose 020b0604020202020204}Arial;}
{\f2\fmodern\fcharset0\fprq1{\*\panose 02070309020205020404}Courier New;}{\f3\froman\fcharset2\fprq2{\*\panose 05050102010706020507}Symbol;}{\f52\froman\fcharset238\fprq2 Times New Roman CE;}{\f53\froman\fcharset204\fprq2 Times New Roman Cyr;}
{\f55\froman\fcharset161\fprq2 Times New Roman Greek;}{\f56\froman\fcharset162\fprq2 Times New Roman Tur;}{\f57\fbidi \froman\fcharset177\fprq2 Times New Roman (Hebrew);}{\f58\fbidi \froman\fcharset178\fprq2 Times New Roman (Arabic);}
{\f59\froman\fcharset186\fprq2 Times New Roman Baltic;}{\f60\froman\fcharset163\fprq2 Times New Roman (Vietnamese);}{\f62\fswiss\fcharset238\fprq2 Arial CE;}{\f63\fswiss\fcharset204\fprq2 Arial Cyr;}{\f65\fswiss\fcharset161\fprq2 Arial Greek;}
{\f66\fswiss\fcharset162\fprq2 Arial Tur;}{\f67\fbidi \fswiss\fcharset177\fprq2 Arial (Hebrew);}{\f68\fbidi \fswiss\fcharset178\fprq2 Arial (Arabic);}{\f69\fswiss\fcharset186\fprq2 Arial Baltic;}{\f70\fswiss\fcharset163\fprq2 Arial (Vietnamese);}
{\f72\fmodern\fcharset238\fprq1 Courier New CE;}{\f73\fmodern\fcharset204\fprq1 Courier New Cyr;}{\f75\fmodern\fcharset161\fprq1 Courier New Greek;}{\f76\fmodern\fcharset162\fprq1 Courier New Tur;}
{\f77\fbidi \fmodern\fcharset177\fprq1 Courier New (Hebrew);}{\f78\fbidi \fmodern\fcharset178\fprq1 Courier New (Arabic);}{\f79\fmodern\fcharset186\fprq1 Courier New Baltic;}{\f80\fmodern\fcharset163\fprq1 Courier New (Vietnamese);}}
{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;
\red128\green0\blue128;\red128\green0\blue0;\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 
\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \snext0 Normal;}{\s1\qc \li0\ri0\widctlpar\dxfrtext187\dfrmtxtx187\dfrmtxty187\wrapdefault\faauto\outlinelevel0\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 
\b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext0 heading 1;}{\s2\ql \li0\ri0\widctlpar\wrapdefault\faauto\outlinelevel1\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 
\b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext0 heading 2;}{\s3\ql \li0\ri0\widctlpar\wrapdefault\faauto\outlinelevel2\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 
\b\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext15 heading 3;}{\*\cs10 \additive Default Paragraph Font;}{\*
\ts11\tsrowd\trftsWidthB3\trpaddl108\trpaddr108\trpaddfl3\trpaddft3\trpaddfb3\trpaddfr3\tblind0\tblindtype3\tscellwidthfts0\tsvertalt\tsbrdrt\tsbrdrl\tsbrdrb\tsbrdrr\tsbrdrdgl\tsbrdrdgr\tsbrdrh\tsbrdrv 
\ql \li0\ri0\widctlpar\wrapdefault\aspalpha\aspnum\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af0\afs20 \ltrch\fcs0 \fs20\lang1024\langfe1024\cgrid\langnp1024\langfenp1024 \snext11 \ssemihidden Normal Table;}{
\s15\ql \fi-720\li720\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin720\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext15 Normal Indent;}{
\s16\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs20\alang1025 \ltrch\fcs0 \f1\fs20\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext16 endnote text;}{\s17\ql \li0\ri0\widctlpar
\tqc\tx4819\tqr\tx9071\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext17 header;}{
\s18\ql \li1440\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin1440\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext18 footnote text;}{
\s19\ql \fi-720\li1440\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin1440\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon15 \snext19 Double Indent;}{
\s20\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext20 Action;}{
\s21\ql \li0\ri0\sb120\sa120\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext0 caption;}{
\s22\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af2\afs16\alang1025 \ltrch\fcs0 \f2\fs16\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \sbasedon0 \snext22 program;}{\s23\ql \li0\ri0\widctlpar
\tx480\tx960\tx1440\tx1920\tx2400\tx2880\tx3360\tx3840\tx4320\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af2\afs20\alang1025 \ltrch\fcs0 \f2\fs20\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 \snext23 macro;}{
\s24\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1503\posy238\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 \rtlch\fcs1 \af2\afs16\alang1025 \ltrch\fcs0 \f2\fs16\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 
\sbasedon0 \snext24 Body Text;}{\*\cs25 \additive \rtlch\fcs1 \af0 \ltrch\fcs0 \ul\cf2 \sbasedon10 \styrsid2520671 Hyperlink;}}{\*\latentstyles\lsdstimax156\lsdlockeddef0}{\*\listtable{\list\listtemplateid1735439532\listsimple{\listlevel\levelnfc0
\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'02\'00.;}{\levelnumbers\'01;}\rtlch\fcs1 \af0 \ltrch\fcs0 \hres0\chhres0 \fi-360\li1492\jclisttab\tx1492\lin1492 }{\listname ;}\listid-132}
{\list\listtemplateid883598524\listsimple{\listlevel\levelnfc0\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'02\'00.;}{\levelnumbers\'01;}\rtlch\fcs1 \af0 \ltrch\fcs0 \hres0\chhres0 \fi-360\li1209
\jclisttab\tx1209\lin1209 }{\listname ;}\listid-131}{\list\listtemplateid1566310500\listsimple{\listlevel\levelnfc0\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'02\'00.;}{\levelnumbers\'01;}\rtlch\fcs1 
\af0 \ltrch\fcs0 \hres0\chhres0 \fi-360\li926\jclisttab\tx926\lin926 }{\listname ;}\listid-130}{\list\listtemplateid1460155832\listsimple{\listlevel\levelnfc0\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext
\'02\'00.;}{\levelnumbers\'01;}\rtlch\fcs1 \af0 \ltrch\fcs0 \hres0\chhres0 \fi-360\li643\jclisttab\tx643\lin643 }{\listname ;}\listid-129}{\list\listtemplateid79494728\listsimple{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0
\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0\hres0\chhres0 \fi-360\li1492\jclisttab\tx1492\lin1492 }{\listname ;}\listid-128}{\list\listtemplateid-1066774604\listsimple{\listlevel\levelnfc23\levelnfcn23
\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0\hres0\chhres0 \fi-360\li1209\jclisttab\tx1209\lin1209 }{\listname ;}\listid-127}{\list\listtemplateid-382019860\listsimple
{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0\hres0\chhres0 \fi-360\li926\jclisttab\tx926\lin926 }{\listname ;}\listid-126}
{\list\listtemplateid1896003126\listsimple{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0\hres0\chhres0 \fi-360\li643\jclisttab\tx643\lin643 }
{\listname ;}\listid-125}{\list\listtemplateid-1760808696\listsimple{\listlevel\levelnfc0\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'02\'00.;}{\levelnumbers\'01;}\rtlch\fcs1 \af0 \ltrch\fcs0 
\hres0\chhres0 \fi-360\li360\jclisttab\tx360\lin360 }{\listname ;}\listid-120}{\list\listtemplateid475724448\listsimple{\listlevel\levelnfc23\levelnfcn23\leveljc0\leveljcn0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext
\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0\hres0\chhres0 \fi-360\li360\jclisttab\tx360\lin360 }{\listname ;}\listid-119}{\list\listtemplateid-1\listsimple{\listlevel\levelnfc0\levelnfcn0\leveljc0\leveljcn0\levelfollow0\levelstartat0\levelspace0
\levelindent0{\leveltext\'01*;}{\levelnumbers;}\rtlch\fcs1 \af0 \ltrch\fcs0 \hres0\chhres0 }{\listname ;}\listid-2}}{\*\listoverridetable{\listoverride\listid-2\listoverridecount1{\lfolevel\listoverrideformat{\listlevel\levelnfc23\levelnfcn23\leveljc0
\leveljcn0\levelfollow0\levelstartat1\levelold\levelspace0\levelindent283{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0\hres0\chhres0 \fi-283\li283\lin283 }}\ls1}{\listoverride\listid-119\listoverridecount0\ls2}{\listoverride\listid-125
\listoverridecount0\ls3}{\listoverride\listid-126\listoverridecount0\ls4}{\listoverride\listid-127\listoverridecount0\ls5}{\listoverride\listid-128\listoverridecount0\ls6}{\listoverride\listid-120\listoverridecount0\ls7}{\listoverride\listid-129
\listoverridecount0\ls8}{\listoverride\listid-130\listoverridecount0\ls9}{\listoverride\listid-131\listoverridecount0\ls10}{\listoverride\listid-132\listoverridecount0\ls11}}{\*\rsidtbl \rsid2520671}{\*\generator Microsoft Word 11.0.8106;}{\info
{\title Partitioning Problems (JLComp)}{\author Jonathan Lewis}{\keywords Oracle partition tables performance trouble-shooting tuning}{\doccomm Problems with Oracle's partitioned tables}{\operator Jonathan Lewis}{\creatim\yr2000\mo12\dy22\hr19\min57}
{\revtim\yr2007\mo8\dy19\hr14\min46}{\printim\yr1998\mo6\dy7\hr9\min53}{\version4}{\edmins9}{\nofpages4}{\nofwords1901}{\nofchars10839}{\*\company JL Computer Consultancy}{\nofcharsws12715}{\vern24611}{\*\password 00000000}}{\*\xmlnstbl {\xmlns1 http://sc
hemas.microsoft.com/office/word/2003/wordml}{\xmlns2 urn:schemas-microsoft-com:office:smarttags}}\paperw11909\paperh16834\margl1440\margr1440\margt1080\margb1080\gutter0\ltrsect 
\widowctrl\ftnbj\aendnotes\donotembedsysfont0\donotembedlingdata1\grfdocevents0\validatexml0\showplaceholdtext0\ignoremixedcontent0\saveinvalidxml0\showxmlerrors0\prcolbl\cvmme\otblrul\brkfrm\swpbdr\lytprtmet\hyphcaps0\horzdoc\fracwidth\dghspace120
\dgvspace120\dghorigin1701\dgvorigin1984\dghshow0\dgvshow3\jcompress\viewkind1\viewscale117\viewzk2\pgbrdrhead\pgbrdrfoot\rsidroot2520671 \fet0{\*\wgrffmtfilter 013f}\ilfomacatclnup0\ltrpar \sectd \ltrsect
\sbknone\linex0\headery1080\footery1080\colsx709\endnhere\sectdefaultcl\sftnbj {\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl2\pnucltr\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang 
{\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang {\pntxta )}}{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl6\pnlcltr\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl7
\pnlcrm\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}\pard\plain \ltrpar
\s1\qc \li0\ri0\widctlpar\dxfrtext187\dfrmtxtx187\dfrmtxty187\wrapdefault\faauto\outlinelevel0\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 
\ltrch\fcs0 \insrsid5263853 Problems with Partitions}{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid2520671  (Dec 2000)}{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 .
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \ai\af1 \ltrch\fcs0 \i\insrsid5263853 
\par Table Partitioning is potentially one of the most important new features in Oracle 8 aimed at addressing the problems of 
large databases, and 24 x 7 activity.  However I believe that there are a couple of fundamental problems in the way that Oracle Corp. has implemented partitioned tables that requires them to be used with caution. This article may be a timely warning to th
ose of you contemplating the use of partitioned tables as part of your current design strategy.
\par 
\par }{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 \sect }\sectd \ltrsect\sbknone\linex0\headery1080\footery1080\cols2\endnhere\sectdefaultcl\sftnbj {\*\pnseclvl1\pnucrm\pnqc\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl2
\pnucltr\pnqc\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl3\pndec\pnqc\pnstart1\pnindent720\pnhang {\pntxta .}}{\*\pnseclvl4\pnlcltr\pnqc\pnstart1\pnindent720\pnhang {\pntxta )}}{\*\pnseclvl5\pndec\pnqc\pnstart1\pnindent720\pnhang {\pntxtb (}
{\pntxta )}}{\*\pnseclvl6\pnlcltr\pnqc\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl7\pnlcrm\pnqc\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}{\*\pnseclvl8\pnlcltr\pnqc\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}
{\*\pnseclvl9\pnlcrm\pnqc\pnstart1\pnindent720\pnhang {\pntxtb (}{\pntxta )}}\pard\plain \ltrpar\s2\ql \li0\ri0\widctlpar\wrapdefault\faauto\outlinelevel1\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 
\b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 What are partitioned tables ?
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
Oracle version 8.0 introduced the concept of partitioned tables as a type of object that could be split into a number of completely independent data segments in a way that was transparent to the end-user but recognised automatically by the optimiser. 

\par 
\par Given appropriate application design, it was possible to get truly dramatic performance improvements at query time, and eliminate massive overheads at data loading and data purging time. 
\par 
\par Fig. 1 is an example of the SQL needed to create a 'range-partitioned' table.
\par }\pard \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1503\posy185\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 create table pt_demo (
\par \tab date_loaded\tab date,
\par \tab vc1\tab \tab varchar2(10),
\par \tab numb1\tab \tab number(8,2),
\par \tab numb2\tab \tab number(8,2)
\par )
\par partition by range (date_loaded) 
\par (
\par partition p_01 values less than (to_date('01-jan-2000','dd-mon-yyyy')),}{\rtlch\fcs1 \af0\afs16 \ltrch\fcs0 \f0\fs16\insrsid5263853 
\par }{\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 partition p_02 values less than (to_date('01-feb-2000','dd-mon-yyyy')),}{\rtlch\fcs1 \af0\afs16 \ltrch\fcs0 \f0\fs16\insrsid5263853 
\par }{\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 partition p_03 values less than (to_date('01-mar-2000','dd-mon-yyyy'))
\par );}{\rtlch\fcs1 \af0\afs16 \ltrch\fcs0 \f0\fs16\insrsid5263853 
\par }\pard\plain \ltrpar\s21\ql \li0\ri0\sb120\sa120\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1503\posy185\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {
\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853 Figure }{\field{\*\fldinst {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853  SEQ Figure \\* ARABIC }}{\fldrslt {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 
\fs16\lang1024\langfe1024\noproof\insrsid5263853 1}}}\sectd \ltrsect\sbknone\linex0\headery1080\footery1080\cols2\endnhere\sectdefaultcl\sftnbj {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853   Creating a partitioned table.}{\rtlch\fcs1 \af1 
\ltrch\fcs0 \insrsid5263853 
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par 
\par In this example, the table PT_DEMO is actually split into three separate data segments, one is guaranteed to hold rows where the 'date_loaded' column is strictly prior to 1}{\rtlch\fcs1 \af1 \ltrch\fcs0 \super\insrsid5263853 st}{\rtlch\fcs1 \af1 
\ltrch\fcs0 \insrsid5263853  Jan 2000, one is guaranteed to hold data from the midnight starting 1}{\rtlch\fcs1 \af1 \ltrch\fcs0 \super\insrsid5263853 st}{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853  Jan up to 11:59:59 on 31}{\rtlch\fcs1 \af1 
\ltrch\fcs0 \super\insrsid5263853 st}{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853  Jan, and the final partition will hold data from the midnight of 1}{\rtlch\fcs1 \af1 \ltrch\fcs0 \super\insrsid5263853 st}{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
 Feb 2000 up to 11:59:59 on 29}{\rtlch\fcs1 \af1 \ltrch\fcs0 \super\insrsid5263853 th}{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853  Feb 2000.  As it stands the table will not be able to hold data with a date_loaded column greater than or equal to 1}{
\rtlch\fcs1 \af1 \ltrch\fcs0 \super\insrsid5263853 st}{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853  Mar 2000.
\par 
\par }\pard\plain \ltrpar\s2\ql \li0\ri0\widctlpar\wrapdefault\faauto\outlinelevel1\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 
\insrsid5263853 What are the benefits ?
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
If we examine the example, we can see immediately that there is a possible performance benefit in query execution. Assume you execute a query that explicitly identifies your need for data from 15}{\rtlch\fcs1 \af1 \ltrch\fcs0 \super\insrsid5263853 th}{
\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853  Feb (say), the optimizer can determine that the query can be executed against just the middle partition - a potential factor of three improvement in performance.
\par 
\par The second benefit appears as the data ages - it may not be an obvious requirment in a table of only three partitions, but when data i
s so old that it is no longer needed we can simply 'drop partition' rather than deleting millions of rows. Instead of an operation taking perhaps a few hours, we have an operation that takes only a few seconds.
\par 
\par Finally, we have the converse opportunity wh
en we add data to the table. Rather than inserting data into an existing large table, with all the index maintenance and rollback contention that that implies, we are allowed to prepare a totally separate table with new, clean data, and then 'shuffle' the
 data dictionary to make the new data belong to the old table - fig. 2 outlines the process.
\par 
\par }\pard \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1503\posy214\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 create table new_data (
\par \tab date_loaded\tab date,
\par \tab vc1\tab \tab varchar2(10),
\par \tab numb1\tab \tab number(8,2),
\par \tab numb2\tab \tab number(8,2)
\par );
\par 
\par /*  
\par }\pard \ltrpar\ql \fi720\li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1503\posy214\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 populate NEW_DATA with new data
\par using your favourite methods
\par }\pard \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1503\posy214\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 */
\par 
\par analyze table new_data 
\par estimate statistics sample 1 percent;
\par 
\par alter table pt_demo 
\par add partition p_04 values less than (to_date('01-Apr-2000','dd-mon-yyyy'));
\par 
\par analyze table pt_demo partition (p_04)
\par estimate statistics;
\par 
\par alter table pt_demo
\par exchange partition p_04 
\par with table new_data
\par including indexes
\par without validation;
\par 
\par }\pard\plain \ltrpar\s21\ql \li0\ri0\sb120\sa120\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1503\posy214\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {
\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853 Figure }{\field{\*\fldinst {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853  SEQ Figure \\* ARABIC }}{\fldrslt {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 
\fs16\lang1024\langfe1024\noproof\insrsid5263853 2}}}\sectd \ltrsect\sbknone\linex0\headery1080\footery1080\cols2\endnhere\sectdefaultcl\sftnbj {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853   Adding a new tranche of data.}{\rtlch\fcs1 \af1 
\ltrch\fcs0 \insrsid5263853 
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par The benefits look terrific, so what could Oracle Corp. have done that makes me warn you to handle partitioned tables with care. 
\par 
\par Inevitably there are bugs, but these are gradually disappearing; and new extra options are appearing with every release; however, there are two fundamental mechanisms that can eliminate the benefits of partitioned tables if you try to push them too hard.

\par 
\par Instead of a monthly partitioning strategy, as in the examples, let's consider an all-too-obvious strategy of the data warehou
se that loads a day's worth of data in a big batch every night.  Given the typical requirement to 'compare this year with last year' we inevitably end up with at least 730 partitions (365 x 2) in the table. What side-effects does this have ?
\par 
\par }\pard\plain \ltrpar\s2\ql \li0\ri0\widctlpar\wrapdefault\faauto\outlinelevel1\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 
\insrsid5263853 Partitions are numbered.
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
The first problem to address is that of partition maintenance.  This has a number of minor effects, but the most obvious one appears when you come to drop a partition from the end of history.  On a simple experiment involving a partitioned tabl
e with two local indexes and 730 partitions, this took a solid 21 CPU seconds. Why ?
\par 
\par If you look in the data dictionary at the table sys.tabpart$ (sys.indpart$ for indexes), you will find that each partition of a table (index) has a position number that s
orts the partitions into the correct order. When you drop the oldest partition (part# zero in the dictionary) }{\rtlch\fcs1 \ab\ai\af1 \ltrch\fcs0 \b\i\insrsid5263853 every other partition has to be renumbered}{\rtlch\fcs1 \af1 \ltrch\fcs0 
\insrsid5263853 . My 21 seconds of CPU went into the two statements in fig 3
\par }\pard\plain \ltrpar\s17\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posyt\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 
\af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 Table patch - 729 executions
\par 
\par update\tab tabpart$ 
\par set\tab part# = :1 
\par where\tab obj# = :2 
\par and\tab bo# = :3
\par }{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par }{\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 Index patch - 1,458 executions:
\par 
\par update\tab indpart$ 
\par set\tab part# = :1 
\par where\tab obj# = :2
\par and\tab bo# = :3
\par 
\par }\pard\plain \ltrpar\s21\ql \li0\ri0\sb120\sa120\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posyt\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {
\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853 Figure }{\field{\*\fldinst {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853  SEQ Figure \\* ARABIC }}{\fldrslt {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 
\fs16\lang1024\langfe1024\noproof\insrsid5263853 3}}}\sectd \ltrsect\sbknone\linex0\headery1080\footery1080\cols2\endnhere\sectdefaultcl\sftnbj {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853   Dropping the bottom partition.}{\rtlch\fcs1 \af1 
\ltrch\fcs0 \insrsid5263853 
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par Of course, 21 CPU seconds doesn't se
em to be a lot to suffer if you only drop a single partition once per day, and manage to schedule it when nothing else is going on. However, this could turn into a surprisingly large number of physical data reads (especially if you have been adding one pa
r
tition per day, and therefore have to read large numbers of data dictionary blocks to do the updates), so the elapsed time could be quite significant.  How long could it take if you have a partitioned table with 3,000 partitions and 12 bitmap indexes ?  H
ow big would your shared pool have to be to ensure that you weren't continuosly losing dictionary cache information if you had several such tables rather than one ?
\par 
\par Apart from the time, there are other side-effects - for example this single drop caused tho
usands of block changes and half a megabyte of redo log generation; the rate of rollback cycling for all the recursive update also happened to cause "ORA-01555: snapshot too old" to appear on another session.
\par 
\par Inevitably, because every partition definition has changed, the entire dictionary cache relating to this object is flushed and all dependent SQL in the library cache is invalidated and has to be reparsed. (Fortunately, PL/SQL that references the table is 
not invalidated).
\par 
\par If you happen to be doing this with a partitoned IOT (index organized table) any query against the table that starts after the drop will wait for the drop to complete before failing with an "ORA-04020: deadlock detected" error; but that's
 a bug rather then an inherent implementation problem. 
\par 
\par }\pard\plain \ltrpar\s2\ql \li0\ri0\widctlpar\wrapdefault\faauto\outlinelevel1\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 
\insrsid5263853 Parsing time:
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
One of the comments in the manuals about partitioned tables is that the table's global statistics are used for deciding an access path, and the same access path is then used for every p
artition. Why then, does Oracle load information about every single partition in the table when you issue the following statement :
\par }\pard\plain \ltrpar\s23\ql \li0\ri0\widctlpar\tx480\tx960\tx1440\tx1920\tx2400\tx2880\tx3360\tx3840\tx4320\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af2\afs20\alang1025 \ltrch\fcs0 
\f2\fs20\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af2 \ltrch\fcs0 \insrsid5263853 
\par select * 
\par from partitioned_table 
\par where partitioning_column = 
\par \tab literal_constant 
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par Part of the problem may be to do with th
e fact that the information used to put boundaries on the data going into each partition is stored as a LONG column, rather than as a structured data pattern.  (Aren't LONG columns a deprecated feature?). Perhaps Oracle has to load the information for eve
ry single partition before it can begin the process that starts interpreting the content of these LONG columns.
\par 
\par When dealing with partitioned tables, you have to remember that the optimizer has to deal with a very large number of independent objects. Parse times for partitioned objects can be very large. Consider the very simple statement:
\par 
\par }\pard\plain \ltrpar\s23\ql \li0\ri0\widctlpar\tx480\tx960\tx1440\tx1920\tx2400\tx2880\tx3360\tx3840\tx4320\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af2\afs20\alang1025 \ltrch\fcs0 
\f2\fs20\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af2 \ltrch\fcs0 \insrsid5263853 insert into tableX values (1,1,1);
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par On a small test system, with table definitions loaded, and ensuring that all required meta-data was already in the dictionary cache, 
the parse time for this statement was less than 20 milliseconds for a simple table, but more than 130 milliseconds for my partitioned table of 730 partitions. Once parsed, subsequent executions operated at much higher speeds - but this seems to demonstrat
e the supreme importance of using bind variables if you use large partitioned tables.
\par 
\par However, consider the query in fig. 4:
\par 
\par }\pard \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posy0\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 select 
\par }\pard \ltrpar\ql \fi720\li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posy0\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 count(*) 
\par }\pard \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posy0\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 from
\par }\pard \ltrpar\ql \fi720\li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posy0\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 jpl1, 
\par jpl2, 
\par pt_demo
\par }\pard \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posy0\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 where 
\par }\pard \ltrpar\ql \fi720\li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posy0\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 pt_demo.n1 between to_number(:b1) 
\par \tab     and to_number(:b2)
\par }\pard \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posy0\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid5263853 and\tab jpl1.owner = 'SCOTT'
\par and\tab pt_demo.v1 = jpl1.object_name
\par and\tab jpl2.owner = 'OUTLN'
\par and\tab pt_demo.v2 = jpl2.object_name
\par 
\par }\pard\plain \ltrpar\s21\ql \li0\ri0\sb120\sa120\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx6399\posy0\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {
\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853 Figure }{\field{\*\fldinst {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853  SEQ Figure \\* ARABIC }}{\fldrslt {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 
\fs16\lang1024\langfe1024\noproof\insrsid5263853 4}}}\sectd \ltrsect\sbknone\linex0\headery1080\footery1080\cols2\endnhere\sectdefaultcl\sftnbj {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid5263853   Joining to a partitioned table.}{\rtlch\fcs1 \af1 
\ltrch\fcs0 \insrsid5263853 
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par The table PT_DEMO is partitioned on the column N1. If you supply a query as above, the parse time and memory demand will be large as Oracle will load and inspect the statistics for every single partition (not, as per the manual, the global table statistic
s
) when trying find the optimum path.  On the other hand, if you actually had the literal values available, the parse time and memory demand could be much smaller, as Oracle would inspect only the statistics of the targetted partitions. This can be observe
d
 set switching SQL_TRACE one, and setting event 10053; the resulting trace file for a new parse statement will show you the code that Oracle 8 uses to load statistcs, and the information it is using to calculate the cost of each execution path (see fig. 5
 for examples)
\par }\pard\plain \ltrpar\s24\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 \rtlch\fcs1 \af2\afs16\alang1025 \ltrch\fcs0 
\f2\fs16\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af2 \ltrch\fcs0 \insrsid2520671 Recursive SQL to load statistics
\par 
\par select 
\par }\pard \ltrpar\s24\ql \fi720\li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 {\rtlch\fcs1 \af2 \ltrch\fcs0 \insrsid2520671 bucket_cnt, row_cnt, cache_cnt,
\par null_cnt, timestamp#,
\par sample_size, minimum, maximum,
\par distcnt, lowval, hival, density,
\par col#, spare1, spare2, avgcln 
\par }\pard \ltrpar\s24\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 {\rtlch\fcs1 \af2 \ltrch\fcs0 \insrsid2520671 from
\par }\pard \ltrpar\s24\ql \fi720\li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 {\rtlch\fcs1 \af2 \ltrch\fcs0 \insrsid2520671 hist_head$ 
\par }\pard \ltrpar\s24\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 {\rtlch\fcs1 \af2 \ltrch\fcs0 \insrsid2520671 where\tab obj#=:1 
\par and\tab intcol#=:2;
\par 
\par Sample of stats used to evaluate cost.
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 
\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid2520671 Column: N1\tab \tab -- Column name
\par {\*\xmlopen\xmlns2{\factoidname State}}{\*\xmlopen\xmlns2{\factoidname place}}Col{\*\xmlclose}{\*\xmlclose}#:   1      \tab -- Column number
\par Part#:  0\tab \tab -- Parition number
\par Table:  PT_DEMO   \tab -- Table name
\par Alias:  PT_DEMO\tab -- Table alias
\par }\pard\plain \ltrpar\s24\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 \rtlch\fcs1 \af2\afs16\alang1025 \ltrch\fcs0 
\f2\fs16\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af2 \ltrch\fcs0 \insrsid2520671 NDV:    0\tab \tab -- distinct values
\par NULLS:  0         \tab -- number of nulls
\par Dens:   0.0000e+000    -- density
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 
\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af2\afs16 \ltrch\fcs0 \f2\fs16\insrsid2520671 
\par }\pard\plain \ltrpar\s21\ql \li0\ri0\sb120\sa120\widctlpar\brdrt\brdrs\brdrw15\brsp20 \brdrl\brdrs\brdrw15\brsp20 \brdrb\brdrs\brdrw15\brsp20 \brdrr\brdrs\brdrw15\brsp20 
\pvpara\phpg\posx1525\posy240\absh1297\absw3973\dxfrtext180\dfrmtxtx180\dfrmtxty0\wrapdefault\faauto\adjustright\rin0\lin0\rtlgutter\itap0\pararsid2520671 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 
\b\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid2520671 Figure }{\field\flddirty{\*\fldinst {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid2520671  SEQ Figure \\* ARABIC }}{\fldrslt {
\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\lang1024\langfe1024\noproof\insrsid2520671 5}}}\sectd \ltrsect\sbknone\linex0\headery1080\footery1080\cols2\endnhere\sectdefaultcl\sftnbj {\rtlch\fcs1 \af1\afs16 \ltrch\fcs0 \fs16\insrsid2520671 
  The optimiser at work.}{\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid2520671 
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
\par Obviously, if you re-use the query text (as you would hope to, perhaps) the second, and subsequent, parses would be much quicker as they would be 
'soft' parses, rather than 'hard' parses.  However queries against partitioned tables with large numbers of partitions demand a lot of memory in the shared pool for is big, and it does not take many different queries before the shared pool starts flushing
 
and recycling - I have, for example, seen more complex queries against objects with 3,000 partitions soak more than 30MB of memory during parse time, even when hinted to an extreme degree. It is possible that on a system with a reasonable variety of queri
es, the use of bind variables will NOT save you from the cost of 'hard' parses, because you rarely get the chance to re-use a parse-tree before it is flushed from the shared pool. 
\par 
\par This may sound a little far-fetched, but remember that Oracle has introduce
d star-transformations, and partiton-wise joins in recent releases.  The former more or less dictates the presence of large numbers of local bitmap indexes on partition tables, the latter assumes that you will have systems where you want to join (at least
) two partitioned tables. Systems with large numbers of partitioned objects, with non-trivial queries are an almost inevitable consequence of the features that Oracle has introduced.
\par 
\par }\pard\plain \ltrpar\s2\ql \li0\ri0\widctlpar\wrapdefault\faauto\outlinelevel1\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \ab\af1\afs24\alang1025 \ltrch\fcs0 \b\f1\fs24\ul\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 
\insrsid5263853 Conclusion:
\par }\pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 \f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \af1 \ltrch\fcs0 \insrsid5263853 
Partitioned tables can give you enormouse performance benefits, but have two inherent defects. 
\par 
\par First, every partition is physically stamped with a sequential ID to identify its order in the boundary value list, this introduces a serious performance impact during partition maintenenace procedures, most particularly 'drop partition'. 
\par 
\par Second, the nee
d to check against boundary values can introduce a massive overhead to parse times and memory requirements even when, or in some cases because of, using bind variables. Parse times can be extremely high if the number of partitions involved is very large.

\par 
\par \sect }\sectd \ltrsect\sbknone\linex0\headery1080\footery1080\colsx709\endnhere\sectdefaultcl\sftnbj \pard\plain \ltrpar\ql \li0\ri0\widctlpar\wrapdefault\faauto\adjustright\rin0\lin0\itap0 \rtlch\fcs1 \af1\afs24\alang1025 \ltrch\fcs0 
\f1\fs24\lang2057\langfe1042\cgrid\langnp2057\langfenp1042 {\rtlch\fcs1 \ai\af1 \ltrch\fcs0 \i\insrsid5263853 
\par Jonathan Lewis is a freelance consultant with more than 15 years experience of Oracle.  He specialises in physical database design and the strategic use of the Oracle database engine, and is author of 'Practical Orac
le 8I - Designing Efficient Databases' to be published by Addison-Wesley Longman in December 2000.  He can be contacted on 07973-188785, or e-mailed at }{\field{\*\fldinst {\rtlch\fcs1 \ai\af1 \ltrch\fcs0 \i\insrsid2520671  HYPERLINK "mailto:
jonathan@jlcomp.demon.co.uk" }{\rtlch\fcs1 \ai\af1 \ltrch\fcs0 \i\insrsid2520671\charrsid13503710 {\*\datafield 
00d0c9ea79f9bace118c8200aa004ba90b02000000170000001c0000006a006f006e0061007400680061006e0040006a006c0063006f006d0070002e00640065006d006f006e002e0063006f002e0075006b000000e0c9ea79f9bace118c8200aa004ba90b460000006d00610069006c0074006f003a006a006f006e006100
7400680061006e0040006a006c0063006f006d0070002e00640065006d006f006e002e0063006f002e0075006b000000}}}{\fldrslt {\rtlch\fcs1 \ai\af1 \ltrch\fcs0 \cs25\i\ul\cf2\insrsid2520671\charrsid13503710 jonathan@jlcomp.demon.co.uk}}}\sectd \ltrsect
\sbknone\linex0\headery1080\footery1080\colsx709\endnhere\sectdefaultcl\sftnbj {\rtlch\fcs1 \ai\af1 \ltrch\fcs0 \i\insrsid5263853 
\par }{\rtlch\fcs1 \ai\af1 \ltrch\fcs0 \i\insrsid2520671 \'a9 December 2000
\par }}