Home » RDBMS Server » Performance Tuning » Required Index .(Urgent help)
Required Index .(Urgent help) [message #65864] Fri, 14 January 2005 06:59 Go to next message
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 Go to previous message
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?
Previous Topic: Database reports
Next Topic: row cache lock: Cache id 8 - dc tables on QUERIES.
Goto Forum:
  


Current Time: Tue Sep 29 06:19:10 CDT 2020