Page 769
You've already seen that UNION can be a performance hit. What you should be more interested in is using MINUS or INTERSECT. These can actually help performance. The following query:
SQL> SELECT ACCOUNT_ID 2> FROM JOBS_COMPLETED 3> WHERE ACCOUNTS_ID NOT IN(ACCOUNTS_PAID);
can be rewritten as:
SQL> SELECT ACCOUNTS_ID FROM JOBS_COMPLETED 2> MINUS 3> SELECT ACCOUNTS_ID FROM ACCOUNTS_PAID;
Both of these queries give the same functional result: They return those accounts that have their jobs completed but have not been fully paid. However, using EXPLAIN PLAN will likely reveal that the total logical reads for the first are much higher than for the second. In Oracle, at least, using the MINUS operator is very efficient.
INTERSECT is the complement of MINUS, except that MINUS is an asymmetrical (one-way) and INTERSECT is a symmetrical (two-way) operator. That is, a symmetrical operator works the same in both directions. UNION is symmetrical. If you reversed which table you selected from using MINUS in the previous example, you would get a different answer. You would get all the accounts that have been paid but have not had their jobs completed (which may or may not be a typical business practice). For another example, to find all accounts that have had their jobs completed and have been fully paid, use:
SQL> SELECT ACCOUNTS_ID FROM JOBS_COMPLETED 2> INTERSECT 3> SELECT ACCOUNTS_ID FROM ACCOUNTS_PAID;
A Boolean expression is one that evaluates to TRUE or FALSE. The WHERE clause of an SQL statement is an example of a Boolean expression. You can take advantage of this fact by coming up with functions that convert WHERE clauses (Boolean expressions) to numeric values, such as 1 for TRUE and 0 for FALSE. How can this help? Consider the following queries to obtain varying tax rates given four types of marital status (S=Single, M=Married, D=Divorced, and W=Widowed):
SQL> SELECT SINGLE_TAX "TAX" FROM TAXES WHERESTATUS='S'; SQL> SELECT MARRIED_TAX "TAX" FROM TAXES WHERE STATUS='M'; SQL> SELECT DIVORCED_TAX "TAX" FROM TAXES WHERE STATUS='D'; SQL> SELECT WIDOWED_TAX "TAX" FROM TAXES WHERESTATUS='W';Again, putting aside the issue of database design, because this may be a poorly designed table, you can see that you must make four full-table scans of the TAXES table to get the information you need. This is a good example because you're dealing with more than two distinct values. Can you do better? First, you need a Boolean conversion. In other words, you need a function to return SINGLE_TAX when STATUS=`S', MARRIED_TAX when STATUS=`M', and so forth.
Page 770
Oracle provides just such a function, DECODE. If you carefully walk through the following query, you see that it fully replaces the previous four queries, provides the same functional result, and yet requires only one table scan.
SQL> SELECT DECODE(STATUS, `S', 1, 0)*SINGLE_TAX+ 2> DECODE(STATUS, `M', 1, 0)*MARRIED_TAX+ 3> DECODE(STATUS, `D', 1, 0)*DIVORCED_TAX+ 4> DECODE(STATUS, `W', 1, 0)*WIDOWED_TAX 5> "TAX" 6> FROM TAXES;
This is an incredible efficiency gain. However, as you can tell, the readability and maintainability suffer. In addition, using the DECODE function may not always work. You may have to use other contrived functions. In this case, what you needed was an associative array, and DECODE just happened to provide that type of functionality. Obviously, the hard work is reviewing the original query to come up with a function that gives you an equivalent answer.
A few new features have been added to Oracle8 regarding indexing that will no doubt be useful for future applications development. These are discussed in the remainder of this chapter.
Oracle8 enables you to partition, or physically divide, indexes along a partition key. This means partitioning an index along the same column(s) that make up the index. Partitions can be stored on separate tablespaces with separate storage parameters. Hence, partitions are sub-tablespaces. The following is an example:
SQL8> CREATE INDEX EMPL_IDX ON EMPLOYEES(EMPLOYEE_ID) 2> PARTITION BY RANGE (EMPLOYEE_ID) 3> (PARTITION ip1 VALUES LESS THAN (499999) 4> TABLESPACE empl_id[ts]1, 5> (PARTITION ip2 VALUES LESS THAN (1000000) 6> TABLESPACE empl_id[ts]2);
In Oracle8, if you create an index as local, Oracle automatically equi-partitions it. That is, it uses the same partition key, the same number of partitions, and the same partition boundaries as the table it references. A local index is one in which all the index keys in one partition point to all the data in one table partition. There is a one-to-one mapping. A global index is one in which this does not hold true. This ensures that a table and its index are equi-partitioned. Aside from higher availability similar to striping, equi-partitioning with a local index enables the optimizer to be partition aware. The following is an example:
SQL8> CREATE INDEX EMPL_IDX ON EMPLOYEES(EMPLOYEE_ID) 2> LOCAL 3> (PARTITION ip1 TABLESPACE empl_id[ts]1, 4> PARTITION ip2 TABLESPACE empl_id[ts]2);
Page 771
As just mentioned, if you create equi-partitioned, local indexes, the optimizer can generate query plans by using partition knowledge. Therefore, it can parallel some operations.
In Oracle8, you can now create an index-only table. This is also known as an in-place index. Essentially, the table is physically sorted, rather than logically sorted with a B*Tree. This has the obvious performance benefit of removing the logical reads from the B*Tree to the table because the data and the index are one and the same. Again, you would normally use the primary key as the indexing column. An example is as follows:
SQL8> CREATE TABLE EMPLOYEES 2> (EMPLOYEE_ID NUMBER(6) CONSTRAINT empl_pk PRIMARY KEY, 3> <column, column, ...>) 4> ORGANIZATION INDEX TABLESPACE empl_dat1;
The ORGANIZATION INDEX clause tells Oracle8 this is an index-only table.
A Reverse key index is one in which the order of the individual column bytes is reversed (not the column order). Reverse key indexes have proven useful in improving Oracle Parallel Server (OPS) performance. Use the REVERSE keyword on CREATE INDEX to create one for OPS usage.