<p>How do you determine when to use <a href="http://www.iselfschooling.com/mc4articles/mc4cluster.htm" rel="noreferrer">table clusters</a>? There are two types, index and hash, to use for different cases. In your experience, have the introduction and use of table clusters paid off?</p>

<p>If none of your tables are set up this way, modifying them to use table clusters would add to the complexity of the set up. But would the expected performance benefits outweight the cost of increased complexity in future maintenance work? </p>

<p>Do you have any favorite online references or books that describe table clustering well and give good implementation examples? </p>

<p>//Oracle tips greatly appreciated.  </p>

[{'answer_id': 29318, 'author': 'yukondude', 'author_id': 726, 'author_profile': 'https://Stackoverflow.com/users/726', 'pm_score': 1, 'selected': False, 'text': '<p>I haven\'t used Oracle\'s table clusters myself, but I understand that its index table clusters are very much like MS SQL Server\'s clustered indexes. That is, the row data is physically organized by the clustered index\'s key.</p>\n\n<p>That makes one ideal for a heavily-accessed column that has a reasonably small number of possible values (compared to the total number of rows), where most queries want to retrieve all rows with a particular value. Because all such rows are physically stored together, disk I/O, particularly seek time, is reduced.</p>\n\n<p>"Reasonably small" is not easily defined, but postal or zip codes in an address table seems reasonable if you\'re often querying for all addresses in a single code\'s region. Province/state/territory codes are likely too small a selection for a country-wide address table.</p>\n\n<p>So, you don\'t want to use them on columns with few possible values (e.g., M/F for gender) because then the clustering doesn\'t buy you anything and likely costs you for insertions. You also never want to use clustering on "autonumber" surrogate key columns (from sequences in Oracle) because that will create a "hot spot" in the last extent of the table as all insertions must physically happen there. You also don\'t want to apply clustering to a column value that will be updated because the RDBMS will have to physically move the record to maintain the clustered ordering.</p>\n'}, {'answer_id': 6435129, 'author': 'Markus Winand', 'author_id': 312881, 'author_profile': 'https://Stackoverflow.com/users/312881', 'pm_score': 3, 'selected': False, 'text': '<p>The killer feature of table clusters is that you can store <strong>related rows of different tables</strong> at the same physical location.</p>\n\n<p>That can improve join performance by an order of magnitude. However, it doesn\'t pay of so often as it sounds.</p>\n\n<p>The only time I used it was a three-table join, executed by two hash joins. It took too long ;). However, the join was on the same column, so it was possible to use a hash table cluster keyed by the join column. That caused all related rows to be stored alongside (ideally, in the same database block). Knowing that, Oracle can execute the join with a special optimization ("cluster join").</p>\n\n<p>It\'s more or less pre-joined, but still feeling like normal tables (for INSERT/SELECT/UPDATE/DELETE).</p>\n\n<p>On the other hand, there are "single table clusters" that are mostly used to control the "clustering factor" -- A similar idea like clustered indexes (called Index-Organized-Table in Oracle) but not adding high cost if using a secondary index.</p>\n'}, {'answer_id': 6437029, 'author': 'andr', 'author_id': 324117, 'author_profile': 'https://Stackoverflow.com/users/324117', 'pm_score': 2, 'selected': False, 'text': '<p>One can speak a lot about clustering, but I found that almost ultimate explanation about Oracle clusters (pros and cons, when to use and <strong>how</strong> to use) can be found in Tom Kyte\'s book - <a href="https://rads.stackoverflow.com/amzn/click/com/0072230657" rel="nofollow noreferrer" rel="nofollow noreferrer"><strong>Effective Oracle by Design</strong></a>, also you can search <a href="http://asktom.oracle.com/pls/apex/f?p=100:1:0:::RP,APP" rel="nofollow noreferrer">asktom</a> for some specific cluster usage examples (<a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4536286321836" rel="nofollow noreferrer">1</a>, <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1308402202736" rel="nofollow noreferrer">2</a> etc). You should definitely take a look at this book if you haven\'t yet.</p>\n\n<p>Some info you can also find <a href="http://www.dba-oracle.com/oracle_tip_hash_index_cluster_table.htm" rel="nofollow noreferrer">here</a>.</p>\n\n<p>But the thing you should always do before creating complex schema structures is to try, to test, to benchmark and choose the one solution that best fits your  needs :)</p>\n\n<p>Hope this helps.</p>\n'}]
['https://Stackoverflow.com/questions/28009', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']