OraFAQ Forum: Performance Tuning » Query taking long time on MVs

Home » RDBMS Server » Performance Tuning » Query taking long time on MVs (Oracle 11g, UNIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query taking long time on MVs [message #537543]

Thu, 29 December 2011 04:08

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

Report message to a moderator

Re: Query taking long time on MVs [message #537549 is a reply to message #537543]

Thu, 29 December 2011 04:37

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A normal b*tree index?

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

Regards
Michel

Report message to a moderator

Re: Query taking long time on MVs [message #537552 is a reply to message #537549]

Thu, 29 December 2011 04:47

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?

Report message to a moderator

Re: Query taking long time on MVs [message #537556 is a reply to message #537552]

Thu, 29 December 2011 05:00

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Query taking long time on MVs [message #538210 is a reply to message #537552]

Wed, 04 January 2012 12:00

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).

Report message to a moderator

Previous Topic:	Same query using different execution plans, why??
Next Topic:	Explain Plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 15:53:40 CST 2025