Home » RDBMS Server » Performance Tuning » Query taking long time on MVs (Oracle 11g, UNIX)
Query taking long time on MVs [message #537543] Thu, 29 December 2011 04:08 Go to next message
ind9
Messages: 65
Registered: January 2009
Member
Dear All,
Let me take this opportunity to say Happy new year in advance to all...

I have an issue in materailized view which has got one of the nullable column and query on this column taking approximately 2 mins where as other indexed columns takes less than 10 sec.

Here is the summary
SQL> Select Count (1), Count (VAT_NO) From Mv_customer;

  COUNT(1)         COUNT(VAT_NO)
---------------------------------
   2893561            1516



If an index is created on VAT_NO will that improve the performance. If so please suggest what kind of index can be created considering very less number of records has got VAT_NO

Thank you in advance to all.

BR, Ind
Re: Query taking long time on MVs [message #537549 is a reply to message #537543] Thu, 29 December 2011 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A normal b*tree index?

Do NOT use count(1) use COUNT(*).

Regards
Michel
Re: Query taking long time on MVs [message #537552 is a reply to message #537549] Thu, 29 December 2011 04:47 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Thanks for your response.
But creating indexes on columns which consists of less number of not null values are not recommendable isn't it?
Re: Query taking long time on MVs [message #537556 is a reply to message #537552] Thu, 29 December 2011 05:00 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
If an index is created on VAT_NO will that improve the performance

The simplest way to find that out would be to create an index and see what happens.
Re: Query taking long time on MVs [message #538210 is a reply to message #537552] Wed, 04 January 2012 12:00 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You can create an index on two columns (on vat_no and on one of not null columns).
Previous Topic: Same query using different execution plans, why??
Next Topic: Explain Plan
Goto Forum:
  


Current Time: Thu Mar 28 11:38:26 CDT 2024