-- 일반 프리스캇 씨피유에서 10분정도 걸림.
spool c:result.txt
drop table "SCOTT"."TEST2" ;
drop sequence "SCOTT"."TEST2VAL" ;
CREATE TABLE "SCOTT"."TEST2" ("ID" NUMBER(10) NOT NULL, "DATA"
    VARCHAR2(30) NOT NULL,
    "VAL1" number(2) NOT NULL,
    "VAL2" number(2) NOT NULL,
    CONSTRAINT "PK_TEST2" PRIMARY KEY("ID"))     TABLESPACE "EXAMPLE"  ;

CREATE SEQUENCE "SCOTT"."TEST2VAL" INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;

CREATE INDEX "SCOTT"."TEST2VAL1"
    ON "SCOTT"."TEST2"  ("VAL1");

CREATE BITMAP
    INDEX "SCOTT"."TEST2VAL2"
    ON "SCOTT"."TEST2"  ("VAL2");


CREATE  OR  REPLACE  PROCEDURE test2insert
 (
 su number,gap number
)
as
 tmp varchar2(10);
begin
    tmp := 'testval';
    for i in 1..su loop
       insert into scott.test2
            values(
  test2val.nextval,
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ!!!!',mod(test2val.currval,gap),mod(test2val.currval,gap));
    end loop;
    commit;
end;
/

 

CREATE  OR  REPLACE  PROCEDURE duB(
  su number
  )
 as
  v_count number;
  v_startTime timestamp;
  v_endTime timestamp;
  v_diff        number(11,6);
  v_toFindNumberT   number;
  v_toFindNumberB   number;
  v_avg          number(11,6) default 0;
begin
  select MAX(val1)+1 into v_toFindNumberT
 from scott.test2;
  select MAX(val2)+1 into v_toFindNumberB
 from scott.test2;


  DBMS_RANDOM.INITIALIZE(to_number(to_char(systimestamp,'ff')));
  --------------------------------------------------------------
  v_avg:=0;
  for i in 1..su loop
  v_startTime := systimestamp;
  -- 질의 시작
   select count(*) into v_count
  from scott.test2
   where val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB)
     or val2=mod(abs(dbms_random.random),v_toFindNumberB);
     -- 질의끝
   v_endTime := systimestamp;
   v_diff :=      to_number(to_char(v_endTime ,'sssss.ff')) -  to_number(to_char(v_startTime ,'sssss.ff'));
   v_avg:=v_avg+v_diff;
   DBMS_OUTPUT.PUT_LINE(i||'번째 비트맵 인텍스 : '||v_diff);
  end loop;
   DBMS_OUTPUT.PUT_LINE('  -평균시간 : '|| v_avg/su);
--------------------------------------------------------------
  v_avg:=0;
  for i in 1..su loop
  v_startTime := systimestamp;
  -- 질의 시작
   select count(*) into v_count
  from scott.test2
  where val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT)
     or val1=mod(abs(dbms_random.random),v_toFindNumberT);
   -- 질의끝
   v_endTime := systimestamp;
   v_diff := to_number(to_char(v_endTime ,'sssss.ff')) -  to_number(to_char(v_startTime ,'sssss.ff'));
   v_avg:=v_avg+v_diff;
   DBMS_OUTPUT.PUT_LINE(i||'번째 B트리 인텍스 : '||v_diff);
  end loop;
  DBMS_OUTPUT.PUT_LINE('  -평균시간 : '|| v_avg/su);
 
 -----------------------------------------------------------
  DBMS_RANDOM.TERMINATE;
end;
/

 

CREATE  OR  REPLACE  PROCEDURE duT(
  su number
  )
 as
  v_count number;
  v_startTime timestamp;
  v_endTime timestamp;
  v_diff        number(11,6);
  v_toFindNumberT   number;
  v_toFindNumberB   number;
  v_avg          number(11,6) default 0;
begin
  select MAX(val1)+1 into v_toFindNumberT
 from scott.test2;
  select MAX(val2)+1 into v_toFindNumberB
 from scott.test2;


  DBMS_RANDOM.INITIALIZE(to_number(to_char(systimestamp,'ff')));
  --------------------------------------------------------------
  v_avg:=0;
  for i in 1..su loop
  v_startTime := systimestamp;
  -- 질의 시작
   select count(*) into v_count
  from scott.test2
   where val2=mod(abs(dbms_random.random),v_toFindNumberB);
     -- 질의끝
   v_endTime := systimestamp;
   v_diff :=      to_number(to_char(v_endTime ,'sssss.ff')) -  to_number(to_char(v_startTime ,'sssss.ff'));
   v_avg:=v_avg+v_diff;
   DBMS_OUTPUT.PUT_LINE(i||'번째 비트맵 인텍스 : '||v_diff);
  end loop;
   DBMS_OUTPUT.PUT_LINE('  -평균시간 : '|| v_avg/su);
--------------------------------------------------------------
  v_avg:=0;
  for i in 1..su loop
  v_startTime := systimestamp;
  -- 질의 시작
   select count(*) into v_count
  from scott.test2
  where val1=mod(abs(dbms_random.random),v_toFindNumberT) ;
   -- 질의끝
   v_endTime := systimestamp;
   v_diff := to_number(to_char(v_endTime ,'sssss.ff')) -  to_number(to_char(v_startTime ,'sssss.ff'));
   v_avg:=v_avg+v_diff;
   DBMS_OUTPUT.PUT_LINE(i||'번째 B트리 인텍스 : '||v_diff);
  end loop;
  DBMS_OUTPUT.PUT_LINE('  -평균시간 : '|| v_avg/su);
 
 -----------------------------------------------------------
  DBMS_RANDOM.TERMINATE;
end;
/

 

set  serveroutput on

--64가지 값으로 테스트---------------------------------------
SELECT '64가지 값으로 테스트' from dual;
exec test2insert(10000,64)
exec duT(9)
exec duB(9)

--32가지 값으로 테스트---------------------------------------
drop table "SCOTT"."TEST2" ;
drop sequence "SCOTT"."TEST2VAL" ;
CREATE TABLE "SCOTT"."TEST2" ("ID" NUMBER(10) NOT NULL, "DATA"
    VARCHAR2(30) NOT NULL,
    "VAL1" number(2) NOT NULL,
    "VAL2" number(2) NOT NULL,
    CONSTRAINT "PK_TEST2" PRIMARY KEY("ID"))     TABLESPACE "EXAMPLE"  ;

CREATE SEQUENCE "SCOTT"."TEST2VAL" INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;

CREATE INDEX "SCOTT"."TEST2VAL1"
    ON "SCOTT"."TEST2"  ("VAL1");

CREATE BITMAP
    INDEX "SCOTT"."TEST2VAL2"
    ON "SCOTT"."TEST2"("VAL2");


SELECT '32가지 값으로 테스트' from dual;
exec test2insert(10000,32)
exec duT(9)
exec duB(9)

--16가지 값으로 테스트---------------------------------------
drop table "SCOTT"."TEST2" ;
drop sequence "SCOTT"."TEST2VAL" ;
CREATE TABLE "SCOTT"."TEST2" ("ID" NUMBER(10) NOT NULL, "DATA"
    VARCHAR2(30) NOT NULL,
    "VAL1" number(2) NOT NULL,
    "VAL2" number(2) NOT NULL,
    CONSTRAINT "PK_TEST2" PRIMARY KEY("ID"))     TABLESPACE "EXAMPLE"  ;

CREATE SEQUENCE "SCOTT"."TEST2VAL" INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;

CREATE INDEX "SCOTT"."TEST2VAL1"
    ON "SCOTT"."TEST2"  ("VAL1");

CREATE BITMAP
    INDEX "SCOTT"."TEST2VAL2"
    ON "SCOTT"."TEST2"("VAL2");

SELECT '16가지 값으로 테스트' from dual;
exec test2insert(10000,16)
exec duT(9)
exec duB(9)

 

--8가지 값으로 테스트---------------------------------------
drop table "SCOTT"."TEST2" ;
drop sequence "SCOTT"."TEST2VAL" ;
CREATE TABLE "SCOTT"."TEST2" ("ID" NUMBER(10) NOT NULL, "DATA"
    VARCHAR2(30) NOT NULL,
    "VAL1" number(2) NOT NULL,
    "VAL2" number(2) NOT NULL,
    CONSTRAINT "PK_TEST2" PRIMARY KEY("ID"))     TABLESPACE "EXAMPLE"  ;

CREATE SEQUENCE "SCOTT"."TEST2VAL" INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;

CREATE INDEX "SCOTT"."TEST2VAL1"
    ON "SCOTT"."TEST2"  ("VAL1");

CREATE BITMAP
    INDEX "SCOTT"."TEST2VAL2"
    ON "SCOTT"."TEST2"("VAL2");

SELECT '8가지 값으로 테스트' from dual;
exec test2insert(10000,8)
exec duT(9)
exec duB(9)

 


--4가지 값으로 테스트---------------------------------------
drop table "SCOTT"."TEST2" ;
drop sequence "SCOTT"."TEST2VAL" ;

CREATE TABLE "SCOTT"."TEST2" ("ID" NUMBER(10) NOT NULL, "DATA"
    VARCHAR2(30) NOT NULL,
    "VAL1" number(2) NOT NULL,
    "VAL2" number(2) NOT NULL,
    CONSTRAINT "PK_TEST2" PRIMARY KEY("ID"))     TABLESPACE "EXAMPLE"  ;

CREATE SEQUENCE "SCOTT"."TEST2VAL" INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;

CREATE INDEX "SCOTT"."TEST2VAL1"
    ON "SCOTT"."TEST2"  ("VAL1");

CREATE BITMAP
    INDEX "SCOTT"."TEST2VAL2"
    ON "SCOTT"."TEST2"("VAL2");

SELECT '4가지 값으로 테스트' from dual;
exec test2insert(10000,4)
exec duT(9)
exec duB(9)

SELECT '-- 인덱스가 차지하는 크기도 비교할것..!!' from dual;

spool off

 

 

이 게시물을..