1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Select Query Performance with and without hard coded values

Discussion in 'SQL PL/SQL' started by geroni, May 9, 2012.

  1. geroni

    geroni Guest

    Hi everyone, i need help for my sql query

    i have a query looks like this

    select * from
    (
    select obj_id,flag,foreign_id date, statid,type ,value from TIMESERIES, LOOKUP, DATA
    where
    Timeseries.foreign_id= lookup.foreign_id
    and data.statid=lookup.statid
    and data.type=lookup.type

    )
    where obj_id= 76

    tables:
    TIMESERIES (obj_id,flag,foreign_id)
    LOOKUP(obj_id,statid,type, foreign_id)
    DATA(date,statid,type,value)

    this code need to much time to execute

    but if i write it like this -hardcoded-

    select * from
    (
    select obj_id,flag,foreign_id date, statid,type ,value from TIMESERIES, LOOKUP, DATA
    where
    Timeseries.foreign_id= 2
    and data.statid=1103
    and data.type=1

    )
    where obj_id= 76

    it need only 1 minute

    why needs first version too much time and how can i make it better. the bold written part must stay and can not be changed (outer select), but the inner select can be changed
    please help!!!!!!
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The first query joins properly (as per the data available), but the Second query is hard coded t get the value (and it provides the cartesian product by the way , the columns are not properly joined here and you may get duplicate values). Please check whether the index has been created on relevant fields.

    Btw, why dont you want to change the outer query? Anyway try the following.


    Code (SQL):
    SELECT * FROM
    (
    SELECT obj_id,flag,foreign_id DATE, statid,TYPE ,VALUE
      FROM TIMESERIES, LOOKUP, DATA
    WHERE Timeseries.obj_id =76
       AND Timeseries.foreign_id= lookup.foreign_id
       AND lookup.statid=DATA.statid
       AND lookup.TYPE =DATA.TYPE
    )
    WHERE obj_id= 76
    may be you might have to give the alias for the selected fields in the inner query.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your query doesn't run as you posted in in 11.2 as there are too many ambiguously defined columns; I have compiled an example (with modifications to your original query so it will run):

    Code (SQL):
    SQL> CREATE TABLE
      2  TIMESERIES (obj_id NUMBER,flag varchar2(2),foreign_id NUMBER);
     
    TABLE created.
     
    SQL> CREATE TABLE
      2  LOOKUP(obj_id NUMBER,statid NUMBER,TYPE NUMBER, foreign_id NUMBER);
     
    TABLE created.
     
    SQL> CREATE TABLE
      2  DATA(d_date DATE,statid NUMBER,TYPE NUMBER,VALUE varchar2(40));
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1200 loop
      3                  INSERT INTO timeseries
      4                  VALUES(i, 'Y', MOD(i, 432));
      5                  INSERT INTO lookup
      6                  VALUES(i, i, MOD(i, 5), MOD(i, 432));
      7                  INSERT INTO DATA
      8                  VALUES(sysdate, i, MOD(i, 5), 'Value');
      9          END loop;
     10
     11          commit;
     12
     13  END;
     14  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> EXEC dbms_stats.gather_schema_stats('BING');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT COUNT(*) FROM timeseries;
     
      COUNT(*)
    ----------
          1200
     
    SQL> SELECT COUNT(*) FROM lookup;
     
      COUNT(*)
    ----------
          1200
     
    SQL> SELECT COUNT(*) FROM DATA;
     
      COUNT(*)
    ----------
          1200
     
    SQL>
    SQL> SET autotrace ON
    SQL>
    SQL> SELECT * FROM
      2  (
      3          SELECT timeseries.obj_id,flag,lookup.foreign_id, d_date, lookup.statid,DATA.TYPE ,VALUE FROM TIMESERIES, LOOKUP, DATA
      4          WHERE
      5          Timeseries.foreign_id= lookup.foreign_id
      6          AND DATA.statid=lookup.statid
      7          AND DATA.TYPE=lookup.TYPE
      8  ) x
      9  WHERE x.obj_id= 76;
     
        OBJ_ID FL FOREIGN_ID D_DATE        STATID       TYPE VALUE
    ---------- -- ---------- --------- ---------- ---------- ----------------------------------------
            76 Y          76 09-MAY-12         76          1 VALUE
            76 Y          76 09-MAY-12        508          3 VALUE
            76 Y          76 09-MAY-12        940          0 VALUE

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1333314076
    ------------------------------------------------------------------------------------
    | Id  | Operation             | Name       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |            |     3 |   126 |    10  (10)| 00:00:01 |
    |*  1 |  HASH JOIN            |            |     3 |   126 |    10  (10)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL   | LOOKUP     |  1200 | 13200 |     3   (0)| 00:00:01 |
    |   3 |   MERGE JOIN CARTESIAN|            |  1200 | 37200 |     6   (0)| 00:00:01 |
    |*  4 |    TABLE ACCESS FULL  | TIMESERIES |     1 |    10 |     3   (0)| 00:00:01 |
    |   5 |    BUFFER SORT        |            |  1200 | 25200 |     3   (0)| 00:00:01 |
    |   6 |     TABLE ACCESS FULL | DATA       |  1200 | 25200 |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("TIMESERIES"."FOREIGN_ID"="LOOKUP"."FOREIGN_ID" AND
                  "DATA"."STATID"="LOOKUP"."STATID" AND "DATA"."TYPE"="LOOKUP"."TYPE")
       4 - FILTER("TIMESERIES"."OBJ_ID"=76)

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             19  consistent gets
              0  physical reads
              0  redo SIZE
            879  bytes sent via SQL*Net TO client
            419  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              1  sorts (memory)
              0  sorts (disk)
              3  ROWS processed
     
    SQL>
    SQL> CREATE INDEX timeseries_idx ON timeseries(obj_id, foreign_id);
     
    INDEX created.
     
    SQL> CREATE INDEX lookup_idx ON lookup(foreign_id, statid, TYPE);
     
    INDEX created.
     
    SQL> CREATE INDEX data_idx ON DATA(TYPE, statid);
     
    INDEX created.
     
    SQL>

    SQL> EXEC dbms_stats.gather_schema_stats('BING');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT * FROM
      2  (
      3          SELECT timeseries.obj_id,flag,lookup.foreign_id, d_date, lookup.statid,DATA.TYPE ,VALUE FROM TIMESERIES, LOOKUP, DATA
      4          WHERE
      5          Timeseries.foreign_id= lookup.foreign_id
      6          AND DATA.statid=lookup.statid
      7          AND DATA.TYPE=lookup.TYPE
      8  ) x
      9  WHERE x.obj_id= 76;
     
        OBJ_ID FL FOREIGN_ID D_DATE        STATID       TYPE VALUE
    ---------- -- ---------- --------- ---------- ---------- ----------------------------------------
            76 Y          76 09-MAY-12         76          1 VALUE
            76 Y          76 09-MAY-12        508          3 VALUE
            76 Y          76 09-MAY-12        940          0 VALUE

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 901654813
    -------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name           | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |                |     3 |   126 |     4   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                  |                |       |       |            |          |
    |   2 |   NESTED LOOPS                 |                |     3 |   126 |     4   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS                |                |     3 |    63 |     2   (0)| 00:00:01 |
    |   4 |     TABLE ACCESS BY INDEX ROWID| TIMESERIES     |     1 |    10 |     1   (0)| 00:00:01 |
    |*  5 |      INDEX RANGE SCAN          | TIMESERIES_IDX |     1 |       |     1   (0)| 00:00:01 |
    |*  6 |     INDEX RANGE SCAN           | LOOKUP_IDX     |     3 |    33 |     1   (0)| 00:00:01 |
    |*  7 |    INDEX RANGE SCAN            | DATA_IDX       |     1 |       |     1   (0)| 00:00:01 |
    |   8 |   TABLE ACCESS BY INDEX ROWID  | DATA           |     1 |    21 |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       5 - access("TIMESERIES"."OBJ_ID"=76)
       6 - access("TIMESERIES"."FOREIGN_ID"="LOOKUP"."FOREIGN_ID")
       7 - access("DATA"."TYPE"="LOOKUP"."TYPE" AND "DATA"."STATID"="LOOKUP"."STATID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             16  consistent gets
              0  physical reads
              0  redo SIZE
            879  bytes sent via SQL*Net TO client
            419  bytes received via SQL*Net FROM client
              2  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              3  ROWS processed
     
    SQL>
     
    Notice the full table scans in the first run (with no indexes) and the merge join cartesian; they are both gone in the run using the indexes.

    Possibly you should consider creating indexes on these tables.