(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 schemaq_user_schema (Datasource=gis, Time=16ms, Records=4)
that's roughly 100 times fasterq_all_schema (Datasource=gis, Time=1750ms, Records=4)
big schemaq_user_schema (Datasource=gis, Time=234ms, Records=146)
that's roughly 6.5 times fasterq_all_schema (Datasource=gis, Time=1531ms, Records=146)
not only is it faster, but it's also a lot less work for the database
