Outer Join, Self Join rewrite

I am hoping to place an index on a view. Among other things that would
prevent this from happening, the view has both an outer join and a self join
.
Is there any way to rewrite an outer join and self join, so that the view
becomes indexable? This is a sample of my current outer join and self join a
s
used on the view:
FROM dbo.CarCodes AS CC
LEFT OUTER JOIN dbo.CarCodes AS CC_CC
ON CC_CC.CarCodeID= CC.CarCodeFID
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1Hi
A re-write would require a UNION which would also preclude it from having an
index, therefore I don't think it would be possible to include the given
table in a view.
If you are checking for non-existance ie. there is also a where clause with
CC_CC.CarCodeID IS NOT NULL, then you can change the outer join to an inner
join.
You may want to post full DDL and the underlying tables to show all the code
.
John
"cbrichards via droptable.com" wrote:

> I am hoping to place an index on a view. Among other things that would
> prevent this from happening, the view has both an outer join and a self jo
in.
> Is there any way to rewrite an outer join and self join, so that the view
> becomes indexable? This is a sample of my current outer join and self join
as
> used on the view:
> FROM dbo.CarCodes AS CC
> LEFT OUTER JOIN dbo.CarCodes AS CC_CC
> ON CC_CC.CarCodeID= CC.CarCodeFID
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200607/1
>|||So, to rewrite the outer join, it would go something like this?
FROM dbo.CarCodes AS CC
JOIN dbo.CarCodes AS CC_CC
ON CC_CC.CarCodeID= CC.CarCodeFID
WHERE CC_CC.CarCodeID IS NOT NULL
John Bell wrote:[vbcol=seagreen]
>Hi
>A re-write would require a UNION which would also preclude it from having a
n
>index, therefore I don't think it would be possible to include the given
>table in a view.
>If you are checking for non-existance ie. there is also a where clause with
>CC_CC.CarCodeID IS NOT NULL, then you can change the outer join to an inner
>join.
>You may want to post full DDL and the underlying tables to show all the cod
e.
>John
>
>[quoted text clipped - 5 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||Hi
That was not what I was saying. Without seeing the full statement it is not
totally possible to rule out that it could be re-written, the example was a
showing something in which the outer join would have be incorrectly used.
Even knowing the whole statement would not rule out poor table design and
other improvements that may have removed the need for the index on the view.
John
"cbrichards via droptable.com" wrote:

> So, to rewrite the outer join, it would go something like this?
> FROM dbo.CarCodes AS CC
> JOIN dbo.CarCodes AS CC_CC
> ON CC_CC.CarCodeID= CC.CarCodeFID
> WHERE CC_CC.CarCodeID IS NOT NULL
>
>
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200607/1
>|||Hi
I have just noticed that it was a self-join, this would also preclude an
index being created.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> That was not what I was saying. Without seeing the full statement it is no
t
> totally possible to rule out that it could be re-written, the example was
a
> showing something in which the outer join would have be incorrectly used.
> Even knowing the whole statement would not rule out poor table design and
> other improvements that may have removed the need for the index on the vie
w.
> John
> "cbrichards via droptable.com" wrote:
>|||John,
Let's say the full View statement is the following (Please Note: I have
removed the self join):
SELECT cod.CarCode
FROM CarCode cod
LEFT JOIN CarColor col
ON cod.CarCodeID = col.CarCodeFID
WHERE col.CarCodeFID IS NULL
Is there any way to turn this Left Join into an Inner Join, or in other word
s,
is there any way to rewrite this sample View statement to make it indexable?
John Bell wrote:[vbcol=seagreen]
>Hi
>That was not what I was saying. Without seeing the full statement it is not
>totally possible to rule out that it could be re-written, the example was a
>showing something in which the outer join would have be incorrectly used.
>Even knowing the whole statement would not rule out poor table design and
>other improvements that may have removed the need for the index on the view
.
>John
>
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||Hi
Your statement is testing for the non-existance of a corresponding row in
CarColor
An equivaent of this without the join would require a subquery such as
SELECT cod.CarCode
FROM dbo.CarCode cod
WHERE NOT EXISTS ( SELECT CarCodeFID FROM dbo.CarColor col WHERE
cod.CarCodeID = col.CarCodeFID )
Which is also NOT allowed for creating an indexed view. Therefore for your
view it would not be possible to add an index.
If you re-worked your database design so that there is a trigger that will
set set CarCodeID to NULL when a CarColor was deleted from CarColor then yo
u
could use
CREATE VIEW vw_Transparent_Cars WITH SCHEMABINDING AS
SELECT cod.CarCode
FROM dbo.CarCode cod
WHERE cod.CarCodeID IS NULL
CREATE UNIQUE CLUSTERED INDEX idx_vw_Transparent_Cars ON vw_Transparent_Cars
( CarCode )
HTH
John
"cbrichards via droptable.com" wrote:

> John,
> Let's say the full View statement is the following (Please Note: I have
> removed the self join):
> SELECT cod.CarCode
> FROM CarCode cod
> LEFT JOIN CarColor col
> ON cod.CarCodeID = col.CarCodeFID
> WHERE col.CarCodeFID IS NULL
> Is there any way to turn this Left Join into an Inner Join, or in other wo
rds,
> is there any way to rewrite this sample View statement to make it indexabl
e?
> John Bell wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200607/1
>|||> Let's say the full View statement is the following (Please Note: I have
> removed the self join):
> SELECT cod.CarCode
> FROM CarCode cod
> LEFT JOIN CarColor col
> ON cod.CarCodeID = col.CarCodeFID
> WHERE col.CarCodeFID IS NULL
> Is there any way to turn this Left Join into an Inner Join, or in other
> words,
> is there any way to rewrite this sample View statement to make it
> indexable?
This query effectively becomes an inner join - just change "LEFT" to "INNER"
and remove the WHERE clause. However, you are most likely oversimplifying
the problem. Without knowing exactly what you are trying to accomplish and
without knowing what your tables (and therefore, your data model) are trying
to represent, it is unlikely that we will be able to adequately respond.
It looks like, per your original post, that you have a two level hierarchy
of carcodes. This can be easily to migrated to a two table design, making
your originally intended view indexable. This is effectively what you have
posted above - but you still are relying upon an outer join. To avoid the
outer join (and still return a result set that "looks" like the outer joined
one), you need to force at least one row into the child table; this is a row
composed solely of PK/FK values and NULLS (or blanks) for all other columns.
A bad design but that is the price for the technique you are trying to use.
Perhaps you should take a different approach and attempt to determine if you
will gain anything significant from indexing this view. It may also be
that this particular technique is not the one best suited to solving your
problem.
One last note - the above query **APPEARS** to assume that there is at most
a 1-to-1 relationship between carcode and carcolor. This is based on the
fact that a 1-to-many relationship would create a resultset that contains
duplicate rows. This should help illustrate why it is important to
understand the data model and what it represents before one attempts to
write queries that are consistent with the model. If we (well - you
actually) rely on this assumption, then that reliance should be captured in
the data model as an enforced constraint on the carcolor table, preventing
the insertion of multiple rows with the same carcodefid value.|||>> SELECT cod.CarCode[vbcol=seagreen]
Oops - could have sworn that said "is not null". That's what happens looking
at two different posts simultaneously. Therefore, ignore the comments about
the "change left to inner blah blah blah". The others still stand though.
Without destroying the model and creating a bad schema, indexing doesn't
look like a viable option.|||Hi
Just to add...
.. with SQL 2005 you will not need a trigger to set nullability as a new
cascade SET NULL option is available.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Your statement is testing for the non-existance of a corresponding row in
> CarColor
> An equivaent of this without the join would require a subquery such as
> SELECT cod.CarCode
> FROM dbo.CarCode cod
> WHERE NOT EXISTS ( SELECT CarCodeFID FROM dbo.CarColor col WHERE
> cod.CarCodeID = col.CarCodeFID )
> Which is also NOT allowed for creating an indexed view. Therefore for your
> view it would not be possible to add an index.
> If you re-worked your database design so that there is a trigger that will
> set set CarCodeID to NULL when a CarColor was deleted from CarColor then
you
> could use
> CREATE VIEW vw_Transparent_Cars WITH SCHEMABINDING AS
> SELECT cod.CarCode
> FROM dbo.CarCode cod
> WHERE cod.CarCodeID IS NULL
> CREATE UNIQUE CLUSTERED INDEX idx_vw_Transparent_Cars ON vw_Transparent_Ca
rs
> ( CarCode )
> HTH
> John
> "cbrichards via droptable.com" wrote:
>