Home » SQL & PL/SQL » SQL & PL/SQL » inline views
inline views [message #245233] Fri, 15 June 2007 10:50 Go to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi,

some problems with inline views.

Say, I have a SQL like this

select a1, a2 from
(
select a1,a2,a3 a4 from b1, b2 where b1.c1=b2.c1 and b2.c2 in ('1','2','3')
)

Now if I create a view like

create or replace view V1 as
select a1, a2 from
(
select a1,a2,a3 a4 from b1, b2 where b1.c1=b2.c1 and b2.c2 in ('1','2','3')
)

and execute

select * from V1 where a1= 'sdf' and a2='sdfsd'
it goes for a full table scan on b1 and b2, though you have indexes..

Wheras

if given like this

select a1, a2 from
(
select a1,a2,a3 a4 from b1, b2 where b1.c1=b2.c1 and b2.c2 in ('1','2','3')
) where
a1='dsf' and
a2='dsf'

it works fast hitting the indexes..

or if I still give

select a1, a2 from
(
select a1,a2,a3 a4 from b1, b2 where b1.c1=b2.c1 and b2.c2 in ('1','2','3') and a1='dsf' and a2='dsf'
)

it is again good.

Now, my problem is , I need to outer join this result to another big SQL. and the values for a1 and a2 comes from some other tables in that "big" SQL.

How to accomplish this task in a simpler way?

/Raajesh



Re: inline views [message #245236 is a reply to message #245233] Fri, 15 June 2007 11:06 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS

In addition, post the indexes you have and explain plan.

Regards
Michel
Previous Topic: What will be maximum size of character variable
Next Topic: Advantages & Dis-advantages of Truncate & Delete...
Goto Forum:
  


Current Time: Tue Dec 06 00:27:05 CST 2016

Total time taken to generate the page: 0.10399 seconds