The following example will help you understand and guide you to choose which one you would prefer.
Let's suppose we have four tables, each with a single column primary index, and have five million rows in each of them:
- Table A (PI=x1)
- Table B (PI=y1)
- Table C (PI=y1)
- Table D (PI=x2)
Following the query that we will analyze of multi column statistics.
Select A1.*, B2.*, C3.*, D4.* from A1, B2, C3, D4 where A1.x1=B2.x1 and A1.x2=D4.x2 and A1.x3=007 and A1.x4='BOND' and B2.y1=C3.y1 and B2.y2 in ('JAI','BOM') and C3.z1 = 15 and C3.z2 = '*' and D4.r1 <> 'DELTA' and D4.r2 = '7690';
Let's suppose we have collected stats on PI columns of all the four tables:
- STATS ON A (x1,x2): Don't collect. Multi-column stats won't be used in ...