Using ALTER FRAGMENT

Posted in 1999

Jeff Screws — Mon, 29 Mar 1999 12:23:04 -0500 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BE7A08.CE4C50AC Content-Type: text/plain; charset="iso-8859-1" I am having a problem with full table scans happening on some pretty large fragmented tables, and it is degrading performance of the whole system. There are 9 large tables(~140million rows) that are fragmented in 70 dbspaces with the following expression fragment by expression ((fragcol >= 0 ) AND (fragcol <= 110201 )) in b_qtrly11 , ((fragcol >= 110202 ) AND (fragcol <= 110300 )) in b_qtrly12 , ((fragcol >= 110301 ) AND (fragcol <= 110310 )) in b_qtrly01 , ((fragcol >= 110311 ) AND (fragcol <= 110401 )) in b_qtrly02 , ((fragcol >= 110402 ) AND (fragcol <= 110502 )) in b_qtrly03 , ((fragcol >= 110503 ) AND (fragcol <= 110506 )) in b_qtrly04 , and so on......... What I am seeing is that the only time fragment elimination is happening is when a query has something like fragcol = 110000. If I modify the above table to look like the following, the frag elimination is done on queries with ranges. fragment by expression ((fragcol >= 0 ) AND (fragcol < 110201 )) in b_qtrly11 , ((fragcol >= 110201 ) AND (fragcol < 110300 )) in b_qtrly12 , ((fragcol >= 110300 ) AND (fragcol < 110310 )) in b_qtrly01 , ((fragcol >= 110310 ) AND (fragcol < 110401 )) in b_qtrly02 , ((fragcol >= 110401 ) AND (fragcol < 110502 )) in b_qtrly03 , ((fragcol >= 110502 ) AND (fragcol < 110506 )) in b_qtrly04 , and so on......... Now here is my real question. What are the risk associated with doing an ALTER FRAGMENT statement to correct this problem? I don't want to actuallymove any rows from one dbspace to another, but just change the fragmentation expression. I want to clean it up a bit, also. I think I will have to change the database from unbuffered logging to no logging to prevent a long transaction. Should this change be relatively quick, or will it have to scan the whole table before making the change? ALTER FRAGMENT ON TABLE table1 INIT FRAGMENT BY EXPRESSION fragcol <= 110201 and fragcol > 0 in b_qtrly11, fragcol <= 110300 and fragcol > 110201 in b_qtrly12, fragcol <= 110310 and fragcol > 110300 in b_qtrly01, fragcol <= 110401 and fragcol > 110310 in b_qtrly02, fragcol <= 110502 and fragcol > 110401 in b_qtrly03, fragcol <= 110506 and fragcol > 110502 in b_qtrly04, and so on....... Any thoughts or suggestions are most appreciated! Jeff Screws ------_=_NextPart_001_01BE7A08.CE4C50AC Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>Using ALTER FRAGMENT</TITLE> </HEAD> <BODY> I am having a problem with full table = scans happening on some pretty large fragmented tables, and it is = degrading performance of the whole system.  There are 9 large = tables(~140million rows) that are fragmented in 70 dbspaces with the = following expression fragment by expression     ((fragcol >=3D = 0 ) AND (fragcol <=3D 110201 )) in b_qtrly11 ,     ((fragcol >=3D = 110202 ) AND (fragcol <=3D 110300 )) in b_qtrly12 ,     ((fragcol >=3D = 110301 ) AND (fragcol <=3D 110310 )) in b_qtrly01 ,     ((fragcol >=3D = 110311 ) AND (fragcol <=3D 110401 )) in b_qtrly02 ,     ((fragcol >=3D = 110402 ) AND (fragcol <=3D 110502 )) in b_qtrly03 ,     ((fragcol >=3D = 110503 ) AND (fragcol <=3D 110506 )) in b_qtrly04 ,     and so = on......... What I am seeing is that the only time = fragment elimination is happening is when a query has something like = fragcol =3D 110000.  If I modify the above table to look like the = following, the frag elimination is done on queries with = ranges. fragment by expression     ((fragcol >=3D = 0 ) AND (fragcol < 110201 )) in b_qtrly11 ,     ((fragcol >=3D = 110201 ) AND (fragcol < 110300 )) in b_qtrly12 ,     ((fragcol >=3D = 110300 ) AND (fragcol < 110310 )) in b_qtrly01 ,     ((fragcol >=3D = 110310 ) AND (fragcol < 110401 )) in b_qtrly02 ,     ((fragcol >=3D = 110401 ) AND (fragcol < 110502 )) in b_qtrly03 ,     ((fragcol >=3D = 110502 ) AND (fragcol < 110506 )) in b_qtrly04 ,     and so = on......... Now here is my real question.  = What are the risk associated with doing an ALTER FRAGMENT statement to = correct this problem?  I don't want to actually move any rows from = one dbspace to another, but just change the fragmentation = expression.  I want to clean it up a bit, also.  I think I = will have to change the database from unbuffered logging to no logging = to prevent a long transaction.  Should this change be relatively = quick, or will it have to scan the whole table before making the = change? ALTER FRAGMENT ON TABLE table1    INIT FRAGMENT BY = EXPRESSION       = fragcol <=3D 110201 and fragcol > 0 in b_qtrly11,       = fragcol <=3D 110300 and fragcol > 110201 in b_qtrly12,       = fragcol <=3D 110310 and fragcol > 110300 in b_qtrly01,       = fragcol <=3D 110401 and fragcol > 110310 in b_qtrly02,       = fragcol <=3D 110502

Using ALTER FRAGMENT

Related threads

Your privacy choices