| Required Index .(Urgent help) [message #65864] |
Fri, 14 January 2005 06:59  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Can anybody help me on what are the indexes required for the following tables. sorry for posting twice.
SELECT t.cyear, t.cmonth, c.ccustgrpcd, c.ccustgrp, cprodname,
prod.prodrank,
allprod.allrank
FROM timedim t, customer c, products p, yeartomnthcust yr,
(SELECT ddate, ccustgrpcd, cprodcode,
RANK() OVER (PARTITION BY ddate, cprodcode ORDER BY namtcurr desc)
PRODRANK
FROM (SELECT ddate, cprodcode, ccustgrpcd, SUM((namtcurr/nworked)
*nworkdays)
namtcurr
FROM products p, customer c, yeartomnthcust yr
WHERE ddate >= last_day(add_months(trunc(sysdate),-6))
AND c.ncustid = yr.ncustid AND p.nprodid = yr.nprodid
GROUP BY ddate, cprodcode, ccustgrpcd )) PROD,
(SELECT ddate, ccustgrpcd ,
RANK() OVER (PARTITION BY ddate ORDER BY namtcurr desc) ALLRANK
FROM (SELECT ddate, ccustgrpcd, SUM((namtcurr/nworked) *nworkdays)
namtcurr
FROM customer c, yeartomnthcust yr
WHERE ddate >= last_day(add_months(trunc(sysdate),-6))
AND c.ncustid = yr.ncustid
GROUP BY ddate, ccustgrpcd ))ALLPROD
WHERE t.ddate = yr.ddate
AND c.ncustid = yr.ncustid
AND p.nprodid = yr.nprodid
AND t.ddate = prod.ddate
AND p.cprodcode = prod.cprodcode
AND c.ccustgrpcd = prod.ccustgrpcd
AND t.ddate = allprod.ddate
AND c.ccustgrpcd = allprod.ccustgrpcd
|
|
|
|
| Re: Required Index .(Urgent help) [message #65869 is a reply to message #65864] |
Mon, 17 January 2005 03:52  |
Tony Andrews
Messages: 29
Registered: January 2005
|
Junior Member |
|
|
|
If your tables all have primary keys (which are always indexed), and if you have also indexed all the foreign keys, then you will probably have all the indexes you need for this query. What does EXPLAIN PLAN show?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
