106 lines
5.5 KiB
HTML
106 lines
5.5 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="MQT supported function" />
|
||
|
|
<meta name="abstract" content="Although a MQT can contain almost any query, the optimizer only supports a limited set of query functions when matching MQTs to user specified queries. The user specified query and the MQT query must both be supported by the SQE optimizer." />
|
||
|
|
<meta name="description" content="Although a MQT can contain almost any query, the optimizer only supports a limited set of query functions when matching MQTs to user specified queries. The user specified query and the MQT query must both be supported by the SQE optimizer." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqmqt.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqdispatcher.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqmqtalgorithm.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rzajqmqtsupport" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>MQT supported function</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqmqtsupport"><a name="rzajqmqtsupport"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">MQT supported function</h1>
|
||
|
|
<div><p>Although a MQT can contain almost any query, the optimizer only
|
||
|
|
supports a limited set of query functions when matching MQTs to user specified
|
||
|
|
queries. The user specified query and the MQT query must both be supported
|
||
|
|
by the SQE optimizer.</p>
|
||
|
|
<div class="section"><p>The supported function in the MQT query by the MQT
|
||
|
|
matching algorithm includes:</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"> <ul><li>Single table and join queries </li>
|
||
|
|
<li>WHERE clause </li>
|
||
|
|
<li>GROUP BY and optional HAVING clauses </li>
|
||
|
|
<li>ORDER BY </li>
|
||
|
|
<li>FETCH FIRST n ROWS </li>
|
||
|
|
<li>Views, common table expressions, and nested table expressions</li>
|
||
|
|
<li>UNIONs</li>
|
||
|
|
<li>Partitioned tables</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>There is limited support in the MQT matching algorithm for the
|
||
|
|
following:</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><img src="./delta.gif" alt="Start of change" /><ul><li>Scalar subselects </li>
|
||
|
|
<li>User Defined Functions (UDFs) and user defined table functions </li>
|
||
|
|
<li>Recursive Common Table Expressions (RCTE)</li>
|
||
|
|
<li>The following scalar functions:<ul><li>ATAN2</li>
|
||
|
|
<li>DAYNAME</li>
|
||
|
|
<li>DBPARTITIONNAME</li>
|
||
|
|
<li>DECRYPT_BIT</li>
|
||
|
|
<li>DECRYPT_BINARY</li>
|
||
|
|
<li>DECRYPT_CHAR</li>
|
||
|
|
<li>DECRYPT_DB</li>
|
||
|
|
<li>DIFFERENCE</li>
|
||
|
|
<li>DLVALUE</li>
|
||
|
|
<li>DLURLPATH</li>
|
||
|
|
<li>DLURLPATHONLY</li>
|
||
|
|
<li>DLURLSEVER</li>
|
||
|
|
<li>DLURLSCHEME</li>
|
||
|
|
<li>DLURLCOMPLETE</li>
|
||
|
|
<li>ENCRYPT_RC2</li>
|
||
|
|
<li>GENERATE_UNIQUE</li>
|
||
|
|
<li>GETHINT</li>
|
||
|
|
<li>INSERT</li>
|
||
|
|
<li>MONTHNAME</li>
|
||
|
|
<li>NEXT_DAY</li>
|
||
|
|
<li>RADIANS</li>
|
||
|
|
<li>REPEAT</li>
|
||
|
|
<li>REPLACE</li>
|
||
|
|
<li>SOUNDEX</li>
|
||
|
|
<li>VARCHAR_FORMAT</li>
|
||
|
|
</ul>
|
||
|
|
</li>
|
||
|
|
</ul>
|
||
|
|
<img src="./deltaend.gif" alt="End of change" /></div>
|
||
|
|
<div class="section"><p id="rzajqmqtsupport__mqtpm"><a name="rzajqmqtsupport__mqtpm"><!-- --></a><img src="./delta.gif" alt="Start of change" />It is recommended that the MQT only contain
|
||
|
|
references to columns, and column functions. In many environments, queries
|
||
|
|
that contain constants will have the constants converted to parameter markers.
|
||
|
|
This allows a much higher degree of ODP reuse. The MQT matching algorithm
|
||
|
|
attempts to match constants in the MQT with parameter marks or host variable
|
||
|
|
values in the query. However, in some complex cases this support is limited
|
||
|
|
and may result in the MQT not matching the query.<img src="./deltaend.gif" alt="End of change" /></p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqmqt.htm" title="Materialized query tables (MQTs) (also referred to as automatic summary tables or materialized views) can provide performance enhancements for queries.">Materialized query table optimization</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="rzajqdispatcher.htm" title="The function of the Dispatcher is to route the query request to either CQE or SQE, depending on the attributes of the query. All queries are processed by the Dispatcher and you cannot bypass it.">Query Dispatcher</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="rzajqmqtalgorithm.htm" title="What follows is a generalized discussion of how the MQT matching algorithm works.">Details on the MQT matching algorithm</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|