0 Replies Latest reply on May 27, 2015 1:55 PM by eaf3ab7e-6e9d-41ba-a60f-45f084772733

    What doese Oracle DB do for the first query of a clustered table?




      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),



        END LOOP;



      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!