Home » RDBMS Server » Performance Tuning » Materialized view to improve Search queries (Oracle 10g R2 on RHEL)
Materialized view to improve Search queries [message #541380] Tue, 31 January 2012 01:03 Go to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello

In search queries generally we select 10-25 columns (more can't be displayed on the screen) from 5-10 tables

Say in case of insurance related application, the search might be on policy number, policy holder's first name, policy holder's last name, region, policy type etc.

And not to many columns we are displaying on the screen, say, 4 tables have collectively 4 * 20 = 80 columns, then we are displaying say 12-15 columns with 2-3 columns have aggregates on it.

since the search criteria (e.g. first name, last name, policy number etc.) is not known till last moment it will be a generic dynamic query

Is it possible that instead we create a Materialized view with query with only joining conditions but no filter conditions and selecting only columns to be displayed on the screen and then we will refresh the materialized view (to take care of recent business transactions) and fire refined query with filter criteria on this materialized view

Select col1,col2,col3,col4,col5
From tab1,tab2,tab3,tab4
Where tab1.col1=tab2.col1
And tab2.col2=tab3.col2
And tab2.col2=tab4.col2;

Will it help improve performance of the search functionality

Thanks in Advance

Regards
OraPratap
Re: Materialized view to improve Search queries [message #541391 is a reply to message #541380] Tue, 31 January 2012 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58635
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is likely it will, however you have to benchmark it.

Regards
Michel
Re: Materialized view to improve Search queries [message #541653 is a reply to message #541391] Wed, 01 February 2012 10:43 Go to previous message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Thanks for the reply

Now I can try it without much doubt in mind and will post the results here


Thanks and Regards
OraPratap
Previous Topic: How to find Literal SQL statement
Next Topic: What info should be gathered while doing sql tuning?
Goto Forum:
  


Current Time: Thu Jul 31 14:17:56 CDT 2014

Total time taken to generate the page: 0.06384 seconds