Vertical partitioning in database design

NORTH- HOLLAND

Vertical Partitioning

in D a t a b a s e D e s i g n

YIN-Irl" HITAN(;

~ll|f[ ('llIN-lll:l':l VAN l.)cpartmel~t of l'h'ctronlc l"ngmccrzn 9, .V~d~onnl }'~l/~?~ l~st~tnt~: of 7i:chnology. 123 [:Tl~l'cTszty tbmd, ;it,/ Nor'.. }'Tttllt~t. 7hzwart. 1,40. l¢cpublic of (,'h~na

-\ BS'I'I¢ A("T \Vlwlt ~t t r a n s a c t loll ill a relal ional dat aba.sc sysll,ln is processed, t r a n s a c t ion r e s p o n s e l illlO is likely domi,mt¢,d by Ill(, disk a c c e s s t i m e . By p a r t i t i o n i n g a rclat ioll illlO fr~tglll(,lll,,-;, aCCOl'di~lg to l]w re(lllir(,lllt'nl o[ llallS~l('liOllS, it trall.,4ncl.iOll c a n avoid a c c e s s i n g t h e useless d a t a . In this p a p e r , ;Ul al,gorilhm u s i n g t l w A" t e c h n i q u e , w h i c h cat, lind t h e global o p t i m a l parl.itioll quickly, is p r e s e n l e d . T w o lolill(qllcqll, i i l e t h o d s ledllct.ioll o[" tht. t-;t';tl'C']l s[)D.('o ~llld g o o d t!st.illlD.liOll ill'l* also l)l'.p¢~sed t i, illlprov~' lh(' pf?lf(lrlll;-illCO of till, sOltl(]l ])loct,dlll'C.

1.

IN'I'I-{ODUCTI()N

Wh(,n a transa(:tion in a r~qational (latabas(' system is processed, it may Im required to access a large alnOtlllt of data. By partitioning a relation into fragments according to the r(,quirements of the tral~sactions, a transaction can avoid accessing useless data. The partitioning method could be vertical or horizontal. Vertical partitioning subdivides attributes into fragments, whereas horizontal partitioning subdivides tuph's into fragments i7]. The problem of vertical partit.ioning is to determine how to partition a relation into fragments, such that the performance of a database system is the most efficient. Selecting an optimal partition is a difficult problem. As pointed out in {,1], a relation with m attributes can be partitioned into B(m) different ways, where B(m) is the Bell number: for large m, B(m) approaches rn TM. Thus heuristic approaches are necessary to determine the near-optimal partition in the presence of ;t laro~e relation. This work was s u p p o r t e d by the National ~cieuce Council under contract NSC b20113-E-22,1-015-'['.

INFORM,4 T/ON S('II:'N('I(S 86. 19-35 II u951 .c Elsevier S c i e n c e Inc. 1995 h55 A v e n u e o f the America,,. N e w York. NY ]0(ll()

(~0211-0255/95/$9.5tt SSI)I 0020-0255(95)001118-K

20

Y.-F. HUANG AND C.-H. VAN

Hammer and Niamir [4] designed a mechanism that can find a nearoptimal vertical partition, although it conducts a search through the space of all possible partitions by mnploying the hill-climbing technique. Cornell and Yu [1. 2] developed an integer programming formulation to solve the problem of vertical partitioning. At each iteration, tile integer programruing fornmlation finds an optimal partitioning that splits the relation into two fragments. The integer programming formulation can be applied recursively until no protitable split can be found. However, this approach only finds a local optimal partition. Navathe and Ra [9] proposed a vertical partitioning algorithm using the graph technique. They used the affinity [8] between attributes to generate fragments. This method can find a feasible partition, but partitioning by affinity cannot imply that tile solution found can reduce the number of disk accesses [1, 2 I. In this paper, an A* algorithm is proposed to find the global optimal partition that minimizes the number of disk accesses. In Section 2, the basic concept of how to apply the A* algorithm to solve the problem of vertical partitioning will be described. The refinement methods are proposed in Section 3. Section 4 presents the algorithm and some experimental results. Section 5 contains a summary. 2.

BASIC C O N C E P T S

In this paper, tile A* heuristic search algorithm is used to search the large solution space of partitions and to choose one partition that yMds the minilllllIn number of disk accesses. A decision graph is constructed in tile search technique. Ill this graph, each edge corresponds to it decision of assigning an attribute to a fragment and a node identifies a specified partition. A decision graph of partitioning a three-attribute relatkm is shown in Figure 1. Before describing the search procedure, some definitions are presented. DEFINITION 1. A partitioning is called a completely specified partitioning (CSP) if each attribute has already been assigned to a fragment. A partially specitied partitioning (PSP) is when some of the attributes have been ~ussigned to a fragment and tile others have not yet been assigned.

DEFINrI'ION 2. A specified partitioning sp (CSP or PSP) is the successor of a partially specified partitioning psp, if it is possible to derive sp by assigning unassigned attributes to the fragments of psp. The cost of a CSP is the sum of disk access required by each transaction under this specified partition: Cost(CSP) =

~ "I'x , E T

Access_Cost(rx,,CSP),

(2.1)

V E R T I C A L P A R T I T I O N I N G IN DATABASE DESIGN

21

jJ "-..

J {a}

{a,b)

(a},{o} .

{:}

{a).{c}

{a.-c}

........~ : ~ : ~

-.-~_____~

{a},{b},{c} Fig 1.

'ibt

{a,c},{b}

{a},{b,c}

(b},{c}

{b.c}

...._ ~ . ..........

{a,b},{c}

{a,b,c}

A decision graph for partitioning a thr~e-attribute relation.

where T is a set of transactions that are chosen as the usage patterns of the partitioned relation, and Access_Cost (Tx~, CSP) is the number of disk I / O when Tx, is processed under the partition CSP. The cost of a PSP is defined as the minimum cost among all tile P S P ' s successors: Cost(PSP) =

rain

sPi ~Succ(PSP)

[Cost(sp/)].

(2.2)

where Succ(PSP) is a collection of all tile successors of PSP. Obviously, the cost of a PSP is difficult to derive and should be estimated. The estimated_cost of a PSP is always underestimated; therefore the A* algorithm will eventually find the optimal partition [5]. A good estimator for the cost of a PSP will make the A* algorithm find an optimal partition quickly. The following formula is used to compute the estimated_cost of a PSP: Estimated_Cost ( P S P ) =

~ T:r, ET

[Access_Cost(Tz,, Optimal(Tz~,PSP))].

(2.a)

where Optimal (Tz~, PSP) is a CSP that yield the minimum number of disk I / O among all the PSP's successors. An attribute ~j is named as the scan key of a transaction Tzj if a¢ is ill the restriction clause of the transaction Txj and is used as the key to scan the whole relation. A fragment that contains the scan key of T z 3 is called the primary fragment of transaction T z 3. A fragment that contains the required attributes of 2'az) and is not the primary fragment is called tile seconda~" fragment of T z 3. A transaction has exactly one primary fragment, and the number of secondary fragments varies with different

22

Y.-F. HUANG AND C.-H. VAN

partitioniug. T h e disk I / O for processing a transaction comes from two parts. One is the disk I / O of accessing tile p r i m a r y fragment and the other is the disk I / O of a(:(:essing tile secondary fragments. T h e following tormula [6] is used to (:Oml)ute the average n u m b e r of disk I / O when a transaction retrieves the l)rimary fragment. Clustered zndc~" .sc~u~:

N u m b e r of disk I / O = (cardinality) × (sele(:tivily) × (tuple length) page size (2.4) UT~clustcred ind
Numb(u" of disk I / ( ) = (cardmality) × (s,,h,ctivity).

(2.5)

Scqu~1~tial ,scan:

((:ardinality) x (tuple length) . N u m b e r of disk I / ( ) = (t)age size) × (prefetch blocking factor)

(2.6)

T h e n u m b e r of disk I / O of ac(:('ssing the secondary fragtnents is exl)ressed in the formula Numb('r ~)f disk 1/O -- (tile n u m b e r of secondary fragments) × (cardinality) × (s(,h,ctivily).

(2.7)

E X A M P L E 1. T h e R)llowing i)arameters describe the relation considered and system (,nvironment: numt0er of a t t r i b u t e s = 4 length of a t t r i b u t e s a , b, c. d i = 10.10.10.501 {bytes) length of F I D ~. ,1 bytes cardinality = 10,000 page size = 5000 bytes l)reR,tch blocking fact.or = 10 (-lustered key is c a P S P ix given as {{c}. {d}}

T h e characteristics of the different transaction types accessing the relation are shown ill Tal)h, 1. "l'abh, 2 shows the access (:()st of each transa.(:tioli. The estimated_cost of tim P S P is

(2> 1)+ 10×5~, (10+10)--

72.

VERTICAL PARTITI()NIN(; IN DATABASE DESIGN

23

TABLE 1 Transaction t'rofih' fl)r Example 1 ["r c( lUelw.v

At t r i b u t e s required

,.~call key

l'.r I

2

b. r"

,.

l'.r ,

IO

.. h

,~

I r:¢

1

b . , ' . ,l

,I

Seh,<'t ivil v 0001 0(101 {}.001

"I:\I~LE 2

Access ( !ost of Each 'lransm:tion

Optimal(T.r,, PSI'} 1'I illKll'~," ll'agllllHlt ~(,C(llid~ll'V fl'}lOliltql| l ' y p e ~t sc;mnillg. ( p r i m a r y fragmcn!} ( '(~sl of acc(,ssing I Iw l)l'illKtlV [l'/iglll(!ll t. ('~)st (d" av(:cssing lhe s~'(Olldn ly ['l'~l~lllOlll

l'.r i

"1.r2

7"2":~

{b.c}.{..d}

{..b}.{,'} {,/}

{/~, F} X(IIII* ("lustm',,d m(h,x 1

{It. I,} .'k,"IH1U St.qm.:~t i;tl

{..{b,,:}.{d/ {dl {b.(,}

",

l()

()

i~

I()

index

'i'h,, real c,)st of t h e t ' S P is Cost ( { { , . b . c } . { d } } ; is u n d e r e s t i m a t , ' d .

:l.

[ 7nchlst ercd

- 92. T h e , ' s t i t n a t e d ( t , s t

REFINEMENT METII()DS Il~ this s,,ction, ttw m e t h o d s t h a t can improve t h e p e r f o r m a n c e of t h e

s ( ' a r c h plOt'(~(.hllO al'(' I ) l o p o s c t ] .

:~'. 1.

I~1"1.)L.'(."I'ION 0t." T t t K S E A R( "1I S P A ( "£"

\ \ ' h e n a node is e x p a n d e d , any unassigned a t t r i b u t e can be a.~signed to any f r a g n w n t s in t h e PSP. Therefl)re. this cxpan(ling, m e t h o d F will generato ]t|nas.si~ned t t t r i b u t e s I x ([f'ragnient t- 1) suc(essors (where lunassign(!d a t t r i b u t e s ~ is the nmM)er of unassigned a t t l i b u t ~ s and ifl'aVnlent] is the n u m b e r of f r a g m e n t s in t h e P S P ) . A l t h o u g h a large n m n b e r of nodes are .
()llC I ) l e d e ( ' e s s o r ,

s o t l l ; t t xVhOll ~l l l o ( l o

is ,Aen~'ratocl. it is llt'(('ss;tlX." to check whot:ht,r this lleW l l o d o is ill the o w n set to avoid dutfiication. Therefbre. the tim(. and t h e space for I" hi'( ~ (qlOrlll()/is.

24

Y.-F. HUANG AND C.-H. VAN {} {a)

{a},{b} {a},{b},{c} Fig. 2.

{a,c},{b}

{a.~. ........ {a},{o,c}

,a.b},{c}

{a.'~,c}

Using F', t h e decision g r a p h s h o w n in Figure 1 b e c o m e s a decision tr, e.

'Ib reduce the search space, a new expanding method F' is used. When a node is expanded by F', only one attribute is assigned to any fragment. This will generate (Ifragmentl + 1) nodes, and the decision graph will become a decision tree. Using r', the decision tree is as shown in Figure 2. Replacing F with F' yields some advantages as follows: (1) When a new node is expanded, it is not necessary to check whether the node has already been in the open set. (2) When expanding a node, less new nodes are generated (lunassigned attribute I x (Ifragrnentl + 1) --* (Ifragment I + 1)).

3.2.

.4 GOOD ESTIMATION

The closer the estimated cost is, the sooner the search terminates. This influences the efficiency of the search procedure. Before the estimated_cost of a PSP is computed, it is necessary to determine the optimal partition of each transaction; however, a transaction's optimal partition may conflict with another transaction's optimal partition. When computing the estimated_cost of a PSP, an unassigned attribute, which is required by most transactions, is called an important attribute. If tile assigning order of attributes is based on their importance, the degree of inconsistency may be decreased, and the estimated_cost will be closer to the real cost. E X A M P L E 2. Redo Example 1 for different assigning order of attributes. The importance of each attribute can be expressed by Access(Tx~, uj),

Tx,¢T where Access(Tx~, aj) has value 1 if Tx~ needs attribute aj, and 0 otherwise. Then the importance of attributes (a, b, c, d) = (1,3, 2, 1). Consider the PSPs with only two assigned attributes. All the PSPs that can derive the optimal partition, CSP {{a, b, c}, {d}}, are {{a, b}}, {{a, c}},

VERTICAL PARTITIONING IN DATABASE DESIGN

25

TABLE 3 The Estimated_Cost of PSPs Assigning order of attributes

PSPi

Estimated_cost

a,b,c,d

{{a,b}}

62

a,d,b,c

{{a}{d}}

62

b,d,a,c c,d.a,b

({bI{dI} {{c}{d}}

72 72

{{a}, {d}}, {{b,c}}, {{b}.{d}}, and {{c}.{d}}. The estimated_costs are listed in Table 3. For Example 1, the real cost of the PSP, is Cost({{a, b,c}, {d}}) = 92, where i = 1 , . . . ,6. Based oll the attribute's importance, the ,assigning order should be b, c, a, d, and its Estimated_cost is closest to the real cost. To explain how a good estimation influences the efficiency of the search pro(:edure, refer to Table 7 shown in Section 4.3. 4.

ALGORITHM

4. I.

PA RA M E T E R S

There are three types of parameters used in our algorithm: 1. relation parameters 2. transaction parameters 3. system parameters Relation parameters are (1) the attributes in tile relation and the length of each attribute, (2) the number of tuples in the relation (cardinality), and (3) the clustered key, which is used to cluster the relation. The transaction parameters are (1) the scan key, which is used `as the key to scan tile whole relation, (2) the attributes accessed by the transaction, (3) tile number of occurrences of the transaction (frequency), and (4) the average fraction of tuples that satisfy the predicate on the scan key (selectivity). The system parameters are (1) the page size and (2) the number of pages read in each disk access (prefetch blocking factor). 4.2.

D E S C R I P T I O N OF THE A L G O R I T H M

A node in the algorithm is a specified partition. A node is described by three properties: Unassigned_attribute, Partitioned_relation, and

26

Y.-F. HUANG A N D C.-H. VAN

Estinlated_cost. Unassigned_attribute is a set of at,trilmtes t h a t have not been assigned to a fragment. Partitioned_relation is a set ,~f fragments that ha\'~ t)t~tll g~nel'ated. Estimated_cost is the sum of disk access required by." each transa(:tion under this specified partition. ( ) P E N is a list of nodes sorted by ~kscending Estimated_cost. Initially, the list ( ) P E N contains only one node called the root. T h e un~kssigned attributes of the root node ar~ all the attributes in a universal relation and there is m~ fragment ill the root node. In each iteration, a node wit h the smallest cost in t.h¢' list ( ) P E N is chosen to be, expanded, if a node t h a t is chosen contains no unassigned attribute, then the loop will stop aml fragments in the Partit.ioned_relat.ioll of the node are the tinal result. A detailed procedure is shown in Figure 3. Expall(ling a node has the following operations. First. an a t t r i b u t e that is the most i m p o r t a n t in Una,ssigned_att.ributes is chosen. T h e way to determine the importance is described in Section 3.2. T h e n the successors of a no(h' are generated by ~msigning the i m p o r t a n t a t t r i b u t e into any fragment in Partitioned_relation: all successors with Estimated_cost are inserte(l into the list ( ) P E N . Finally, the expa,Med node is removed from the list (.)PEN. A more detailed procedure, which expands a node. is shown in Figure 4. T h e Estimated_cost of a node can be obtained from (2.3). Belbre comlmting the cost, the optimal partition tor each translt(:t.ion should be de(,ermined. Det~,rmining the ()primal (T:rj. P S P ) means to assign all the unassigned attributes to the. fragments in the P S P according to the r~quiremmlt of T:rj. 'l'h~ m e t h o d of determining the ()ptmml(T:rj, P S P ) is described in three steps. For a transaction 7':r j. there ar~ three types of attributes: sc~l.I1 key, attributes required by "l':ro, and attributes not required by "/':rj. "l'he first step is to determine how to assign the scan key. If the scan key of T:rj has been assigned, the primary fragment is the one t h a t contains the scan key: otherwise a fragment t h a t has the smallest tuple hmgth anion ° all the secondary fragments of T:cj is chose, n to be the p r i m a r y fragment. ' t h e n the scan key is assigne(l to the chosen fragment. T h e reasol! is that the access cost of the p r i m a r y fragment rises wheu the tuple length of the p r i m a r y fragment incre~ses. If the, secondary fragment of i/':rj does not exist in the PSP. a fragment t h a t contains all the attributes required by transaction T.r a is generated. T h e re~kson is that the best situation for it transaction is when only' one fragment needs to be accessed and there is no irrelewmt a t t r i b u t e m the fragment. T h e second step is to determine how to ~ s i g n the attributes required t)3' T:r). If the secondary fragment of T.r a already exists in the PSP, all the u n ~ s i g n e d attributes required by T z 3 are assigned to any one, of T z a ' s

VE1TFI('AI, PARTITIONING IN DATABASE DESIGN NI.rI]('I

, ,,,,

'

.t

,]((i!l~.,:

',,, : ' . i ' , , ? . ' . 1

var(~l'! ',

:;,:!'il

I,L."

,;

.~,

'

-~" : I I ! . ! t ! ' , ~ ' ? ~ ' - .

:-., ,.i~,;,! .... ;r',',l !:', ,>,,!.,i

rr]airl

[)r()~U ,lrll

V,LI ~

' '!,"

27

r;e,.-:

• ,

[:,l(2!i"

II '

!"

,I

"~

a)[

,l~l:;!i':II:~

'

::,:',.,! ,::-'.

{)1'!i\ • G,q'll"

"}:'"

f'),){

2q I i ! : , , . Q . - l : ¢ ' r I

I:

:1 = '

*

If(),!''

.,l':l{!l,;[,

:''*li''ii'l

W h l ] ( ~: > ':~111.'51~111'(]

- ,,I.' 'lur, ; t ! ' [ l ' , ' l ' ~ , <

i? rll,. ":hl\,'Fq,i]

:!~!:t:i(,Tl:

{)!'ITN

,ll'l!i~lll!'

-; '~',

,~,:p;,ll,! I S ,:

('ml whih' ()lll.;)ll[,

"; !)ilIl'{{(l:;l'(i

:,",,llillll

'

end rrlain l)rog,ram l"iK 3.

Searching l)r,)cedure

secoI|Cl~tr)" fr~t~iilelit.s. ].'tie re~L~Oll is that. the ac(:e.~s cost of t h e s e c o l l d a r y fl'agment rises when t h e n u m b e r of s e c o n d a r y f r a g m e n t s incre~L~es. If T.rj does not have a n y s e c o n d a r y f r a g m e n t in t h e PSP. all t h e u n a s s i g n e d att r i b u t e s t h a t are required by T z j itre assigned to t h e p r i n m r y fragment. T h e reason is t h a t l,he *recess cost rises wh,,n an ~2Xtl'~t s e c o n d a r y f r a g m e n t is involved. T h e last s t e p is to d e t e r m i n e how t.o assign the a t t r i b u t e s t h a t are not required by 2'.r.~. Because t h e access cost of "l':r a rises when t h e irrelevant a t t r i b u t e s are accessed, all t h e a t t r i b u t e s not required by 2":rj ~tr(e tLssigned to a n y one of the f r a g m e n t s t h a t are not: required by ]'.rj.

Y.-F. HUANG AND C.-H. VAN

28 procedure expand ! S ' (}~()l;:q' Ihp !11o~ •"

;II.III[HI!I'

li)l)\

(I I

~]~;])()[ II¢~i[ ~[L]'][)[I~,( ~ {1 i ,~]~l(~Ilg SIt]I]~l.gsi~I]p(]__J].[[Iib||[c' iS il:¢?~i~]l(?¢] I() /I,l] 0111[)13." [T';t,O_,lll(Hlt ~,/

;I ]IL'W ~l(}(iI'%t~e',, h'O:H ~ ;

I('lliOV('

¢1i I'l(llll ~um,..llll6.'~Sigll('(I

( I'¢'il t.t'

.I

II("A

[ I ~l.~lll('ll!

/,,,....I)+ttliti():lc+l

;'lttl'ibu'.l'

.,/.'"1.~,]

;

cOl',Litirlitlt~

OIIIV

.:

~',1(]

;(Id

6"('¢I

illlO

t'r'li:tiot+ :

~ :l[l::,llv*l

t'Oq : "l~t..s 2.

i:.'.t'll ~ , , . .

Ill;¢1 !!ii' li:,~ 01'1'[.~

: " iltllii~lll( ' t: i i?~ &:;Sioll~'¢i I 0 0 , t ( ' ! ) I'l'il~lI:O!d itl H.l)iLI'~,il.'.ouod

r('[atiOl] "

for each !)a~meut ])¢tq) ill S.paltiLioned relationdo ('Opt,' ~i IlV~.~ llO(i('Hnew frolll .g; I(HIIO\V

¢I i

J.l(}lll ~l:vv..:lllilgFi~llf~fl__.ill,!|'iblll('[

add ,:, hllo .iye2ql iu .S,,(., .Partitiotwd _lehltioI:: I'
('O~il, t ~.e,"

I[

i::>('ll .St:v.,. h:lu Ih(' Ii>: OPI';*': e n d for ),.m()~.c S Itl))n /h(' ,~x,'"O I ' I ( N :

e n d proc(:dLlr(: ( ' X l : a n d F i g . 4.

Expanding

procedure,

After the OptinmI(Txj, PSP) is determined, the scan type of transaction Tzj is considered for choosing the cheapest cost. The number of disk I/O to access the primary fragment can be computed by using formulas (2.4), (2.5), and (2.6). The nmnber of disk I/O to access the secondary fragments is expressed in formula (2.7). By summing these results, the totM number of disk I/O required by Txj is obtained. For each transaction, the number of disk I/O is computed in the same way, and the Estimated_cost of a node is obtained. The detailed procedure of computing the Estimated_cost is shown in Figure 5. ~.3.

EXPERIMENTAL

RESULTS

In this subsection, two experiments are performed to show the superiority of the algorithm proposed in this paper.

V E R T I C A L P A R T I T I O N I N G IN D A T A B A S E D E S I G N llrli(:(!lltll('+

29

i i~ '+

+"~tirrLal (+¢i_ (1+-'

viLr ( ' N J ' : :,(" o l +t+it,t~t/It?rli~: .:" a ctJmil'+('ioly spocific2d ])3.rtit.iOTI * " S ('>:1':1/11.I'+!

tt)NI.

:= [1

[Ol-+',tt!; 11~LI'.5~LCI!OII

]r, do

:t+'q : = t l w fre(ltl+'+lc } (if ']J: : ('q['

: = ~>p';ilt,al

Ib+illIitt('+i

7~, . % :

i(+h:

:

?+; +,','++litli,l+tl'(i

', 1+I -.- +t('( l ~ ;teC'+"~'

¢'t>s:: ]'¢j ,( 'BI'):

e n d for end l)ro(:edllrt! ( ( H l i i ) l l [ ( ' .

+ (:,!

function

opt, i m a l

: 7 r . ~

v a t S[*

-~" ul t : ~ m ~ , r : ' . < :

%1' := % p a t t i : t u n e d + f!~:!c':tllitlc'

..

asl>('~i!ied:~a:tith)e~.

".'

i{,k'.liotl :

h~lw to iP,.,d'~II Iht, ~ca~l ],:(~', */

i f ~ ] w ~~l~: I
"l:r~+'>sec~mllan.~ f+'agzn~.':'

,i it +tRltl+'tll It,l!'.tn+'r:l.

then

irt t h ( ' S P

then

',t ).!( +i I :il)l+' h'::+:' h ', r}w ,qll;1+l]P,-:'.

+)ri+' alyrlolH.~

'/'./'j r.% q(,(,(l~+l(!ai+]. V

i,~ ('ho4i'e;

'h+' sc+t'.~ I<<'v ,+1 /~t+: Is +tbqio.nod t o tile (h()S('ll fNl~rliC!llt : else l"ig. 5,

4.3,1.

Estimated_cost

computation

Experiment I

In Experiment I, the same examples ,as Cornell are used to show the etIiciency of our algorithm. Although the results of Cases 1-4 are the same as Cornell's, tile execution time of both algorithms is different to some extent. This experiment is done on the machine Pyramid MISever 4-1; Cornell's experiment wa,s done on an IBM 3081. For comparison purposes, we list their computing capabilities as follows. The Pyramid MISever 4-1 has a 32 bit RISC processor, with a 100-ns machine cycle time. The performance

30

Y.-F. H U A N G A N D C.-H. VAN ., I:;tR:u(,:il w h i c h ¢ o n l a i n s a!] l h e ~lll. li})ll|A!S req,.:irod hv 7'r~ is < o n e r a t e d ,,,,,! u d d , ' d iw:!,~ ! h e SI' : cndif endif ..' ,b'!,','u!',,.l,,xx

'¢~,,->v.,:,;,li~ilm:~,s,,,,i~i(}l

a!(?t,'f;ui]~'d I;v T::, "..;

it"q,,,',.,~i ..',nunlnin, , !,'q,[l!,',l ilv /.lj il~:~'::(~: !J,'('t, ,'.s,i,_'uo(I t h e n if l h , q c i, ;, /'* '- -~u:n,da,!', i!,,,.:,,,t'm if, ~.l:c' >;J' Ih(:n ,;,:,:'.':l '.};~",~ a ' l : i h : ! ~ l ' v

Ii} ,111\ (;11(' II 5 7 2 : ' 5 ; >t*{OlJ,[;!:V !];t.~ll;.'l!{

;:-<;e,n l i : c N , ' ; , ' { I ! [ K ' . I ! ' >

I0

i[; I}i(' N! ) i

els(:

/t'j

" I ) ; i ! : ! ; t : 5 ' [t,te.rl!('lll

iri !1!1' q [ I :

cmli[ cndif '

I }.., II'III+llEI!.'I tl:++I'qlEII('~! i t l ! [ I l l l t ( `" }ll(' I l i o : ' l ' w h ! ( h ([(.II'I!'H!,p

,:-,'!'.'

}it~v,

'()

,l-:!!.,ll

'il('

i?lllil)lllr':~

wi:i(h

:hl'-~' ,!;;:iJlti:('x '(, , Iix {)!1 r' ()( (llkE!l!l'[i{': i!1

,t[(' ]lilt t + ' ( l t ; i : ( ' l l }+x ].rj " .'

aLl[' :iCJl I ]][~

51 ) .

l('(+llil(*(!

}J~," "['rj

L.

\Vili(]l itl(' I)(ll I(!(l'.lil('(]

.." >1' > ,L:, <)Dl!:l:,tl l)atl!;:(,!l +)I ]'l'3 +; return:

Sl' ,

e n d f u n c t i o n tl[Jl :.ll:,l', Fig. 5 . - - - C o n t i l m e d

of Pyramid MISever 4-t is about seven times that of V A X l l / 7 8 0 . T h e IBM 30Sl processor unit has two symmetrically organized central processors, each with a 26-ns machine cycle time, and executes IBM System/a70 instructions at approximately twice the rate of the IBM 3033 [al. CASE 1. The. following parameters describe the relation considered and the system environment: le.ngth of T I D = 4 bytes number of attributes = 20 length of attributes = ( 8 8 8 8 4 8 8 cardinality = i0,000 page size = 5000 bytes prcfetch blocking factor = 10 clustered key is 1

12 20 2 2 4 8 6 5

3 30 1 2 8 6 6 )

V E R ' F I C A L P A R T I T I O N I N G IN D A T A B A S E D E S I G N rtlrl(:ti()rl . ~ , , . - -

(~!.

-,'1 't '1',:''~

•

,',

-;),'(!! ~',', ':;t~

''!

(/J)

[ ' ~ ,]I

[:;tl'.

i" (ts~[

• ,;:;,','

:~a~!{tm ,>!

,"d'<
i)r(.dica'-

IF.('

j.:

;.;..I '.

:

,', r'~ ,p('

J,(

I:(-',,'p~!i

:!r"

,l'~r

I;il'

"h,'IIZT!

i,';'i~,:'.

] i,~'

./.

31

'

I-

,1'

,; '!'

'+i " ~ '

/,".~:,:im;,l'. ,:~."

,l!

.)! il' .n" [~:.*. '. 'h' :

'..t. >

!:'

'J'l'l'"

-

!:,l~mf'u~

; f!~'-'i:!!; ~l)]iIIl,l:',

,.~',i~uaii~'.

I1iL9-II(~:i1%

1!'~'

.,'-('1',

'~'lJl

ill (1<] '

-,~(~)Ti~Ji~:',"

"'(hL).'.! 4 ~''1 •

!

I:~P.'.::l(~:~l. < iLrl(]

Ti:!'

~I'I'(IH']

'

~',:

:

!

1~::::,;:'.,

~

<,,;d:m:l

:',

,

( ':..:,.:1',1

i::(!I"<

~ lu<1,'!,'f!

I l
'

:I,LL'

:;[) '

(',l:(]::.;Li: "~,

"

M'In'I"]',!T',

~i)lifliKl),

•

('I:~(' ,

(!ndif return; crid

fmlcLiort

MIN

' -c,!,:.:~':;~l.,tp.,l':

al (e-;:-

!,i,'d

I,i.x.,!':-t,'r.,!_/u,i('x

('(F:I

I"ig..").

( ',)nt mu(,d

T a b l e 4 shows the c]mracteristic,~ ()f the (Iifl'erent t r a n s a c t i o n t y p e s a('cessing t h e relation. Th(, a l g o r i t h m p a r t i t i o n s t h e relation into two fra~n|ellts as follows: F r a g m e n t 1: F r a g m e n t '2:

1, 3. 4, 5, 6, 7, 8. 10. 11, 17, la '2. 9. 12, 13, 14, 15. 16. 19. 2(I

The, (:<)st is 251, aim the t o t a l disk acces.ses are reduced by 11%. Tile, run t i m e of our a l g o r i t h m is a b o u t 1.1 s, whereas t h e run t i m e of C o r n e l l ' s alg()rithm is a b o u t 20 s {2). C A S E 2. T h e tbllowinx f)arameters descril)(, the r(,lation considered and t 11<' SVSt('III ('llVil'Olllll('ll|: lenNth of T I D .... 4 I)vl<.,s numb('r ()f a t t r i b u t ( ' s = 10

32

Y.-F. H U A N G A N D C.-H. V A N TABLE 4 Transaction Profile for Case 1

"l'zl Ta2 7":r3 "l'x4 Tar, Yz(i T.r7 Txs

Tx~ "l'x]o

TXll 7"x12 7'Xl:~

T:rl4 "l'xl5

l"requency

Attributes required

Scan key

Selectivity

1 1 1 1 i l 1 1 1 1 1 1 1 1 1

1. 4, 5, 6, 8 2, 9. 12, 13, 1-1 3, 7. 10, 11, 17. 18 15, 16, 19, 20 1, 5. 8 1, 8, 1[), II 3. 7, 10, 11. 17 2, 12, 13, 14, 15, 16, 18. 20 2, 5, 11, 14, 19 1, 9, t6, 18 1, 2, 3, 5, 6, 9, 12. 13 ,1, 7, 10, 1,1, 19, 20 8, 11, 15, 16, 17, 18 l, 2, 3, 4, 5, 6, 7, 8, 9 15, 16, 17, 18, 19, 20

,1 2 4 15 1 1 7 12 5 9 6 I0 11 S 16

0.0050 0.0050 0.0050 0.0050 0.0015 0.0015 00015 0.0015 0.0015 0.0010 0.0010 0.0010 0.0010 0.0005 0.0005

l e n g t h of a t t r i b u t e s = (10 8 4 6 15 14 3 5 9 12) c a r d i n a l i t y = 10,000 p a g e size -- 5000 b y t e s p r e f c t c h b l o c k i n g factor = 10 (:lustered key is 1 T a b l e 5 shows t h e c h a r a c t e r i s t i c s of t h e different t r a n s a c t i o n t y p e s a c c e s s i n g t h e r e l a t i o n . T h e a l g o r i t h m p a r t i t i o n s t h e r e l a t i o n i n t o two f r a g m e n t s as follows: F r a g m e n t 1: F r a g m e n t 2:

1, 2, 3, 4, 5, 6, 7 8, 9, 10

T h e cost is 74, a n d t h e t o t a l disk accesses are r e d u c e d by 20%. T h e r u n t i m e of o u r a l g o r i t h m is less t h a n 0.1 s, w h e r e a s t h e r u n t i m e of C o r n e l l ' s a l g o r i t h m is less t h a n 1 s [2]. For C ~ e 3, t h e t o t a l disk accesses are r e d u c e d by 19%. T h e r u n t i m e of o u r a l g o r i t h m is a b o u t 0.1 s, w h e r e a s t h e r u n t i m e of C o r n e l l ' s a l g o r i t h m is a b o u t 3 s. For C a s e 4, t h e t o t a l disk accesses are r e d u c e d by 51%. T h e r u n t i m e of o u r a l g o r i t h m is a b o u t 0.1 s, w h e r e a s t h e r u n t i m e of C o r n e l l ' s a l g o r i t h m is a b o u t 5 s. T h e d e t a i l e d r e s u l t s are o m i t t e d here.

~.3.2.

Experiment II

I n [2], C o r n e l l a n d Yu a s s u m e d t h a t n o m a t t e r how t h e r e l a t i o n was p a r t i t i o n e d , t h e q u e r y o p t i m i z e r still decides t h e s c a n t y p e of a t r a n s a c t i o n

33

V E R T I C A L P A R T I T I O N I N G IN DATABASE DESIGN TABLE 5 '['ransaction Profile tor Case 2 Attributes required

Frequency Txl Tz2 Tx3 T:r4

1 1 1 1 1 1 l 1

7'.c5

T.r6 "/'x7 7'x~

1, 2. 4, 2. 1, 1. 3. 3,

5. 3. 6, 7, 2, 5 9 4,

Scan key

Selectivity

1 2 .| 7 1 1 3 3

0.0025 0.0050 (1.0025 0.0035 0.0025 0.0025 [),0025 0.0015

7 8, 9 l0 8 3, 5. 7. 8, 9

6, 9, 10

b ~ e d on the original relation. This makes the solution tbund by Cornell's algorithm be nonoptimal. In Experiment II, we show the phenomenon. CASE 5. The following parameters describe tile relation considered and the system enviromnent: length of T I D -- 4 bytes number of attributes = 3 length of attributes (a, b. c) = (2, 2.10) cardinality = 10,000 page size = 1000 bytes prefetch blocking factor = 1 clustered key is c Table 6 shows the characteristics of the different transaction types accessing the relation. In [2], all transactions adopt index scan according to formulas (2.4)-(2.6). No partitioning is recommended in Cornell's algorithm. The result is {{a, b,c}} and the cost is 202; 2'x~ uses unclustered index scan and Tx2 uses clustered index scan. On the other hand, the solution found in our algorithm is {{a,b}, {c}} and the cost is 181; the scan type of T:rt is sequential and the scan type of ?/'x2 is clustered index scan. The cost is reduced by 10(~. TABLE 6 '~ansaction Profile for Case 5 Frequency

Attributes required

Scan key

Txl

2

a, b

u

"l'x2

1

b, c

c

Selectivity 0.01 0,01

34

Y.-F. HUANG AND C.-H. VAN

TA Bl,t,; 7 Statistical Results When Two Alternative Assigning Orders Are Used Arbitrary

B~.scd ()ll inlportallc(~ No. of

N(J. (d

generated

No. of

No. [~1

generated

it ( ! r a t i[)llS

] l(idc~,"

i( ~ ! r a t i()l|.b

l 1( ) dt~,'4

(;mse 1

~;37

2!127

28:1

!)79

c~use 2 Ca.s(: 3 ( !a.se 4

22 139 490

63 ,IS7

1.1 39 26

34 107 ~:l

1875

Finally, Table 7 shows the statistical results of Cases 1 4 in Experiment I. In this table, two alternative assigning orde.rs are used, and the time and space needed are shown. "No. of iterations" means the time required by the algorithm, where~Ls "No. of generated nodes" lll(,ltllS the space required by the algorithm. The table demonstrates how a go(M estimation influences the efficiency of the search procedure. 5.

C()NCLUSI()NS

By t)artit.ionmg a relation into fragments according to the requirements of the transactions, a transaction can avoid accessing useless data. Thus the l)erformance of relational database systems can be enhanced. We have described all algorithm tbr vertical partitioning that uses the A* Algorithm t:eclmique. The major feature of this algorithm is that the global optimal partition can be found quickly. Two refinement methods reduction of the search space and a good estimation are proposed to improve the t)erformance of the search procedure. Although the (:()st function used in this algorithm is the same as in [1] and [2], the solution found by the algorithm is better. REFERENCES 1.

2.

3.

13. W. Cornell and P. S. Yu, A vertical partitioning algorithm tor relational databases, m Proceedings of the Third International Couference on Data Engineering, 1987, pp. 30-35. D . W . Cornell and P. S. Yu, An efl'ective approach to vertical partitioning for physical design of relational databases, IEEE Trans. Software Eng. 16(2):248-258 (1990). K. | l w a n g and F. A. Briggs, Computer Archztecturc and Parallel Processing, MeGraw-llill, New York, 1984.

VERTICAL PARTH'IONING IN DATABASE DESIGN ,1.

5. (i.

7. S. 9.

:35

M. l l a m m e r and B. Niamir, A heuristic approach to a t t r i b u t e partitioning, m A CM S I ( ; M O D hdern..h.on..l (7oTtferelt.cc o1~ Man¢zgemcnt of Data, 1979: pp. 9:3-101. N. ,I. Nilsson. f'roblem-Yohm~ 9 Methods ~ .,lrtificied h~.t,'lligence. Mc('.rawHill, New York, 1971. P. (1. Selinger, M..',1. Astrahan. D. D. ('hamberlin. tl A. l,orie, and T. (;. Price, Access path selection in a relational databa.se m a n a g e m e n t sysl.em. in A C M S I G M O D hdcrn,tZo71al (:o71fer,.7,ce (m :'#amzgement of Data, 1979. pp. 23-34. S. ('eri and G. t>elag~tti, D~stribulcd l)at,bcrs~:.~" l'r~ltciple a71d ,S'?l,stcms. McC, raw-Hill, New York, 198.1. S. Navathe, S. Ceri. C,. Wii,derhold. lind .I. l)ou. Vertical partitioning algorith ms for databa.se design. A CM 7'raT~s. l)(dabase .91/.~t. 9(-1):681 710 (1!184). S. Navathe and M. tla, Vertical partitioning for databa.se design: A graphical algorithm, in A CM S I ( ; M O I ) lTdcrtmtioTml (h>Tlfl'ren~ c ¢)n MaTm:lemCTd of Data. 1!)89, pp..1,10 ,1.'50.

l¢.ccezvex.t 28 ,lune 199/~: re'vtscd i~7 0ctoh¢'r 199 d