Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Partitioned Table vs. Partitioned View

Partitioned Table vs. Partitioned View

From: Terrian, Thomas <Thomas.Terrian_at_standardregister.com>
Date: Mon, 5 Jun 2000 10:22:27 -0400
Message-Id: <10519.107703@fatcity.com> 





This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.



------_=_NextPart_000_01BFCEF9.79DC6A10


Content-Type: text/plain;


        charset="iso-8859-1"



We are in the process of converting our partitioned views over to
partitioned tables (since partitioned views will not be supported in future
releases.



However, the performance of the partitioned table is much worst that the
partitioned view.  Can anyone explain why?  At the bottom you will see an
explain plan from the view (DSS_SALES_ORG) and the table (DSS_SALES_ORG_NEW)
....



    
  
  1.   The same sql script was used for the test (except DSS_SALES_ORG was
replaced with DSS_SALES_ORG_NEW).

  
  2.   Neither one of scripts uses an index on the partitioned view or table.

  
  3.   The run times for each script:





View     Table
----     -----
1:54     6:01
1:02     5:45
1:01     5:56
1:02     5:42

     Why does the partitioned table perform so slowly compared to the


partitioned view?



4.  The table is range partitioned on fiscal_month.  Several indexes have
been created for the view and the table, however, none of them are used in
this sql statement. 



5.  For both the view and the table, partition pruning is taking place.



6.  Why are the stats. so different?







Partitioned View:



14010 rows selected.



Elapsed: 00:00:44.62



Execution Plan




   0	  SELECT STATEMENT Optimizer=CHOOSE (Cost=118768 Card=1997637
Bytes=701170587)


   1	0   SORT* (GROUP BY) (Cost=118768 Card=1997637 Bytes=701170587


:Q115700 ) 1



   2	1     NESTED LOOPS* (Cost=42710 Card=1997637 Bytes=701170587)


:Q115700 0


   3	2	NESTED LOOPS* (Cost=22734 Card=1997637 Bytes=671206032


:Q115700 )	0

   4	3	  VIEW* OF 'DSS_SALES_ORG' (Cost=2757 Card=1997637 Byt


:Q115700 es=627258018)0

   5	4	    UNION-ALL* (PARTITION) :Q115700  0
   6	5	      TABLE ACCESS* (FULL) OF 'DSS_SALES_ORG_199901' (


:Q115700 Cost=504 Card=34 Bytes=10676)0

   7	5	      TABLE ACCESS* (FULL) OF 'DSS_SALES_ORG_199902' (


:Q115700 Cost=345 Card=9 Bytes=2286)0

   8	5	      TABLE ACCESS* (FULL) OF 'DSS_SALES_ORG_199903' (


:Q115700 Cost=339 Card=9 Bytes=2340)0

   9	5	      FILTER*


:Q115700 0

  10	9		TABLE ACCESS* (FULL) OF 'DSS_SALES_ORG_199904'


:Q115700 (Cost=591 Card=18 Bytes=3924)0

  11	5	      FILTER*


:Q115700 0

  12   11		TABLE ACCESS* (FULL) OF 'DSS_SALES_ORG_199905'


:Q115700(Cost=523 Card=1 Bytes=209)0

  13	5	      FILTER*


:Q115700 0

  14   13		TABLE ACCESS* (FULL) OF 'DSS_SALES_ORG_199906'




:Q115700(Cost=379 Card=1 Bytes=244)0



<cut>


  51	3	  TABLE ACCESS* (BY INDEX ROWID) OF 'PRODUCT_DIMEN' (C



:Q115700 ost=1 Card=4262 Bytes=93764)0



  52   51	    INDEX* (UNIQUE SCAN) OF 'PK_PRODUCT_DIMEN' (UNIQUE



:Q115700)0


  53	2	TABLE ACCESS* (BY INDEX ROWID) OF 'SALES_ORG_DIMEN' (C


:Q115700 ost=1 Card=4633 Bytes 69495)		       0

  54   53	  INDEX* (UNIQUE SCAN) OF 'PK_SALES_ORG_DIMEN' (UNIQUE




:Q115700) 0




   1 PARALLEL_TO_SERIAL 	   SELECT



A1.C0,A1.C1,A1.C2,A1.C3,A1.C4,A1.C5,A


                                   1.C6,A1.C7,A1.C8,SUM(A1.C9),SUM(A1.C


   2 PARALLEL_TO_PARALLEL	   SELECT /*+ ORDERED NO_EXPAND USE_NL(A2)

INDE



                                   X(A2 "PK_SALES_ORG_DIMEN") */ A1.C0



   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_COMBINED_WITH_PARENT
   5 PARALLEL_COMBINED_WITH_PARENT
   6 PARALLEL_COMBINED_WITH_PARENT
   7 PARALLEL_COMBINED_WITH_PARENT
   8 PARALLEL_COMBINED_WITH_PARENT
   9 PARALLEL_COMBINED_WITH_CHILD
  10 PARALLEL_COMBINED_WITH_PARENT
  11 PARALLEL_COMBINED_WITH_CHILD
  12 PARALLEL_COMBINED_WITH_PARENT
  13 PARALLEL_COMBINED_WITH_CHILD
  14 PARALLEL_COMBINED_WITH_PARENT




  <cut>

  51 PARALLEL_COMBINED_WITH_PARENT
  52 PARALLEL_COMBINED_WITH_PARENT
  53 PARALLEL_COMBINED_WITH_PARENT
  54 PARALLEL_COMBINED_WITH_PARENT






Statistics



	  0  recursive calls
      22678  db block gets
    5307218  consistent gets
      19459  physical reads
	  0  redo size
    2814049  bytes sent via SQL*Net to client
     103987  bytes received via SQL*Net from client
	935  SQL*Net roundtrips to/from client
	 26  sorts (memory)
	  0  sorts (disk)
      14010  rows processed

============================================================================






Partitioned Table:



14010 rows selected.



Elapsed: 00:05:31.66



Execution Plan




   0	  SELECT STATEMENT Optimizer=CHOOSE (Cost=31859 Card=1327361
Bytes=148664432)


   1	0   SORT* (GROUP BY) (Cost=31859 Card=1327361 Bytes=148664432)


:Q115800 3



   2	1     NESTED LOOPS* (Cost=14800 Card=1327361 Bytes=148664432)


:Q115800 2



   3	2	HASH JOIN* (Cost=1526 Card=1327361 Bytes=128754017)


:Q115800 2



   4	3	  TABLE ACCESS* (BY INDEX ROWID) OF 'PRODUCT_DIMEN' (C



:Q115800 ost=3 Card=4262 Bytes=9376  0


   5	4	    BITMAP CONVERSION (TO ROWIDS)
   6	5	      BITMAP INDEX (FULL SCAN) OF 'PRODUCT_DIMEN_IDX01'
   7	3	  PARTITION RANGE* (ITERATOR)


:Q115800 2

   8	7	    TABLE ACCESS* (FULL) OF 'DSS_SALES_ORG_NEW' (Cost=


:Q115800 1407 Card=1327361 Bytes=99552075) 1

   9	2	TABLE ACCESS* (BY INDEX ROWID) OF 'SALES_ORG_DIMEN' (C


:Q115800 ost=1 Card=4633 Bytes=69495)2

  10	9	  INDEX* (UNIQUE SCAN) OF 'PK_SALES_ORG_DIMEN' (UNIQUE




:Q115800 )2





   1 PARALLEL_TO_SERIAL 	   SELECT



A1.C0,A1.C1,A1.C2,A1.C3,A1.C4,A1.C5,A


                                   1.C6,A1.C7,A1.C8,SUM(A1.C9),SUM(A1.C


   2 PARALLEL_TO_PARALLEL	   SELECT /*+ ORDERED NO_EXPAND USE_NL(A2)

INDE



                                   X(A2 "PK_SALES_ORG_DIMEN") */ A1.C5



   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_FROM_SERIAL
   7 PARALLEL_COMBINED_WITH_PARENT
   8 PARALLEL_TO_PARALLEL	   SELECT /*+ NO_EXPAND ROWID(A1) */
A1."PRODUC
				   T_DIMEN_VERSION" C0,A1."PRODUCT_CODE





   9 PARALLEL_COMBINED_WITH_PARENT



  10 PARALLEL_COMBINED_WITH_PARENT



Statistics



     157732  recursive calls
      31107  db block gets
    2178802  consistent gets
      30559  physical reads




    3794572  redo size


    2745586  bytes sent via SQL*Net to client

     103987  bytes received via SQL*Net from client
	935  SQL*Net roundtrips to/from client
	 11  sorts (memory)
	  4  sorts (disk)
      14010  rows processed







Thanks,



Tom Terrian


Oracle DBA


(937) 221-1647


Thomas.Terrian_at_standardregister.com



------_=_NextPart_000_01BFCEF9.79DC6A10


Content-Type: application/ms-tnef


Content-Transfer-Encoding: base64



eJ8+IiAOAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy
b3NvZnQgTWFpbC5Ob3RlADEIAQWAAwAOAAAA0AcGAAUACgAWABsAAQAeAQEggAMADgAAANAHBgAF
AAoAFgAdAAEAIAEBCYABACEAAABFQjU1QTUxMkUzM0FENDExOUFBRTAwQTBDOUQ1RDdGQQBYBwEE
gAEAJwAAAFBhcnRpdGlvbmVkIFRhYmxlIHZzLiBQYXJ0aXRpb25lZCBWaWV3ACAOAQ2ABAACAAAA
AgACAAEDkAYAvBAAADEAAAADAAGACCAGAAAAAADAAAAAAAAARgAAAABShQAA8BMAAB4AAoAIIAYA
AAAAAMAAAAAAAABGAAAAAFSFAAABAAAABAAAADguNQALAA6ACCAGAAAAAADAAAAAAAAARgAAAAAG
hQAAAAAAAAMAA4AIIAYAAAAAAMAAAAAAAABGAAAAAAGFAAAAAAAACwAEgAggBgAAAAAAwAAAAAAA
AEYAAAAAA4UAAAAAAAALAAWACCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMABoAIIAYAAAAA
AMAAAAAAAABGAAAAABCFAAAAAAAAAwAHgAggBgAAAAAAwAAAAAAAAEYAAAAAEYUAAAAAAAADAAiA
CCAGAAAAAADAAAAAAAAARgAAAAAYhQAAAAAAAB4ACYAIIAYAAAAAAMAAAAAAAABGAAAAADaFAAAB
AAAAAQAAAAAAAAAeAAqACCAGAAAAAADAAAAAAAAARgAAAAA3hQAAAQAAAAEAAAAAAAAAHgALgAgg
BgAAAAAAwAAAAAAAAEYAAAAAOIUAAAEAAAABAAAAAAAAAAsADIALIAYAAAAAAMAAAAAAAABGAAAA
AACIAAAAAAAACwANgAsgBgAAAAAAwAAAAAAAAEYAAAAABYgAAAAAAAACAQkQAQAAAMsKAADHCgAA
rR0AAExaRnXkcnCqAwAKAHJjcGcxMjXiMgNDdGV4BUEBAwH3/wqAAqQD5AcTAoAP8wBQBFY/CFUH
shElDlEDAQIAY2jhCsBzZXQyBgAGwxEl9jMERhO3MBIsETMI7wn3tjsYHw4wNREiDGBjAFCzCwkB
ZDM2FlALp2MBMHAgV2UgCsAdQAuAINR0aB1AcANgYweQBCBIb2YgBaBudgSQdP0LgGcegAhwHgAK
wB8gHyDTAiAJgCB2CJB3HnEe8a0dwG8fqwGRbAeRKACQXm4eQB+vBCAD8GwDIG4KbwVAYh1Ac3Vw
cMcXwSBBHaFmdXQIcB1AyxggIkBhFBBzLgqiCoShCoBIb3dlHvEsHcT7BJACEHIDgSLBHpEd0yFu
ax2QBCBtGtBoJAAFsHMbBUAd0GErQiLfdy4g1xIgA5EAcHkgISAOwAtT0SQAaHk/LQBBK5QG4OsC
QANwIC2AdSQEFBAdQf8DoC3WC1ElgQNhHcMgciJwAERTU19TQUxF4TJwT1JHKS1RIfEd4UMqJDJM
X05FVzMgLis1YSaqMSzxVB3hc2FbB4AkwHEwAQUEdyZQIH51FBAgUChhM3QHkAVAKP8OwB5ABTEy
WzfTGCALUR5AvyBQA/Ad0Dm8NQMmmzIs8f8HwDuBEoEtkh6RBPQ4AwQg9wORC4ABAHg+YSuvMfM4
gssiIiabMzZ1cnUdsQdzrzhyJkAq0QT0OiaqVjIC7UYyVCIiJqQtRzFGM0cyIi02BTo1NEYzNjo2
MBrzSFIwFEBGMjU6lDQ1SUcxSeU1NklPLjQOUCa5RjNXLmAgZH5vB5EpLyozKDUkwCEwc9kXsHds
TgAFoG0KsSHi1yEwQH8ggT8mqjQ2dSonWnIAcGdR3EBBZgQAY+0HQF8EYAIwaCzxBmAe8f8HQD/U
B5ET4B7wJJEJ8B6wvxggK4A4STHzM0sn0Ggndv8kYD6EHdEvcB1iOCMdoyqBfzczAZAOsAeAAjAs
8CaqNf0s8UYFsS8hKuBZf1qFIvffHgFDoB8yKoEBkGsfMjsD/SabNizxTeIdYh3SXaImgPVQQmQG
kGYEkF4BUvsmpH49Z69ov2nPat9rESaqUOcjCUXyRSsxNEkgFlADYLcgoRQQIkBjJTEmm0ULYMJw
ODE6IDAwSRBxUKo0U8A2TMtFV9BjJbD9YcJQMSFG2HQvdT92T0fnT0YxAUABkUYxU0UysEMCVAYA
VEFURU1Fmk548E8FMAdwaXoEkOA9Q0hPT3igInAIUAErMD0xMTg3NjgnLRELIHsQOTl7UDM3SXg1
Qnk48T03SSAx9X0gNXtAKU03AFB4QhZQE1bxMvBUKiJwR1JP9FVQfLBZMyB6z3vWfL7oIDpRgGA1
fSAWUDMgf0k1RjEOUHhCSwQ1EHkQRchEIEx6cFBTfzGAE/1MsDdu4YDffQotAIKnAUC/TUYVMHhC
g/SEv4AxMoYA7jNIoIZffQA2hhAB0Bww3jMUQIKofmRNNzR4M4l0GS0AVkk1IH8wT0Yg6icyWyeL
BjeC4IvPgpiXjNKGAA4wOEkgOCmI2A9KYHhCjzRGMlVOSU+UTi0yoEx/MVBBfxD+SZdQlpAzIIKn
iMlLkHhCN5VkmcR5IEIysBDAQ0NzMsCFYkZVltAzIJB/Xz174TlJIJGBgpiAEzUw34u1i6F8xG7g
jQA2lLl8RG+Zf5qPm5+cozKdD56hNa17hDmHFYtwOJ+KOKBft6Fvon+cWDOkP56gM6XgK6Wci6Aw
lLk5p29GSfJMeUBSKngzr/+n54g/f34xeCSuZHgzqH+pj5xYNOeRgIKngAQ1OUsAe5R7cPN8xKxw
MjSUuIBgrn+vj/+wn7GvsrVJ0roVs++0/5wNPjW3CLe1rXB7haX2MDn/uXmJdLqPu5+8r72/fkBI
ou/Flb+fwK+cDTbCvnwwrIVXxEdxoJS2PHLhPk02NQ9+VI+Wy21/wCBJTkSERVgH8E9XSUTMlBJQ
f3BEVXjgX0RJ/3lhkYKCmIAje4RMsHHQhxX6OXwwNrlpDkBKAn5URjIn09N/MZZhUVV6oFND5kGX
odSjS1/U7tplgqbv2EmJetK/088nNHjVb9Z8D3wgw9B8w0jwOTQ5Nf+ONMh62FhIot412c/a2+E/
f9yfiMZ9yHNAlzCWwXiwX+xUTzKAxwBJMqDIZ3ilokE2YEMwLO5yMe7DqjLuwzPuwzTuwzXuwK8m
pceflgPugTbuwzfuw8A4LFNVTSjucsTgP/OXTN0UQOxa7FbtjS8qqit5oFLT8FKE8U7s8L/UAJcg
0+CWUHigNQBM89BeMjMg4CLwz8g6WPnRIJIi6N9OIjMgKi/uZIdM3cPQ7FdDT01C4CDlhPBf1FBU
SPaSeXGOqP//bwB/lRQB7wL/mMQEbwV//5/0Bu8H/6cECW8Kf64UC+/3DPV6UMbQRLKYDm8M/7nU
/xDfD3++pBNvEf/FYxXPFG//yiQYXxbvTYHQ/Rq/G8/Ypf8eHx8v3gMgjyGf5iUjHyQvnWbbU12x
KoBzAGNzc5//Ki8rP3bP+qV4AeVQZjBy4Nhyc2lYMVZRbCkV5VQ/i3CNAHtwThB4YDPgb2O8ayBV
AD8wTTjmsDCUQPu4oVDhbi5wORBeATDNfjH35HC4EOVQcE3wLnBWUTrR/GFkKRUtik4gb1B6AU04
76ZwbrHkgOVQYuQTb2AywfVgIGFXAFGW4D3wk0BRgfxjbEYAXhBNOW7grHCCcb839S4hPgBbMFKS
ONhmbxD3UDA5qrM0M6VwOOdvEEOgzGR0ROFOUW8vPO/lFN/XUOVQUFBhgEQQKF3wVpA8cnl9tS2I
QaVl4HNr/324fiJuw28EYgAwkOQwXKH/Zu9HX0hvSX9Kj0ufbI9T8B9UUm3vbv9wD3EVNToz/e6A
NpkQcg9zH1RfVW9Wf993X3hveX96iFHgODQhw/R7jXCLkDbERk7wpoDYIDT/jXB9v37Pf9dbb1x/
l7WUcP9egObAg3+Ej4AFXQCdomCvD2G6Ymhx5YlNSEFTSPggSk/gIGUnw7AG0GYP/58hpnCSEE8B
iAJn347/3v//4A/Uz+JGYsKlE9bv1/KYWmOVbhkQVE1Bf6AmIE56VscAU5aBzDDs4HDUU39diJkf
dpZwhMxD2spxql/9cRBYnOGfvebGlydw0Nrw/EdFXwGXYMcAWXCcYOS5/8h/aEynVKBEqD/MP5wX
ZGB+V5GGYmhO8ZI2ZmucsDX/w7Ax0OSgXhGt7N6vcB/gz/9yH+LvdDXkZGiHsxvnL+g//+lP3Ndi
wpDW60/sX+1v7n//74/wn/Gv8r/zz/Tf9e/2///4D/kf+i/7P/xP/V7GAP5/1yWvJr8BrEZfQE2Y
hQjP/6tfCu+hb6J/o4OkWHDjn1F//caoAHGjpe+AunxWd0Ui/8PgmjO19XHwr/Clxw3fr9//EmwQ
z7x/J58or8Ifwy/EP/9Xm14ByXBrwNjxLi8vOFHg308gOwEwTzcVbOA4YsDY8f8yT8fZUbA0Ly8J
dLA0ATHg+zYvL5M3NBBisHTROA85H/86Lzs/PE89Xz5vP39AjBMxf0GfLUnmcUOPRJ9Fr98vCsRU
aFOwa3Ms3+vY4Y5UWmDYQFO2T3Jh0nDpx0BEQpw1KHShZ8Av0HgxLTFncINA4FXY4GG0cy7iFUCP
MFOwZIgBpTTwZ8sici7K4G021AJ958AAHgBwAAEAAAAnAAAAUGFydGl0aW9uZWQgVGFibGUgdnMu
IFBhcnRpdGlvbmVkIFZpZXcAAAIBcQABAAAAFgAAAAG/zvmkJ5F32Gc6vBHUndkAwE/yhfoAAAsA
AgABAAAAAwDeP69vAABAADkAEGrcefnOvwEDAPE/CQQAAB4AMUABAAAACAAAAFRURVJSSUEAAwAa
QAAAAAAeADBAAQAAAAgAAABUVEVSUklBAAMAGUAAAAAAAwD9P+QEAAADACYAAAAAAAMANgAAAAAA
AwCAEP////8CAUcAAQAAADUAAABjPVVTO2E9IDtwPVN0ZFJlZztsPU9IX0RBWVROX1hDSDAtMDAw
NjA1MTQyMjI3Wi00MjE1AAAAAAIB+T8BAAAAYwAAAAAAAADcp0DIwEIQGrS5CAArL+GCAQAAAAAA
AAAvTz1TVEFOREFSRCBSRUdJU1RFUiBDT01QQU5ZL09VPVNSQ19DT1JQT1JBVEUvQ049UkVDSVBJ
RU5UUy9DTj1UVEVSUklBAAAeAPg/AQAAABAAAABUZXJyaWFuLCBUaG9tYXMAHgA4QAEAAAAIAAAA
VFRFUlJJQQACAfs/AQAAAGMAAAAAAAAA3KdAyMBCEBq0uQgAKy/hggEAAAAAAAAAL089U1RBTkRB
UkQgUkVHSVNURVIgQ09NUEFOWS9PVT1TUkNfQ09SUE9SQVRFL0NOPVJFQ0lQSUVOVFMvQ049VFRF
UlJJQQAAHgD6PwEAAAAQAAAAVGVycmlhbiwgVGhvbWFzAB4AOUABAAAACAAAAFRURVJSSUEAQAAH
MCC952/5zr8BQAAIMKjvCHv5zr8BHgA9AAEAAAABAAAAAAAAAB4AHQ4BAAAAJwAAAFBhcnRpdGlv
bmVkIFRhYmxlIHZzLiBQYXJ0aXRpb25lZCBWaWV3AAAeADUQAQAAAEUAAAA8Nzc4MkJFNkNGRDY0
RDIxMTlBN0IwMEEwQzlENUQ3RkEwNTAxRTE2OEBvaF9kYXl0bl94Y2gwMi5zdGRyZWcuY29tPgAA
AAALACkAAAAAAAsAIwAAAAAAAwAGELhIUCMDAAcQUBIAAAMAEBAAAAAAAwAREAAAAAAeAAgQAQAA
AGUAAABXRUFSRUlOVEhFUFJPQ0VTU09GQ09OVkVSVElOR09VUlBBUlRJVElPTkVEVklFV1NPVkVS
Received on Mon Jun 05 2000 - 09:22:27 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US