Very Computer

I wonder is it possible (and how exactly if it is possible) to make a view
witch is joining two tables and returns the fields from them,
and then when you use this view it will not use the join if you just ask for
the fields out of one of the tables.
I don't know how join like this should looks like .. but basically the idea
is that:

create table t1(k1 int, f1 int)
create table t2(k1 int, f2 int)
create view v1 as select t1.k1,t1.f1,t2,f2 from t1 inner join t2 on t1.k1 =
t2.k1

and then ...

select f1 from v1 where k1=1

.. will have execution plan hitting only t1 since f1 is a field from t1 (and
witch assumption that the query can be satisfied only with t1)

select f2 from v1 where k1=1

.. will have execution plan hitting only t2 ...

I know that mechanism similar like this exists into the server query/plan
builder for picking up the right index for the query .. and if there is
index witch will completely satisfy the query it will be the only object
used into the execution plan.

any ideas?

Nope.

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group

Quote:> I wonder is it possible (and how exactly if it is possible) to make a view
> witch is joining two tables and returns the fields from them,
> and then when you use this view it will not use the join if you just ask
for
> the fields out of one of the tables.
> I don't know how join like this should looks like .. but basically the
idea
> is that:

> create table t1(k1 int, f1 int)
> create table t2(k1 int, f2 int)
> create view v1 as select t1.k1,t1.f1,t2,f2 from t1 inner join t2 on t1.k1
=
> t2.k1

> and then ...

> select f1 from v1 where k1=1

> .. will have execution plan hitting only t1 since f1 is a field from t1
(and
> witch assumption that the query can be satisfied only with t1)

> select f2 from v1 where k1=1

> .. will have execution plan hitting only t2 ...

> I know that mechanism similar like this exists into the server query/plan
> builder for picking up the right index for the query .. and if there is
> index witch will completely satisfy the query it will be the only object
> used into the execution plan.

> any ideas?

Quote:> I wonder is it possible (and how exactly if it is possible) to make a view
> witch is joining two tables and returns the fields from them,
> and then when you use this view it will not use the join if you just ask
for
> the fields out of one of the tables.

...

I guess the big question is why?  If you only need fields from the one
table, then why not select just from the table and skip the view?  If the
concern is performance on the join, why not just optimize for the join and
when the records are not needed, the performance will be the same
regardless.

Why don't you try it and see?  The Query Analyzer will show you the
execution path and you'll be able to tell if there's a join or not.  In
principle, views are expanded as though their SQL were used in their place,
so the query analyzer should see a query in which you are only asking for
columns from one table out of a two table join.  Unfortunately, this is
different than asking for those same columns out of that one table (the join
could produce more rows), so the analyzer should show you a join.  I can see
why you want to do this (consolidated management), but I don't think what
you want to do is possible.

Quote:> I wonder is it possible (and how exactly if it is possible) to make a view
> witch is joining two tables and returns the fields from them,
> and then when you use this view it will not use the join if you just ask
for
> the fields out of one of the tables.
> I don't know how join like this should looks like .. but basically the
idea
> is that:

> create table t1(k1 int, f1 int)
> create table t2(k1 int, f2 int)
> create view v1 as select t1.k1,t1.f1,t2,f2 from t1 inner join t2 on t1.k1
=
> t2.k1

> and then ...

> select f1 from v1 where k1=1

> .. will have execution plan hitting only t1 since f1 is a field from t1
(and
> witch assumption that the query can be satisfied only with t1)

> select f2 from v1 where k1=1

> .. will have execution plan hitting only t2 ...

> I know that mechanism similar like this exists into the server query/plan
> builder for picking up the right index for the query .. and if there is
> index witch will completely satisfy the query it will be the only object
> used into the execution plan.

> any ideas?