Mapguide FDO, making oracle describe schema perform

(this was posted on fdo-users)

This is the query being used at the moment for the describe schema in King Oracle,
(I did rewrite the sql a bit, I got rid of there 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')
--and s.sequence_owner=t.owner criteria
ORDER BY a.owner, a.table_name

it's pretty slow because of the use of the all tables, I think there's
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 criteria back in

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,
while the all_tab_columns lists everything that the user has access to

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) that's roughly 100 times faster
q_all_schema (Datasource=gis, Time=1750ms, Records=4)

big schema
q_user_schema (Datasource=gis, Time=234ms, Records=146) that's roughly 6.5 times faster
q_all_schema (Datasource=gis, Time=1531ms, Records=146)

not only is it faster, but it's also a lot less work for the database