• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Retrieving results through views

 
Ranch Hand
Posts: 205
Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 205
Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 205
Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic