94 lines
6.0 KiB
HTML
94 lines
6.0 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="concept" />
|
|
<meta name="DC.Title" content="Query performance and optimization" />
|
|
<meta name="abstract" content="Queries that reference partitioned tables need to be carefully considered because partitioned tables are often very large. It is important to understand the effects of accessing multiple partitions on your system and applications." />
|
|
<meta name="description" content="Queries that reference partitioned tables need to be carefully considered because partitioned tables are often very large. It is important to understand the effects of accessing multiple partitions on your system and applications." />
|
|
<meta name="DC.subject" content="Partitioned tables, performance" />
|
|
<meta name="keywords" content="Partitioned tables, performance" />
|
|
<meta name="DC.Relation" scheme="URI" content="partitionedtables.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="rzaf3sqept.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="cqe.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="indexpt.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="../rzajq/rzajqkickoff.htm" />
|
|
<meta name="DC.Relation" scheme="URI" content="../rzajq/queryoptimize.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="performancept" />
|
|
<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>Query performance and optimization</title>
|
|
</head>
|
|
<body id="performancept"><a name="performancept"><!-- --></a>
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|
<h1 class="topictitle1">Query performance and optimization</h1>
|
|
<div><p>Queries that reference partitioned tables need to be carefully
|
|
considered because partitioned tables are often very large. It is important
|
|
to understand the effects of accessing multiple partitions on your system
|
|
and applications.</p>
|
|
<p>Partitioned tables can take advantage of all optimization and parallel
|
|
processing available using SQL on DB2 Universal Database™ for iSeries™.
|
|
For general information about query optimization, see the Database performance
|
|
and optimization. For partitioned tables, all the data access methods described
|
|
in the Database performance and optimization topic can be used to access the
|
|
data in each partition. In addition, if the DB2<sup>®</sup> UDB Symmetric Multiprocessing feature
|
|
is installed, the parallel data access methods are available for the optimizer
|
|
to consider when implementing the query.</p>
|
|
<p>If queries need to access only an individual partition in a partitioned
|
|
table, creating an alias for that individual partition and then using that
|
|
alias in the query can enhance performance. The query acts as a nonpartitioned
|
|
table query.</p>
|
|
<p>Partitioned tables conceptually implemented as a nested table where each
|
|
partition is unioned to the other partitions. </p>
|
|
<div class="p">For example, if you perform the following query: <pre>SELECT LASTNAME, SALARY FROM PRODLIB.PAYROLL
|
|
WHERE SALARY > 20000</pre>
|
|
The implementation of the query can be
|
|
described as: <pre>SELECT LASTNAME, SALARY FROM
|
|
(SELECT LASTNAME, SALARY FROM PRODLB.PAYROLL(PART00001)
|
|
UNION ALL
|
|
SELECT LASTNAME, SALARY FROM PRODLB.PAYROLL(PART00002)
|
|
UNION ALL
|
|
SELECT LASTNAME, SALARY FROM PRODLB.PAYROLL(PART00003))
|
|
X (LASTNAME, SALARY)
|
|
WHERE X.SALARY > 20000</pre>
|
|
</div>
|
|
<p>The implementation of partitioned table queries depends on which query
|
|
engine is used: the Classic Query Engine (CQE) or the SQL Query Engine (SQE).
|
|
For more information about the query engines, see the SQE and CQE Engines
|
|
article in the Database performance and optimization topic. There are different
|
|
considerations for each engine.</p>
|
|
</div>
|
|
<div>
|
|
<ul class="ullinks">
|
|
<li class="ulchildlink"><strong><a href="rzaf3sqept.htm">Queries using SQL Query Engine</a></strong><br />
|
|
The SQL Query Engine (SQE) provides targeted optimization for partitioned tables using dynamic partition expansion optimization.</li>
|
|
<li class="ulchildlink"><strong><a href="cqe.htm">Queries using Classic Query Engine</a></strong><br />
|
|
When queries are implemented using Classic Query Engine (CQE), you need to be aware of how the query is optimized including materialization and index usage.</li>
|
|
</ul>
|
|
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="partitionedtables.htm" title="DB2 UDB for iSeries supports partitioned tables using SQL.">Partitioned tables</a></div>
|
|
</div>
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
|
<div><a href="indexpt.htm" title="Indexes can be created as partitioned or nonpartitioned. A partitioned index creates an individual index for each partition. A nonpartitioned index is a single index spanning all partitions of the table.">Indexes with partitioned tables</a></div>
|
|
<div><a href="../rzajq/queryoptimize.htm">SQE and CQE Engines</a></div>
|
|
</div>
|
|
<div class="relinfo"><strong>Related information</strong><br />
|
|
<div><a href="../rzajq/rzajqkickoff.htm">Database performance and optimization</a></div>
|
|
</div>
|
|
</div>
|
|
</body>
|
|
</html> |