Retrieving results through views

[Skanda Raman]

i am working on legacy application having the two views as A and B.

View A looks as below

A
===========
ID || DATE || AMT

View B looks like below

B
===========
ID || DATE || AMT


The query written in view C is some thing as below

select A.ID, A.DATE, A.AMT, B.AMT from A, B
where A.ID=B.ID(+) and A.DATE=B.DATE(+)
UNION
select ID,DATE,0,ID,AMT from B where DATE NOT IN(SELECT DATE FROM A)

There are around 1Lakh records in view A and View B.

While i run this module having the view C, it takes more than 3 hrs to retrieve the results.

If i run this query in TOAD it takes 2.45 hrs to give the output.

Please advice me if i can tune this in any other way.

Please suggest.

[Praison Selvaraj]

Hi Eshwar,

1. Is the Id column the primary key in Table A and id column in table B the corresponding foreign key?
2. Is the date column part of the key? If not create an index on the date column.

Cheers,
Praison

[Skanda Raman]

thanks selva for quick reply

both A and B are different views

Not sure if ID is primary key in both views. How could i find it. Can views also have primary key. sorry for basic question, please let me know.

ID in view B is not a foreign key, as both these views are totally different.

Date is not part of the key, so i can create a index. thanks for that suggestion. However, can we have index to the views as well. please let me know

However, while you notice this query, there are three conditions getting set while the developer wrote this.

a) He needs all the records in A
b) He needs all the records that are common in A and B, hence he uses the right join (+)
c) He needs all the records in B for the date that is not matching in A

From this I was thinking, if the query could be rewtitten in some way, so that the performance could be tuned well.

Your suggestions would be helpful

Thanks

[Praison Selvaraj]

Hi,

I meant, whether the underlying tables have indexes?

Create the indexes and check if the indexes are making the query to execute any faster?

Thanks,
Praison

[Skanda Raman]

Thanks selva

I checked that underlying table have indexes.

However, my module uses only the query having views.

Could i create indexes for view as well.

Here, while i tested the query, NOT IN(SELECT DATE FROM A) takes more time than rest.