1 建立測試資料，無partition

  --  Create Table


CREATE TABLE "MIKE1"."TABLE6"
   ( "SEQ" NUMBER NOT NULL ENABLE,
"DATESTR" DATE NOT NULL ENABLE,
"R" NUMBER DEFAULT 10 NOT NULL ENABLE
   )
  TABLESPACE "TB" ;
 
 
--  Create Sequ

CREATE SEQUENCE  "MIKE1"."TABLE6_SEQ"  MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 200921 CACHE 20 NOORDER  NOCYCLE ;
 

-- Insert Data

begin
for i in 1..100000
loop
  insert into mike1.table6 values ( mike1.table6_seq.nextval , to_date ('2018/01/01','yyyy/mm/dd') + round(dbms_random.value(1,365)) , round(dbms_random.value(1,10000))) ;
end loop;
commit ;
end ;


--Query

無迴圈

declare
v_out int ;
 timestart NUMBER;
  BEGIN
    dbms_output.enable;
    timestart:=dbms_utility.get_time();
   select count(*)  into v_out  from mike1.table6  T6 where T6.R > round(dbms_random.value(1,1000))  and T6.R < round(dbms_random.value(2000,8000))  ;
 
         dbms_output.put ( v_out  || ' ==> ')   ;
    dbms_output.put_line    ( dbms_utility.get_time()-timestart);
    -- save time
end ;


-- 沒有index , 執行約 8秒鐘
declare
v_out int ;
 timestart NUMBER;
  BEGIN
    dbms_output.enable;
    timestart:=dbms_utility.get_time();

for i in 1..10
  loop
   select count(*)  into v_out  from mike1.table6  T6 where T6.R > round(dbms_random.value(1,1000))  and T6.R < round(dbms_random.value(2000,6000))  ;
          dbms_output.put_line ( v_out  )   ;
   end loop ;
 
    dbms_output.put_line    ( dbms_utility.get_time()-timestart);
    -- save time
end ;


-- Create Index


  CREATE INDEX "MIKE1"."IDX_DATE_TABLE6_DATESTR" ON "MIKE1"."TABLE6" ("DATESTR")
  TABLESPACE "TB" ;

  CREATE INDEX "MIKE1"."IDX_TABLE6_SEQ" ON "MIKE1"."TABLE6" ("SEQ")
  TABLESPACE "TB" ;


  CREATE INDEX "MIKE1"."IDX_TABLE6_R" ON "MIKE1"."TABLE6" ("R")
  TABLESPACE "TB" ;


建立完index 約  0.15秒