Is it possible to use the optimizer for complex queries (like this)? This is my first day using this product. I attempted to optimize the SQL and it returned this info/error.
The Parser did not identify the text as a valid SQL statement.
< my SQL statement is here -- attached to this ticket >
ORA-00922: missing or invalid option
Invalid SQL statement.
Possible causes of this error are:
-
The schema for the SQL statement is not selected or incorrect.
-
You have inserted PL/SQL code or more than one SQL statement.
Only a single SELECT/INSERT/DELETE/UPDATE/MERGE statement can be optimized.
For multiple SQL statements, or PL/SQL code, first use the Scan SQL module to extract the SQL statements. -
The Local Variable Conversion parameter from the Scan SQL was not replaced in the SQL statement.
Here's the SQL I want to optimze:
alter session set current_schema = xdstore1;
var BIND_1 VARCHAR2(32);
var BIND_2 VARCHAR2(32);
var BIND_3 VARCHAR2(32);
-- Bind Variables Assignment
exec :BIND_1 := '1145';
exec :BIND_2 := '1146';
exec :BIND_3 := '/root/%';
-- SQL Statement
SELECT /*+ MONITOR */ count(1) FROM
( select sace.id, sace.path, sace.type, sace.owner, sace.userid,
decode(nvl(sace.DA, 0) + nvl(sace.A, 0), -16, 'x', 0, '-', 16, 'A') ||
decode(nvl(sace.DR, 0) + nvl(sace.R, 0), -1, 'x', 0, '-', 1, 'R') ||
decode(nvl(sace.DP, 0) + nvl(sace.P, 0), -2, 'x', 0, '-', 2, 'P') ||
decode(nvl(sace.DC, 0) + nvl(sace.C, 0), -4, 'x', 0, '-', 4, 'C') ||
decode(nvl(sace.DD, 0) + nvl(sace.D, 0), -8, 'x', 0, '-', 8, 'D') as permissions
from (
-- build summary ACE records by path, userid which enforces weighted permission scheme values for O/U/G/M ACES
select wace.id id, wace.path path, wace.type type, wace.owner owner, wace.userid userid, decode(mod(max(wace.wmask),2), 0, wace.mask, 0 - wace.mask) mask
from (
-- build ACE records per userid by concatenating the userids found from individual O/U/G/M ACES
select ace.id, ace.path, ace.type, ace.owner, ace.o_uid || svg.userid || svm.userid || ace.u_uid userid, ace.mask, ace.wmask
from
-- recursive group resolution
(select connect_by_root group_id group_id, su.userid
from sas_group_member sgm, sas_user su
where principal_type = 'sas:BIND_user'
and su.id (+)= principal_id
connect by prior sgm.principal_id = sgm.group_id) svg,
-- membership resolution
(select distinct smm.membership_id membership_id, svg.userid || su.userid userid
from sas_membership_member smm, sas_user su, (
-- recursive group resolution for membership
select connect_by_root group_id group_id, su.userid
from sas_group_member sgm, sas_user su
where principal_type = 'sas:BIND_user'
and su.id (+)= principal_id
and su.userid is not null
connect by prior sgm.principal_id = sgm.group_id) svg
where su.id (+)= smm.principal_id
and svg.group_id (+)= smm.principal_id and (svg.userid || su.userid) is not null) svm, (
-- core SQL which pulls id, path and raw ACE data
select svp.id id, o_asid.sid owner, svp.path path, svp.type type,
decode(substr(asid.sid, 1, 6), 'OWNER_', substr(asid.sid, 7), null) o_uid, -- parse owner userid (no lookup)
decode(substr(asid.sid, 1, 6), 'GROUP_', substr(asid.sid, 7), null) gid, -- parse group ids
decode(substr(asid.sid, 1, 6), 'MEMBER', substr(asid.sid, 9), null) mid, -- parse membership id
decode(substr(asid.sid, 1, 6), 'OWNER_', null, 'GROUP_', null, 'MEMBER', null, asid.sid) u_uid, -- not owner, group or membership so explicit user id
mask, granting,
decode(substr(asid.sid, 1, 6), 'OWNER_', decode(granting, 1, 6, 7), 'GROUP_', decode(granting, 1, 2, 3), 'MEMBER', decode(granting, 1, 0, 1), decode(granting, 1, 4, 5)) wmask -- determine weighted mask values based on O/U/G/M ACES
from acl_object_identity aoi, acl_entry ae, acl_sid asid, acl_sid o_asid,
-- maps repository ids with paths
(select xp_id.string_value id,
xu.full_path path,
xp_typeid.string_value type
from xyf_urls xu,
xyf_files xf,
xyf_properties xp_id,
xyf_properties xp_typeid
where xu.file_id = xf.file_id
AND XU.FULL_PATH NOT LIKE '%/.trashcan%'
AND XU.FULL_PATH NOT LIKE '/root/Users/%'
-- idx2/3 use all columns from UK index
AND xp_id.property_owner_type (+) = 0
AND xp_id.property_owner_id (+) = xf.file_id
AND xp_id.version_number (+) = 1
AND xp_id.property_definition_id (+) = :BIND_1
AND xp_id.value_position (+) = 0
-- idx2/3 use all columns from UK index
AND xp_typeid.property_owner_type (+) = 0
AND xp_typeid.property_owner_id (+) = xf.file_id
AND xp_typeid.version_number (+) = 1
AND xp_typeid.property_definition_id (+) = :BIND_2
AND xp_typeid.value_position (+) = 0) svp
where to_char(svp.id) = aoi.object_id_identity
and ae.acl_object_identity = aoi.id
and ae.mask <> 256
and ae.sid = asid.id
and aoi.owner_sid = o_asid.id
) ace -- raw ACEs
where svg.group_id (+)= gid
and svm.membership_id (+)= mid
) wace -- weighted ACEs
group by wace.id, wace.path, wace.type, wace.owner, wace.userid, wace.mask order by wace.userid, wace.mask
) pivot (
max(mask)
for mask in ( -1 DR, 1 R, -2 DP, 2 P, -4 DC, 4 C, -8 DD, 8 D, -16 DA, 16 A)
) sace -- summary ACEs
);
Thanks,
Scott