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?

    eaf3ab7e-6e9d-41ba-a60f-45f084772733

      Hi,

       

      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:

      declare

        lower NUMBER := 1;

        upper NUMBER := 10000000;

      begin

        FOR i In lower .. upper LOOP

          INSERT INTO test_tab1 VALUES(floor(i / 800000),

                                 floor(i / 800000),

                                 'xxxxxxxxxxxxxxxxxxx',

                                 'xxxxxxxxxxxxxxxxxxx');

        END LOOP;

      end;

       

      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!