> Does anyone know the correct synatx to the option query
> hint keep plan BOL
Try this:
SELECT mycolumns
FROM mytable
WHERE mycondition = true
OPTION (KEEP PLAN)
> God bless it is sometimes sparce in its explanations.
Yes, but it IS complete - that is, if you follow the information carefully
enough, it tells you everything you need to know, usually. For example, in
this case, you found the information for the query hint itself. Then, to
find out how to use that option within a SELECT statement, you have to read
the SELECT syntax:
SELECT statement ::=
< query_expression >
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]
[ BY expression [ ,...n ] ]
]
[ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }
[ , XMLDATA ]
[ , ELEMENTS ]
[ , BINARY base64 ]
}
]
[ OPTION ( < query_hint > [ ,...n ]) ]
< query expression > ::=
{ < query specification > | ( < query expression > ) }
[ UNION [ ALL ] < query specification | ( < query expression > ) [...n ]
]
< query specification > ::=
SELECT [ ALL | DISTINCT ]
[ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { < table_source > } [ ,...n ] ]
[ WHERE < search_condition > ]
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
[ WITH { CUBE | ROLLUP } ]
]
[ HAVING < search_condition > ]
You'll notice that in the first section, you've got your SELECT, followed by
< query_expression >, which is resolved in the second section, followed by
optional ORDER BY, COMPUTE, FOR and OPTION clauses. The second section uses
the third section to resolve itself.
Finally, you might want to test performance after using this option, since
usually the database does a pretty good job of optimizing queries; this is
true for most hints, I think. Quoth BOL:
"Caution: Because the query optimizer usually selects the best execution
plan for a query, it is recommended that <join_hint>, <query_hint>, and
<table_hint> be used only as a last resort by experienced database
administrators."
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
