Tom
Please could you comment on the appropriateness of an IOT for this problem. If appropriate - under what circumstances would the cluster solution be better?

Tom - in this question here, you are suggesting a single table might be worth clustering to co-locate the data. In your book (pg. 238) you make reference to a special case of the single table hash cluster. You say this is designed for those occassions when you want to access a table by primary key and don't wish to cluster other tables with it. "If you need fast access to an employee record by EMPNO - a single table hash cluster might be called for".

In the example in the book it looks like just a regular old table with a standard primary key. In this example in the thread it looks like there are many records with the same value.

So..
If I have a table that is not joined to other tables and I need fast access to it's primary key, do I want a single table hash cluster? If so, isn't it somewhat more common to just make use of the index on the primary key? If I can predict the size of this table is it worth single table hash clustering? In this question thread, it seems we can store many values together but in the book example, I think you imply that empno is guaranteed to be completely unique between 1 and 50,000.

The example here makes sense but the book example is confusing me. Tables are accessed by primary key quite frequently - I would assume I would see more single table hash clusters if they are worth doing in single table mode.

Tom, in your new book on page 385, you mention collision in hash table. Could you please elaborate what it is and how exceeding value for hashkeys parameter affect the hash table? Thanks for your time. Your book is great.

I understand that where id=60 will take two LIOs. Why for id=50 and id=55 also take 2 LIOs? Thanks again.

In your new book, you compared the different methods of doing lookups.

For heap table, you used an example of TABLE ACCESS BY INDEX ROWID, which is not so efficient as SINGLE-TABLE HASH CLUSTER or IOT.

I am wondering if we could achieve the same or better performance if we use INDEX FAST FULL SCAN on the heap table?

Thanks.

Thanks Tom.

According to Oracle DBA Guide:

"The SINGLE TABLE option is valid only for hash clusters. HASHKEYS must also be specified."

Is it right that I should not use a single-table b-tree index cluster.

As it was said, insert into IOT table is more expensive than insert into clustered table. Is this the only benefit?

As for me the biggest cons of clustered table in common are:
1) Not possible to partition
2) Not possible to direct path load (same for IOT, I think)

So, when we store huge amount of data into clustered table, deleting from this table turns into nightmare. From this point IOT wins (truncate/drop partition).

Understand, Oracle data dictionary is an excellent example - changes to data dictionary are rare (usally), quick response is required, low IO.

Unfortunalely, I had seen rather strange table design on two billing systems regarding using of cluster.
In the first case, weekly CDR tables (big tables) were clustered on customer code column, and were joined in partitioned view. So, no problem with getting rid of data for one week, nevertheless, inserts of new CDR's suffered. Everybody believed that billing process is much faster using these clustered tables, until we tested (by accident) the whole process on regular heap tables with regular B-tree indexes. No impact on performance at all!

Second case - different client, different billing system but the same CDR story. Even worse - really huge clustered table and problem with deleting. Just nightmare!

Of course, I agree with you that this all about TOOLS to be used in proper way.

(Just reading your Effective Oracle by Design, excellent book!)

Just to clarifying:
Scenario:

DB A

t_source ( id_contract varchar2(17), name_con varchar2(40), ... )

DB B
t_interface ( id_contract varchar2(17), dt_load date )

t_process (id_contract varchar2(17), dt_process date )

t_contract (id varchar2(17) pk, name ..., state check ('A','B','C','D','E'))

t_history (qty_rows_process number(7), qty_files ...)

There is no fk references between tables.

1) A job #1 wakes up --> collect all data from t_source between dt1 and dt2 and insert into t_interface set null in dt_load. About 2,000,000 rows at time -- interval 1h (8am - 5pm) we talking about 20,000,000 rows/day.

2) Another job #2 wakes up --> insert into t_process from t_source where dt_load is null, set dt_load.

3) Another yet job #3 wakes up --> select t_process, t_contract where id_contract = id and state in ('A','B') -- export some files with information based on state, insert into stats into t_history and set dt_process.

The tables t_contract and t_source I can't touch.
Wich would be create rest of them (t_interface, t_process and t_history)? IOT/b*tree Hash/heap/Other? and please explain...

The system above is all about. As is!
I've found full relevant information about iot, b*tree cluster and so on your book, but I'd like know your opinion about what type of creation would be the tables in the scenario above -- if you don't mind.

Btw - fyi: You have yet the warning on followup page about your trip. "Bear in mind, I am out of the country from Jan 10th ..."

Tom,

One of the apps I support has a query like this.

select many columns from part_iot_table
where ctry_cd = 'XX' and id = 999;

On average there are 9 rows per id, ctry_cd will always be the same.

I decided to create the table as a partition IOT to colocate the data together around the id column. However, the team has decided that they also want to query the table like so.

select many columns from part_iot_table
where id = 999;

Now my partition elimiation scheme is out the window. I could create a global secondary index on the subj_id, however, I was thinking that maybe a single hash cluster on id might solve both problems at once.

My problem is that loading the single table hash cluster is so slow that this option does not seem feasible. Hopefully you can shed your experience on increasing load performance for these types of objects. So far it has been running for about 8 hours and I have no way to estimate when it will complete.

Since I am loading from the IOT and the data is already sorted by id, I would expect that most blocks would be cached after the first read and not accessed again.

I tried to test the single hash cluster by loading a single partition of 30M rows.

My cluster was created with the following syntax.

create cluster match_data_cluster
( subj_id number(13))
single table
hashkeys 30000000 -- should have used 30M/9 rows per key
size 94
tablespace SCOTT_DONT_USE
pctfree 0
parallel 4

Can you suggest anything I can do to increase the load performance. I am not running in Archive log mode on my test server.

My next option, if I cannot get the load to speed up, would be to try and create a hash partitioned IOT on the id column and add a dummy column using rownum to ensure that I have a primary key. A head table was out of the question as we sometimes need to lookup 5000 id at a time in the 300M table and there is too much bouncing around.

I would appreciate your thoughts and comments. I am running 9.2.0.3 on HP 11.

Thanks once again
Scott Watson

That was my plan if the hash cluster didn't work. However, I didn't want to give up on the single table hash cluster just yet. I was hoping you might know why the load would be taking so long.

The 94 bytes is the space used per row. I guess I should be using 94*9 instead, an oversite on my part.

The IOT is a great option and it is what I had been recommending. The problem was that they added another query that wasn't suited for how the table was origninally built.

So given the new set of requirements, I thought I would build one IOT version and one single table hash cluster and compare the performance difference between the two. ( I would have never thought of using the cluster until I read your new book) Once I saw the terrible performance of the load into the cluster I decided I did something wrong and sought your advice. I did not mean for you to think that I was not considering the IOT.


Thanks

When you have btree single table clusters, and you set the size low, i.e. several rows with different cluster key share the same block, how are the blocks linked when 1 block is not enought to hold the data?

Is it simply "all those cluster keys share that linked list of data blocks" ? Or is it "the next block for cluster key <bla> is this <rowid>" ?

We have a big table (300 milion rows, ~120 bytes per row) which has a list_id having ~150 000 distinct values, very skewed. For some list_id values we will have 1 row, for others 1 million rows. I was thinking of using a btree cluster index. I desided I will first try the cluster with 2 columns (list_id, domain_id). I am testing it now, but the problem is with those small list_ids, I would have 10 rows with the same list_id, but different domain_ids, thus will be over 10 datablocks. Initially I desided that I will have 1 cluster key per block, but now I may need to reconsider my decision, and depending on your anwers I may just drop the idea.

I am also open to other ideas.

9.2

Is it possible to create clustered MV for query and use heap table for DML?

well, as long as you understand that the DML applied to the heap table is ultimately also applied to the MV... sure, the MV can have very different structural qualities from the parent table.


ops$tkyte@ORA9IR2> create cluster single_table_hash_cluster
  2  ( hash_key number(4) )
  3  hashkeys 50000
  4  size 45
  5  single table
  6  /
 
Cluster created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> alter table emp add constraint emp_pk primary key(empno);
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view emp_mv
  2  cluster single_table_hash_cluster(empno)
  3  as
  4  select * from emp;
 
Materialized view created.
 
 

Is there a way to change the HASHKEYS parameter value after the cluster is created and populated? I was wondering that could help to avoid "collisions". Thanks.

Tom,

In your above example with X=5 and X=6, are you suggesting that we should try to load the data with the same clustered key at the same time to get the most benefit?

If this is not possible, then would using a cluster still make sense?

Thanks,
-Peter

Hi Tom,
I have got an application where there are some tables storing geographical data like cities, regions, states and so on. Primary keys are numbers generated by sequences and a typical "where" clause contains only equality operators against primary keys, like for instance "where region_id = :1 and location_id = :2 ".
This kind of data doesn't change over time, indeed it has been loaded once for good therefore the correct size of the hash key could be easily calculated. These tables aren't big, say the biggest will contain 20.000 rows, while the smallest is only 50 rows, but they are likely to be queried at all times.
Are they a candidate for some sort of multi table cluster because, in the end, the less blocks you read, the better is, or is it a completely useless exercise because it would be much better to cache direcly the smaller ones and let an ordinary unique index scan do the rest of the job?

Thanks and bye,
Flavio

PS: great books, I love them!

Yes Tom,
I forgot to include the joins...

region_id is on the "provinces" table and is a foreign key to the "regions" table. Province_id is on the "locations" table and is a foreign key to the "provinces" table.
This is a snowflake sort of thing.

So, when I query the tables there are always the joins between the tables in the where clause.

Bye,
Flavio

It's an HTMLDB based application query.

select i.item, i.ean, i.id, i.unit, i.scale, i.category_name, l.net_price - l.discount price, l.net_price, l.last_date, l.store_name, l.store_group_id,
l.location_id, s.province_id, p.region_id
from items_list i, last_prices l, locations s, provinces p
where i.id = l.item_id
and l.location_id = s.id
and s.province_id = p.id
and (l.store_id = :P120_STORE_ID or :P120_STORE_ID = 0)
and (l.location_id = :P120_LOCATION_ID or :P120_LOCATION_ID = 0)
and (s.province_id = :P120_PROVINCE_ID or :P120_PROVINCE_ID = 0)
and (p.region_id = :P120_REGION_ID or :P120_REGION_ID = 0)
and (l.store_group_id = :P120_STORE_GROUP_ID or :P120_STORE_GROUP_ID = 0)
and (i.category_id = :P120_CATEGORY_ID or :P120_CATEGORY_ID = 0)

Tom,
It means any region or any location and so on.
When I first wrote the clause instead of "OR :Pxxx_yyy_zzz = 0" there was "OR :Pxxx_yyy_zzz is null" because the logic was that a null value should mean any value.
Then I found out that HTMLDB is a bit naive in the way it represents null values in drop down lists as a null value is actually represented by the string '%null%' if you don't specify something else, but only after submitting the page.
So you end up with a query that needs to check if a value is null or if it is equal to '%null%'. That's why I preferred to use my own "null" value and I opted for 0.

Back to clusters, what you say is also my understanding, but do you see any advantage in clustering together just the two biggest tables (locations and provinces)?

The other two tables are not eligible I guess, one is a view containing OLAP functions.

Thanks!
Flavio

Tom,
if understand correctly you suggest to fine-tune the WHERE clause omitting some ORs instead of having a unique sql statement using bind variables.
Isn't that a breach of the 11th commandment????
"Don't clutter up the shared pool with unrecyclable sql statements" (Kyte, 17, 11)

;-)

Now, seriously, what is the exact risk behind using that kind of boolean logic?

Currently there are no I/O issues, but, as you teach, the sooner you tackle possible bottlenecks, the better...

Bye,
Flavio

that's certainly true Tom, I made a comparison between the execution plans and what you say is perfectly reasonable.
However this leads me to ask how and if you normally weigh the tradeoffs between higher performance and coding readability.
Are there situations where you privilege the latter or do you always favor performance, even if there is only a slight difference?
In other words, where is the turning point, if any?

Bye,
Flavio

I'll modify the query type then and see the results.

Thanks for you time and patience!

Bye,
Flavio

Tom,

At the start of the discussion you mention that a primary key lookup with a hash cluster will require only 1 I/O. However, subsequently you mention that 50 could be in chained blocks also. How can it be possible if that column is the primary key?

Please clarify

Tom,

Sorry for not being very clear. The "50" in my question refers to the value that we lookup against. You mention that we need to keep looking in the chained list (once the data block address of the starting block is determined using the hashing algorithm) because there is a possiblity the value of 50 could also be in another block in the same list of blocks. But the initial thread is that these are primary keys. How can 50 be in another block in the chain?

I might just add that in 10g we get a SORTED hash cluster, so that example of Tom's to which you refer (I think it was from 2003, so not surprising if Tom has forgotten about it!) would not have to take place, even if the hash cluster was not clustered on the primary key.

If there were a number of rows with values 50, 55 and 60, then we have to search all possible hash buckets in the chain, even for the 50 and 55 values, because they might be hiding anywhere in the chain. But if you sort the values, then all the 50s would be together, all the 55s would be together and so forth... at which point, as soon as you find your first 55, you know there cannot be any more 50s, and you can stop searching.

I like sorted hash clusters a lot!

The point from Tom's original example was that in an unsorted hash cluster, you know that 50, 55, 60, 65 and so on are all going to be found in the one hash bucket (and that 51, 56, 61, 66 would all be in another hash bucket). But you don't know where in the bucket they are, and so you search until you find the one you want. I can't recall if the optimiser understood that the hash key was a primary key, and therefore having found one value of 50 it could assume there would be no others, but I don't think that's the main point even so: in the absence of a sort within the cluster, you cannot know that 50 will be in the first part of the particular hash bucket, and therefore *in principle*, hash collisions give rise to extra I/O.

(1) Can one create a view on cluster segements? any restrictions?
(2) can one implement FGA control on cluster tables?
Thanks.

Hi tom,

If we create a cluster having m number of tables("key1" as pk ) on a cluster key say "key1"
then select on these will be faster instead of joining all these tables then
1.insertion/updation will be slow?
2.LIO's might be high? depending on the query u fired.
3.If it's a OLTP env, will there be any contension for latches in momory while doing select/dml operations?

Thankx
Anil

Hi tom,

Thankx for the quick turnaround.

Regarding the LIO's if query being fired is somewhat like this
select * from a;
select * from b;
select * from c;
etc
and a ,b ,c are part of cluster then in this case LIO's won't be higher as the data is across multiple blocks?

Thankx,
ANIL

Hi tom,

Thankx for the quick turnaround.

Regarding the LIO's if query being fired is somewhat like this
select * from a;
select * from b;
select * from c;
etc
and a ,b ,c are part of cluster then in this case LIO's won't be higher as the data is across multiple blocks?

Thankx,
ANIL

tom,

Please help me in investigating why the LIO's and some wait events are on higher side(like db file seq.read,log file sync and latch free)


STATSPACK report for

DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
MRDP 354944511 MRDP 1 8.1.7.4.0 NO mrdutpc1pr

Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 58 14-Oct-05 10:46:08 512
End Snap: 59 14-Oct-05 11:49:20 512
Elapsed: 63.20 (mins)

Cache Sizes
~~~~~~~~~~~
db_block_buffers: 180000 log_buffer: 5242880
db_block_size: 8192 shared_pool_size: 350000000

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 84,660.44 3,760.70
Logical reads: 4,542.45 201.78
Block changes: 476.59 21.17
Physical reads: 69.20 3.07
Physical writes: 15.38 0.68
User calls: 543.61 24.15
Parses: 218.49 9.71
Hard parses: 16.06 0.71
Sorts: 4.40 0.20
Logons: 1.35 0.06
Executes: 359.59 15.97
Transactions: 22.51

% Blocks changed per Read: 10.49 Recursive Call %: 50.75
Rollback per transaction %: 11.52 Rows per Sort: 11.23

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 98.48 In-memory Sort %: 99.99
Library Hit %: 97.84 Soft Parse %: 92.65
Execute to Parse %: 39.24 Latch Hit %: 99.11
Parse CPU to Parse Elapsd %: 80.91 % Non-Parse CPU: 88.36

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 39.64 83.23
% SQL with executions>1: 7.69 5.97
% Memory for SQL w/exec>1: 10.83 9.05

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 193,827 165,114 67.98
log file sync 87,051 22,982 9.46
SQL*Net break/reset to client 141,005 16,198 6.67
latch free 155,806 11,999 4.94
log file parallel write 74,366 9,773 4.02
-------------------------------------------------------------
Wait Events for DB: MRDP Instance: MRDP Snaps: 58 -59
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file sequential read 193,827 0 165,114 9 2.3
log file sync 87,051 2 22,982 3 1.0
SQL*Net break/reset to clien 141,005 9 16,198 1 1.7
latch free 155,806 86,079 11,999 1 1.8
log file parallel write 74,366 0 9,773 1 0.9
SQL*Net more data to client 260,636 23 7,958 0 3.1
db file scattered read 7,732 0 3,469 4 0.1
db file parallel write 6,948 0 2,839 4 0.1
log file sequential read 4,068 0 995 2 0.0
buffer busy waits 3,014 0 626 2 0.0
enqueue 2,047 0 356 2 0.0
control file parallel write 1,368 0 341 2 0.0
direct path read 1,927 0 64 0 0.0
direct path write (lob) 440 0 59 1 0.0
file open 3,636 0 50 0 0.0
direct path read (lob) 333 0 28 1 0.0
LGWR wait for redo copy 918 0 8 0 0.0
log file switch completion 3 0 7 23 0.0
buffer deadlock 29 29 3 1 0.0
direct path write 560 0 2 0 0.0
control file sequential read 349 0 2 0 0.0
file identify 14 0 1 1 0.0
library cache pin 12 0 1 1 0.0
refresh controlfile command 10 0 0 0 0.0
log file single write 4 0 0 0 0.0
library cache load lock 2 0 0 0 0.0
virtual circuit status 1,272,864 83 6,374,114 50 14.9
SQL*Net message from client 1,594,984 0 71,694 0 18.7
SQL*Net more data from clien 16,944 0 15,379 9 0.2
SQL*Net message to client 1,594,984 0 1,067 0 18.7

thanks a lot,
Anil

Hi Tom,
I've got a question regarding single-table hash clusters. If your table primary key is a varchar2 type column, Oracle doesn't allow you to specify it in the 'HASH IS' part, since that only takes a NUMBER type. Is there any negative impact in this case of using the internal hashing function? What is the reasoning behind only allowing you to specify a NUMBER type for the Hash function?
Thanks

ops$tkyte@ORA10GR2> create cluster Hash_Cluster
  2  ( id varchar2(200) )
  3  SINGLE TABLE
  4  hashkeys 75000   -- number of distinct lookups we expect over time
  5  size 125         -- size of data associated with key
  6  /

Cluster created.


no sure what you are trying to do exactly?  have example? 

Hi Tom,
  This is what I'm trying to do:

SQL> create cluster hash_cluster
  2  (id varchar2(2000)) 
  3  single table
  4  hashkeys 200
  5  hash is id 
  6  size 125;
create cluster hash_cluster
*
ERROR at line 1:
ORA-02456: The HASH IS column specification must be NUMBER(*,0)


  I was trying to use the cluster key as the hash function.  But Oracle doesn't like it since it is a varchar2 column.   

Right - you need to apply some function to your string resulting in some number, eg:

ops$tkyte@ORA10GR2> create cluster hash_cluster
  2  (id varchar2(2000))
  3  single table
  4  hashkeys 200
  5  hash is TO_NUMBER(id)
  6  size 125;

Cluster created.

It need not be TO_NUMBER, just some function that takes your inputs and hashes them - returning a number.

but in general, you don't even need the "hash is", we'll hash your string into a number for you. 

Hi Tom,
Thanks for the quick response. I do realise that if you don't specify the 'HASH IS' clause, Oracle uses its own hashing function.

My question was how different will this be in terms of performance, if any? The manual says

'If no HASH IS clause is specified, the database uses an internal hash function. If the cluster key is already a unique identifier that is uniformly distributed over its range, you can bypass the internal hash function and specify the cluster key as the hash value, as is the case in the preceding example. You can also use the HASH IS clause to specify a user-defined hash function.'

I was just curious if there would be any performance difference in using the different hash functions (internal, user-defined and just using the cluster key)
Regards

This is the first time I will use cluster tables and I don't know how to set the right size:
using single table hash cluster, using multi table hash cluster or using btree cluster.
Can you tell me how to calculate that size?
Thanks,

Thanks, for your comments about cluster size!, but now I want to know how to check if I get cluster collision?

Respected Mr. Tom,

I have read 10 times the following lines from Oracle 9i Database Concepts – Chapter 10 and sorry I could not understand the following two (2) sentences. If possible, could you kindly explain me with example?

Sentence -1
--------------
Storage parameters cannot be specified when a clustered table is created or altered.

Sentence -2
--------------
The storage parameters set for the cluster always control the storage of all tables in the cluster.

It’s true; I am not much more familiar with cluster table.

when you create a CLUSTER - you are creating a segment, the segment (the storage) is the CLUSTER.

when you create a table in a cluster, you do not create a segment, you are just creating a table.  the segment (the storage) is the cluster segment, not the table.


So, when you create a table in a cluster (also known as a clustered table), you cannot specify storage parameters like initial, next and so on because the TABLE doesn't control the storage, the cluster itself does.  And the table just resides in the cluster segment.


Here is a short writeup from Expert Oracle database Architecture on this:

<quote>
So, let’s look at how we might go about creating a clustered object. Creating a cluster of tables in the object is straightforward. The definition of the storage of the object (PCTFREE, PCTUSED, INITIAL, and so on) is associated with the CLUSTER, not the tables. This makes sense since there will be many tables in the cluster, and they will be on the same block. Having different PCTFREEs would not make sense. Therefore, a CREATE CLUSTER looks a lot like a CREATE TABLE with a small number of columns (just the cluster key columns):

ops$tkyte@ORA10GR1> create cluster emp_dept_cluster
  2  ( deptno number(2) )
  3  size 1024
  4  /
Cluster created.

Here, we have created an index cluster (the other type being a hash cluster, which we’ll look at in the next section). The clustering column for this cluster will be the DEPTNO column. The columns in the tables do not have to be called DEPTNO, but they must be NUMBER(2), to match this definition. We have, on the cluster definition, a SIZE 1024 option. This is used to tell Oracle that we expect about 1,024 bytes of data to be associated with each cluster key value. Oracle will use that to compute the maximum number of cluster keys that could fit per block. Given that we have an 8KB block size, Oracle will fit up to seven cluster keys (but maybe less if the data is larger then expected) per database block. This is, the data for departments 10, 20, 30, 40, 50, 60, and 70 would tend to go onto one block, and as soon as we insert department 80, a new block will be used. That does not mean that the data is stored in a sorted manner; it just means that if we inserted the departments in that order, they would naturally tend to be put together. If we inserted the departments in the order 10, 80, 20, 30, 40, 50, 60, and then 70, the final department, 70, would tend to be on the newly added block. As we’ll see below, both the size of the data and the order in which the data is inserted will affect the number of keys we can store per block.

The SIZE parameter therefore controls the maximum number of cluster keys per block. It is the single largest influence on the space utilization of our cluster. Set the size too high, and we’ll get very few keys per block and we’ll use more space then we need. Set the size too low, and we’ll get excessive chaining of data, which offsets the purpose of the cluster to store all of the data together on a single block. It is the most important parameter for a cluster.

Now for the cluster index on our cluster. We need to index the cluster before we can put data in it. We could create tables in the cluster right now, but we’re going to create and populate the tables simultaneously, and we need a cluster index before we can have any data. The cluster index’s job is to take a cluster key value and return the block address of the block that contains that key. It is a primary key in effect, where each cluster key value points to a single block in the cluster itself. So, when we ask for the data in department 10, Oracle will read the cluster key, determine the block address for that, and then read the data. The cluster key index is created as follows:

ops$tkyte@ORA10GR1> create index emp_dept_cluster_idx
  2  on cluster emp_dept_cluster
  3  /
Index created.

It can have all of the normal storage parameters of an index and can be stored in another tablespace. It is just a regular index, so it can be on multiple columns; it just happens to index into a cluster and can also include an entry for a completely null value (see Chapter 11 for the reason why this is interesting to note). Note that we do not specify a list of columns in this CREATE INDEX statement—that is derived from the CLUSTER definition itself. Now we are ready to create our tables in the cluster:

ops$tkyte@ORA10GR1> create table dept
  2  ( deptno number(2) primary key,
  3    dname  varchar2(14),
  4    loc    varchar2(13)
  5  )
  6  cluster emp_dept_cluster(deptno)
  7  /
Table created.
 
ops$tkyte@ORA10GR1> create table emp
  2  ( empno    number primary key,
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number,
  6    hiredate date,
  7    sal      number,
  8    comm     number,
  9    deptno number(2) references dept(deptno)
 10  )
 11  cluster emp_dept_cluster(deptno)
 12  /
Table created.

Here, the only difference from a “normal” table is that we used the CLUSTER keyword and told Oracle which column of the base table will map to the cluster key in the cluster itself. Remember, the cluster is the segment here, therefore this table will never have segment attributes such as TABLESPACE, PCTFREE, and so on—they are attributes of the cluster segment, not the table we just created. 
</quote> 

Hi Tom -

Can you explain how an IOT with a primary key populated by sys_guid() would work?

Just not sure how the IOT would determine what order to put RAW values like 6FB56BAF0F5140E184C5E9FDA101389F, B3457FA7049F4C41A48A6E250982CEF6, 8DC7D39212FD4BA390A53A0925D47D7D, . . .

How does it determine if 6FB56BAF0F5140E184C5E9FDA101389F should be stored before or after the value B3457FA7049F4C41A48A6E250982CEF6 ???

Thanks for your advice

Hi Tom,

I am considering the use of a cluster to store together data from 5 tables than are joined frequently.

I have set up the following test:

create cluster simple_cluster (id number) size 128;

create index idx_simple_cluster on cluster simple_cluster;

create table t1 (id number primary key, a varchar2(40)) cluster simple_cluster (id);

create table t2 (id number primary key, b date) cluster simple_cluster (id);

create table ht1 (id number primary key, a varchar2(40));

create table ht2 (id number primary key, b date);

insert
when (1=1) then
  into t1 values (id,a)
when (1=1) then
  into t2 values (id,b)
select object_id id, object_name a, created b from all_objects where rownum <= 10000;

commit;

insert into ht1 select * from t1;

insert into ht2 select * from t2;

commit;

analyze cluster simple_cluster compute statistics;

analyze index idx_simple_cluster compute statistics;

analyze table t1 compute statistics for table for all indexes for all indexed columns;

analyze table t2  compute statistics for table for all indexes for all indexed columns;

analyze table ht1 compute statistics for table for all indexes for all indexed columns;

analyze table ht2 compute statistics for table for all indexes for all indexed columns;

analyze index idx_simple_cluster validate structure;

select lf_blks, br_blks, height, distinct_keys from index_stats;

   LF_BLKS    BR_BLKS     HEIGHT DISTINCT_KEYS
---------- ---------- ---------- -------------
        27          1          2         10000

select dbms_rowid.rowid_block_number(t1.rowid) t1_block,
       dbms_rowid.rowid_block_number(t2.rowid) t2_block
from t1, t2
where t1.id = t2.id
  and t1.id = 258;

  T1_BLOCK   T2_BLOCK
---------- ----------
     22085      22085

set autotrace traceonly

select *
from t1, t2
where t1.id = t2.id
  and t1.id = 258;

...
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    38 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |     1 |    38 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2          |     1 |    11 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C006814 |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |    27 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C006813 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
...
Estadistica
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
...

select *
from ht1, ht2
where ht1.id = ht2.id
  and ht1.id = 258;
...
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    39 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |     1 |    39 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| HT2         |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C006816 |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| HT1         |     1 |    26 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C006815 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
...
Estadistica
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
...

alter index idx_simple_cluster rebuild;

analyze index idx_simple_cluster validate structure;

set autotrace off

select lf_blks, br_blks, height, distinct_keys from index_stats;

   LF_BLKS    BR_BLKS     HEIGHT DISTINCT_KEYS
---------- ---------- ---------- -------------
        24          1          2         10000

set autotrace traceonly

select *
from t1, t2
where t1.id = t2.id
  and t1.id = 258;
/
...
skiped the first plan
...
--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |    38 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS         |                    |     1 |    38 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS CLUSTER| T2                 |     1 |    11 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN  | IDX_SIMPLE_CLUSTER |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS CLUSTER| T1                 |     1 |    27 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN  | IDX_SIMPLE_CLUSTER |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
...
Estadistica
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
...

The cluster index has a height of 2. Therefore, with 3 blocks of I/O, in theory, we can get the data from both tables we are looking for. How is it possible to take 8 consistent gets?

Thanks in advance,

Hi Tom,

I have obtained the following statistics from a trace file:

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=8 pr=0 pw=0 time=1440 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=50728 op='TABLE ACCESS CLUSTER T2 (cr=4 pr=0 pw=0 time=1362 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=50725 op='INDEX UNIQUE SCAN IDX_SIMPLE_CLUSTER (cr=2 pr=0 pw=0 time=635 us)'
STAT #1 id=4 cnt=1 pid=1 pos=2 obj=50726 op='TABLE ACCESS CLUSTER T1 (cr=4 pr=0 pw=0 time=51 us)'
STAT #1 id=5 cnt=1 pid=4 pos=1 obj=50725 op='INDEX UNIQUE SCAN IDX_SIMPLE_CLUSTER (cr=2 pr=0 pw=0 time=26 us)'

Meaning that I performed 4 consistent gets for the cluster index and other 4 for the cluster access. But, actually, all the data for that cluster key is in one block. ¿?

Thanks

Hi Tom,

I researched a bit more in my test case and find something interesting.

I looked at the data distribution:

select table_name, blocks, empty_blocks,  avg_space
from user_tables
where table_name in ('T1','T2','HT1','HT2')
order by table_name;

TABLE_NAME                         BLOCKS EMPTY_BLOCKS  AVG_SPACE
------------------------------ ---------- ------------ ----------
HT1                                   370           14       1694
HT2                                   370           14       1694
T1                                    748           20       1016
T2                                    748           20       1016

select i.table_name, i.index_name, s.blocks
from user_segments s, user_indexes i
where (i.table_name in ('T1','T2','HT1','HT2') or i.index_name = 'IDX_SIMPLE_CLUSTER')
  and s.segment_name = i.index_name
order by 1,2;

TABLE_NAME                     INDEX_NAME                         BLOCKS
------------------------------ ------------------------------ ----------
HT1                            SYS_C006875                           104
HT2                            SYS_C006876                           104
SIMPLE_CLUSTER                 IDX_SIMPLE_CLUSTER                    104
T1                             SYS_C006873                            96
T2                             SYS_C006874                            96

So, the cluster is more or less the same size than the two heap tables together. Indeed, we have two indexes for the heap tables and three for the cluster (two for each table and another one for the cluster index).

Depending on the plan we can use a distinct index. Therefore, we are using more space in the cluster approach.

I set up another test, based on chapter 7, p391 of your book "Effective Oracle by Design".

********************************************************************************
SELECT T1.ID, T1.A, T2.B
FROM
 T1, T2 WHERE T1.ID = T2.ID AND T1.ID = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 387590     28.20      22.90          0          0          0           0
Fetch   387590     45.36      31.64        151    1275180          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   775181     73.57      54.55        151    1275180          0      100000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 58     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  NESTED LOOPS  (cr=1275180 pr=151 pw=0 time=40365241 us)
 100000   TABLE ACCESS CLUSTER T2 (cr=975180 pr=151 pw=0 time=21235320 us)
 100000    INDEX UNIQUE SCAN IDX_SIMPLE_CLUSTER (cr=775180 pr=22 pw=0 time=9269972 us)(object id 50725)
 100000   TABLE ACCESS CLUSTER T1 (cr=300000 pr=0 pw=0 time=7004082 us)
 100000    INDEX UNIQUE SCAN IDX_SIMPLE_CLUSTER (cr=200000 pr=0 pw=0 time=2838852 us)(object id 50725)


********************************************************************************

SELECT HT1.ID, HT1.A, HT2.B
FROM
 HT1, HT2 WHERE HT1.ID = HT2.ID AND HT1.ID = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 387590     29.00      23.69          0          0          0           0
Fetch   387590     46.24      31.98        130    1175180          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   775181     75.25      55.68        130    1175180          0      100000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 58     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  NESTED LOOPS  (cr=1175180 pr=130 pw=0 time=40937034 us)
 100000   TABLE ACCESS BY INDEX ROWID HT2 (cr=875180 pr=54 pw=0 time=21063360 us)
 100000    INDEX UNIQUE SCAN SYS_C006816 (cr=775180 pr=30 pw=0 time=9443417 us)(object id 50733)
 100000   TABLE ACCESS BY INDEX ROWID HT1 (cr=300000 pr=76 pw=0 time=7366452 us)
 100000    INDEX UNIQUE SCAN SYS_C006815 (cr=200000 pr=31 pw=0 time=3148368 us)(object id 50731)

As you can see, with my cluster approach I am making more physical and logical I/O. With that results, I am discouraged to use clusters in our design.

Thanks,

Hello Tom,

If I set up the following example:

create cluster scott.emp_dept_cluster
( deptno number(2) )
tablespace users
size 1024
/

Cluster created.

create index scott.emp_dept_cluster_idx
on cluster scott.emp_dept_cluster
tablespace users
/

Index created.

create table scott.emp
( empno    number primary key,
ename    varchar2(10),
job      varchar2(9),
mgr      number,
hiredate date,
sal      number,
comm     number,
deptno number(2) references scott.dept(deptno)
)
cluster scott.emp_dept_cluster(deptno)
/

Table created.

insert into scott.emp (empno,ename,job,mgr,hiredate,sal,comm     ,deptno) values (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,20);

1 row inserted.

commit;

update scott.emp set DEPTNO = 30 where ENAME = 'SCOTT';

1 row updated.

commit;

Can one conclude the record for employee Scott has been moved from the block with the cluster key id = 20 to the block with the cluster key id = 30?

ops$tkyte%ORA9IR2> create cluster emp_dept_cluster
  2  ( deptno number(2) )
  3  tablespace users<b>
  4  size 8k -- to make a deptno have a block to itself in my 8k block db</b>
  5  /

Cluster created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create index emp_dept_cluster_idx
  2  on cluster emp_dept_cluster
  3  tablespace users
  4  /

Index created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table emp
  2  ( empno    number primary key,
  3  ename    varchar2(10),
  4  job      varchar2(9),
  5  mgr      number,
  6  hiredate date,
  7  sal      number,
  8  comm     number,
  9  deptno number(2)
 10  )
 11  cluster emp_dept_cluster(deptno)
 12  /

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into emp select * from scott.emp;

14 rows created.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> select deptno, ename, rowid, dbms_rowid.rowid_block_number(rowid) from emp order by deptno;

    DEPTNO ENAME      ROWID              DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ---------- ------------------ ------------------------------------
        10 CLARK      AAAH68AAJAAAA+3AAA                                 4023
        10 KING       AAAH68AAJAAAA+3AAB                                 4023
        10 MILLER     AAAH68AAJAAAA+3AAC                                 4023
        20 SMITH      AAAH68AAJAAAA+4AAA                                 4024
        20 ADAMS      AAAH68AAJAAAA+4AAD                                 4024
        20 JONES      AAAH68AAJAAAA+4AAB                                 4024
        20 SCOTT      AAAH68AAJAAAA+4AAC                                 4024
        20 FORD       AAAH68AAJAAAA+4AAE                                 4024
        30 ALLEN      AAAH68AAJAAAA+0AAA                                 4020
        30 WARD       AAAH68AAJAAAA+0AAB                                 4020
        30 MARTIN     AAAH68AAJAAAA+0AAC                                 4020
        30 BLAKE      AAAH68AAJAAAA+0AAD                                 4020
        30 TURNER     AAAH68AAJAAAA+0AAE                                 4020
        30 JAMES      AAAH68AAJAAAA+0AAF                                 4020

14 rows selected.

ops$tkyte%ORA9IR2> update emp set DEPTNO = 30 where ENAME = 'SCOTT';

1 row updated.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> select deptno, ename, rowid, dbms_rowid.rowid_block_number(rowid) from emp order by deptno;

    DEPTNO ENAME      ROWID              DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ---------- ------------------ ------------------------------------
        10 CLARK      AAAH68AAJAAAA+3AAA                                 4023
        10 KING       AAAH68AAJAAAA+3AAB                                 4023
        10 MILLER     AAAH68AAJAAAA+3AAC                                 4023
        20 SMITH      AAAH68AAJAAAA+4AAA                                 4024
        20 FORD       AAAH68AAJAAAA+4AAE                                 4024
        20 ADAMS      AAAH68AAJAAAA+4AAD                                 4024
        20 JONES      AAAH68AAJAAAA+4AAB                                 4024
        30 ALLEN      AAAH68AAJAAAA+0AAA                                 4020
        30 WARD       AAAH68AAJAAAA+0AAB                                 4020
        30 MARTIN     AAAH68AAJAAAA+0AAC                                 4020
        30 BLAKE      AAAH68AAJAAAA+0AAD                                 4020
        30 TURNER     AAAH68AAJAAAA+0AAE                                 4020
        30 JAMES      AAAH68AAJAAAA+0AAF                                 4020
        30 SCOTT      AAAH68AAJAAAA+4AAC                                 4024

14 rows selected.

ops$tkyte%ORA9IR2> create index ename_idx on emp(ename);

Index created.

ops$tkyte%ORA9IR2> insert into emp select * from scott.emp;

14 rows created.

ops$tkyte%ORA9IR2> select * from emp where ENAME='SCOTT';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> @mystat continued
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE
---------------------- ----------
table fetch continued           5
row


ops$tkyte%ORA9IR2> set autotrace traceonly statistics
ops$tkyte%ORA9IR2> select * from emp where ENAME='SCOTT';


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        805  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA9IR2> set autotrace off
ops$tkyte%ORA9IR2> @mystat2
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
table fetch continued           5                0
row


ops$tkyte%ORA9IR2> update emp set DEPTNO = 30 where ENAME = 'SCOTT';

1 row updated.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> @mystat continued
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE
---------------------- ----------
table fetch continued           5
row


ops$tkyte%ORA9IR2> set autotrace traceonly statistics
ops$tkyte%ORA9IR2> select * from emp where ENAME='SCOTT';


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        805  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA9IR2> set autotrace off
ops$tkyte%ORA9IR2> @mystat2
ops$tkyte%ORA9IR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
table fetch continued           6                1
row

Tom,

Thank you for the exceptionally detailed walk through. Professional and to the point as always.

Hi Tom,

Trying the hash cluster examples from your new book (pg 385). I've changed the table and cluster names, but I think I've got everything straight. I can't seem to get past this error - any quick thoughts?

ops$us\suzzell@OPERA> create cluster single_tab_hash_clust
  2  ( hash_key number(10) )
  3  hashkeys 75000
  4  size 150
  5  single table
  6  hash is HASH_KEY
  7  /

Cluster created.

Elapsed: 00:00:01.00
ops$us\suzzell@OPERA> create table t10_9_hashed
  2  cluster single_tab_hash_clust(object_id)
  3  as
  4  select owner, object_name, subobject_name,
  5         cast( object_id as number(10) ) object_id,
  6         data_object_id, object_type, created,
  7         last_ddl_time, timestamp, status, temporary,
  8         generated, secondary
  9    from dba_objects
 10  /
cluster single_tab_hash_clust(object_id)
                              *
ERROR at line 2:
ORA-01753: column definition incompatible with clustered column definition

Should probably mention I'm using 9i...

Thanks,

I tested in 10.2.0.2 and it worked.

At least I can rest knowing I didn't miss anything stupid.

Thanks, Tom

Hash accessing consumes much more CPU than index accessing even if hash accessing uses 1 LIO and index accessing uses at least 2 LIO.
In expect one-on-one page 303.
"The hash cluster query took significantly more CPU. This too could be anticipated.The act of performing a hash is very CPU¿intensive"

But for the test with a table using "hash is" (page 304), since oracle don't need perform a hash, so it won't take much CPU. But the result of your testing is that the CPU is still much more than index accessing.

I did a test on a huge single clustered table, an index access with 5 LIO uses nearly the same CPU as hash accessing with 1 LIO. I don't know whether the extra CPU goes to. And based on the test, there is no advantage to use hash cluster as for normal table, data are tightly packed, but for cluster table, data are loosely packed. That means if using cluster table we will have a much less cache hit.

Thanks Tom, 

I post my test result below and I also have several questions/guess here, please confirm:

1: what's the overhead of hashing? Could hashing overhead be ignored in comparison with 1 LIO?  

My test told me hashing seems not very expensive as if using the "hash is" instead of 
the default hash function,there is only very little improvement in elapse time. 

2: Seems one LIO to access an index block is much cheaper than one LIO to access
 a block by hash  When access a block by hash, oracle has to scan the whole block,
 but to access index block, oracle could quick locate the row in the block 
 which save lots of cpu  Only in this way I could explain why 1 LIO for hash is almost 
the same costly as 5 LIO as index access  (4 LIO index blocks and 1 LIO for data block access by rowid)

3: The big table(30G) I want to turn into cluster is a partition table, so the 
contention is not so huge.
   And to avoid hash collision, I'd like to allocate double space than 
the original one,   then the cache hit ratio could drop a lot. 

If 1 LIO for hash access is nearly the same as 5LIO for index access,
 then seems there is no need to make the change, am I right?


SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> declare
  2   v_record LOOKUP%rowtype;
  3  begin
  4     for i in 1..10000
  5     loop
  6        select * into v_record from LOOKUP where user_id=74560223;
  7     end loop;
  8  end;
  9  /
PL/SQL procedure successfully completed.
SQL>
SQL> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
SQL> declare
  2   v_record LOOKUP%rowtype;
  3  begin
  4     for i in 1..10000
  5     loop
  6        select /*+ INDEX(LOOKUP,LOOKUP_PK) */* into v_record from LOOKUP where user_id=74560223;
  7     end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.rs_stop(5)
Run1 ran in 149 hsecs
Run2 ran in 160 hsecs
run 1 ran in 93.13% of the time
      
Name                                  Run1        Run2        Diff
STAT...db block gets                    18          24           6
STAT...db block gets from cach          18          24           6
LATCH.channel operations paren          56          50          -6
LATCH.active service list               14          21           7
STAT...recursive cpu usage           1,089       1,069         -20
STAT...CPU used by this sessio       1,110       1,090         -20
STAT...DB time                       1,122       1,090         -32
STAT...CPU used when call star       1,123       1,090         -33
STAT...Elapsed Time                  1,080       1,134          54
STAT...bytes received via SQL*       1,577       1,635          58
STAT...redo size                     3,044       3,104          60
LATCH.checkpoint queue latch         2,560       2,496         -64
LATCH.undo global data                   5          74          69
LATCH.enqueues                         132         206          74
LATCH.enqueue hash chains              138         213          75
STAT...session uga memory           -2,336       3,944       6,280
STAT...cluster key scans            10,000           0     -10,000
STAT...table fetch by rowid              2      10,002      10,000
STAT...buffer is not pinned co      10,004      20,004      10,000
STAT...cluster key scan block       10,000           0     -10,000
STAT...rows fetched via callba           2      10,002      10,000
STAT...no work - consistent re      10,000           0     -10,000
STAT...index fetch by key                2      10,002      10,000
LATCH.cache buffers chains          20,158      50,169      30,011
STAT...consistent gets              10,015      50,017      40,002
STAT...consistent gets from ca      10,015      50,017      40,002
STAT...session logical reads        10,033      50,041      40,008 
           ===> hash access: 1 LIO, index access: 5 LIO 
STAT...consistent gets - exami          10      50,012      50,002
      
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
23,590      53,763      30,173     43.88%

PL/SQL procedure successfully completed.

Sir
Regarding the index cluster tables.
Is it necessary to load the data with cluster key ?

Assume I have two tables that are clustered.

First, I insert big amount of data to table1.
All the blocks in the cluster will be filled.

Now I insert to table2.

How can oracle put the clustered column to same block in table1.

The clustered blocks are already full.

ops$tkyte%ORA10GR2> drop table t1;

Table dropped.

ops$tkyte%ORA10GR2> drop cluster my_cluster;

Cluster dropped.

ops$tkyte%ORA10GR2> create cluster my_cluster ( id number ) size 2000;

Cluster created.

ops$tkyte%ORA10GR2> create index my_cluster_idx on cluster my_cluster;

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t1 ( id number, data char(450) ) cluster my_cluster( id );

Table created.

ops$tkyte%ORA10GR2> insert into t1 select rownum, 'x' from dual connect by level <= 100;

100 rows created.

ops$tkyte%ORA10GR2> select min(cnt), max(cnt), avg(cnt) , count(*)
  2    from (
  3  select count(*) cnt
  4    from t1
  5   group by dbms_rowid.rowid_block_number( rowid )
  6         );

  MIN(CNT)   MAX(CNT)   AVG(CNT)   COUNT(*)
---------- ---------- ---------- ----------
         4          4          4         25

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t1;

Table dropped.

ops$tkyte%ORA10GR2> drop cluster my_cluster;

Cluster dropped.

ops$tkyte%ORA10GR2> create cluster my_cluster ( id number ) size 1000;

Cluster created.

ops$tkyte%ORA10GR2> create index my_cluster_idx on cluster my_cluster;

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t1 ( id number, data char(450) ) cluster my_cluster( id );

Table created.

ops$tkyte%ORA10GR2> insert into t1 select rownum, 'x' from dual connect by level <= 100;

100 rows created.

ops$tkyte%ORA10GR2> select min(cnt), max(cnt), avg(cnt) , count(*)
  2    from (
  3  select count(*) cnt
  4    from t1
  5   group by dbms_rowid.rowid_block_number( rowid )
  6         );

  MIN(CNT)   MAX(CNT)   AVG(CNT)   COUNT(*)
---------- ---------- ---------- ----------
         4          8 7.69230769         13

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t1;

Table dropped.

ops$tkyte%ORA10GR2> drop cluster my_cluster;

Cluster dropped.

ops$tkyte%ORA10GR2> create cluster my_cluster ( id number ) size 8000;

Cluster created.

ops$tkyte%ORA10GR2> create index my_cluster_idx on cluster my_cluster;

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t1 ( id number, data char(450) ) cluster my_cluster( id );

Table created.

ops$tkyte%ORA10GR2> insert into t1 select rownum, 'x' from dual connect by level <= 100;

100 rows created.

ops$tkyte%ORA10GR2> select min(cnt), max(cnt), avg(cnt) , count(*)
  2    from (
  3  select count(*) cnt
  4    from t1
  5   group by dbms_rowid.rowid_block_number( rowid )
  6         );

  MIN(CNT)   MAX(CNT)   AVG(CNT)   COUNT(*)
---------- ---------- ---------- ----------
         1          1          1        100

Sir;

As far as I know the purpose of the cluster is two or more column share the same block.

In this scenerio,
If I insert into table1,we'll put 4 cluster keys on a (block1) and then move on to the (block2).
Then I insert into table2, we'll again put 4 cluster keys on a different (block3) and then move on to the (block4).

The thing is,the clustered column of table1 and table2 will not reside in the same block..
They are all using different blocks.

Sir.
Thanks a lot for your enlightining explanation. I am now clear.
I apprecate your time.
Sorry for any inconvinience.

Tom,

Having read this article, we've done some benchmarks and have decided that we will implement single table hash clusters (STHC).

We now need to implement this in a production environment by converting an existing HEAP table (referenced by foreign keys) to a STHC. It's not a huge table, approx 4 million rows.

Is there a "quick" command to do this?

ops$tkyte%ORA10GR2> create table t1
  2  as
  3  select * from all_objects
  4   order by dbms_random.random;

Table created.

ops$tkyte%ORA10GR2> alter table t1 add constraint t1_pk primary key(object_id);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create cluster Hash_Cluster
  2  ( id number )
  3  SINGLE TABLE
  4  hashkeys 80000 -- number of distinct lookups we expect over time
  5  size 125         -- size of data associated with key
  6  /

Cluster created.

ops$tkyte%ORA10GR2> create table t2 (
  2  OWNER                  VARCHAR2(30),
  3  OBJECT_NAME            VARCHAR2(30),
  4  SUBOBJECT_NAME         VARCHAR2(30),
  5  OBJECT_ID              NUMBER,
  6  DATA_OBJECT_ID         NUMBER,
  7  OBJECT_TYPE            VARCHAR2(19),
  8  CREATED                DATE,
  9  LAST_DDL_TIME          DATE,
 10  TIMESTAMP              VARCHAR2(19),
 11  STATUS                 VARCHAR2(7),
 12  TEMPORARY              VARCHAR2(1),
 13  GENERATED              VARCHAR2(1),
 14  SECONDARY              VARCHAR2(1),
 15  NAMESPACE              NUMBER,
 16  EDITION_NAME           VARCHAR2(30)
 17  )
 18  cluster hash_cluster( object_id );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_redefinition.can_redef_table( user, 'T1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> variable nerrors number
ops$tkyte%ORA10GR2> begin
  2          dbms_redefinition.copy_table_dependents
  3          ( user, 'T1', 'T2',
  4            copy_indexes => dbms_redefinition.cons_orig_params,
  5            num_errors => :nerrors );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> drop table t2;

Table dropped.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T1"
   (    "OWNER" VARCHAR2(30) CONSTRAINT "SYS_C0039325" NOT NULL ENABLE NOVALIDATE,

        "OBJECT_NAME" VARCHAR2(30) CONSTRAINT "SYS_C0039326" NOT NULL ENABLE NOVALIDAT
E,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER CONSTRAINT "SYS_C0039327" NOT NULL ENABLE NOVALIDATE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE CONSTRAINT "SYS_C0039328" NOT NULL ENABLE NOVALIDATE,
        "LAST_DDL_TIME" DATE CONSTRAINT "SYS_C0039329" NOT NULL ENABLE NOVALIDATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(30),
         CONSTRAINT "T1_PK" PRIMARY KEY ("OBJECT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE NOVALIDATE
   ) CLUSTER "OPS$TKYTE"."HASH_CLUSTER" ("OBJECT_ID")

Thank you. dbms_redefinition - most useful to know!

Tom,

In one of our data warehouses we have a currency conversion table that gets populated with conversion rates on a daily
basis. The data is kept historically so that reports can compute the conversion rate for any day in the past. The math
to do the rate conversion is very simple but the problem is that it can be done up to a 100 million times per day and
this consumes quite a bit of CPU when the reports are running. The conversion rate table has about 500,000 rows and I
thought I would pre-compute the conversions with a materialized view so that there was no math involved - only a select.
The resulting MV will contain about 170 million rows. Initially I built this with a MV with an IOT table, but was
considering a single-table hash cluster instead, since this seems to be your suggestion in "Effective Oracle by Design".
I built both versions and was a little suprised that the hash cluster solution is slower (elapsed time) and also
consumes more CPU when I check the consumption in v$sqlarea. Could you check out my solution and let me know if

A) I've missed something with my hash cluster solution and this could be improved.
B) I'm better off with the IOT solution
C) I'm better off with something else entirely.

I'm running Oracle 9.2.0.7 on Sun Solaris. Many thanks in advance, your book does rock!

Thanks

Stacey

-- Create the IOT table

--DROP TABLE STG.MV_WSG_IOT;
CREATE TABLE STG.MV_WSG_IOT
(
CONV_DATE DATE NOT NULL,
FROM_CURRENCY_CODE VARCHAR2(6 BYTE) NOT NULL,
TO_CURRENCY_CODE VARCHAR2(6 BYTE) NOT NULL,
CONV_RATE NUMBER NOT NULL,
A_ROWID ROWID NOT NULL,
B_ROWID ROWID NOT NULL,
CONSTRAINT MV_WSG_PK_IOT
PRIMARY KEY
(CONV_DATE, FROM_CURRENCY_CODE, TO_CURRENCY_CODE)
)
ORGANIZATION INDEX
NOLOGGING
TABLESPACE AUDIT_SHIPMENT_DATA
STORAGE (
INITIAL 1M
NEXT 1M
)
COMPRESS 2
NOPARALLEL;

-- Load the IOT with 500000 rows of data
begin
insert/*+ APPEND */ into STG.MV_WSG_IOT select * from stg.mv_wsg where rownum < 500001;
commit;
end;

-- Create the cluster

--DROP TABLE STG.MV_WSG_HASH_CLUSTER;
--DROP CLUSTER STG.CLU_CURR_CONV_LKP;
CREATE CLUSTER STG.CLU_CURR_CONV_LKP
(
CONV_DATE DATE,
FROM_CURRENCY_CODE VARCHAR2(6 BYTE),
TO_CURRENCY_CODE VARCHAR2(6 BYTE)
)
TABLESPACE AUDIT_SHIPMENT_DATA
SIZE 61
STORAGE (
INITIAL 1M
NEXT 1M
)
SINGLE TABLE
HASHKEYS 500000
NOROWDEPENDENCIES
NOCACHE
NOPARALLEL;

-- Create the hash clustered table

--DROP TABLE STG.MV_WSG_HASH_CLUSTER
CREATE TABLE STG.MV_WSG_HASH_CLUSTER
(
CONV_DATE DATE NOT NULL,
FROM_CURRENCY_CODE VARCHAR2(6 BYTE) NOT NULL,
TO_CURRENCY_CODE VARCHAR2(6 BYTE) NOT NULL,
CONV_RATE NUMBER NOT NULL,
A_ROWID ROWID NOT NULL,
B_ROWID ROWID NOT NULL
)
CLUSTER STG.CLU_CURR_CONV_LKP(CONV_DATE,FROM_CURRENCY_CODE,TO_CURRENCY_CODE)
;

-- Load the Hash clustered table with 500000 rows of data
begin
insert/*+ APPEND */ into STG.MV_WSG_HASH_CLUSTER select * from stg.mv_wsg where rownum < 500001;
commit;
end;

-- Analyze both tables

BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'STG'
,TabName => 'MV_WSG_HASH_CLUSTER'
,Estimate_Percent => NULL
,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
commit;
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'STG'
,TabName => 'MV_WSG_IOT'
,Estimate_Percent => NULL
,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
commit;
END;

--Run an explain plan for each of the queries:

SELECT * FROM STG.MV_WSG_IOT t
WHERE 1=1
AND t.conv_date = '05-JUL-04'
AND t.from_currency_code = 'JPY'
AND t.to_currency_code = 'CNY'

Plan
SELECT STATEMENT CHOOSE Cost: 2 Bytes: 43 Cardinality: 1 IO Cost: 2
1 INDEX UNIQUE SCAN UNIQUE STG.MV_WSG_PK_IOT Search Columns: 3 Access Predicates: "T"."CONV_DATE"='05-JUL-04' AND "T"."FROM_CURRENCY_CODE"='JPY' AND "T"."TO_CURRENCY_CODE"='CNY' Cost: 2 Bytes: 43 Cardinality: 1 IO Cost: 2

SELECT * FROM STG.MV_WSG_HASH_CLUSTER t
WHERE 1=1
AND t.conv_date = '05-JUL-04'
AND t.from_currency_code = 'JPY'
AND t.to_currency_code = 'CNY'

Plan
SELECT STATEMENT CHOOSE Bytes: 46 Cardinality: 1
1 TABLE ACCESS HASH STG.MV_WSG_HASH_CLUSTER Object Instance: 1 Search Columns: 3 Access Predicates: "T"."CONV_DATE"='05-JUL-04' AND "T"."FROM_CURRENCY_CODE"='JPY' AND "T"."TO_CURRENCY_CODE"='CNY' Bytes: 46 Cardinality: 1

-- Create a jumbled list to use in querying both tables.

create table stg.test_params
tablespace AUDIT_SHIPMENT_DATA
compress
as
select conv_date,from_currency_code,to_currency_code from (
select * from stg.mv_wsg x where rownum < 500001) x2
order by to_char(conv_date,'dd'),to_char(conv_date,'yy') desc, to_char(conv_date,'mm'), substr(from_currency_code,2,1),substr(to_currency_code,3,1) desc;

-- Run the following test block to time random conversion rate lookups...

DECLARE
CURSOR c_master IS SELECT * FROM stg.test_params WHERE rownum < 100001;
CURSOR c_baseline_query(b1 IN stg.mv_wsg_iot.conv_date%TYPE
,b2 IN stg.mv_wsg_iot.from_currency_code%TYPE
,b3 IN stg.mv_wsg_iot.to_currency_code%TYPE
) IS
SELECT t.conv_rate FROM STG.MV_WSG_IOT t WHERE t.conv_date = b1 AND t.from_currency_code = b2 AND t.to_currency_code = b3;
CURSOR c_replacement_query(
b1 IN stg.mv_wsg_hash_cluster.conv_date%TYPE
,b2 IN stg.mv_wsg_hash_cluster.from_currency_code%TYPE
,b3 IN stg.mv_wsg_hash_cluster.to_currency_code%TYPE
) IS
SELECT t.conv_rate FROM STG.MV_WSG_HASH_CLUSTER t WHERE t.conv_date = b1 AND t.from_currency_code = b2 AND t.to_currency_code = b3;
-- Constants and Variables
num_seconds_in_a_day NUMBER := 24*60*60;
start_time DATE;
end_time DATE;
baseline_elapsed_time NUMBER := 0;-- In seconds
replacement_elapsed_time NUMBER := 0;-- In seconds
percent_change NUMBER := 0;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
COMMIT;
-- Run and time the baseline query
start_time := SYSDATE;
DBMS_OUTPUT.put_line('Baseline run began at: ' || TO_CHAR(start_time, 'mm/dd/yyyy hh24:mi:ss'));
COMMIT;
FOR m IN c_master LOOP
FOR r IN c_baseline_query(m.conv_date,m.from_currency_code,m.to_currency_code) LOOP
NULL;
END LOOP;-- c_test
END LOOP;--c_master
end_time := sysdate;
DBMS_OUTPUT.put_line('Baseline run ended at: ' || TO_CHAR(end_time, 'mm/dd/yyyy hh24:mi:ss'));
COMMIT;
baseline_elapsed_time := (end_time - start_time) * num_seconds_in_a_day;
-- Run and time the replacement query
start_time := SYSDATE;
DBMS_OUTPUT.put_line('Replacement query run began at: ' || TO_CHAR(start_time, 'mm/dd/yyyy hh24:mi:ss'));
COMMIT;
FOR m IN c_master LOOP
FOR r IN c_replacement_query(m.conv_date,m.from_currency_code,m.to_currency_code) LOOP
NULL;
END LOOP;-- c_test
END LOOP;--c_master
end_time := sysdate;
DBMS_OUTPUT.put_line('Replacement query run ended at: ' || TO_CHAR(end_time, 'mm/dd/yyyy hh24:mi:ss'));
COMMIT;
replacement_elapsed_time := (end_time - start_time) * num_seconds_in_a_day;
-- Summarize the results
DBMS_OUTPUT.put_line('******************************* SUMMARY *******************************');
DBMS_OUTPUT.put_line('Baseline elapsed time in seconds -> '||to_char(baseline_elapsed_time));
DBMS_OUTPUT.put_line('New query elapsed time in seconds -> '||to_char(replacement_elapsed_time));
percent_change := ROUND(ABS(((baseline_elapsed_time-replacement_elapsed_time)/baseline_elapsed_time)*100));
IF (replacement_elapsed_time < baseline_elapsed_time) THEN
dbms_output.put_line('New query is '||to_char(percent_change)||'% faster than the original.');
ELSIF (replacement_elapsed_time > baseline_elapsed_time) THEN
dbms_output.put_line('New query is '||to_char(percent_change)||'% slower than the original.');
ELSE
dbms_output.put_line('No significant performance difference detected, you might try a larger test case');
END IF;
COMMIT;
END;

Gives these results:


Baseline run began at: 10/13/2009 20:04:43
Baseline run ended at: 10/13/2009 20:05:10
Replacement query run began at: 10/13/2009 20:05:10
Replacement query run ended at: 10/13/2009 20:05:41
******************************* SUMMARY *******************************
Baseline elapsed time in seconds -> 27
New query elapsed time in seconds -> 30.99999999999999999999999999999999999997
New query is 15% slower than the original.

DBMS_OUTPUT.put_line('New query elapsed time in seconds -> '||to_char(replacement_elapsed_time));
percent_change := ROUND(ABS(((baseline_elapsed_time-replacement_elapsed_time)/baseline_elapsed_time)*100));
IF (replacement_elapsed_time < baseline_elapsed_time) THEN
dbms_output.put_line('New query is '||to_char(percent_change)||'% faster than the original.');
ELSIF (replacement_elapsed_time > baseline_elapsed_time) THEN
dbms_output.put_line('New query is '||to_char(percent_change)||'% slower than the original.');
ELSE
dbms_output.put_line('No significant performance difference detected, you might try a larger test case');
END IF;
COMMIT;
END;

Gives these results:


Baseline run began at: 10/13/2009 20:04:43
Baseline run ended at: 10/13/2009 20:05:10
Replacement query run began at: 10/13/2009 20:05:10
Replacement query run ended at: 10/13/2009 20:05:41
******************************* SUMMARY *******************************
Baseline elapsed time in seconds -> 27
New query elapsed time in seconds -> 30.99999999999999999999999999999999999997
New query is 15% slower than the original.

Hi Tom,

As usual, I love the learnings I get from this site. However, while browsing I still cannot find a good way to calculate what I should put in as size given the ff:

1. Have two tables, A and B.
2. For Table A, AVG_ROW_LEN is 172.
3. For table B, it's just 26, but I know this is because there's one column there that people haven't started populating. The possible values for that column has an average length of 226 so I'm thinking I should for now use 26+226=252 as average row length for Table B (I know that's a bit simplistic but until I really have a good idea on the values for that column, it will do for now)
4. And on the average, there are 3.77 entries on table B for each entry on table A.

Given the above, what I have so far as candidate cluster size is 172 + (252 * 3.77) = 1122. That still doesn't take into consideration indexes I want to put in for the columns aside from the clustering column and I currently have no idea how to factor them in. Would appreciate pointers. Thanks.

Sorry about the "ff" - I've always used it as "following" and I thought that was the norm even before days of IM and SMS.

Thanks for the clarification. I guess I can proceed with my computation.

Hi Tom,

Similar question to the ones before - what are the performance implications of choosing between a clustered table and a partitioned table (list partitioned by what would have been the cluster key) - both for selects based on the cluster key, and DML?

(I guess I'm struggling to see how the organisation of the table data would be different in any meaningful way - and hence, if there would be any performance difference)

Thanks for the quick reply.

FYI, this isn't a work-related question so there's no urgency for an answer on my end. I think we agree that clustering is specifically aimed at improving performance while partitioning is more general-purpose in nature. The motivation for my question is just academic curiosity :).

Getting back to your response, sure - I understand that in the general case, partitioning will not get rows with equal keys organised together. That's because ordinarily the relationship between partition key values and partitions is typically many keys to one partition, not one-to-one and Oracle's under no obligation to put the same key values together.

So in general, a query on the partitioned field still has to scan the partition and that partition contains data that doesn't match the key you're looking for, so relative to clustering you're performing more LIOs. Even if you had (say) a local index on that partition key - and Oracle actually used it (which is doubtful) you're still worse off than if you'd clustered in the first place.

That wasn't my question though - I was asking about a more specific case. The case I'm interested in is where you are list partitioning the table by what would have been the cluster key, and you have set up the partitioning so there is one partition per key value.

For example, say you had this table containing a code field and the table is frequently accessed by that field:

create table blah ( ..., code varchar2(50) not null, ... );

Say code has 10 distinct values. In one scenario you cluster it by "code", in the other you list partition it by "code" with one partition per distinct "code" value (so there are 10 partitions).

I think we both agree that clustering still makes more sense even in this more specific case (even in this more specific case), if our goal is to make the "code" values sit together in the table and hence reduce LIO. What clinches it for me is that clustering just says what I mean. What I'm trying to see is what the performance implications of choosing partitioning instead would be.

Having explained the difference between my question and what I think you thought my question was... I think I now know how Oracle would treat them differently.

I think the issue is that with my partitioning scheme, "it just so happens" that partition keys and paritions map one to one - this kind of setup is a special corner case. My guess would be that Oracle would (under some circumstances) be unable to take advantage of that fact when optimising queries against the partitioned table.

If you list partition by CODE and each CODE has its own partition - it would be very much like clustering - even a bit better in fact. Both cases would have blocks that are filled with just a single CODE value - but the partitioning example would also have these blocks adjacent to each other (better for the full scan) AND it would have it so that we'd know all of the blocks that need to be full scanned for a partition - whereas the cluster would not, you'd have to full scan the entire cluster if you were to full scan.

Ah... I see where my confusion has come from. I didn't read the manual properly in the first place ;).

What I was *thinking* clustering did was physically order rows by the cluster key (a kind of generalised IOT, except the key fields didn't need to be the primary key). I couldn't see a difference because I thought that advantage (row ordering) applied in both cases.

If I read the docs correctly, what clustering actually does is organise the row data so that for any cluster key value the rows corresponding to that value occupy the minimum possible number of blocks. If there's multiple blocks involved, they could potentially be anywhere.

Running with the example further, I can't think of anything I could do (short of adding a parallel clause - and that's cheating) to make that clustered table outperform the list partitioned table for queries that have "where code = :x" in the SQL.

The data I'm after is already in the minimum possible number of blocks, Oracle knows exactly where those blocks are, and those blocks are all together. In terms of data organisation, I think that's as good as it gets.

Indexing the cluster key on my clustered table might improve its performance (such an index might not get used), but even if it did I don't think it would outperform the list partitioned table for the same query.

Thanks Tom - that makes complete sense to me now. Funny how that happens when I go back and re-check my assumptions, isn't it? ;)

Two more questions - I mentioned earlier that how I *thought* clusters worked (i.e. that it imposed a partial ordering on the data based on some combination of fields, so data with the same "key" sits together on disk like they do with an IOT).

First - I'm wondering whether Oracle currently supports this kind of table? It seems to me having "a table where the rows are ordered by columns x,y,z" where those columns aren't necessarily unique could be quite useful.

Second - any idea when you're likely to start taking new questions?

I do have another one regarding a situation where I think the MV refresh engine is doing "the wrong thing", but I won't take up your time here since it isn't really related to clustering at all.

To accomplish storing all data with the same key together would reqire either preallocating a ton of space for each key and hoping we didn't run out of room or shifting and entire table down when you insert a key at the top or moving all of the rows with the same key to somewhere else when the don't fit any more.

Hm... fair enough, but I think I'm still missing something then. Why isn't this an issue with IOTs?

I take new questions all of the time... took at least 36 last week...

Perhaps it has something to do with the time of day that I'm looking to submit questions? Seems almost every time I look at the main page it says you're unable to take new questions.

Hello Tom,
I have 6-7 tables involved in a number of left outer joins. There is an activity table which tracks activities by members of a loyalty program. This table has many millions of records with unique activity numbers (millions of customers, each with many activities). Depending on the 'type' of activity- redemption, accrual, tier change etc, the details of the activities will be in the child tables (the other 6 tables). They are joined by the activitynum.
We have one query which lists the activities along with the details (from child tables) for any one member, for the past year.. To tune the query, we tried partitioning (i.e. activity table has an curr_year flag - set to Y or N). We have indexes on the child tables and the query is doing mostly PK-index unque scans. Still the query takes a few seconds while we are tying to make ti retieve data in under a second. Will clustering all these tables on the activity number help?

Hi Tom,
I am trying to answer all your questions - though not in the same order.
We did not have full scans. But I thought that if the active - i.e. current year's data is a different partition, and we ensured that the data is filtered on the partition key (which we are), it will help. We made local indexes.
The second time the query is executed, it does come back very fast - i.e. physical IO does seem to contribute to the delay.
TKPROF -

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID DOMPARAMS (cr=3 pr=0 pw=0 time=0 us cost=2 size=61 card=1)
      1   INDEX UNIQUE SCAN DOMPARAMS_PK (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 245981)
      1  TABLE ACCESS BY INDEX ROWID DOMPARAMS (cr=3 pr=0 pw=0 time=0 us cost=2 size=61 card=1)
      1   INDEX UNIQUE SCAN DOMPARAMS_PK (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 245981)
      0  TABLE ACCESS BY INDEX ROWID DOMPARAMS (cr=0 pr=0 pw=0 time=0 us cost=2 size=61 card=1)
      0   INDEX UNIQUE SCAN DOMPARAMS_PK (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 245981)
      0  TABLE ACCESS BY INDEX ROWID DOMPARAMS (cr=0 pr=0 pw=0 time=0 us cost=2 size=61 card=1)
      0   INDEX UNIQUE SCAN DOMPARAMS_PK (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 245981)
      0  TABLE ACCESS BY INDEX ROWID MEMBERACTIVITY (cr=0 pr=0 pw=0 time=0 us cost=3 size=32 card=1)
      0   INDEX UNIQUE SCAN MEMBERACTIVITY_PK (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 246166)
      0  TABLE ACCESS BY INDEX ROWID DOMPARAMS (cr=0 pr=0 pw=0 time=0 us cost=2 size=61 card=1)
      0   INDEX UNIQUE SCAN DOMPARAMS_PK (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 245981)
   1688  SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
      0   NESTED LOOPS  (cr=3 pr=0 pw=0 time=0 us cost=2 size=61 card=1)
      0    TABLE ACCESS BY INDEX ROWID MEMBERMASTER (cr=3 pr=0 pw=0 time=0 us cost=2 size=30 card=1)
      0     INDEX RANGE SCAN MEMBERMASTER_PK (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 246200)
      0    INDEX UNIQUE SCAN PRGMEMSTMACTDTL_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=31 card=1)(object id 246197)
  13504  SORT ORDER BY (cr=15651 pr=0 pw=0 time=24305 us cost=50 size=10801 card=7)
  13504   NESTED LOOPS OUTER (cr=15651 pr=0 pw=0 time=642539 us cost=49 size=10801 card=7)
  13504    NESTED LOOPS OUTER (cr=15643 pr=0 pw=0 time=602157 us cost=41 size=10458 card=7)
  13504     NESTED LOOPS OUTER (cr=15643 pr=0 pw=0 time=580501 us cost=40 size=10269 card=7)
  13504      VIEW  (cr=11553 pr=0 pw=0 time=515406 us cost=20 size=9961 card=7)
  13504       NESTED LOOPS OUTER (cr=11553 pr=0 pw=0 time=508655 us cost=20 size=10052 card=7)
   1688        VIEW  (cr=8174 pr=0 pw=0 time=58322 us cost=16 size=2802 card=2)
   1688         COUNT  (cr=8165 pr=0 pw=0 time=43982 us)
   1688          FILTER  (cr=8165 pr=0 pw=0 time=40617 us)
   1688           NESTED LOOPS OUTER (cr=8165 pr=0 pw=0 time=41572 us cost=16 size=2362 card=2)
   1688            VIEW  (cr=4918 pr=0 pw=0 time=30968 us cost=12 size=2328 card=2)
   1688             NESTED LOOPS OUTER (cr=4918 pr=0 pw=0 time=30245 us cost=12 size=464 card=2)
   1688              NESTED LOOPS OUTER (cr=4270 pr=0 pw=0 time=24702 us cost=9 size=416 card=2)
   1688               NESTED LOOPS OUTER (cr=668 pr=0 pw=0 time=10242 us cost=6 size=366 card=2)
   1688                TABLE ACCESS BY INDEX ROWID MEMBERACTIVITY (cr=439 pr=0 pw=0 time=4820 us cost=5 size=266 card=2)
   1688                 INDEX RANGE SCAN TCC_INDEX (cr=14 pr=0 pw=0 time=1566 us cost=3 size=0 card=2)(object id 299024)
      0                TABLE ACCESS BY INDEX ROWID PRGMANACCUPDACT (cr=229 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      0                 INDEX UNIQUE SCAN PRGMANACCUPDACT_PK (cr=229 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 246144)
   1688               TABLE ACCESS BY INDEX ROWID MEMACRACT (cr=3602 pr=0 pw=0 time=0 us cost=2 size=25 card=1)
   1688                INDEX UNIQUE SCAN MEMACRACT_PK (cr=1914 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 246164)
      0              TABLE ACCESS BY INDEX ROWID PRGTIRCHGACT (cr=648 pr=0 pw=0 time=0 us cost=2 size=24 card=1)
      0               INDEX UNIQUE SCAN PRGTIRCHGACT_PK (cr=648 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 246225)
   1688            TABLE ACCESS BY INDEX ROWID ACTIVITYPRG (cr=3247 pr=0 pw=0 time=0 us cost=2 size=17 card=1)
   1688             INDEX UNIQUE SCAN ACRACTPGM_PK (cr=1559 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 245844)
  13504        TABLE ACCESS BY INDEX ROWID PNTTYPEMASTER (cr=3379 pr=0 pw=0 time=0 us cost=3 size=140 card=4)
  13504         INDEX RANGE SCAN PNTTYPEMASTER_PK (cr=3 pr=0 pw=0 time=3920 us cost=1 size=0 card=8)(object id 246216)
   1200      TABLE ACCESS BY INDEX ROWID PRGMEMPNTTXN (cr=4090 pr=0 pw=0 time=0 us cost=3 size=44 card=1)
   1200       INDEX RANGE SCAN TCC_INDEX1 (cr=3702 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 298527)
      0     TABLE ACCESS BY INDEX ROWID COMBNSMST (cr=0 pr=0 pw=0 time=0 us cost=1 size=27 card=1)
      0      INDEX UNIQUE SCAN COMBNSMST_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 245953)
      0    TABLE ACCESS BY INDEX ROWID MEMBERACTIVITYOTHLNGDES (cr=8 pr=0 pw=0 time=0 us cost=2 size=49 card=1)
      0     INDEX RANGE SCAN MEMACTMUTLINGDES_PK (cr=8 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 246171)
********************************************************************************

Snce the tables are linked on the activity/activityreference number, my thought was to cluster the tables on that column. But the tables involved are big - each one a few hundred MB. So I haev concerns on the insert/update performance.

I'm following an example in your book where I implement a Single Table Hash Cluster when the HASHKEY is a surrogate primary key generated from a sequence, ie. it will never have a hash collision.

As the column is the primary key - I want to declare a constraint to state as such - however, that means it will also create a UNIQUE INDEX to enforce the constraint?

Can that cause the optimizer to use the UNIQUE index when accessing the table? Kind of defeats the purpose of having the cluster to reduce I/O ?

Thank you.

One more question;

If I had a lookup table where I always go in by primary key (generated from a sequence) - what's the difference between using a Single Table Hash cluster and an IOT ? What's the reasons I would choose one over the other ?

Thank you.

I was perhaps thinking you would use an IOT for a lookup table where don't know with certainty how many rows there are going to be? Looking at your book again, that would appear logical as you state that hash clusters is suitable when "you know with a good degree of accuracy how many rows the table will have..."

Following on from that, another reason would be when you need to perform range scanning of the cluster key?

Think I kind of answered my own question based on the "opposites" theories for using hash clusters in your book!

Tom, is there a way of seeing how well you've sized a hash cluster after you've loaded it up?

I have a single table hash cluster with a fixed, never-changing number of rows. I thought I had correctly sized it, but a hash cluster version of the data takes up 10G, whilst the IOT version just 300K !

Clearly I've sized it wrongly, but how can I see where I've gone wrong?