Page 149
The DBMS_LOB routines can be grouped into three categories. Table 7.1 lists the routines in each category and provides brief descriptions. Note that all functions and procedures that operate on two LOBs require that they be of the same type.
Table 7.1. The DBMS_LOB package.
Routine | Function |
COMPARE | Compares specified sections of two LOBs |
GETLENGTH | Gets the length of the LOB in bytes (for BLOBs) or characters |
INSTR | Searches for the source pattern of bytes (BLOBs) or characters in a specified section of the target LOB |
READ | Reads a specified section of a LOB into a supplied VARCHAR2 (CLOB, NCLOB) or RAW buffer (BLOB, BFILE) |
SUBSTR | Returns the data instead of populating an OUT parameter buffer ( essentially , a function version of the READ procedure) |
APPEND | Appends a source LOB to the end of the destination LOB |
COPY | Copies a specified section of the source LOB to a specified section of the destination LOB (overwriting the section in the destination) |
ERASE | Erases a specified section of a LOB, zero-byte (BLOB), or space (CLOB, NCLOB) filling, if the section erased is in the middle |
TRIM | Truncates LOB data to a specified number of bytes |
WRITE | Writes data to a LOB column from an input buffer of RAW (BLOB) or VARCHAR2 data |
FILECLOSE | Closes an open BFILE |
FILECLOSEALL | Closes all open BFILEs for the current session |
FILEEXISTS | Checks the file system for the existence of a BFILE |
FILEGETNAME | Determines the path and filename of a BFILE, given a locator |
FILEISOPEN | Checks to see whether a given BFILE already is open |
FILEOPEN | Opens a BFILE (read-only) |
Page 150
NOTE |
It is important to understand the snapshot processes involved in LOB selection and LOB updates. These are the primary rules that apply to LOB locators: |
In addition to incremental improvements, the Oracle8 cost-based optimizer contains many enhancements to support the new physical storage options and object-relational features. This section focuses on three of the most significant improvements in the cost-based optimizer, including improvements to hash joins, histograms, and star query processing.
Hash joins improve the performance of equijoins , particularly when used with the Parallel Query option. A hash join starts by performing full table scans of the two tables being joined, splitting them into as many partitions as possible (based on available memory). Partition by partition, the hash join generates a hash key for the rows of the smaller table and searches for matching hash keys in the corresponding partition of the larger table. With the Parallel Query option, separate query processes can be used to process separate partition pairs simultaneously . Three initialization parameters are used to control the behavior of hash joins:
TIP |
You can use ALTER SESSION to override these settings for a particular session. |
Page 151
Oracle8 allows the creation of histograms for a particular column to provide additional selectivity information. Histograms are used when the data for a column that is used frequently in a WHERE clause is not distributed evenly. University exam scores may range from 0 to 100, for example, but are likely to have a large concentration in a particular range, such as 70 to 90. The histogram represents the distribution of values in terms of evenly sized buckets. Consider the following example:
ANALYZE TABLE exam_scores COMPUTE STATISTICS FOR COLUMNS score SIZE 5;
The SIZE parameter indicates the number of buckets into which the values are divided. If there were 10,000 rows in the table, each bucket would represent 2,000 rows. A larger number of buckets provides a more detailed histogram. Increasing SIZE to 100 would result in 100 rows per bucket. You may need to experiment to find the best size for a particular column. Histograms are not useful for columns that do not appear in WHERE clauses or that are distributed evenly. Even distribution is assumed when a histogram is not present.
The new Oracle8 star-transformation capabilities will be of great interest to designers and developers of data warehousing applications. Bitmap indexes, as discussed earlier, can provide dramatic performance increases , particularly for indexes containing a small number of distinct column values. Using star transformation, the Oracle8 optimizer capitalizes on the bitmap index representation. Suppose that the query in Listing 7.7 is executed against a very large fact table and three of its dimensions.
Listing 7.7. Query containing a single fact table and several dimension tables.
SELECT sum(fact_t.measure) FROM fact_t, dim_t1, dim_t2, dim_t3 WHERE fact_t.fk1 = dim_t1.code AND fact_t.fk2 = dim_t2.code AND fact_t.fk3 = dim_t3.code AND dim_t1.description BETWEEN `01-JAN-96' AND `31-MAR-96' AND dim_t2.description = `NORTHEAST REGION' AND dim_t3.description IN (`CONSUMER', `SMALL BUSINESS');
Assuming that there are bitmap indexes on each of the fact table columns used in the join, star transformation first performs subqueries on the dimension tables to retrieve the code values associated with the descriptions specified in the WHERE clause, retrieving the portions of the bitmap indexes corresponding to the specific code values returned. These three bitmap images then are merged and ANDed, which results in a bitmap image that corresponds to the exact rows in the fact table that meet all three conditions. These rows are retrieved, and the value of measure is summed to produce the result.