[fdo-users] big oracle describe schema performance boost
Zac Spitzer
zac.spitzer at gmail.com
Mon Aug 25 08:05:41 EDT 2008
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-td15021175.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
More information about the fdo-users
mailing list