| Tune the query [message #486465] |
Wed, 15 December 2010 15:08  |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
The following query is taking neraly 6 minutes.
Can you please help me to tune this query
SELECT
a.empno,
c.phtp,
c.ccode,
c.city,
c.phone
FROM b,
a,
c,
d,
e,
f
WHERE c.empid = a.empno
AND TRUNC (c.dt) = (SELECT TRUNC (MAX (dt))
FROM c)
AND c.phtp IN ('MOB','LAN','WTK')
AND b.plid(+) = a.plid
AND a.empno = d.empno
AND c.status = 'G'
AND d.empno = e.empid
and b.cd=f.cd
ORDER BY a.empno
The table A is having 18856 records.
The table B is having 2702 records.
The table C is having 322067 records.
The table D is having 18856 records.
The table E is having 19721 records.
The table F is having 239 records.
c.phtp IN ('MOB','LAN','WTK') is retrieveing 128908
c.status is having total 322067 records with the values only "G" and "H"
WHERE status ='H' is having 52171 records.
WHERE STATUS='G' is having 269896 records.
The following are the indexes are available.
TABLE_NAME COLUMN_NAME
A EMPNO
B PLID
C EMPID
C EMPID
C PHTP
C PHTP
F CD
F CD
If we craete Bitmap index on c.status column ,
can performance will be improved?
It is(c.status) having only the values "G" and "H" for "G" 269896 records,for "H" 52171 records.
Could you please check whether the Driving tables and Driver tables are postioned correctly or not.
Because I am not having much idea on driver and drivining table.
Please help me to improve the performance of this query.
Thanks in advance
|
|
|
|
|
|
| Re: Tune the query [message #486472 is a reply to message #486467] |
Wed, 15 December 2010 17:05  |
ajaykumarkona
Messages: 399
Registered: August 2010
|
Senior Member |
|
|
c.status is having only the values "G" and "H" for "G" 269896 records,for "H" 52171 records.
Is it right way to create Bitmap Index on this column.
|
|
|
|
|
|
| Re: Tune the query [message #486474 is a reply to message #486473] |
Wed, 15 December 2010 17:17 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What does this give:
SELECT COUNT(*) from c
WHERE TRUNC (c.dt) = (SELECT TRUNC (MAX (dt))
FROM c)
AND c.phtp IN ('MOB','LAN','WTK')
AND c.status = 'G';
Why is the outer-join there? Why optionally join to a table you select no data from?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
