Home » SQL & PL/SQL » SQL & PL/SQL » about indexes
about indexes [message #227958] Fri, 30 March 2007 07:53 Go to next message
parasuramchekuri
Messages: 32
Registered: March 2007
Location: vishakapatnam
Member
hi
We are running Database on Oracle 9.2, we are created views, and data retrieving from those views are very slow,
if any possible to create indexes on views, let me tell the procedure to create the indexes on views

Thanks in Advance ...
Ram...
Re: about indexes [message #227959 is a reply to message #227958] Fri, 30 March 2007 07:57 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Indexes would be on the underlying tables. If the view is slow, then the view was poorly created based on join conditions, etc. What happens when you run the query that the view is based on?
Re: about indexes [message #227960 is a reply to message #227959] Fri, 30 March 2007 08:06 Go to previous messageGo to next message
parasuramchekuri
Messages: 32
Registered: March 2007
Location: vishakapatnam
Member
hi
actually we have to create view using nearly 17 or 18 joins
and when running the querry based on view it takes some time minmum 30 to 45 sec, how to resolve this problem
pls clarify me
Thanks in Advance...
Ram...
Re: about indexes [message #227962 is a reply to message #227960] Fri, 30 March 2007 08:17 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I can't understand from your email if the query is running well and the view isn't, or neither of them are running well. Please be as specific as possible with running times, and show the explain plan.
Re: about indexes [message #227963 is a reply to message #227960] Fri, 30 March 2007 08:19 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You still give almost no information.
We haven't seen the view, we havent seen any statistics when you query the view, we dont know the amount of data etc etc....

I you really want some help, provide more information
Re: about indexes [message #227965 is a reply to message #227958] Fri, 30 March 2007 08:23 Go to previous messageGo to next message
parasuramchekuri
Messages: 32
Registered: March 2007
Location: vishakapatnam
Member
hi
actually we joined the 17 tables, each table having nearly 20 columns and each table having nearly 1000 rows we are using view for generating reports
is this information is useful or not
Thanks
Ram
Re: about indexes [message #227967 is a reply to message #227965] Fri, 30 March 2007 08:24 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
parasuramchekuri wrote on Fri, 30 March 2007 09:23
hi
actually we joined the 17 tables, each table having nearly 20 columns and each table having nearly 1000 rows we are using view for generating reports
is this information is useful or not
Thanks
Ram


No, it's not. would it be helpful to you if someone asked this question to you?
Re: about indexes [message #227968 is a reply to message #227965] Fri, 30 March 2007 08:27 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Still cant see any explain plan nor code.....
But joy_division might be a better guesser than me Very Happy
Re: about indexes [message #228007 is a reply to message #227968] Fri, 30 March 2007 12:34 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
tahpush wrote on Fri, 30 March 2007 09:27
Still cant see any explain plan nor code.....
But joy_division might be a better guesser than me Very Happy



I'll quit while I'm ahead Wink
Too many factors in this one.
And no punctuation to boot.

[Updated on: Fri, 30 March 2007 12:35]

Report message to a moderator

Re: about indexes [message #228019 is a reply to message #228007] Fri, 30 March 2007 14:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please stick around.. I have this feeling we will see yet another date-string mix-up! Wink
Re: about indexes [message #228032 is a reply to message #227967] Fri, 30 March 2007 16:17 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
joy_division wrote on Fri, 30 March 2007 14:24
No, it's not. would it be helpful to you if someone asked this question to you?

I have a query that joins 18 tables, and it's slow. How do I solve this problem?
Re: about indexes [message #228062 is a reply to message #228032] Sat, 31 March 2007 02:16 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
create index on table cloumns which is use on that query and u will get better performance
Re: about indexes [message #228063 is a reply to message #228062] Sat, 31 March 2007 02:31 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
.. and you will end up with indexes on all columns of all tables and a lousy performance.
Previous Topic: please help with statistics!
Next Topic: sql help( how can use floor and ceil)
Goto Forum:
  


Current Time: Fri Dec 09 17:42:20 CST 2016

Total time taken to generate the page: 0.43141 seconds