Toad World® Forums

Optimize SQL with multiple SELECTS

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

The first time I tried to do this I used the Advanced option, which opened the full version of SQL optimizer. Today I tried the Auto Optimize SQL and that seems to be working. I must be missing something.

Thanks,
Scott