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
