Hi, I have recently upgraded from 10.6 to 11.5 and noticed the schema browser filter is very slow. After investigating, I believe the cause is that in 11.x and up, UPPER() is being used in the object_name for the query. I have both queries below and in version 11 you can see AND UPPER (object_name) LIKE :filt) o. Is there anyway to get around this?
version 10
/* Formatted on 6/26/2015 2:33:44 PM (QP5 v5.215.12089.38647) */
SELECT o.*,
NVL (d.debuginfo, ‘F’) DEBUGINFO,
NVL (p.AUTHID, ‘DEFINER’) authid
FROM (SELECT :own owner,
object_name,
object_type,
DECODE (status, ‘VALID’, ‘V’, ‘I’) status,
last_ddl_time,
object_id,
created
FROM sys.user_objects
WHERE 1 = 1
AND object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
AND object_name LIKE :filt) o,
sys.all_probe_objects d,
( SELECT object_id, AUTHID
FROM sys.user_procedures
WHERE 1 = 1 AND subprogram_id = 0 AND object_type = ‘PACKAGE’
GROUP BY object_id, AUTHID) p
WHERE p.object_id(+) = o.object_id
AND o.OBJECT_ID = d.object_id(+)
AND d.owner(+) = :own
AND O.Object_Name = D.Object_Name(+)
AND ( (d.object_type IS NULL)
OR (d.object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)))
ORDER BY 3, 2
version 11
/* Formatted on 6/26/2015 3:29:13 PM (QP5 v5.215.12089.38647) */
SELECT o.*,
NVL (d.debuginfo, ‘F’) DEBUGINFO,
NVL (p.AUTHID, ‘DEFINER’) authid
FROM (SELECT :own owner,
object_name,
object_type,
DECODE (status, ‘VALID’, ‘V’, ‘I’) status,
last_ddl_time,
object_id,
created
FROM sys.user_objects
WHERE 1 = 1
AND object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
AND UPPER (object_name) LIKE :filt) o,
sys.all_probe_objects d,
( SELECT object_id, AUTHID
FROM sys.USER_PROCEDURES
WHERE 1 = 1 AND subprogram_id = 0 AND object_type = ‘PACKAGE’
GROUP BY object_id, AUTHID) p
WHERE p.object_id(+) = o.object_id
AND o.OBJECT_ID = d.object_id(+)
AND d.owner(+) = :own
AND O.Object_Name = D.Object_Name(+)
AND ( (d.object_type IS NULL)
OR (d.object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)))
ORDER BY 3, 2
