Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: Query optmization

    Please help to optimize the following query

    SELECT R.NAME, R.STYPE, R.TTYPE, (SELECT COMMERCIAL_FILTER FROM TOPMD_TYPE T
    WHERE T.NAME=R.STYPE) SOURCE_COMMERCIAL_FILTER,
    (SELECT COMMERCIAL_FILTER FROM TOPMD_TYPE T
    WHERE T.NAME=R.TTYPE) TARGET_COMMERCIAL_FILTER
    FROM TOPMD_RELATIONS R, TOPMD_TYPE H
    WHERE ( R.STYPE=H.NAME OR R.TTYPE=H.NAME )

    thanks a lot for the help.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Post the DDL (CREATE TABLE) of all tables involved
    Post defined indexes of all tables involved
    Post the execution plan for the query

    Any use [code] tags around your (properly formatted) SQL statements!

  3. #3
    Join Date
    Jul 2010
    Posts
    3
    TOPMD_TYPE table definition:

    CREATE TABLE "M41LS"."TOPMD_TYPE"
    (
    "NAME" VARCHAR2(128 BYTE),
    "OEXID_LABEL" VARCHAR2(200 BYTE),
    "TECHNO_NAME" VARCHAR2(128 BYTE),
    "CUSTOMER" NUMBER(1,0) NOT NULL ENABLE,
    "COMMERCIAL_FILTER" VARCHAR2(4000 BYTE),
    "IS_COVERED_BY_RLO" NUMBER(1,0),
    CONSTRAINT "TOPMD_TYPE_PK" PRIMARY KEY ("NAME") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "MUSE" ENABLE
    )

    TOPMD_RELATIONS DEFINITION:

    CREATE TABLE "M41LS"."TOPMD_RELATIONS"
    (
    "NAME" VARCHAR2(128 BYTE),
    "STYPE" VARCHAR2(128 BYTE),
    "TTYPE" VARCHAR2(128 BYTE),
    "IS_ADJACENCY" NUMBER(1,0) DEFAULT 0,
    "MAIN_SOURCE_TYPE" VARCHAR2(128 BYTE),
    "MAIN_TARGET_TYPE" VARCHAR2(128 BYTE),
    CONSTRAINT "TOPMD_RELATIONS_PK" PRIMARY KEY ("NAME") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "MUSE" ENABLE
    )
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
    (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
    )
    TABLESPACE "MUSE" ;

    CREATE UNIQUE INDEX "M41LS"."TOPMD_RELATIONS_PK" ON "M41LS"."TOPMD_RELATIONS"
    (
    "NAME"
    )
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
    (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
    )
    TABLESPACE "MUSE" ;

    Explain Plan:

    -----------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 22 | 770 | 6 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| TOPMD_TYPE | 1 | 96 | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | TOPMD_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
    | 3 | TABLE ACCESS BY INDEX ROWID| TOPMD_TYPE | 1 | 96 | 1 (0)| 00:00:01 |
    |* 4 | INDEX UNIQUE SCAN | TOPMD_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
    | 5 | CONCATENATION | | | | | |
    | 6 | NESTED LOOPS | | 21 | 735 | 3 (0)| 00:00:01 |
    | 7 | TABLE ACCESS FULL | TOPMD_RELATIONS | 22 | 572 | 3 (0)| 00:00:01 |
    |* 8 | INDEX UNIQUE SCAN | TOPMD_TYPE_PK | 1 | 9 | 0 (0)| 00:00:01 |
    | 9 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
    | 10 | TABLE ACCESS FULL | TOPMD_RELATIONS | 22 | 572 | 3 (0)| 00:00:01 |
    |* 11 | INDEX UNIQUE SCAN | TOPMD_TYPE_PK | 1 | 9 | 0 (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------


    Formatted query:

    SELECT R.NAME,
    R.STYPE,
    R.TTYPE,
    (SELECT COMMERCIAL_FILTER FROM TOPMD_TYPE T WHERE T.NAME=R.STYPE
    ) SOURCE_COMMERCIAL_FILTER,
    (SELECT COMMERCIAL_FILTER FROM TOPMD_TYPE T WHERE T.NAME=R.TTYPE
    ) TARGET_COMMERCIAL_FILTER
    FROM TOPMD_RELATIONS R,
    TOPMD_TYPE H
    WHERE ( R.STYPE=H.NAME
    OR R.TTYPE =H.NAME )


    thank you very much.

  4. #4
    Join Date
    Jul 2010
    Posts
    3
    -----------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -----------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 22 | 770 | 6 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| TOPMD_TYPE | 1 | 96 | 1 (0)| 00:00:01 |
    |* 2 | INDEX UNIQUE SCAN | TOPMD_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
    | 3 | TABLE ACCESS BY INDEX ROWID| TOPMD_TYPE | 1 | 96 | 1 (0)| 00:00:01 |
    |* 4 | INDEX UNIQUE SCAN | TOPMD_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
    | 5 | CONCATENATION | | | | | |
    | 6 | NESTED LOOPS | | 21 | 735 | 3 (0)| 00:00:01 |
    | 7 | TABLE ACCESS FULL | TOPMD_RELATIONS | 22 | 572 | 3 (0)| 00:00:01 |
    |* 8 | INDEX UNIQUE SCAN | TOPMD_TYPE_PK | 1 | 9 | 0 (0)| 00:00:01 |
    | 9 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
    | 10 | TABLE ACCESS FULL | TOPMD_RELATIONS | 22 | 572 | 3 (0)| 00:00:01 |
    |* 11 | INDEX UNIQUE SCAN | TOPMD_TYPE_PK | 1 | 9 | 0 (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    So you didn't find the keys on your keyboard to type the [code] tag.
    That makes reading the execution plan and the SQL statements really hard.

    But as far as I can tell, creating one index on TOPMD_RELATIONS.STYPE and another one on TOPMD_RELATIONS.RTYPE should improve the query.

    I'm not sure the nested SELECT is really needed.
    My gut-feeling is that this can be achieved with a regular join as well, something like:
    Code:
    SELECT r.name,
           r.stype,
           r.ttype,
           source.commercial_filter AS source_commercial_filter,
           target.commercial_filter AS target_commercial_filter
    FROM topmd_relations R,
      JOIN topmd_type source ON r.stype = source.name
      JOIN topmd_type target ON r.rtype = target.name
    (See how nicely the SQL looks when written between [code] tags?)

    I'm not entirely sure it does the same as your statement, but if it does, it should be faster. Creating the two indexes would speed that query up as well.

Posting Permissions

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