We have a question about oracle clusters and clustered tables.
Essentially we created a cluster, a clustered table and a cluster index:
create cluster testcluster1(id number(5)) size 1000 storage(initial 1024m);
create table test_tab1(id number(5), num number(5), str2 varchar2(20),str3 varchar2(20)) cluster testcluster1(id);
create index test1clusterindex on cluster testcluster1;
Then we inserted data into the table:
lower NUMBER := 1;
upper NUMBER := 10000000;
FOR i In lower .. upper LOOP
INSERT INTO test_tab1 VALUES(floor(i / 800000),
floor(i / 800000),
The time required for an ordinary full table scan is ~ 6s:
select sum(t.num) from TEST_TAB1 t;
The time required for the first run of a cluster scan is ~61s:
select sum(t.num) from TEST_TAB1 t where t.id <= 4
The later runs of the same cluster scan is super fast though (~ 0.4s) because the cluster, the clustered table and the cluster index were loaded into the default buffer cache (we know this because flushing the buffer cache restores the long time ~61s for the cluster scan). But other than loading the cluster and the clustered table (which takes ~ 6s even for a full table scan) in the first run of the cluster scan, Oracle DB should have done something else (which takes ~ 55s).
So our question here is what does oracle DB do in the first run of the cluster scan during this ~55s time? Any comments will be appreciated!