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...
FROM JCS_VACANCY VAC, JCS_EMPLOYER EMP, JCS_JOBSEEKER JS
, JCS_JOBSEEKERQUALIFICATION APP, jcs_masco_occupation_group mo,
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 '))
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).