Re: DW query tuning - explain on

Hi Art, As I said, it is definitely a Poor Mans version, because you would have to determine how you wanted to build the representations on your own. You would have to build the bitmap of rowids, or some similar binary representation, using the datatype. It is by no means a true replacement, but with the binaryvar datatype you could build those underlying bitmap of rowids, or other direct row pointers, on your own, and then index them. Obviously it would be a tad ungainly, and you would have to know what the column you added was meant to address, but it is certainly doable. ----- Original Message ---- From: Art S. Kagel (Oninit LLC) <art@oninit.com> To: Mark Jamison <majp51@yahoo.com> Cc: bill.abler@gmail.com; informix-list@iiug.org Sent: Monday, February 11, 2008 10:35:33 AM Subject: Re: DW query tuning - explain on Mark Jamison wrote: Mark, Unless I'm misunderstanding how you intend the BDT to be used, I don't think that it substitutes for a bitmap index. I true bitmap index is an inversion index each of the nodes of which contain a bitmap of rowids (or other direct row pointers) of rows which contain that key value. The true value of a bitmap index is that if the DB engine can use multiple bitmap indexes on a single table, it can quickly determine rows that satisfy all criteria on those indexes by AND'ing the bitmaps together. Multiple criteria within a single key (ANDs and ORs can similarly be satisfied just be AND and OR operations on the bitmaps.. The resulting compound bitmap will have a bit on for each rowid that contains all of the required key values. How can adding some bit field to each row help accomplish that? What am I missing? XPS has true bitmap indexes and can indeed apply multiple index keys on a single table in the FROM clause, IDS cannot. Those abilities from XPS would be needed in IDS to make it a viable DW engine for very large databases. Currently, IDS gives respectable DW performance for smaller databases, but not VLBs. If IBM did add multi-index and bitmap indexes to IDS, then the only thing missing from making a complete Arrowhead engine would be distributed tables/databases and XPS's advanced distributed query management capabilities. Art S. Kagel > Actually, you can build a poor mans bitmap index quite easily > beginning in version 10.00.xC6 and above. > > Beginning wiht that version you have access to the Binary Datatype, > via the BDT blade. If you add a binary column to the table, and > process your values, you can then index it, and thus have an > equivalent Bitmap index. > > For some simple info describing the BDT blade and how it works, either > check the 11.10 documentation, or just read the post I did for it: > > http://www-128.ibm.com/developerworks/blogs/page/idsteam?tag=BDT > > > > > ----- Original Message ---- > From: Jack Parker <jack.parker4@verizon.net> > To: bill.abler@gmail.com; informix-list@iiug.org > Sent: Sunday, February 10, 2008 9:17:19 PM > Subject: RE: DW query tuning - explain on > > No, in dbaccessset PDQPRIORITY to 100, otherwise you are asking for 5% > (your session) of 5% (MAXPDQ) of 878MB = 2.2MB. > > Not sure what you mean by star transformation. AFAIK, there is no bitmap > index in 10 which is what is required to truly handle this query properly. > I suspect the reasons for this to be more political than technical. > > cheers > j. > > Sane ego te vocavi. Forsitan capedictum tuum desit. > > -----Original Message----- > From: informix-list-bounces@iiug.org > <mailto:informix-list-bounces@iiug.org> > [mailto:informix-list-bounces@iiug.org > <mailto:informix-list-bounces@iiug.org>]On Behalf Of > bill.abler@gmail.com <mailto:bill.abler@gmail.com> > Sent: Sunday, February 10, 2008 8:59 PM > To: informix-list@iiug.org <mailto:informix-list@iiug.org> > Subject: Re: DW query tuning - explain on > > > OK - I've tried the queries with PDQ set to 0 & 5 in dbaccess, run > times are almost exactly the same. One underlying question I have, > does this version, of IDS 10.00.FC3X6 (non-XPS) Informix support star > transformation? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org <mailto:Informix-list@iiug.org> > http://www.iiug.org/mailman/listinfo/informix-list > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org <mailto:Informix-list@iiug.org> > http://www.iiug.org/mailman/listinfo/informix-list > > ------------------------------------------------------------------------ > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > =========================================================================================== Please access the attached hyperlink for an important electronic communications dis