Using Oracle Views in Mapinfo 9.5

[huw...@googlemail.com]

Hi all - not sure if anyone can help me out with this sticky mess!

It's taken me a while but I've managed to create Oracle views mappable
in MapInfo 9.5, but the performance is dreadful, in that it takes 20
minutes to open about 5 records (fairly complex polygons) through the
view (compared to <1 second if the data is stored in an Oracle
table). I've been through the following steps:

1. Create the view, making sure that MI_PRINX is defined as the
primary key

CREATE OR REPLACE VIEW view_name (
column1, column2, column3, column4, column5,
CONSTRAINT name_of_pk PRIMARY KEY
(name_of_pk_column) DISABLE
NOVALIDATE
)
AS
SELECT column1, column2, column3, column4, column5
FROM table
WHERE column = value

2. Added an entry in sde_geom_metadata

INSERT INTO USER_SDO_GEOM_METADATA
VALUES ('table_name','spatial_column',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', 0, 750000, 0.001),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 1350000, 0.001)),
81989
);

3. Make the table mappable
Table - Maintenence - make DBMS table mappable Set up the symbols etc
here.

4. Open the table through the Oracle Spatial connection - it has to be
a linked .tab - as it's a big dataset

5. Wait while MapInfo hangs up for 20 minutes!!

It's almost like there's a problem with the spatial index - but you
can't have a spatial index on the view (presumably it uses the index
from the underlying table) - so not sure what's going on! Any
ideas?? Is this par for the course for an Oracle view??

[Peter Horsbøll Møller]

That is not as it should be would be my first comment.

Normally accessing the data thru a view would be (almost) as fast as accessing the data from the original table.

 

One thing a notices was that you wrote this: "Create the view, making sure that MI_PRINX is defined as the primary key" How do you make sure that MI_PRINX is defined as the primary key?

 

It should be enough just to select the primary index column from the base table and give this column the alias MI_PRINX. I would normally do this simply by adding the primary index column as the last column and adding a alias to this column. So in MapInfo my primary index would exist twice, once with the original name and once with the alias MI_PRINX.

 

So maybe this part of you view definition is the problem:

CONSTRAINT name_of_pk PRIMARY KEY
(name_of_pk_column) DISABLE
NOVALIDATE

 

Not sure what that actually does!?

 

Peter Horsbøll Møller

[huw...@googlemail.com]

Thanks for the post Peter - From an earlier post on the list, I
thought that the MI_PRINX had to be a primary key - but I've just
recreated my view and it still works without the pk - so I've taken
the view definition that you suggested might be causing the problem -
out.

However........ it's still taking 20 minutes to load even a small
piece of data!!

I wondered whether it might be something to do with the table
coordinate definition/ bounds - the table that it's using uses a OS UK
projection, and the bounds are defined for the whole of the UK. It
seems like it's scanning the whole base table (which is huge) with
every SELECT that MapInfo does. Just to give some background on the
data - it's Ordnance Survey MasterMap which is large scale data
designed to be viewed at 1:1250. It contains polygons for each
discrete topographic feature - buildings, roads, vegetation, water
etc. I'm trying to full out a single feature class (roads) in the
view.

Any other ideas??

[Thomas Rodger]

One thing might be to check that you have created an index in Oracle on
the column that you are querying to create your view.

Thomas

Hackney Council may exercise its right to intercept any communication
with any employee or agent of the Council using its telephony or data
networks. By using these networks you give your consent to Hackney
Council monitoring and recording your communication.

If you have received this e-mail in error please delete it immediately
and contact the sender.

For further information about Hackney Council policies please contact
Hackney Service Centre on: 020 8356 3000


**********************************************************************
London Borough of Hackney may exercise its right to intercept any communication on its networks - for more information see
http://www.hackney.gov.uk/email_disclaimer.html
**********************************************************************

[Eric_Bl...@mapinfo.com]

I am not sure what key I hit, but I sent the previous message while editing the last sentence.

In an earlier post, it was stated that the table was "linked" but perhaps that is not the case. Look in the .tab file created for this view table. If it says "Type LINKED" then it is. If it says "Type ODBC" then it is not.  You can also see the table types inside Professional by using Table, List Open Tables and check the "Group by type" checkbox but given how long it takes to open this, just looking at the .tab might be easier.

If it is ODBC, try changing the metadata in the .TAB file that was created for this connection to Oracle, try changing the metadata key that says:
"\MBRSEARCH" = "ON"

to say OFF.

By default, the queries we use to a spatial server are.. spatial. That can mean, when your query is of the form that limits the number of features through non-spatial means, that the server is receiving a spatial query (of the entire bounds) and the part that is the view.

Conceptually (no specific syntax here), the worst case example is "Select * from sometable where BoundsIntersects(large rectangle) AND where uniqueID = 4.

It is clearly much faster to just look for where uniqueID=4.

By changing  the MBRSEARCH to be OFF, Professional should only send the user supplied part to the server, which in this case should be to just execute the view.

If this is truly a linked table, then something else if going on. The linked query will not add a spatial query on its own. If you supply one, we will execute it. It should execute the query once, download the records and then access everything locally.


Eric Blasenheim
Chief Product Architect
Pitney Bowes Business Insight [MapInfo]



Mail List:grbounce-yvy1equaaaajbprysysrydkk7vpghp_9=mail_list=mapin...@googlegroups.com

From:	"Thomas Rodger" <Thomas...@Hackney.gov.uk> on 11/11/2008 09:47 AM GMT
To:	<mapi...@googlegroups.com>
cc:
Subject:	[MI-L] Re: Using Oracle Views in Mapinfo 9.5

[huw...@googlemail.com]

Thanks all for the responses!

Both were really useful points I hadn't created an index on query
column for the view - that sped up the row count in the view massively
(8 secs to 0.5) in SQLPlus.
Thomas' point about the linked tables was also true - my connection is
"Type ODBC" - and I was able to change MBRSEARCH to be OFF.

All of which resulted in no difference in MapInfo - still a 20 minute
hang. And then it came to me - the old adage 'if your not sure - it's
probably permissions related' (well that's my adage anyway). The
piece of information that I didn't put in my post because I didn't
think it was important was that I had created my view using an admin
and once I'd assigned permissions, I was viewing it through a read-
only user. Turns out that it's really important because when I opened
the view in MapInfo using my admin user - it worked great! I think
that it's something to do with the spatial index not being picked up
by any other users than the creator?!

So my question now is: It's obviously not acceptable to have all my
read-only users connecting through the admin user, so how to tell
Oracle to use the spatial index no matter which user is accessing the
view! I've tried 'hinting' using the following when creating the view
but it doesn't work - this must surely be a common problem?!

CREATE OR REPLACE FORCE VIEW
"OSMM"."TOPO_ROADS_V3" ("DESCRIPTIVEGROUP", "DESCRIPTIVETERM",
"FEATURECODE", "FID", "MAKE", "MI_PRINX", "MI_STYLE", "POLYGON",
"THEME") AS
SELECT /*+ TOPOAREA_IDX */ DESCRIPTIVEGROUP, DESCRIPTIVETERM,
FEATURECODE, FID, MAKE, MI_PRINX, MI_STYLE, POLYGON, THEME
FROM TOPOGRAPHICAREA
WHERE FEATURECODE = 10172;

[Matthew Hodgskiss]

Hi Hywel,

I've attached some code which should enable you to do what you want

Regards

Matt

Matt Hodgskiss BSc Msc
GIS Officer
Survey, Cartography & GIS
Engineering Consultancy Services
103 Wellington Road South
Stockport
SK1 3TT
Tel:0161 474 4913
Web: http://www.stockport.gov.uk/ecs
Email:matthew....@stockport.gov.uk

On Nov 11, 3:38 pm, Hywel - Monmouthshire <St.ill...@googlemail.com>
wrote:
> Dear All
> I am working my way through a layer of Polygons, each with a large
> amount of textual data in the Browser view of the table.
> When I'm reviewing them, some polygons just need a little adjustment
> which I am dealing with fine.
> But when I decide that the existing poylgon is very wrong, I often
> want to copy a toid from the base mapping (OS Mastermap).
>
> I want this polygon to replace(overwrite) the polygon In my original
> table, while retaining the textual data, but I cant find out how to do
> this.
>
> I'm sure there must be some way of doing this easily ??
>
> Thanks is advance for your thoughts
>
> Hywel Clatworthy
>
> GIS Officer
> Monmouthshire County Council
> County Hall
> Cwmbran
> NP44 2XH
>
> Phone 01633 644271
> Fax 01633 644409
> Email hywelclatwor...@monmouthshire.gov.uk

**********************************************************************
This email, and any files transmitted with it, is confidential and
intended solely for the use of the individual or entity to whom they
are addressed. As a public body, the Council may be required to disclose this email, or any response to it, under the Freedom of Information Act 2000, unless the information in it is covered by one of the exemptions in the Act.

If you receive this email in error please notify Stockport ICT, Business Services via email...@stockport.gov.uk and then permanently remove it from your system.

Thank you.

http://www.stockport.gov.uk
**********************************************************************

[Peter Horsbøll Møller]

If you are using MapInfo Professional 9.5, the MapCAD tool has a specific tool that can help you out as well.

With MapCAD you can copy a selected object to a stamp, and either insert this as a new record or replace the object of an existing record.

 

Peter Horsbøll Møller

[huw...@googlemail.com]

Just a follow up from my Oracle View problems - just in case anyone is
having the same issues as me. The issue was with an lack of Oracle
permissions for the READONLY user/ role. There is a 'MERGE ANY VIEW'
privilege that has to be set by the DBA, I couldn't grant it using my
admin user. I'm not a DBA - I have no idea what this does, but it
fixed the problem by some how allowing the READONLY user access to the
spatial index on the base table. I am now getting reasonable
performance on the views in MapInfo.

One thing that is a problem though is how long it takes to load each
layer - it takes about 20 seconds to load the .tab files that
reference the view. OK this is much better than 20 minutes..... but
18-19 seconds longer than it takes to load the base table. Once the
view is loaded, panning and zooming is only marginally slower than the
base table (as I would expect) - anyone got any ideas why this might
be?

The another thing to note was that I tried setting MBRSEARCH to be OFF
as suggested by Eric - but this drastically increased the time it took
the view to load first time around. Strange - if anyone else has any
other tips for optimising views either in the database or through MI,
I'd be interested!

Huw