[fdo-users] big oracle describe schema performance boost

[Zac Spitzer]

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