Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    52

    Question Unanswered: How to increase the speed of a query

    Hi al friends


    I have two tables: t1 & t2.

    t1 contains about 1 million registers and 16 fields
    t2 contains about 200,000 registers and 16 fields

    and i have a the next query:


    SELECT
    T1.f1,T1.f2,T1.f3,T1.f4 ,TRUNC(T1.f5) f5,T1.f6,
    TRUNC(T1.f7) f7,TRUNC(T1.f8) f8,TRUNC(T1.f9) f9,TRUNC(T1.f10) f10,
    TRUNC(T1.f111) f11,TRUNC(T1.f12) f12,TRUNC(T1.f13) f13,TRUNC(T1.f14) f14,
    T2.f15,T2.f16 || ' ' || T2.f17 || ' ' || T2.f18 AS field19,
    T2.f20
    FROM
    table1 T1,
    table2 T2
    WHERE
    T2.idcommon = T1.idcommon
    AND condition1
    AND coondition2


    but the problem is that it last too much time, about 50 minuites,

    so my question is:

    is there a better way to make the query??? i mean i wanna to decrease the time that the query lasts.

    Any ideas????
    Could you give me any advice????


    I hope you can help me.


    Regards!!!

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    what's the explain plan?

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Make sure ur table columns are index i.e. Columns ur are using in the where clasue of the query. Explain gives better idea of how oracle is executing the query. So if you post explain plan, we could give u better solution. To use explain plain u should have Plan_table in ur schema.
    U can used below query to find the explain plan of any query

    set echo on

    delete from plan_table
    where statement_id = 'MINE';
    commit;
    COL operation FORMAT A30
    COL options FORMAT A15
    COL object_name FORMAT A20
    EXPLAIN PLAN set statement_id = 'MINE' for
    Select * from emp where ename='SCOTT';
    set echo off
    select operation, options, object_name
    from plan_table
    where statement_id = 'MINE'
    start with id = 0
    connect by prior id=parent_id and prior statement_id = statement_id;
    set echo on
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Feb 2004
    Location
    Viet Nam
    Posts
    6

    Re: How to increase the speed of a query

    SELECT
    T1.f1,T1.f2,T1.f3,T1.f4 ,TRUNC(T1.f5) f5,T1.f6,
    TRUNC(T1.f7) f7,TRUNC(T1.f8) f8,TRUNC(T1.f9) f9,TRUNC(T1.f10) f10,
    TRUNC(T1.f111) f11,TRUNC(T1.f12) f12,TRUNC(T1.f13) f13,TRUNC(T1.f14) f14,
    T2.f15,T2.f16 || ' ' || T2.f17 || ' ' || T2.f18 AS field19,
    T2.f20
    FROM
    (select * from table1 where condition1) T1,
    (select * from table2 where condition2) T2
    WHERE
    T2.idcommon = T1.idcommon


    may be help you
    create index on field in condition1 and condition2

  5. #5
    Join Date
    Feb 2004
    Posts
    5
    As I am in a good mood...

    Use hints!

    And don't forget to analyze your tables!

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by rslegers
    As I am in a good mood...

    Use hints!

    And don't forget to analyze your tables!
    just use the CBO.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to increase the speed of a query

    Please post your real SQL.

    If "condition1" is "datecol >= TRUNC(SYSDATE)" that is very different from "TRUNC(datecol) = TRUNC(SYSDATE)" for example. We may be able to suggest improvements.

Posting Permissions

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