Re: Clusters.....why?

On Sun, 23 Aug 1998 21:28:22 -0500, Arthur Merar <amerar_at_unsu.com> wrote:

A cluster is a physical grouping of information from different tables which are somehow logically related.

For example, consider an ORDERS and PRODUCTS_ORDERED tables. The ORDERS table contains the Order_No, Date and Employee and PRODUCTS_ORDERED contains Product_No, Order_No, Quantity, Price, etc.. Order_No is a FOREIGN KEY to the ORDERS table, i.e., for every value in this column, an equal value exists in the ORDERS table, column Order_No.

Since ORDERS and PRODUCTS_ORDERED are logically related and it is common to present Ordered Products while browsing through Orders, then it would be a good idea to put both tables in a cluster, for performance and space conservation. When you place both tables on a cluster, Oracle will place Orders and Ordered Products which are related through the same Order No in the same database block. Moreover, Oracle will store the common column (Order No) only once.

You'll get faster response times when selecting Orders with their corresponding products ordered.

Be aware that there are also disadvantages. I once started using clusters, but then I realized that I was having a performance penalty in some SELECT statements. Since rows from the 2 (or more tables) are stored on the same database blocks on a cluster, whenever you have to perform a full scan on one table (e.g. for summing a column's values), it will take much longer, since Oracle will have to read many more blocks, it is similar to performing two full table scans - one for each table!

Hope this helps,

Nuno Guerreiro

>2) What is a cluster? Although I have read the Oracle book on
>clusters, I cannot even begin to build a picture of this in my
>mind.......
Received on Mon Aug 24 1998 - 04:27:18 CDT