Re: Horrible SELECT !!!!

to_char(sysdate, 'YYYY') - to_char(c.cust_birthdate, 'YYYY') BETWEEN 18 AND 99 OR c.cust_birthdate IS NULL

For Oracle 7.*, The upper sentence will not use the index even if there is an index including column c.cust_birthdate. For Oracle 8 you can create a function index. But I think it is not neccessary here. Just change this part as
c.cust_birhtday between add_months(sysdate, 18 * 12) and add_months(sysdate, 99 * 12)

Leo Van Nieuwenhuyse wrote in message ...
>On wich columns are the indexes?
>Did you run statistics?
>You can improve the between dates part using
>Between to_date('19000101','YYYYMMDD') and to_date('19820101','YYYYMMDD')
>
>Angelica Veron <Faro_at_globalserve.net> schreef in berichtnieuws
>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 Mon Jun 28 1999 - 11:18:25 CEST