BTree Index Leading Column(s) Thought Question?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 Mar 2012 14:10:25 -0600
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)
48 BUs, 14 APs
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
Chris Taylor
"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)
Any views and/or opinions expressed herein are my own and do not necessarily
reflect the views of Ingram Industries, its affiliates, its subsidiaries or its
employees.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
