help in finding the problem. H E L P !!!

[Gopal]

Hello: I am new to oracle, and I am trying to maintain some Oralce procedures written here. The problem I am having is that:

1) I created a view on 2 tables. This works fine.
2) when I do a select * from the view where rownum <2 just to see if the view works, the select runs for more than 3 to 4 hours and still does not return any values.

Can some guide me as to what to look for please.

Thanks in advance for any help.

Gopal

[pwoneill]

How large are the tables? Have you tried just running the query with a wrapper for the rownum? Eg

select * from (select for view) where rownum < 2;

What is the statement you used to create the view?

Possibly you didn't join the tables correctly and it's doing a cartesian product. If the tables are very large it could take that long.

[sonaliak]

Can you do select count(*) from view; just to see how many records does it have ?

[chrisrlong]

I'm not sure, but if he can't run the SELECT for ROWNUM < 2, then doing a full blown SELECT is unlikely to work as well.

It sounds like your view is terribly non-optimized :) and what you really need help with is fixing your view.

In order to do *that*, we will need the actual SELECT statement behind the view, and some stats on the tables involved wouldn't hurt either.

- Chris

[pwoneill]

Yeah I figured it wouldn't make a difference but throwing out things to try :)