Database

tonyong wrote:

I am not sure of the indexes on the tables as this is a company database and I am actually a student on attachment and the company ask me to try this

SELECT ppar_engparameters.parmval, ppar_engparameters.parmname,
    prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag,
    prcd.prodstatis, catg_pll.catnumber, catg_pll.category
FROM ppar_engparameters
INNER JOIN prcd
  ON prcd.prcdname = ppar_engparameters.prcdname
  AND prcd.activeflag = 'A'
LEFT OUTER JOIN catg_pll
  ON catg_pll.partprcdname = prcd.prcdname
  AND catg_pll.partprcdversion  = prcd.prcdversion
WHERE ppar_engparameters.parmval= 'MyValue'

--For the link from product-parameter to product.
create unique index prcd_idx1 on prcd (prcdname, prcdversion)
--For the link from product.
create index catg_pll_idx1 on catg_pll (partprcdname, partprcdversion)
--For the search on parameter values.
create index ppar_engparameters_idx1 on ppar_engparameters
     (parmval, activeflag)

select distinct ppar_engparameters.parmval, ppar_engparameters.parmname,<br />
prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag, prcd.prodstatus, <br />
catg_pll.catgnumber, catg_pll.category<br />
from ppar_engparameters<br />
inner join prcd<br />
on prcd.prcdname = ppar_engparameters.prcdname and prcd.activeflag='A'<br />
inner join catg_pll<br />
on catg_pll.partprcdname = ppar_engparameters.prcdname and catg_pll.partprcdversion = <br />
ppar_engparameters.prcdversion<br />
where ppar_engparameters.parmval='MyValue'

<br />
SELECT catnumber, category <br />
FROM catg_pll<br />
where catg_pll.partprcdname = prcd.prcdname AND catg_pll.partprcdversion = prcd.prcdversion <br />

Johan Lombaard wrote:

Has anyone seen this ?