A script-enabled browser is required for this page to function properly.

Improving performance using WHERE clauses

Consider adding a WHERE clause for the matrix cell query in a multiquery data model. If you are using a multiquery data model and your dimension queries are restricted by a WHERE clause, adding a WHERE clause to the matrix cell query ensures that you do not retrieve more records than are necessary. For example, suppose that you had the following queries for your dimensions:

Q_Dept

SELECT DEPTNO FROM DEPT
WHERE DEPTNO < 100

Q_Job

SELECT DISTINCT JOB FROM EMP
WHERE DEPTNO < 100

Q_Filler

To ensure that your cell query only retrieves the records that are necessary, you would write the following SELECT statement:

SELECT DEPTNO, JOB, SUM(SAL) FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
WHERE DEPTNO < 100) AND JOB IN (SELECT
DISTINCT JOB FROM EMP WHERE DEPTNO < 100)
GROUP BY DEPTNO, JOB

If you did not add the WHERE clause to this query, all rows would be retrieved from the database, regardless of what you selected in Q_Dept and Q_Job.

Note: If you added a WHERE clause that did not use the subqueries (for example, WHERE EMP.DEPTNO = DEPT.DEPTNO), the query would be executed once for each combination of values in the cross-product. This can lead to excessive execution of the filler query, if the cross-product has a lot of combinations.