Re: To fragment or not to fragment - opinion

Jon C. Vemo (jvemo@cyberspace.com) wrote: : Chris - : Can you elaborate a bit. Your response has me very concerned, as I am = : about to implement fragmentation. I thought that with fragmentation, if = : a query could rule-out a fragment, it would not scan it. This a = : significant piece of functionality which, according to you, fails. My testing only involved placing the table and index in seperate dbspaces. I have not had the opportunity to explore it further. The query I used was a CURSOR SELECT with an open ended WHERE clause, ie WHERE COLA >= 'abc', and an ORDER BY COLA. COLA was indexed. According to my understanding of the optimiser, I believed that because of the existence of an index there would be no need for a temporary table and that at each FETCH the next row would be returned. What I saw in my sqexplain.out was the use of a temporary table for the sort. This was confirmed by watching the temporary table being created in /tmp. The "fragment" keyword in the sqexplain.out prompted me to retry the query with the table and index in the same dbspace. Subsequently the FETCH completed almost instantaneously. It appears that just because the index is removed from the same dbspace as the table that the optimiser considers it fragmented and uses a temporary table. (Note: the table is considered fragmented if the IN dbspace clause is used in the CREATE INDEX statement even if the dbspace name is the dbspace that the table was created into.) I had hoped it would be able to recognise there was only a single index fragment and, since the querys WHERE and ORDER BY clauses only used columns from the index, that the optimiser would not need a temporary table. At worst I would of thought it should only need a temporary index. I, also, would be interested in the full story on how the optimiser works with fragmented tables. : The need for temp tables if your index is in another dbspace, is again, = : another significant problem. : Have you been able to identify if this is query or query-type = : dependent?? If not, how could Informix possibly expect people to wait = : for 7.2 ?? : Can anyone from Informix shine any light on this???? : Jon Vemo : ---------- : From: Chris Wegener[SMTP:chrispw@mincom.oz.au] : Sent: Sunday, September 17, 1995 2:31 PM : To: informix-list@rmy.emory.edu : Subject: To fragment or not to fragment - opinion : Whatever you choose to do, be aware that when fragmenting the optimiser : will need to search all fragments (or all index fragments) when : processing your query. : In fact in INFORMIX-OnLine 7.10.UD1 even if your table is not : fragmented but it's index is in another dbspace, when you do an OPEN : on a cursor all selectable rows are retrieved into a temporary table. : This happens even if the index could be used to identify the selected : rows. : Refer problem no 40006 to be resolved in 7.20. : So, I respectfully suggest, if you decide to fragment, use SET EXPLAIN : to check that the optimiser is getting confused. : -- : Regards, : Chris : -------------------------------------------------------------------------= : ----- : Chris Wegener Mincom Pty Ltd, : Database Consultant Wyandra Street, | | | | = : | | : chrispw@tenmail.mincom.oz.au Teneriffe, Brisbane, | | | | = : | | : Phone: +61 7 3303 3333 X2288 Queensland, | | | | = : | | : Fax: +61 7 3303 3232 Australia 4005 |M |I |N |C = : |O |M : -------------------------------------------------------------------------= : ----- -- Regards, Chris ------------------------------------------------------------------------------ Chris Wegener Mincom Pty Ltd, Database Consultant Wyandra Street, | | | | | | chrispw@tenmail.mincom.oz.au Teneriffe, Brisbane, | | | | | | Phone: +61 7 3303 3333 X2288 Queensland, | | | | | | Fax: +61 7 3303 3232 Australia 4005 |M |I |N |C |O |M ------------------------------------------------------------------------------