Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: Tuning the Query..

    Hello Oracle DBAs,
    We are using oracle8.1.6 on hp-ux11.0.
    I got a problem here..One of the sql statments is running very slowly..and when I observed there are no indexes defined I defined indexes for all the columns used in where clause(the stmt. joins 6 tables).
    but still it is taking the same amount of time to execute(aprox.10min).
    when I checked the cost in plantable it shows returns two rows with same value(44).
    How do I know whether indexes are being used or not and if not then how to make them use.
    Please help me in this regard.

    Here is the query...
    SELECT ......
    FROM JCS_VACANCY VAC, JCS_EMPLOYER EMP, JCS_JOBSEEKER JS
    , JCS_JOBSEEKERQUALIFICATION APP, jcs_masco_occupation_group mo,
    jcs_msic_sub_division ms
    WHERE VAC.LOGINID = EMP.LOGINID AND
    JS.LOGINID = :1 AND
    vac.positionid = mo.mascooccupid AND
    vac.mascomajorid = mo.mascomajorid AND
    emp.msicmainid = ms.msicmainid AND
    emp.msicsubid = ms.msicsubid AND
    vac.vacancystatus <> 'C' AND vac.vacancystatus <> 'c' AND
    emp.empstatus = 'AU' AND (emp.accstatus = 'A' OR
    emp.accstatus = 'a') AND ((vac.qualificationid = 'A0810' AND
    vac.subqualificationid = 'A0820 '))

    Thanks in advance.
    sridhar

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    First make sure you analyze your tables and indexes. NOTE indexes dont always speed things up, sometimes a FTS is faster.
    Second look at the explain plan to see if they are being used. (do a search on google for oracle explain plan).

    Alan

Posting Permissions

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