Here are some additional thoughts, sorry for the rambling on... Both techniques: copy the data out to a "linked table" TAB file, and Live Access have their place (I won't go into detail here).
Live access does modify the query by adding a spatial qualifier to intersect features with the display window extents (current mapper). If you zoom in within the existing extents it does not have to re-execute the query. If on the other hand you pan or zoom to an extent that is outside the previous extent the query is re-executed with a new window extent for the intersection. It is important that the FIRST table you open in a map window be a local TAB file or something very small, like a states table that only has 50 features. If you use live access for the first table opened it will select all features (the entire extent). It is best to open a small table and reset the current mapper window or induce the user to zoom in to area of interest prior to fetching live access tables. All queries are performed on the table, therefore the entire dataset are candidates. Spatial indexes are required. 1) Oracle has finally changed its tune and now recommends RTREE indexes as a general rule (it only took them about 5 years to figure that one out). The RTREE is simpler to understand and the newer algorithms used have improved performance by orders of magnitude from earlier versions. 2) If you are using Quad Tree indexes you might consider performing some tests using different values for the tessellation to help optimize the query. My "rule of thumb" is to use a tessellation value that is 4 times the "typical" window extent. This may be difficult as there may not be a "typical" window extent used in the application. The standard tessellation recommendation by Oracle may not yield the desired result. This all takes a bit, well actually a lot, of testing. The idea is to perform a feature MBR (min. bounding box)level intersection between the features and the "current mapper" window. Using the primary filter only (SDO_Filter) with query type of WINDOW. This will yield the fastest result set, although some features retrieved may be outside the "current mapper" extent. It may use the secondary filter (SDO_Relate) to obtain only the features intersecting the "current mapper" window, but this is a little more expensive. (There are notes about using querytype: window and join. Join is VERY expensive and does not provide good performance; Oracle recommends that if you need to re-write the query to use the window querytype.) NOTE: If you look at the query in expert mode you will see mapinfo's syntax for the extent intersection. This is different from the actual query being submitted. Peter mentioned turning OFF MBRSEARCH when using mapbasic, I agree. BUT... You must consider what output set (selection) you really want. Turning OFF MBRSEARCH returns All records that meet the attribute qualification, regardless of location. This may be EXACTLY what you want. Allowing the MBR search qualifies the query based upon the attribute AND the spatial extent of the current mapper. Much of the performance question has to do with the quantity of data being retrieved. For best results you should tune your display scale values. You don't want to "refresh" data that can't or shouldn't be seen. Or retrieve large quantities of any dataset. In other words "select * from parcel" when your "current mapper" scale extent covers hundreds of hectors may lead to poor performance. MapInfo looks at what tables need to be re-displayed based upon the layer control and zoom settings, so it is "intelligent" in that respect. Also, if doing this manually or without display extents set, make sure you are zoomed in to an appropriate extent before using live access. -----Original Message----- From: Peter Horsbøll Møller [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 01:53 PM To: 'Christof Kaiser'; [EMAIL PROTECTED] Subject: RE: MI-L MI "live" on Oracle Spatial:how is the querying done? One thing that I know for sure can be a killer is if you open your Live table into a new mapper and the bounds for the table in the MAPINFO_MAPCATALOG are wide, MapInfo tries - more or less - to get every single record from your database. If you are querying on attribute information you might want to turn of the MBRSEARCH (Can as far as I know only be done thru MapBasic, or by altering the TAB-file directly). If this has been turned of MapInfo doesn't use the spatial index when figuring out which records to return. This can boost the performance, espacially if the attribute condition only returns a few (hundred or thousand) records. Another thing that annoys me when using the live connection is "when does MapInfo actual refresh the data" ? When you download the data you can allways push the Refresh button, but is not the case when using live connection. Peter Horsbøll Møller GIS Developer Geographical Information & IT COWI A/S Rugårdsvej 55 DK-5000 Odense Denmark Tel +45 6313 5013 Direct +45 6313 5008 Mob +45 5156 1045 Fax +45 6313 5090 E-mail [EMAIL PROTECTED] http://www.cowi.dk > -----Original Message----- > From: Christof Kaiser [mailto:[EMAIL PROTECTED] > Sent: Monday, October 27, 2003 9:06 PM > To: [EMAIL PROTECTED] > Subject: MI-L MI "live" on Oracle Spatial:how is the querying done? > > > Hi, > > as we know, MapInfo (I am using 7.0) cann connect to an Oracle > (Spatial) Database (using 9 and 9.2). > You can either choose to get a copy of the data in a MapInfo relation > which is updated every now and then if you want to. > Or you get the data live from Oracle just when it is > displayed. The second, of course, is the cooler choise > (database guys hate > redunancy). > > I works ok for smaller datasets, but when in comes to mass data (a > couple of million lines), it stalls. > I suspect that MI is doing some silly query on the whole data > initially. > Maybe it wants to index the objects or something like that. > I havnt done a trace on the database yet, but i was wondering if > somebody already knows what MI > tries to do to the poor database and how I can prevent that. > > Actually, I just want it to do a decent spatial query for the > needed small > window. > That doesnt take so long (if i do it manually). > > I know that there was a problem if attributes are queried as > well. In this > case, MI get all the data and sorts > it out locally rather than shipping a eleborate sql query. > Not too clever in > terms of performance. > > tia > Christof > > > --------------------------------------------------------------------- > List hosting provided by Directions Magazine | > www.directionsmag.com | To unsubscribe, e-mail: > [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] > Message number: 8884 > > --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 8885 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 8914
