Please help speed up queries

[sql99]

Hello everyone,

I have a create view script that consists of 5 select statements similar to the one below. The select below takes about 40 seconds to run. Is there anything I can do to speed up these queries? From my understanding, I cannot use indexes on views? I appreciate any help I can get on this.

SELECT pur.pur_num, pur.pur_date, pers.lastname, pers.firstname, item.pers_id, item.item_date,
item.item_no, ord.order_id, pur.price
FROM person pers, items item, purchases pur, schema1.order@schema ord
WHERE item.item_no = 'TRT'
AND ord.order_id in ('ABC', 'BCD', 'CDE', 'FGH', 'GHI')
AND ord.order_stop_date = to_date('09/09/9999', 'mm/dd/yyyy')
AND pur.state in ('NEW', 'OLD')
AND pur.confirm = 'YES'
AND pur.price > '0.00'
AND pers.pers_id = item.pers_id
AND item.pers_id = pur.pers_id
AND item.item_date = pur.item_date
AND item.pur_num = pur.pur_num
AND pur.pers_id = ord.person_no


Thanks in advance,
sql99

 

[SantaMufasa]

SQL99,

Although you cannot put an index on a VIEW, you can place the index you would have used for the VIEW onto the column(s) of the underlying table(s). Without knowing the volumes, distributions, and percentages of data returned from your query(ies), I cannot give much more "remote support" than what is here.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:09 (07Dec04) UTC (aka "GMT" and "Zulu"),
@ 14:09 (07Dec04) Mountain Time

 

[sql99]

Thanks Dave. I do realize that it's difficult to assist w/o having all the details....thanks again! I did manage to play around with soem of the existing indexes and it did improve the performance....:0)

 

[sbix]

keep in mind:
if pur.confirm could be only YES or NOT and pur.state could be only NEW or OLD then you can use bitmap indexes on those fields