Use of fragmentaion for tables
If you fragment a table, say by expression, I know that using PDQ will result in the use of parallel scan threads. If your application (it is a third party app) does NOT use PDQ, is it still "useful" (better performance) to fragment tables? Will parallel scans still occur, or will not using PDQ "negate" the usefulness (better performance) of fragmenting tables? We are on IDS 10 FC10 on AIX server.
If your applications are not DSS/DW apps but more OLTP you can still take advantage of fragment elimination if your queries include the column(s) used in the fragmentation expressions by setting PDQPRIORITY to 1 (one) which enables fragment elimination but no other parallelism and no fighting with other sessions for PDQ resources (total available PDQ resources is 100% so only two PDQPRIORITY 50 sessions can be actively running at a time - others have to wait). PDQPRIORITY 1 is a special value that does not attempt to acquire PDQ resources like MGM memory. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jun 6, 2012 at 7:57 PM, THOMAS HORNER <trhorner@bellsouth.net>wrote: > If you fragment a table, say by expression, I know that using PDQ will > result > in the use of parallel scan threads. If your application (it is a third > party > app) does NOT use PDQ, is it still "useful" (better performance) to > fragment > tables? Will parallel scans still occur, or will not using PDQ "negate" the > usefulness (better performance) of fragmenting tables? We are on IDS 10 > FC10 > on AIX server. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8ff1c8b41e8d3d04c1d6e8f1
OK, I don't know where my head was last night. My answer was all wrong in detail. The point to be able to use PDQPRIORITY 1 to improve simple queries against fragmented tables stands, but fragment eliination is always active even when PDQPRIORITY is 0. PDQPRIORITY 1 turns on parallel scans with minimal resources but no other optimizations. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jun 6, 2012 at 8:24 PM, Art Kagel <art.kagel@gmail.com> wrote: > If your applications are not DSS/DW apps but more OLTP you can still take > advantage of fragment elimination if your queries include the column(s) > used in the fragmentation expressions by setting PDQPRIORITY to 1 (one) > which enables fragment elimination but no other parallelism and no fighting > with other sessions for PDQ resources (total available PDQ resources is > 100% so only two PDQPRIORITY 50 sessions can be actively running at a time > - others have to wait). PDQPRIORITY 1 is a special value that does not > attempt to acquire PDQ resources like MGM memory. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Wed, Jun 6, 2012 at 7:57 PM, THOMAS HORNER <trhorner@bellsouth.net > >wrote: > > > If you fragment a table, say by expression, I know that using PDQ will > > result > > in the use of parallel scan threads. If your application (it is a third > > party > > app) does NOT use PDQ, is it still "useful" (better performance) to > > fragment > > tables? Will parallel scans still occur, or will not using PDQ "negate" > the > > usefulness (better performance) of fragmenting tables? We are on IDS 10 > > FC10 > > on AIX server. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --e89a8ff1c8b41e8d3d04c1d6e8f1 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba2122577597df04c1e03d31