RE: BTree Index Leading Column(s) Thought Question?

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Fri, 9 Mar 2012 14:15:49 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D6ADD5C1246_at_SPOBMEXC14.adprod.directory>

(Data Distribution Fixed - I think)

Forgive me if this seems like a silly question but it's been a long week and my brain is tired LOL Let's say I have the following considerations:

a.) Large table in GBs

b.) Many queries using similar PREDICATES (but in different orders, for different reasons)

c.) Let's say I look at my data distribution for 2 of the columns (for queries using BUSINESS_UNITS and ACCOUNTING_PERIODS)

d.) Example query: select x,y,z from tableA where business_Unit = 'xxxxx' and accounting_period='yyyyy'

e.) Here is the data distribution

Does it make more sense to have a B-Tree index with Accounting Period as the first column (generally speaking I mean - based on the data dist below)?

Or does it make more sense to have BU first - because if BU != 10000 we can effectively disregard 844,775 rows immediately.

But if we have ACCOUNTING_PERIOD = 1 then we only have to go through 113,358 'rows'

(it's entirely possible I'm thinking about BTree indexes incorrectly here as well)

BUSINESS_UNIT   COUNT(*)                ACCOUNTING_PERIOD       COUNT(*)
10000           844775                                  1       113358
10100           194284                                  12      106288
10110           87229                                   2       105735
10200           61059                                   7       105391
11000           53593                                   8       105151
00001           22406                                   6       104582
10020           22256                                   9       104249
10300           10329                                   10      103754
10302           9920                                    11      103746
10301           8958                                    4       102220
ELIM4           7890                                    3       102190
ELIM3           6468                                    5       101066
10031           5439                                    999     86562
00002           3343                                    0       15751
10030           2810
10905           2663
ELIM2           2116
10120           1919
10904           1762
10902           1312
ELIM5           1274
10901           1109
10032           1009
10122           963
00051           568
10900           547
00056           483
00020           441
00030           384
00055           383
00010           353
10903           287
00057           283
10010           241
00050           231
10121           186
00099           165
00015           143
ELIM6           124
11100           121
00060           105
00052           60
00054           48
ELIM7           38
10201           26
11999           18
ELIM9           18
00053           12

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 09 2012 - 14:15:49 CST