Home » SQL & PL/SQL » SQL & PL/SQL » Creating Indexes in Views
Creating Indexes in Views [message #424872] Tue, 06 October 2009 06:32 Go to next message
ShaShalini
Messages: 59
Registered: January 2007
Member
Hello

I have a question regarding Views. My question is Can we create index on views ?

But after reading a few posts in your forum I came to know that it is not possible . But may be you can give me an idea how I can solve my problem.

I have a view created with unions of 4 different tables. And querying the view takes a lot of time as there are many rows.

Can you give me an idea how can I query my view .

Thanks
Re: Creating Indexes in Views [message #424873 is a reply to message #424872] Tue, 06 October 2009 06:36 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

Create indexes on views its not possible but paste your query over here .. lets try to optimize it.

Regards

Bala
Re: Creating Indexes in Views [message #424887 is a reply to message #424872] Tue, 06 October 2009 07:14 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But, you can still create indexes on underlying tables.
Re: Creating Indexes in Views [message #424891 is a reply to message #424872] Tue, 06 October 2009 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or you could possibly create a materialized view, which you can index. It depends on your requirement which we don't know.
Re: Creating Indexes in Views [message #424893 is a reply to message #424872] Tue, 06 October 2009 07:37 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

I have a view created with unions of 4 different tables. And querying the view takes a lot of time as there are many rows.




Please provide your sql query here.
Also it would be good to get a quick solution if you can provide some more info like

As Littlefoot sir pointed out what are the indexes on underlying tables

The show plan

How big the tables are

Make sure that Stats are upto date (Can have information from LAST_ANALYZED column of user_tables for the tables used in the View)

If you are not bothered about duplicates or you are sure that there will be no duplicates you can use UNION ALL instead of UNION

Thanks

[Updated on: Tue, 06 October 2009 07:40]

Report message to a moderator

Previous Topic: Getting duplicate records
Next Topic: CASE/DECODE
Goto Forum:
  


Current Time: Fri Sep 30 05:29:15 CDT 2016

Total time taken to generate the page: 0.07835 seconds