[fdo-users] big oracle describe schema performance boost
Zac Spitzer
zac.spitzer at gmail.com
Tue Aug 26 05:04:48 EDT 2008
On Tue, Aug 26, 2008 at 6:31 PM, Haris Kurtagic <haris at sl-king.com> wrote:
> 1. Provider could test for multiple geometries in table and use Oracle
> table name for FDO class name but there would be still case with another
> Oracle table with same name but in different Oracle Schemas. Afaian That
> case can be tested again or provider could be changed so he will use
> Oracle schema names for FDO schema names. But all that doesn't sound
> very appealing to me. Truth is that ~ sign in class name and fact that
> provider will not return same FDO class name for new class created by
> apply schema are also not good. Right now solution to this is using
> extra metadata table ( KingFdoClass ). Perhaps some other way...
This problem "Oracle table with same name but in different Oracle Schemas"
is only a result of not mapping the oracle schemas into individual FDO
schema's....
GetSchema returns a stringcollection for a reason, not a single string
By not using the oracle ones and just having "kingora" the multiple
table naming problem exists.
> 2. When FDO class from Oracle tables are created King.Oracle provider
> keeps "schema overrides" so he knows which FDO class maps to which
> Oracle table ( no need to parse from fdo class name ). If in connection
> is used KingFdoClass table than FDO class name can be set by user and it
> can be whatever. Provider is also keeping geometry column overrides in
> case when geometry column is created from table number fields in that
> case also name of geometry column doesn't match Oracle column.
>
> 3. I am not sure if I understand your point here, but Oracle schema and
> Fdo schema doesn't need to be related in any way. I haven't looked in
> exact function " selectFeatures" but many MG functions will expect class
> name as just FDO class name or fully qualified FDO class name which
> includes FDO schema name. But in any case FDO schema name is not related
> to Oracle schema. If you use only FDO class name without FDO schema name
> it will work if you have only one class with that name in FDO schemas.
> But mapping FDO class to Oracle schema.table name is separate process
> and in case of King.Oracle provider is done as I was trying to describe
> in point 2.
They don't need to be related, but there should be a good reason for
breaking the
commonly understood existing model... and if it's that good, it
shouldn't cause other problems
like the describe schema issue performance and the duplicate table name problem
If I have a DBA login on a big spatial database with lots of schemas
and tables,
All of those tables have to be slowly Described.
Thru the lack of real schemas, I can't log in with a DBA login and
access just my own
tables quickly because the provider will access the entire spatial
database metatdata
via the _all views.
Zac
>
> Haris
>
>
>
> -----Original Message-----
> From: fdo-users-bounces at lists.osgeo.org
> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
> Sent: Tuesday, August 26, 2008 3:59 AM
> To: FDO Users Mail List
> Subject: Re: [fdo-users] big oracle describe schema performance boost
>
> On Tue, Aug 26, 2008 at 3:56 AM, Haris Kurtagic <haris at sl-king.com>
> wrote:
>> 1. I don't quite understand your question. In Oracle, table can have
>> many indexed geometry columns. FDO class can have many geometry
>> properties but for each FDO class one geometry property is set as
>> primary. Single geometry per table is most used case but having more
>> than one is not so rare and provider has to support those cases too.
>
> ok, I understand now.
>
> Couldn't the provider support using just table name except when there
> are two
> or more geometries per table? when there are multiple geometry columns
> and the user attempts to access by it purely by a table only classname
> it will throw an error
> "Multiple geometry columns found in table, please include specify
> primary geom column in className"
>
> getClasses would then return, tablename for single and tablename ~geom
> for multiple
>
>> 2. In link you send me , I think it was suggested that database
>> schema/table name is parsed from FDO class name. That is not case.
>> Provider maps FDO class name to table name by keep schema overrides.
>> That allows users to set any FDO class name with KingFdoClass table.
>
> but when there's are no schema overrides how does the provider map the
> class name to schema/table/geom?
>
> sorry if I seem to be keep asking the same question, but it just
> doesn't make sense to me at the moment,
>
> In a schema override free connection, your saying that the classname
> doesn't (always)
> match to "KingOra:schema~table~geomcolumn" but it sure looks that way
>
>> 3. Provider exposes in schema KingOra by default but also schemas set
> by
>> user using KingFdoClass table. I was thinking of using Oracle schema
>> names for FDO schema names but I decided with approach that by default
>> all FDO classes will go into on KingOra FDO schema. I don't see how
> that
>> influence portability. If there is class Kingora.Region or Ora1.Region
>> when it is copied to SHP it becomes SHP.Region.
>
> My point there was that because MGfeatureReader selectFeatures has no
> schemaName
> as a parameter, the provider cannot use the zac:region~geom as the
> schemaName isn't
> passed as a parameter, it will always be passed a 'region~geom' hence
> schema's need to
> be in the class names aka 'zac~region~geom'
>
> I think FDO is showing it's file based origins...
>
> Take the example of moving data from king sql server to king oracle,
> at the moment
> the same dataset under same user name will have a different
> featureName/class despite
> using FDO which is meant to *abstract* the differences between
> datasources
>
> Ignoring single schema providers like SHP just for the moment,
> shouldn't FDO allow the same
> dataset to reside in sqlserver, Oracle, PostGIS, Mysql and SDF and be
> accessed using
> the exactly the same feature / class names.
>
> In mapguide studio when you change the feature source for a layer,
> unless the classes & schema names
> are identical you lose all your layer styling, you can edit the xml but
> that's
>
> the performance issue that I thread started with relates to that as
> well. By using kingora
> you compress an entire database into a single schema, which can be
> huge depending
> on the users access.
>
>> I agree with you, that it is good to be able to easily move data
> around.
>> Also it is not good in King.Oracle provider that when you create new
>> class it will not have same name when you read it back if you are not
>> using additional metadata tables in Oracle.
>
> Yeah it doesn't sound good, can you provide an example?
>
> If i create a class called zac~region~geom with out any metadata
> it will always live in zac.region with a geom col right?
>
>> But truth is also that there are cases when you don't want to have
>> additional metadata tables.
>>
>> Right now I am working on FDO KML provider and I have similar problems
>> how to set FDO schema.class name. Additional problem is when client
>> application will create FDO class for new KML file (apply schema).
>> For KML provider I decided to go with configuration file in which you
>> will describe what will be name of class and other parameters.
>
> z
>
>>
>> Haris
>>
>> -----Original Message-----
>> From: fdo-users-bounces at lists.osgeo.org
>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
>> Sent: Monday, August 25, 2008 3:58 PM
>> To: FDO Users Mail List
>> Subject: Re: [fdo-users] big oracle describe schema performance boost
>>
>> On Mon, Aug 25, 2008 at 11:26 PM, Haris Kurtagic <haris at sl-king.com>
>> wrote:
>>> Zac,
>>>
>>> I have quickly read that discussion, I haven't see it before.
>>>
>>> I will try to explain myself with example:
>>> In case there are two oracle schemas "Ora1" and "Ora2" and two
>> geometry
>>> tables "Region" (one in each oracle schema) and each table has two
>>> geometry columns "Geom1" and "Geom2".
>>> So in Oracle we have 4 geometries which are accessed as:
>>> "Ora1.Region.Geom1", "Ora1.Region.Geom2", "Ora2.Region.Geom1",
>>> "Ora2.Region.Geom2"
>>>
>>> So provider needs to create 4 different FDO classes and obviously FDO
>>> class name can't be just oracle table name "Region".
>>
>> isn't FDO primarily a table approach, in studio there is always a drop
>> down to
>> select which geometry column to use.. why does a table make it two
>> logical feature
>> classes when they are just one?
>>
>> Is their an assumed primary geometry on a featureclass ?
>>
>> a single indexed geometry per table is by far the most common use out
>> there
>>
>>> Provider keeps physical mapping to real oracle schema.table ( it is
>> not
>>> parsed from name ).
>>
>> i don't understand, not parsing implies not using the class name?
>>
>>>
>>> Oracle username in connection doesn't mean that we access only that
>>> Oracle schema. One user can have access to several Oracle schemas.
>>
>> But king oracle only ever exposes everything as single schema, that
>> being kingOra,
>> which makes portability difficult between datasources.
>>
>> shouldn't getschema's return a list of all the schemas available to
>> the current user in oracle?
>>
>> Looking at the Mapguide API i can see why it's implemented the way it
> is
>>
>> virtual MgFeatureReader SelectFeatures(MgResourceIdentifier resource,
>> string className, MgFeatureQueryOptions options);
>>
>> shouldn't there be a schemaName in there, that's why you used the full
>> approach ?
>>
>>> As mentioned in discussion it can be problem to keep same
>> schema.class
>>> name across provider.
>>> For example with SHP provider schema is always SHP so it is problem
>> when
>>> creating application to work over all providers with preset
>> schema.class
>>> names.
>>
>> but then the shape provider could just ignore schema altogether
>>
>> I have lots of sdf's which have kingora: schemas, it's a good way to
>> be able simply swap
>> between oracle and sdf.
>>
>>>
>>> With King.Oracle provider only way to get same FDO class name as you
>>> used in create class is to have extra table (KingFdoClass). I think
>> that
>>> would solve Bruno problem with class names for King.Oracle.
>>>
>>> Haris
>>>
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: fdo-users-bounces at lists.osgeo.org
>>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
>>> Sent: Monday, August 25, 2008 2:06 PM
>>> To: FDO Users Mail List
>>> Subject: Re: [fdo-users] big oracle describe schema performance boost
>>>
>>> On Mon, Aug 25, 2008 at 9:34 PM, Haris Kurtagic <haris at sl-king.com>
>>> wrote:
>>>>
>>>> I think that automatically creating FDO Schema and Class name from
>>>> Oracle schema/table/column is not straight forward .
>>>
>>> I can't see why at least the schema part is not done fdo style
>>>
>>> ZAC:REGION~GEOMETRY
>>>
>>> but why is the geometry column needed in the first place?
>>>
>>> this was previously discussed here
>>>
>>
> http://www.nabble.com/Is-there-a-Feature-class-naming-convention-td15021
>>> 175.html
>>> http://trac.osgeo.org/fdo/ticket/374
>>>
>>> z
>>>
>>>
>>>> For example in case you mentioned in Oracle table Region there could
>>> be
>>>> more than one geometry column.
>>>> Also there could be different Region tables in different Oracle
>>> Schemas.
>>>>
>>>> King.Oracle added one table in which you can override names FDO
>>>> Schema.Class names created by provider.
>>>> One of things I was considering to add is to allow that this naming
>>>> rules can be written in local xml configuration file. One of
>>> advantages
>>>> would be that it is not necessary to add table to Oracle.
>>>>
>>>> I think that bigger problem than that query itself is that for every
>>>> geometry table provider runs 2 more SQL statements.
>>>> In that case big improvements for MapGuide will be when MapGuide
> will
>>>> request FDO schema only for classes he needs to know about.
>>>>
>>>> I will check sequence you mentioned and thank you for your analyses.
>>>>
>>>> Haris
>>>>
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: fdo-users-bounces at lists.osgeo.org
>>>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
>>>> Sent: Monday, August 25, 2008 11:16 AM
>>>> To: FDO Users Mail List
>>>> Subject: Re: [fdo-users] big oracle describe schema performance
> boost
>>>>
>>>> oops, i forgot about them being system tables.. sorry
>>>>
>>>> in regards to having tables/views in different schema, isn't that
>> what
>>>> fdo schema's aren't for?
>>>>
>>>> ie
>>>> ZAC:REGION
>>>> rather than
>>>> KingOra:ZAC~REGION~GEOMETRY
>>>>
>>>> if that was the case then, if the fdo schema is the current user,
> use
>>>> the faster USER_ approach,
>>>> otherwise fall back on the slower ALL_ views for when the user
>>>> explicity asks for something
>>>> which isn't in the default schema.
>>>>
>>>> The performance gets worse and worse depending on the number of
>>>> objects in the oracle database
>>>>
>>>> The following is a solution for the sequence problem
>>>>
>>>> decode(s.sequence_owner,a.owner,s.sequence_name,null) sequence_name,
>>>>
>>>> z
>>>>
>>>>
>>>>
>>>> On Sat, Aug 23, 2008 at 12:36 AM, Barbara Zoladek
>>>> <barbara.zoladek at autodesk.com> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> Using USER_* views is possible only if the connected user and the
>>>> schema are the same. ALTER SESSION SET CURRENT_SCHEMA sets the
>> current
>>>> schema so you don't need to qualify the objects with the schema
> name,
>>>> but it does not change the session user. Another reason for using
>>> ALL_*
>>>> views rather than USER_* is that you may have tables/views in
>>> different
>>>> schema rather than in one.
>>>>> USER_* views are based on the same objects as ALL_* views, the only
>>>> difference is the USER_* views have additional where clause
>>> 'owner=...'.
>>>>>
>>>>> Thanks,
>>>>> Barbara.
>>>>>
>>>>> -----Original Message-----
>>>>> From: fdo-users-bounces at lists.osgeo.org
>>>> [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Zac Spitzer
>>>>> Sent: Thursday, August 21, 2008 5:56 PM
>>>>> To: FDO Users Mail List
>>>>> Subject: [fdo-users] big oracle describe schema performance boost
>>>>>
>>>>> this is the query being used at the moment for an oracle describe
>>>> schema,
>>>>> (i did rewrite it without the where clause )
>>>>>
>>>>> SELECT a.owner, a.table_name, a.column_name, a.srid, a.diminfo,
>>>> b.cs_name,
>>>>> b.wktext, c.index_name, d.sdo_layer_gtype, s.sequence_name,
>>>>> d.sdo_root_mbr, NULL o1, NULL o2, NULL o3, NULL o4, NULL
> o5,
>>>> NULL o6,
>>>>> NULL o7, NULL o8, NULL o9, NULL o10, NULL o111, NULL o12
>>>>> FROM all_tab_columns t
>>>>> INNER JOIN all_sdo_geom_metadata a
>>>>> on t.owner = a.owner
>>>>> AND t.table_name = a.table_name
>>>>> AND t.column_name = a.column_name
>>>>> LEFT JOIN MDSYS.cs_srs b
>>>>> ON a.srid = b.srid
>>>>> LEFT JOIN all_sdo_index_info c
>>>>> ON a.owner = c.table_owner
>>>>> AND a.table_name = c.table_name
>>>>> LEFT JOIN all_sdo_index_metadata d
>>>>> ON c.sdo_index_owner =
>>>> d.sdo_index_owner
>>>>> AND c.index_name =
>>>> d.sdo_index_name
>>>>> LEFT JOIN all_sequences s
>>>>> ON s.sequence_name = CONCAT
>>>> (a.table_name, '_FDOSEQ')
>>>>> ORDER BY a.owner, a.table_name
>>>>>
>>>>> it's pretty slow because of the use of the all tables, i think
> there
>>>>> is a bug in there as well with
>>>>> the sequence join, it will match on duplicate table names in other
>>>>> schemas, but the performance
>>>>> goes to hell if you add the s.sequence_owner=t.owner crtieria
>>>>>
>>>>> if we just use the user views, that problem disappears
>>>>>
>>>>> SELECT a.table_name, a.column_name, a.srid, a.diminfo, b.cs_name,
>>>>> b.wktext, c.index_name, d.sdo_layer_gtype, s.sequence_name,
>>>>> d.sdo_root_mbr, NULL o1, NULL o2, NULL o3, NULL o4, NULL
> o5,
>>>> NULL o6,
>>>>> NULL o7, NULL o8, NULL o9, NULL o10, NULL o111, NULL o12
>>>>> FROM user_tab_columns t
>>>>> INNER JOIN user_sdo_geom_metadata a
>>>>> on t.table_name = a.table_name
>>>>> AND t.column_name = a.column_name
>>>>> LEFT JOIN MDSYS.cs_srs b
>>>>> ON a.srid = b.srid
>>>>> LEFT JOIN user_sdo_index_info c
>>>>> on a.table_name = c.table_name
>>>>> LEFT JOIN user_sdo_index_metadata d
>>>>> on c.index_name =
>>>> d.sdo_index_name
>>>>> LEFT JOIN user_sequences s
>>>>> ON s.sequence_name = CONCAT
>>>> (a.table_name, '_FDOSEQ')
>>>>> ORDER BY a.table_name
>>>>>
>>>>>
>>>>> the performance improvement and load reduction is quite dramatic,
>> the
>>>>> difference is that user_tab_columns
>>>>> lists the objects owner by the users, whereas, the all_tab_columns
>>>>> lists all accessible tables
>>>>>
>>>>> when using these tables, a ALTER SESSION SET
>>>>> CURRENT_SCHEMA='%kingoracle.oracleschema%'
>>>>> would be required at the start of the session if you use a
> different
>>>>> username than the specified oracleschema
>>>>>
>>>>> small schema
>>>>> q_user_schema (Datasource=gis, Time=16ms, Records=4)
>>>>> q_all_schema (Datasource=gis, Time=1750ms, Records=4)
>>>>>
>>>>> big schema
>>>>> q_user_schema (Datasource=gis, Time=234ms, Records=146)
>>>>> q_all_schema (Datasource=gis, Time=1531ms, Records=146)
>>>>>
>>>>> z
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Zac Spitzer -
>>>>> http://zacster.blogspot.com (My Blog)
>>>>> +61 405 847 168
>>>>> _______________________________________________
>>>>> fdo-users mailing list
>>>>> fdo-users at lists.osgeo.org
>>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>> _______________________________________________
>>>>> fdo-users mailing list
>>>>> fdo-users at lists.osgeo.org
>>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Zac Spitzer -
>>>> http://zacster.blogspot.com (My Blog)
>>>> +61 405 847 168
>>>> _______________________________________________
>>>> fdo-users mailing list
>>>> fdo-users at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>> _______________________________________________
>>>> fdo-users mailing list
>>>> fdo-users at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>>
>>>
>>>
>>>
>>> --
>>> Zac Spitzer -
>>> http://zacster.blogspot.com (My Blog)
>>> +61 405 847 168
>>> _______________________________________________
>>> fdo-users mailing list
>>> fdo-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>> _______________________________________________
>>> fdo-users mailing list
>>> fdo-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>>
>>
>>
>>
>> --
>> Zac Spitzer -
>> http://zacster.blogspot.com (My Blog)
>> +61 405 847 168
>> _______________________________________________
>> fdo-users mailing list
>> fdo-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>> _______________________________________________
>> fdo-users mailing list
>> fdo-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/fdo-users
>>
>
>
>
> --
> Zac Spitzer -
> http://zacster.blogspot.com (My Blog)
> +61 405 847 168
> _______________________________________________
> fdo-users mailing list
> fdo-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users
> _______________________________________________
> fdo-users mailing list
> fdo-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users
>
--
Zac Spitzer -
http://zacster.blogspot.com (My Blog)
+61 405 847 168
More information about the fdo-users
mailing list