9 Replies Latest reply: Apr 5, 2013 10:36 AM by 1001104 RSS

    Need help to rewrite multiple outer join statement.

    1001104
      The following statement could not run in a server with limited resources.
      How to optimize it to reduce the required memory size? Please shed a light. Many thanks.

      CREATE VIEW wssdba.WSV_USER AS
      SELECT DISTINCT
      U.USER_KEY AS "USER_KEY"
      , U.USER_SYS_ID AS "USER_SYS_ID"
      , U.USER_NAME AS "USER_NAME"
      , U.MARKIT_NAME AS "MARKIT_NAME"
      , COALESCE( AG.ITEM_STATE, U.GROUP_NAME ) AS "GROUP_NAME"
      , U.USER_PWD AS "USER_PWD"
      , U.CREATED AS "CREATED"
      , U.USER_PRIVILEGES AS "USER_PRIVILEGES"
      , CASE
      WHEN AE.ITEM_STATE IS NULL THEN U.IS_DISABLED
      WHEN AE.ITEM_STATE = 'on' THEN 0
      ELSE 1
      END AS "IS_DISABLED"
      from WSD_USER U
      LEFT OUTER JOIN WSD_ACTIVITY_PERMISSION AU ON (Concat(Concat(U.USER_KEY, '.'), U.USER_SYS_ID) = AU.GROUP_NAME AND AU.PERMISSION_CLASS = 'wseUserPermission')
      LEFT OUTER JOIN WSD_ACTIVITY_PERMISSION AG ON (AU.GROUP_NAME = AG.GROUP_NAME AND AG.ACTION_SUBJECT = 'Group Name' AND AG.PERMISSION_CLASS = 'wseUserPermission')
      LEFT OUTER JOIN WSD_ACTIVITY_PERMISSION AE ON (AU.GROUP_NAME = AE.GROUP_NAME AND AE.ACTION_SUBJECT = 'Is Active User' AND AE.PERMISSION_CLASS = 'wseUserPermission')
      ;

      Bing
        • 1. Re: Need help to rewrite multiple outer join statement.
          APC
          Did you get an error message? If so please tell us what it is.

          You are new to the forum so you might not realise this, but we are not mind readers. All we understand of your situation is what you tell us. So, if you want a quick response you should give us all the necessary details.

          For instance, what version of the database? What do you mean by " server with limited resources"? What constraint prevents the provision of additional resources? What are the sizes of the source tables? How many rows do you estimate in the view?

          Cheers, APC
          • 2. Re: Need help to rewrite multiple outer join statement.
            sb92075
            HOW To Make TUNING request
            SQL and PL/SQL FAQ
            • 3. Re: Need help to rewrite multiple outer join statement.
              Frank Kulash
              Hi, Bing,

              Welcome to the forum!

              Maybe there's another way to get the results you want by outer-joining only 1 copy of wsd_activity_permission, rather than 3.
              For any given group_name, will there every be more than 1 row where permission_class='wseUserPermission' and action_subject='Group Name"? How about for action_subject='Is Active User'?

              Plese post a little sample data (CREATE TABLE and INSERT statements) for the base tables, and the results you want from that data. You don't have to include all the columns; you can include only the columns needed for the join, and maybe 1 or 2 others from each table.
              Always say which version of Oracle you're using (e.g. 11.2.0.2.0).
              See the forum FAQ {message:id=936002}
              • 4. Re: Need help to rewrite multiple outer join statement.
                1001104
                Here is the error message:
                LEFT OUTER JOIN WSD_ACTIVITY_PERMISSION AU ON (Concat(Concat(U.USER_KEY, '.'), U.USER_SYS_ID) = AU.GROUP_NAME AND AU.PERMISSION_CLASS = 'wseUserPermission')
                *
                ERROR at line 17:
                ORA-00603: ORACLE server session terminated by fatal error
                • 5. Re: Need help to rewrite multiple outer join statement.
                  APC
                  user9061538 wrote:
                  ERROR at line 17:
                  ORA-00603: ORACLE server session terminated by fatal error
                  There should be a trace file somewhere in the user dump dest directory giving the details.

                  But, given that you have already mentioned "a server with limited resources", let's have a guess. The chances are the database cannot extend the temporary tablespace because the disk where that tablespace keeps its files is full. So, you need to add more disk space.



                  Cheers, APC
                  • 6. Re: Need help to rewrite multiple outer join statement.
                    1001104
                    Hi,
                    Sorry that I did not give enough information. Oracle version 10.2.0.4.0.

                    CREATE TABLE wssdba.WSD_ACTIVITY_PERMISSION
                    (
                    "GROUP_NAME" VARCHAR2(50) NOT NULL,
                    "PERMISSION_CLASS" VARCHAR2(64)          NOT NULL,
                    "PERMISSION_ACTION" VARCHAR2(64)          NOT NULL,
                    "ACTION_SUBJECT" VARCHAR2(64)          NOT NULL,
                    "ITEM_STATE" VARCHAR2(50) NULL,
                    "PENDING_STATE" VARCHAR2(50) NULL,
                    "PENDING_USER_KEY" VARCHAR2(48) NULL,
                    "PENDING_USER_SYS_ID" VARCHAR2(16) NULL,
                    "MDCUBE_INDEX_ARRAY" BLOB               NULL
                    )
                    LOB(MDCUBE_INDEX_ARRAY) STORE AS B85_MDCUBE_INDEX_ARRAY(TABLESPACE CoreIRDlobs1S)
                    TABLESPACE CoreIRDdata1S
                    ;

                    CREATE TABLE wssdba.WSD_USER
                    (
                    "USER_KEY" VARCHAR2(48) NOT NULL,
                    "USER_SYS_ID" VARCHAR2(16) NOT NULL,
                    "USER_NAME" VARCHAR2(40) NOT NULL,
                    "MARKIT_NAME" VARCHAR2(48) NULL,
                    "UNIQUE_MARKIT_NAME" VARCHAR2(48) NOT NULL,
                    "MARKIT_LAST_UP_DATETIME" DATE NULL,
                    "MARKIT_LAST_UP_USER_KEY" VARCHAR2(48) NULL,
                    "MARKIT_LAST_UP_USER_SYS_ID" VARCHAR2(16) NULL,
                    "MARKIT_LAST_UP_EFF_USER_KEY" VARCHAR2(48) NULL,
                    "MARKIT_LAST_UP_EFF_USER_SYS_ID" VARCHAR2(16) NULL,
                    "GROUP_NAME" VARCHAR2(50) NOT NULL,
                    "USER_PWD" NUMBER(10,0) DEFAULT 0 NOT NULL,
                    "CREATED" DATE DEFAULT(SYSDATE) NOT NULL,
                    "USER_PRIVILEGES" NUMBER(10,0) DEFAULT 0 NOT NULL,
                    "IS_DISABLED" NUMBER(1,0) DEFAULT 0 NOT NULL,
                    "MDCUBE_INDEX_ARRAY" BLOB NULL
                    )
                    LOB(MDCUBE_INDEX_ARRAY) STORE AS B4_MDCUBE_INDEX_ARRAY(TABLESPACE CoreIRDlobs1S)
                    TABLESPACE CoreIRDdata1S
                    ;

                    This WSD_ACTIVITY_PERMISSION table contains 1727 rows. WSD_User table is small, contains 5 rows.

                    This query gives 6 rows back: SELECT * FROM WSD_ACTIVITY_PERMISSION WHERE permission_class='wseUserPermission' and action_subject='Group Name' OR action_subject='Is Active User';

                    This query gives 12 rows back:SELECT * FROM WSD_ACTIVITY_PERMISSION WHERE permission_class='wseUserPermission'
                    Here are some sample data:
                    GROUP_NAME PERMISSION_CLASS PERMISSION_ACTION ACTION_SUBJECT ITEM_STATE
                    121846257.IRD wseUserPermission wseProperties Has Full Tree View off
                    121846257.IRD wseUserPermission wseProperties Is Active User on
                    121846257.IRD wseUserPermission wseProperties Is Administrator off
                    121846256.IRD wseUserPermission wseProperties Is Active User on

                    This query
                    SELECT DISTINCT permission_class FROM WSD_ACTIVITY_PERMISSION;
                    returns 5 rows: wseTradePermission, wseReportPermission, wseUserPermission, wseGroupPermission, wseMarketPermission.

                    Edited by: user9061538 on Apr 4, 2013 10:07 AM
                    • 7. Re: Need help to rewrite multiple outer join statement.
                      1001104
                      I also found that this query runs through and return only 9 rows:
                      SELECT DISTINCT
                      U.USER_KEY AS "USER_KEY"
                      , U.USER_SYS_ID AS "USER_SYS_ID"
                      , U.USER_NAME AS "USER_NAME"
                      , U.MARKIT_NAME AS "MARKIT_NAME"
                      , COALESCE( AU.ITEM_STATE, U.GROUP_NAME ) AS "GROUP_NAME"
                      , U.USER_PWD AS "USER_PWD"
                      , U.CREATED AS "CREATED"
                      , U.USER_PRIVILEGES AS "USER_PRIVILEGES"
                      , CASE
                      WHEN AU.ITEM_STATE IS NULL THEN U.IS_DISABLED
                      WHEN AU.ITEM_STATE = 'on' THEN 0
                      ELSE 1
                      END AS "IS_DISABLED"
                      from WSD_USER U
                      LEFT OUTER JOIN WSD_ACTIVITY_PERMISSION AU ON (Concat(Concat(U.USER_KEY, '.'), U.USER_SYS_ID) = AU.GROUP_NAME AND AU.PERMISSION_CLASS = 'wseUserPermission')
                      LEFT OUTER JOIN WSD_ACTIVITY_PERMISSION AG ON (AU.GROUP_NAME = AG.GROUP_NAME AND AG.ACTION_SUBJECT = 'Group Name' AND AG.PERMISSION_CLASS = 'wseUserPermission')
                      LEFT OUTER JOIN WSD_ACTIVITY_PERMISSION AE ON (AU.GROUP_NAME = AE.GROUP_NAME AND AE.ACTION_SUBJECT = 'Is Active User' AND AE.PERMISSION_CLASS = 'wseUserPermission')


                      So it only terminate the session if I add create view wrap around the script above and run it. I don't quite understand that.
                      • 8. Re: Need help to rewrite multiple outer join statement.
                        1001104
                        I cannot add more disk space because it is run on a VM that is mainteined by our IT dept. in London. I do not have the permission to do so.
                        • 9. Re: Need help to rewrite multiple outer join statement.
                          1001104
                          After I cut the table of WSD_ACTIVITY_PERMISSION to have only 14 rows, the creat view statement still cause the session termination.
                          What happened?