Re: Detached indices in 7.3
In article <34E436E4.5168@bellsouth.net>, John Carlson <carlson1@bellsouth.net> writes >joanned@seanet.com wrote: >> >> W. H. SMITH wrote: >> > >> > Informix 7.23.UC4 >> > HPUX 10.01 >> > >> > Just got off the phone with my third-party (financial) software vendor. >> >> Could it be LAWSON? >> > >The one and only . . . <G> Good to see someone else out there of >similiar structure. What version are you on? We're upgrading to >7.0.4/7.0.6. > >> > The script that creates their table structure automatically detached all >> > indices created (within the same dbspace as the data). After >> > researching the "why's" with Informix (<?>), they came to the conclusion >> > that this would not be a maintenance problem or a performance issue. >> > Besides, they said, Informix 7.3 will not support attached indices. >> >> 7.3 Won't support attached indices? That seems a bit far-fetched. >> "Lets chop off our install base at the knees". >> > >I have a call into Informix to verify this claim. I'll be quite >surprised if it is true. This won't be the first time that Lawson has >gotten a bit 'carried away'. Some day, I'll tell you about the Informix >7.14 issue. > >> > >> > While I like the flexibility of detaching indices and fragmenting data, >> > forcing all indices to be detached could create an administrative >> > nightmare. Is this particular rumor true? >> >> I ran into this problem with Lawson. Since the indicies are detatched, >> separate extents are allocated for indexes. This ate up our disk >> space in a hurry. >> > >Tell me about it. Only problem is that Lawson doesn't think that it >would be a maintenance or a performance issue, based on their contact >with Informix. Unless Informix has completely rewritten everything in >7.3, my guess is that the resultant increase in number of tablespace >extents will cause a performance issue anyway, not to mention a >maintenance headache. > >> After digging around in Lawson, we finally saw that it was >> using the "in dbspace" clause by default when it built indexes >> regardless of whether you used their configuration tables to "truely >> request" a detached index. I think they need to change their >> dictionary tool. I ended up dropping all indexes, and recreating them >> without the "in dbspace clause". >> > >I saw that right after we installed it on our test system. Yes, they >need to change the dictionary tool, but they don't see the need to do >so; that's one reason why I do all of my maintenance in Informix. It's Nope! If the index and data are in separate dbspaces they can be read in parallel. Informix does read-ahead remember? With PDQPRIORITY >1 Informix generate 1 scan thread per fragment i.e. part of table or index in a dbspace. Hence 1 scan thread for index and 1 scan thread for data since they are in seperate 'fragments'. even when it is in the same dbspace, I imagine that Informix would generate a second scan thread for the index...??? >really easy to get into the vendor-pointing contest; only problem is >that I tend to side with Informix; their tech support group is quite >sharp. Besides, we have in-house 4GL also, with our own major >subsystem, so I can't always agree with Lawson's spin on Informix. >Lawson's problem is that they don't require an Informix DBA to maintain >their systems. That may be good, but it pays to have an experienced or >trained individual to look over their shoulders once in a while. > >While you're at it, check out the clustered indices. My "rebuild" >script also strips those out as well. Oh great! Well that decreases performance as well. The cluster index would order data pages such that the most common queries access contingous pages and hence do not need to do any many disk seeks. Put those cluster commands back in again and rebuild all your indexes. > > > >John Carlson >Informix DBA >WH Smith, Inc. -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care