【正文】 （2）下面是測試命令執(zhí)行的代碼。運(yùn)行時(shí)把“待測試命令”替換成自己編寫的測試命令，在查詢分析器中執(zhí)行后，能返回命令執(zhí)行的大致時(shí)間(單位為：毫秒)。declare type t_id is table of %type。 type t_rq is table of %type。 type t_srq is table of %type。 type t_hh is table of %type。 type t_mm is table of %type。 type t_ss is table of %type。 type t_NUM is table of %type。 col_id t_id。 col_rq t_rq。 col_srq t_srq。 col_hh t_hh。 col_mm t_mm。 col_ss t_ss。 col_NUM t_NUM。 dt1 TIMESTAMP。i int。s varchar(200)。hm1 int。hm2 int。begin dt1:=systimestamp。 hm1:=to_number(TO_CHAR(dt1,39。HH39。))*3600000+to_number(TO_CHAR(dt1,39。MI39。))*60000+to_number(TO_CHAR(dt1,39。SS39。))*1000+to_number(substr(TO_CHAR(dt1,39。yyyymmdd hh24:mi:39。), 6, 3))。 請(qǐng)?zhí)娲鷾y試命令 “待測試命令” 此行將用測試命令替代 請(qǐng)?zhí)娲鷾y試命令 dt1:=systimestamp。 hm2:=to_number(TO_CHAR(dt1,39。HH39。))*3600000+to_number(TO_CHAR(dt1,39。MI39。))*60000+to_number(TO_CHAR(dt1,39。SS39。))*1000+to_number(substr(TO_CHAR(dt1,39。yyyymmdd hh24:mi:39。), 6, 3)) hm1。 s:=39。耗時(shí)39。||to_char(hm2)||39。（毫秒）39。 (s)。end。注意：為了能顯示耗時(shí)信息，運(yùn)行前先要執(zhí)行支持顯示命令：set serveroutput on（3）未建索引時(shí)：1) 單記錄插入(約015毫秒（0毫秒表示不足1毫秒）insert into itbl(id,rq,srq,hh,mm,ss,NUM) values(,sysdate,to_char(sysdate, 39。yyyymmdd hh:mi:ss39。),to_number(TO_CHAR(SYSDATE,39。HH39。)), (1,to_number(TO_CHAR(SYSDATE,39。MI39。))),to_number(TO_CHAR(SYSDATE,39。SS39。)),*100)。2) 查詢所有記錄，按id排序(約275毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl order by id。 3) 查詢所有記錄，按mm排序(約300毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl order by mm。4) 查詢mm=1的所有記錄，按id排序(約016毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl where mm=1 order by id。（4）對(duì)itbl表id字段建非聚集索引1) 建索引毫?xí)r(約145毫秒)CREATE INDEX indexname1 ON itbl(id)。檢測上語句執(zhí)行時(shí)間時(shí)，應(yīng)改用動(dòng)態(tài)SQL執(zhí)行，即改為：execute immediate 39。CREATE INDEX indexname1 ON itbl(id)39。2) 單記錄插入(約016毫秒)insert into itbl(id,rq,srq,hh,mm,ss,NUM) values(,sysdate,to_char(sysdate, 39。yyyymmdd hh:mi:ss39。),to_number(TO_CHAR(SYSDATE,39。HH39。)), (1,to_number(TO_CHAR(SYSDATE,39。MI39。))),to_number(TO_CHAR(SYSDATE,39。SS39。)),*100)。3) 查詢所有記錄，按id排序(約285毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl order by id。 4) 查詢所有記錄，按mm排序(約265毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl order by mm。5) 查詢mm=1的所有記錄，按id排序(約16毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl where mm=1 order by id。6) 刪除索引(約60毫秒)execute immediate 39。 drop index indexname139。（5）對(duì)itbl表mm字段建非聚集索引1) 建索引毫?xí)r(約175毫秒)execute immediate 39。CREATE INDEX indexname1 ON itbl(mm)39。2) 單記錄插入(約21毫秒)insert into itbl(id,rq,srq,hh,mm,ss,NUM) values(,sysdate,to_char(sysdate, 39。yyyymmdd hh:mi:ss39。),to_number(TO_CHAR(SYSDATE,39。HH39。)), (1,to_number(TO_CHAR(SYSDATE,39。MI39。))),to_number(TO_CHAR(SYSDATE,39。SS39。)),*100)。3) 查詢所有記錄，按id排序(約286毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl order by id。 4) 查詢所有記錄，按mm排序(約295毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl order by mm。5) 查詢mm=1的所有記錄，按id排序(約15毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl where mm=1 order by id。6) 刪除索引(約80毫秒)execute immediate 39。 drop index indexname139。（6）對(duì)itbl表mm字段建聚簇索引創(chuàng)建cluster的步驟:1）create cluster itbl_cluster (mm NUMBER(2)) tablespace users。 聚簇的創(chuàng)建上面的語句創(chuàng)建了一個(gè)名為itbl_cluster的cluster, cluster ,所以當(dāng)cluster創(chuàng)建完后,還必須在cluster key 上創(chuàng)建索引.2）create index itbl_cluster_idx on cluster itbl_cluster tablespace users。索引的創(chuàng)建說明：若要?jiǎng)?chuàng)建hash cluster，則創(chuàng)建命令如下:Create cluster itbl_cluster2 (mm NUMBER(2)) size 8k hashkeys 1000 tablespace users。參數(shù)hashkeys用來定義分配給表的hash值的數(shù)目，指出了在cluster中唯一性cluster key的最大值。這時(shí)不需創(chuàng)建索引。3）創(chuàng)建參與cluster的表在cluster itbl_cluster上創(chuàng)建表itbl2，命令如下：create table itbl2 cluster itbl_cluster(mm) as select * from itbl。在cluster itbl_cluster2上創(chuàng)建表itbl3，命令如下：create table itbl3 cluster itbl_cluster2 (mm) as select * from itbl。然后可以對(duì)照在itbl、itblitbl3表上的添加記錄、查詢記錄等的性能情況。1）單記錄插入到表itbl2(約0毫秒)insert into itbl2(id,rq,srq,hh,mm,ss,NUM) values(,sysdate,to_char(sysdate, 39。yyyymmdd hh:mi:ss39。),to_number(TO_CHAR(SYSDATE,39。HH39。)), (1,to_number(TO_CHAR(SYSDATE,39。MI39。))),to_number(TO_CHAR(SYSDATE,39。SS39。)),*100)。2）單記錄插入到表itbl3(約016毫秒)insert into itbl3(id,rq,srq,hh,mm,ss,NUM) values(,sysdate,to_char(sysdate, 39。yyyymmdd hh:mi:ss39。),to_number(TO_CHAR(SYSDATE,39。HH39。)), (1,to_number(TO_CHAR(SYSDATE,39。MI39。))),to_number(TO_CHAR(SYSDATE,39。SS39。)),*100)。3）表itbl2查詢所有記錄，按id排序(約278毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl2 order by id。 4）表itbl3查詢所有記錄，按id排序(約280毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl3 order by id。 5）表itbl2查詢所有記錄，按mm排序(約313毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl2 order by mm。6）表itbl3查詢所有記錄，按mm排序(約277毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl3 order by mm。7）對(duì)表itbl2查詢mm=1的所有記錄，按id排序(約016毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl2 where mm=1 order by id。8）對(duì)表itbl3查詢mm=1的所有記錄，按id排序(約016毫秒)Select id,rq,srq,hh,mm,ss,NUM BULK collect into col_id,col_rq,col_srq,col_hh,col_mm,col_ss,col_NUM from itbl3 where mm=1 order by id。9）實(shí)驗(yàn)完成后刪除cluster及其相關(guān)表在刪除cluster的時(shí)候,首先必須刪除參與該cluster的表或者使用including tables子句。不能從正在使用的cluster刪除表。drop cluster itbl_cluster including tables cascade constraints。簇及相關(guān)表一并刪除或先刪除表，再刪除cluster對(duì)象。命令如下：drop table itbl2。 drop cluster itbl_cluster。drop table itbl3。 drop cluster itbl_cluster2。實(shí)驗(yàn)8 存儲(chǔ)過程的基本操作 例81 SQL