BTree Index Leading Column(s) Thought Question?

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

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.

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

Received on Fri Mar 09 2012 - 14:10:25 CST