[fdo-users] big oracle describe schema performance boost

[Zac Spitzer]

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