[fdo-users] big oracle describe schema performance boost
Haris Kurtagic
haris at sl-king.com
Mon Aug 25 09:26:46 EDT 2008
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".
Provider keeps physical mapping to real oracle schema.table ( it is not
parsed from name ).
Oracle username in connection doesn't mean that we access only that
Oracle schema. One user can have access to several Oracle schemas.
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.
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
More information about the fdo-users
mailing list