Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Unanswered: please improve SQL script (without subselects and inline views)

    Hi

    Please improve my SQL. The code gives the desired result, but I guess there is much room to improve.

    I wish to get rid of inline views and subselects.

    Code:
      SELECT job.jc_name || ';' || job.description || ';' || LOG.ess_user
        FROM    (SELECT jc_name, description
                   FROM job_code
                  WHERE     jc_name NOT IN (SELECT jc_name FROM ug_jc)
                        AND jc_name NOT IN (SELECT jc_name FROM jc_jc)
                        AND jc_name LIKE 'MAMMUT%') job
             LEFT JOIN
                (SELECT ESS_user, REPLACE (ENTITY_KEY, 'jc_name=') AS name
                   FROM vw_audit_trail
                  WHERE (vw_audit_trail.entity_key LIKE 'jc_name=%'
                         AND vw_audit_trail.table_name = 'job_code')
                        AND ACTION = 'Insert') LOG
             ON job.jc_name = LOG.name
    ORDER BY job.jc_name
    Is there nothing wrong with this SQL script in coding style?

    Output:

    Code:
    MAMMUT_TRT4_PS;Test Desc;Tommy
    MAMMUT_TRT5_PS;Test Desc;Bruno
    Tables (and views) to reproduce:
    Code:
    CREATE TABLE LORCAN4.JOB_CODE
    (
      JC_NAME          VARCHAR2(25 BYTE)            NOT NULL,
      DESCRIPTION      VARCHAR2(80 BYTE),
      PARENT_JC        VARCHAR2(25 BYTE),
      ORG_ID           NUMBER(11)                   DEFAULT 0,
      ORG_PARENT       VARCHAR2(254 BYTE),
      UPD_TIME         DATE,
      ADD_INFO_MASTER  VARCHAR2(2500 BYTE),
      ADD_INFO_IX_1    VARCHAR2(30 BYTE),
      ADD_INFO_IX_2    VARCHAR2(30 BYTE),
      ADD_INFO_IX_3    VARCHAR2(30 BYTE)
    )
    
    CREATE OR REPLACE FORCE VIEW LORCAN4.VW_AUDIT_TRAIL
    (
       SOURCE,
       SERIAL,
       FROM_EVENT_TIME,
       TO_EVENT_TIME,
       RSS_NAME,
       RSS_TYPE,
       TABLE_NAME,
       ACTION,
       ORIGIN,
       TRANS_SERIAL,
       MESSAGE,
       ESS_USER,
       TRANS_STATUS,
       ENTITY_KEY,
       PROCESS_ID,
       DETAILS_EXIST,
       SERIAL_NEG,
       EXTERNAL_EVENT_TIME,
       ADD_INFO_MASTER,
       ADD_INFO_IX_1,
       ADD_INFO_IX_2,
       ADD_INFO_IX_3
    )
    AS
       SELECT 'C' SOURCE,
              serial,
              from_event_time,
              to_event_time,
              rss_name,
              rss_type,
              table_name,
              action,
              origin,
              trans_serial,
              MESSAGE,
              ess_user,
              trans_status,
              entity_key,
              process_id,
              details_exist,
              serial_neg,
              external_event_time,
              add_info_master,
              add_info_ix_1,
              add_info_ix_2,
              add_info_ix_3
         FROM audit_trail
       UNION ALL
       SELECT 'A' SOURCE,
              serial,
              from_event_time,
              to_event_time,
              rss_name,
              rss_type,
              table_name,
              action,
              origin,
              trans_serial,
              MESSAGE,
              ess_user,
              trans_status,
              entity_key,
              process_id,
              details_exist,
              serial_neg,
              external_event_time,
              add_info_master,
              add_info_ix_1,
              add_info_ix_2,
              add_info_ix_3
         FROM audit_trail_archive;
    	
    CREATE TABLE LORCAN4.AUDIT_TRAIL
    (
      SERIAL               INTEGER                  NOT NULL,
      FROM_EVENT_TIME      DATE                     NOT NULL,
      TO_EVENT_TIME        DATE                     NOT NULL,
      RSS_NAME             VARCHAR2(32 BYTE),
      RSS_TYPE             VARCHAR2(12 BYTE),
      TABLE_NAME           VARCHAR2(30 BYTE),
      ACTION               VARCHAR2(16 BYTE)        NOT NULL,
      ORIGIN               VARCHAR2(32 BYTE)        DEFAULT 'Other'               NOT NULL,
      TRANS_SERIAL         NUMBER(11),
      MESSAGE              VARCHAR2(255 BYTE),
      ESS_USER             VARCHAR2(20 BYTE)        DEFAULT '-'                   NOT NULL,
      TRANS_STATUS         NUMBER(11),
      ENTITY_KEY           VARCHAR2(255 BYTE),
      PROCESS_ID           NUMBER(11),
      DETAILS_EXIST        NUMBER(11)               DEFAULT 0                     NOT NULL,
      SERIAL_NEG           NUMBER(11),
      EXTERNAL_EVENT_TIME  DATE,
      ADD_INFO_MASTER      VARCHAR2(1000 BYTE),
      ADD_INFO_IX_1        VARCHAR2(30 BYTE),
      ADD_INFO_IX_2        VARCHAR2(30 BYTE),
      ADD_INFO_IX_3        VARCHAR2(30 BYTE)
    )
    
    CREATE TABLE LORCAN4.AUDIT_TRAIL_ARCHIVE
    (
      SERIAL               INTEGER                  NOT NULL,
      FROM_EVENT_TIME      DATE                     NOT NULL,
      TO_EVENT_TIME        DATE                     NOT NULL,
      RSS_NAME             VARCHAR2(32 BYTE),
      RSS_TYPE             VARCHAR2(12 BYTE),
      TABLE_NAME           VARCHAR2(30 BYTE),
      ACTION               VARCHAR2(16 BYTE)        NOT NULL,
      ORIGIN               VARCHAR2(32 BYTE)        DEFAULT 'Other'               NOT NULL,
      TRANS_SERIAL         NUMBER(11),
      MESSAGE              VARCHAR2(255 BYTE),
      ESS_USER             VARCHAR2(20 BYTE)        DEFAULT '-'                   NOT NULL,
      TRANS_STATUS         NUMBER(11),
      ENTITY_KEY           VARCHAR2(255 BYTE),
      PROCESS_ID           NUMBER(11),
      DETAILS_EXIST        NUMBER(11)               DEFAULT 0                     NOT NULL,
      SERIAL_NEG           NUMBER(11),
      EXTERNAL_EVENT_TIME  DATE,
      ADD_INFO_MASTER      VARCHAR2(1000 BYTE),
      ADD_INFO_IX_1        VARCHAR2(30 BYTE),
      ADD_INFO_IX_2        VARCHAR2(30 BYTE),
      ADD_INFO_IX_3        VARCHAR2(30 BYTE)
    )
    
    CREATE TABLE LORCAN4.UG_JC
    (
      UG_NAME          VARCHAR2(255 BYTE)           NOT NULL,
      RSS_NAME         VARCHAR2(32 BYTE)            NOT NULL,
      RSS_TYPE         VARCHAR2(12 BYTE)            NOT NULL,
      JC_NAME          VARCHAR2(25 BYTE)            NOT NULL,
      ADD_INFO_MASTER  VARCHAR2(1000 BYTE),
      ADD_INFO_IX_1    VARCHAR2(30 BYTE),
      ADD_INFO_IX_2    VARCHAR2(30 BYTE),
      ADD_INFO_IX_3    VARCHAR2(30 BYTE)
    )
    
    CREATE TABLE LORCAN4.JC_JC
    (
      JC_NAME          VARCHAR2(25 BYTE)            NOT NULL,
      CON_JC_NAME      VARCHAR2(25 BYTE)            NOT NULL,
      DESCRIPTION      VARCHAR2(80 BYTE),
      UPD_TIME         DATE,
      ADD_INFO_MASTER  VARCHAR2(1000 BYTE),
      ADD_INFO_IX_1    VARCHAR2(30 BYTE),
      ADD_INFO_IX_2    VARCHAR2(30 BYTE),
      ADD_INFO_IX_3    VARCHAR2(30 BYTE)
    )

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT * FROM V$VERSION;

    post whole EXPLAIN PLAN for query; including Predicate Information (identified by operation id):
    Last edited by anacedent; 02-28-12 at 11:34.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by clob View Post
    I wish to get rid of inline views and subselects.
    Why would you want to do that?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Why would you want to do that?
    I suspect to maintain SQL portability to DBs that do not support them.

    OP want SQL that is good, fast & cheap!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Lightbulb

    Here's my first attempt
    Code:
    SELECT j.jc_name || ';' || j.description || ';' || t.ess_user
      FROM job_code j
           left join vw_audit_trail t on (j.jc_name = replace(t.entity_key, 'jc_name=') 
                                          AND t.entity_key LIKE 'jc_name=%'
                                          AND t.table_name = 'job_code'
                                          AND t.action = 'Insert') 
     WHERE j.jc_name NOT IN (SELECT jc_name FROM ug_jc)
       AND j.jc_name NOT IN (SELECT jc_name FROM jc_jc)
       AND j.jc_name LIKE 'MAMMUT%'
    ORDER BY j.jc_name
    There was a small trick question here:
    1) you can't move the predicates on the vw_audit_trail table into the where clause because it will no longer outer join with job_code. The job_code table on the other hand is the leading table and so you can move it's predicates to the where clause without affecting the final result.
    2) You can never remove the (SELECT jc_name FROM ??_??) subqueries because there is no other way to get a negative match. You could use NOT EXISTS instead of NOT IN, but you will still need the subselect.

    Of course I can't really test this without any data, I could only see the explain plans, of which the new query eliminates a single plan step. But there is still no change to cost of the plan.

  6. #6
    Join Date
    Feb 2012
    Posts
    44
    Thank you very much dayneo.

    Here explaint plans

    dayneo's script:

    Code:
    SQL> @dayneo
    
    Explained.
    
    SQL> SELECT * FROM TABLE( dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name                     | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |                          |     1 |   229 |   155 |
    |   1 |  SORT ORDER BY                        |                          |     1 |   229 |   155 |
    |   2 |   NESTED LOOPS ANTI                   |                          |     1 |   229 |   154 |
    |   3 |    HASH JOIN OUTER                    |                          |     1 |   211 |   153 |
    |   4 |     HASH JOIN ANTI                    |                          |     1 |    70 |    99 |
    |   5 |      TABLE ACCESS FULL                | JOB_CODE                 |   707 | 36057 |    56 |
    |   6 |      TABLE ACCESS FULL                | UG_JC                    |  2404 | 45676 |    43 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |   7 |     VIEW                              | VW_AUDIT_TRAIL           |     2 |   282 |    53 |
    |   8 |      UNION-ALL                        |                          |       |       |       |
    |   9 |       TABLE ACCESS BY INDEX ROWID     | AUDIT_TRAIL              |     1 |    69 |     6 |
    |  10 |        BITMAP CONVERSION TO ROWIDS    |                          |       |       |       |
    |  11 |         BITMAP AND                    |                          |       |       |       |
    |  12 |          BITMAP CONVERSION FROM ROWIDS|                          |       |       |       |
    |  13 |           SORT ORDER BY               |                          |       |       |       |
    |  14 |            INDEX RANGE SCAN           | AUDIT_TRAIL_IX_7         |    16 |       |     2 |
    |  15 |          BITMAP CONVERSION FROM ROWIDS|                          |       |       |       |
    |  16 |           INDEX RANGE SCAN            | AUDIT_TRAIL_IX_3         |    16 |       |     3 |
    |  17 |       TABLE ACCESS BY INDEX ROWID     | AUDIT_TRAIL_ARCHIVE      |     1 |    76 |    47 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |  18 |        INDEX RANGE SCAN               | AUDIT_TRAIL_ARCHIVE_IX_7 |    94 |       |     3 |
    |  19 |    INDEX RANGE SCAN                   | PK_JC_JC                 |   277 |  4986 |     1 |
    --------------------------------------------------------------------------------------------------
    
    Note
    -----
       - 'PLAN_TABLE' is old version
    
    29 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    
    -----------------------------------------------------------------------------
    | Id  | Operation                         | Name    | Rows  | Bytes | Cost  |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    24 |
    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |       |
    -----------------------------------------------------------------------------
    
    Note
    -----
       - 'PLAN_TABLE' is old version
    
    
    Statistics
    ----------------------------------------------------------
             30  recursive calls
              0  db block gets
             14  consistent gets
              0  physical reads
              0  redo size
           3266  bytes sent via SQL*Net to client
            480  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
             29  rows processed
    original statement

    Code:
    SQL> @clob
    
    Explained.
    
    SQL> SELECT * FROM TABLE( dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name                     | Rows  | Bytes | Cost  |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                          |     1 |   229 |   155 |
    |   1 |  SORT ORDER BY                         |                          |     1 |   229 |   155 |
    |   2 |   NESTED LOOPS ANTI                    |                          |     1 |   229 |   154 |
    |   3 |    HASH JOIN OUTER                     |                          |     1 |   211 |   153 |
    |   4 |     HASH JOIN ANTI                     |                          |     1 |    70 |    99 |
    |   5 |      TABLE ACCESS FULL                 | JOB_CODE                 |   707 | 36057 |    56 |
    |   6 |      TABLE ACCESS FULL                 | UG_JC                    |  2404 | 45676 |    43 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |   7 |     VIEW                               |                          |     2 |   282 |    53 |
    |   8 |      VIEW                              | VW_AUDIT_TRAIL           |     2 |   282 |    53 |
    |   9 |       UNION-ALL                        |                          |       |       |       |
    |  10 |        TABLE ACCESS BY INDEX ROWID     | AUDIT_TRAIL              |     1 |    69 |     6 |
    |  11 |         BITMAP CONVERSION TO ROWIDS    |                          |       |       |       |
    |  12 |          BITMAP AND                    |                          |       |       |       |
    |  13 |           BITMAP CONVERSION FROM ROWIDS|                          |       |       |       |
    |  14 |            SORT ORDER BY               |                          |       |       |       |
    |  15 |             INDEX RANGE SCAN           | AUDIT_TRAIL_IX_7         |    16 |       |     2 |
    |  16 |           BITMAP CONVERSION FROM ROWIDS|                          |       |       |       |
    |  17 |            INDEX RANGE SCAN            | AUDIT_TRAIL_IX_3         |    16 |       |     3 |
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |  18 |        TABLE ACCESS BY INDEX ROWID     | AUDIT_TRAIL_ARCHIVE      |     1 |    76 |    47 |
    |  19 |         INDEX RANGE SCAN               | AUDIT_TRAIL_ARCHIVE_IX_7 |    94 |       |     3 |
    |  20 |    INDEX RANGE SCAN                    | PK_JC_JC                 |   277 |  4986 |     1 |
    ---------------------------------------------------------------------------------------------------
    
    Note
    -----
       - 'PLAN_TABLE' is old version
    
    30 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    
    -----------------------------------------------------------------------------
    | Id  | Operation                         | Name    | Rows  | Bytes | Cost  |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    24 |
    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |       |
    -----------------------------------------------------------------------------
    
    Note
    -----
       - 'PLAN_TABLE' is old version
    
    
    Statistics
    ----------------------------------------------------------
             31  recursive calls
              0  db block gets
             14  consistent gets
              0  physical reads
              0  redo size
           3391  bytes sent via SQL*Net to client
            480  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
             30  rows processed

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Also, using j.jc_name LIKE 'MAMMUT%' is likely relatively selective on JOB_CODE table. You would probably benefit from an index on JOB_CODE.JC_NAME. Perhaps also an index on UG_JC.JC_NAME?

    You will need to benchmark and is entirely dependent on the data involved.

  8. #8
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by clob View Post
    Thank you very much dayneo.

    Here explaint plans
    Plans of what exactly ?

    You showed us only ONE query, but posted a FEW plans.
    Which one is a plan of the query you showed us ?

    What is your database version ?

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @kordirko
    Yeah, he didn't use ECHO ON. But following this thread and looking at the descriptions above the code blocks "dayneo's script" and "original statement" respectively, it would be safe to assume that the first explain plan is for the query that I wrote and the second explain plan is the query @clob wrote. Not so?

    Also, not quite sure how "version" really plays a role here either? Only thing I could think of is that if he were using an older version of Oracle (ie. one that still uses role based optimiser) then he might get a different explain plan. But then again, @clob is using SQL92 syntax and so must be a new generation version of Oracle. Either way, version is a little immaterial here.

    Maybe the point is: To all posters; please include the database version in all posts as this may help provide an answer.
    Last edited by dayneo; 03-01-12 at 03:54.

  10. #10
    Join Date
    Feb 2012
    Posts
    44
    Yeah dayneo is right concerning explain plans.

    Code:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for Solaris: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •