[fdo-users] big oracle describe schema performance boost

[Barbara Zoladek]

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