0149-0151

Previous Table of Contents Next

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
Routines That Apply to All LOBs
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)
Routines That Apply to Internal LOBs
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
Routines That Apply to BFILEs Only
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:
  • A selected LOB locator's snapshot will not be refreshed if the LOB is updated by another session.
  • A LOB locator is refreshed only when updated through the DBMS_LOB package or corresponding OCI routines (locators are not refreshed on DML updates).
  • Deleting a BFILE only deletes the database reference to it. (The file is not deleted from the external file system.)
The net result of these rules is that applications always should lock the row containing the LOB before accessing a locator and should use only the DBMS_LOB package to update the LOB. This ensures that the locator is current at all times.

Optimizer Improvements

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

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:

  • HASH_JOIN_ENABLED enables/disables hash joins at the instance level.
  • HASH_AREA_SIZE sets the maximum amount of memory available for hash join operations.
  • HASH_MULTIBLOCK_IO_SIZE sets the number of blocks to be read from or written to by a hash join in a single operation.
TIP
You can use ALTER SESSION to override these settings for a particular session.

Page 151

Histograms

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.

Star Transformation

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.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net