replacing join operation in XML

**Erland Sommarskog** · Feb 2 '07, 11:35 PM

Re: replacing join operation in XML

(Preeti.s83@gma il.com) writes:

is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect....
>
for this we need to understand the way join information get stored in
xml ....and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
>
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...

You don't say what the purpose would be to change the XML document. When
you talk about "join information get stored in xml" I get a bit nervous.
The XML document is just a representation of the query plan; it's not
a storage of its own.

That said, there is a point with retrieving a query plan and modify it
since you can use it in a plan guide, or with the query hint USE PLAN.
This is quite an advanced feature, and requires good understanding
of query plans to be successful. There is no risk that you will
cause incorrect results with a plan guide, the optimizer still
validates that the plan is correct, in which case it discards the
plan.

I have tried this sort of operation myself, and all I can recommend
is that you look at plans of the type you want to achieve and
play around. It will probably take some time, but you learn a lot
along the way. To get started, you can use query hints to force a
certain type of join, so you get to see different types of joins.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

replacing join operation in XML

replacing join operation in XML

Comment