So to bring things back on track; Christian how are you doing with the idea being put forth - is it actually going to cause trouble for DB2? Or is it something that DB2 needs to handle differently.
Jody On 23/06/2010, at 2:26 PM, [email protected] wrote: > Some clarifications. DB2 makes also makes an access plan, but for one > statement, you have different access plans for different distribution > statistics. A simple example having a table with some indexes. If the > table is populated with 50 rows, no index will be used, if it is > populated with 10000 rows, the optimizer will try to use indexes. > After large modifications for a table, you should update the statics > (There is command called "runstats"). Afterwards, you may have > different access plans for your statements. In the meantime, there is > a feature called "auto runstats", so you don't need to trigger the > command manually. And of course, there are statements performing > better as NON PS. > > Spatial indexes are special in DB2. You create up to three grid sizes > for spatial indexing. There are no statics for this type of index. > Since I have the possibility to store geometry "neighbors" clustered, > I can avoid many jumps from one physical block to another. This setup > is tricky, but it performs well. > > Another reason is that I am always using pregeneralized features, so I > can keep access time and data at a low level. > > According your BBOX example I have no ideas about DB2 performance, > since I always use a more complicated/tuned db setup. > > The link is cool :-) > http://stackoverflow.com/questions/366202/regex-for-splitting-a-string-using-space-when-not-surrounded-by-single-or-double > > > > > > > > Quoting Andrea Aime <[email protected]>: > >> [email protected] ha scritto: >>> Puuh, a long mail, I will try to keep it short. >>> >>> 1) Each db engine has its own optimizer, DB2 as an example has a >>> cost based optimizer (not rule based) using indices based on >>> distribution statistics. Creating an and index for a char(1) field >>> having values "Y" and "N" will have no sense, DB2 will never use >>> this index. If the statistics is up to date, prepared statements >>> outperform non ps statements clearly in most situations. But I >>> think PostGIS and Oracle are mainstream, it is ok to focus on these >>> systems. >> >> So you're saying that the DB2 optimizer does not make a plan for a >> prepared statement, but actually recomputes it every time it receives >> the actual values? Because it's the only way it can recognize the >> actual bbox filter is so large that using the index is counter >> productive. If so, that's quite cool! >> >>> 2) A superb solution is to parametrize the use of a VT, either to >>> use the select statement as a prepared statement or replace the >>> parameters markers with literals. I did this for a DB2 >>> implementation some times ago , but for all our supported db >>> engines this would be a heavy job. I only mention this as an idea. >> >> Eh yeah, indeed a heavy one. >> >>> 3) Using named parameters raises some problems. (For ps or non ps >>> ). At the end of the day, you will need to parse the sql >>> statement, because there could be a parameter marker within a >>> string constant. During the Oracle Georaster stuff, I need to >>> create queries which where composed of a lot of statements >>> delimited by ";" (more like a procedure). A simple search and >>> replace will work in 99.9 %, but not in 100%. >> >> I trust the user not to do too fancy things there. In GeoServer I will >> also design the GUI so that a regexp based matcher will fill the >> parameters table by pure inspection of the query, so if there is >> some odd situation the user should recognize it by the code not >> guessing properly the param names. >> Ok, this is a bit of a long shot, but just today I found out a very cool >> usage of regular expressions >> that allowed me to tokenize a string so that the quoted terms >> were recognized as a single block despite having spaces inside, see: >> http://stackoverflow.com/questions/366202/regex-for-splitting-a-string-using-space-when-not-surrounded-by-single-or-double >>> To bring it to an end, if we offer regexp patterns for the user and >>> warn them about sql injection attacks , I am +0 here. (I cant >>> give a +1 here, my stomach has not the best feeling, I fear most >>> of the users wont even know what sql injection is, but I may be >>> wrong here). >> >> No, I fear you're right, most people do not know what sql inject is >> at all. So we have to warn people with a long red angry explanation >> that either: >> - they are working in a walled garden where no one will perform >> attacks (does such heaven exist?) or >> - if they don't think out fully the protection regexp, well, they >> can kiss goodbye their data (if they are so smart to use a >> account that can also write) or at least their privacy >> (though desinign an attack that will return extra data in >> Geotools is very hard, we just return the data we know is >> in the feature type) >> >> Cheers >> Andrea >> >> >> >> -- >> Andrea Aime >> OpenGeo - http://opengeo.org >> Expert service straight from the developers. > > > > ---------------------------------------------------------------- > This message was sent using IMP, the Internet Messaging Program. > > > > ------------------------------------------------------------------------------ > ThinkGeek and WIRED's GeekDad team up for the Ultimate > GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the > lucky parental unit. See the prize list and enter to win: > http://p.sf.net/sfu/thinkgeek-promo > _______________________________________________ > Geotools-devel mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/geotools-devel ------------------------------------------------------------------------------ ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo _______________________________________________ Geotools-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geotools-devel
