Re: Horrible SELECT !!!!

First the join pattern seems to be c --> s --> sfm and also rm --> talon but there seem to be a missing link between the two groups of tables eg: rm --> c

Secondly: is an index on sfm.period_id may help - but you need to investigate the number of rows and number of distinct period_id's to make sure whether an index would accomplish anything.

Cheers

Niels Jessen
Twinsoft Scandinavia A/S

Angelica Veron <Faro_at_globalserve.net> wrote in message news:3776E49E.97148556_at_globalserve.net...
> Hello Everyone,
>
> I am stuck with a somewhat complex SELECT statement, and wonder if
> anyone can come up with a streamlined version that would make it run
> quicker !.... this is the query, and it does work, only it takes
> forever to give a result !!.... is there a better way to re-write this
> query with the same results ??.... any help would be greatly
> appreciated.
>
>
> CREATE TABLE FaroTemp AS
> (SELECT s.service_id, sfm.cust_id, c.cust_birthdate, s.service_p_type,
> (sfm.serv_loan_balance + sfm.serv_investment_balance) "Total Balance",
> rm.relmgr_id
> FROM customer c, service s, service_fact_monthly sfm,
> relationship_manager rm, talon
> WHERE rm.relmgr_num = talon.relmgr_num
> AND sfm.period_id = 1617
> AND c.cust_literature_flag <> 'N'
> AND s.pers_comm_cd IS NULL
> AND (to_char(sysdate, 'YYYY') - to_char(c.cust_birthdate, 'YYYY')
> BETWEEN 18 AND 99 OR c.cust_birthdate IS NULL)
> AND s.service_id = sfm.service_id
> AND c.cust_id = s.cust_id)
> /
>
> ...perhaps if no simpler query can be generated, can this query
> itself be broken down in steps and then joined to yield the same results
> ??.... please help !
>
>
> Yours Sincerely,
> Angelica Veron
> Faro_at_globalserve.net
>
Received on Wed Jun 30 1999 - 06:58:33 CDT