可以把字段定义为NUMBER类型，填入时用DBMS_UTILITY.GET_TIME就可以了，不过取出来有一个时间粒度，百分之一秒。另：在论坛上提问，往往得到的回答是：为什么要这样；不应该这样而是那样，等等：）我觉得这不是正面回答提问者的问题。25.1.1 Use the DBMS_UTILITY.GET_TIME Function The tools listed in the previous section provide varying levels of detail and granularity; however, they all do require some effort -- often on the part of a person other than the PL/SQL developer in need -- to get them enabled. And then they require even more effort to interpret results. Don't get me wrong; I am not really complaining. It's just that, quite frankly, PL/SQL developers often want to examine the performance of a particular program and do not want to have to deal with all that other stuff. No problem! PL/SQL provides a mechanism to obtain timings of code execution that are accurate to 100th of a second: the DBMS_UTILTY.GET_TIME function. Yes, that's right. I said 100th of a second. For those of you who have programmed in Oracle over the past few years, this should be a welcome surprise. Before the advent of the DBMS_UTILITY package, the only way to measure elapsed time was to use SYSDATE and examine the difference in the time component. Sadly, this component only records times down to the nearest second. This doesn't help much when you need to measure subsecond response time. DBMS_UTILTY.GET_TIME returns the number of hundredths of seconds which have elapsed since some arbitrary point in time. I don't remember what that point is and, well, that's the whole point. A single value returned by a call to dbms_utility.get_time is, by itself, meaningless. If, on the other hand, you call this built-in function twice and then take the difference between the two returned values, you will have determined the number of hundredths of seconds which elapsed between the two calls. So if you sandwich the execution of your own program between calls to DBMS_UTILTY.GET_TIME, you will have discovered how long it takes to run that program. The anonymous block below shows how to use GET_TIME to determine the time it takes to perform the calc_totals procedure: DECLARE time_before BINARY_INTEGER; time_after BINARY_INTEGER; BEGIN time_before := DBMS_UTILITY.GET_TIME; calc_totals; time_after := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE (time_after - time_before); END; I found myself relying on GET_TIME frequently as I developed the code in this book, because I wanted to analyze the performance impact of a particular approach or technique. Is it faster to raise an exception or execute an IF statement? Is it faster to load 100 rows in a table or concatenate 100 substrings into a long string? There are two basic approaches you can take to using this handy function: Write again and again the kind of script you see above, changing the program or lines of code executed. Encapsulate the way dbms_utility.get_time operates inside a package, which will hide the details and make it easier to use. You will find on the companion disk an explanation and code for such a package, sp_timer, in the files sptimer.sps and sptimer.spb. In addition, you will find in Advanced Oracle PL/SQL Programming with Packages a more complete performance timing utility based on DBMS_UTILITY.GET_TIME in the PLVtmr package. Once you have encapsulated your usage of DBMS_UTILITY.GET_TIME, it is very easy to put together scripts which not only analyze performance, but also compare different implementations. The following script, for example, executes two different versions of the is_number function (see "Section 25.4, "Tuning Your Algorithms"" for more information on this function) and displays the resulting elapsed times (using the PLVtmr and p packages from the PL/Vision library; again, see Advanced Oracle PL/SQL Programming with Packages: SET VERIFY OFF DECLARE b BOOLEAN; BEGIN PLVtmr.set_factor (&1) PLVtmr.capture; FOR repind IN 1 .. &1 -- Number of iterations LOOP b := isnum ('&2'); -- The string to test IF repind = 1 THEN p.l (b); END IF; END LOOP; PLVtmr.show_elapsed (`TO_NUMBER Version'); PLVtmr.set_factor (&1) PLVtmr.capture; FOR repind IN 1 .. &1 LOOP b := isnum_translate ('&2'); PLVtmr.last_timing := 15; IF repind = 1 THEN p.l (b); END IF; END LOOP; PLVtmr.show_elapsed (`TRANSLATE Version'); END; /
一般表建立主键都用一个编号来，用一个专门的Sequence 来维护！如果你觉得你的时间是不可以重复的，那就建立一个唯一索引(unique index)，如果你觉得时间可以重复，但是你需要经常对时间进行查询，那就建立一个一般索引(index)；建立表要养成好的习惯！
用sequence产生ID做主键.可以在时间字段上,建立索引
The tools listed in the previous section provide varying levels of detail and granularity; however, they all do require some effort -- often on the part of a person other than the PL/SQL developer in need -- to get them enabled. And then they require even more effort to interpret results. Don't get me wrong; I am not really complaining. It's just that, quite frankly, PL/SQL developers often want to examine the performance of a particular program and do not want to have to deal with all that other stuff. No problem! PL/SQL provides a mechanism to obtain timings of code execution that are accurate to 100th of a second: the DBMS_UTILTY.GET_TIME function. Yes, that's right. I said 100th of a second. For those of you who have programmed in Oracle over the past few years, this should be a welcome surprise. Before the advent of the DBMS_UTILITY package, the only way to measure elapsed time was to use SYSDATE and examine the difference in the time component. Sadly, this component only records times down to the nearest second. This doesn't help much when you need to measure subsecond response time. DBMS_UTILTY.GET_TIME returns the number of hundredths of seconds which have elapsed since some arbitrary point in time. I don't remember what that point is and, well, that's the whole point. A single value returned by a call to dbms_utility.get_time is, by itself, meaningless. If, on the other hand, you call this built-in function twice and then take the difference between the two returned values, you will have determined the number of hundredths of seconds which elapsed between the two calls. So if you sandwich the execution of your own program between calls to DBMS_UTILTY.GET_TIME, you will have discovered how long it takes to run that program. The anonymous block below shows how to use GET_TIME to determine the time it takes to perform the calc_totals procedure: DECLARE
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
BEGIN
time_before := DBMS_UTILITY.GET_TIME;
calc_totals;
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE (time_after - time_before);
END;
I found myself relying on GET_TIME frequently as I developed the code in this book, because I wanted to analyze the performance impact of a particular approach or technique. Is it faster to raise an exception or execute an IF statement? Is it faster to load 100 rows in a table or concatenate 100 substrings into a long string? There are two basic approaches you can take to using this handy function: Write again and again the kind of script you see above, changing the program or lines of code executed. Encapsulate the way dbms_utility.get_time operates inside a package, which will hide the details and make it easier to use. You will find on the companion disk an explanation and code for such a package, sp_timer, in the files sptimer.sps and sptimer.spb. In addition, you will find in Advanced Oracle PL/SQL Programming with Packages a more complete performance timing utility based on DBMS_UTILITY.GET_TIME in the PLVtmr package. Once you have encapsulated your usage of DBMS_UTILITY.GET_TIME, it is very easy to put together scripts which not only analyze performance, but also compare different implementations. The following script, for example, executes two different versions of the is_number function (see "Section 25.4, "Tuning Your Algorithms"" for more information on this function) and displays the resulting elapsed times (using the PLVtmr and p packages from the PL/Vision library; again, see Advanced Oracle PL/SQL Programming with Packages: SET VERIFY OFF
DECLARE
b BOOLEAN;
BEGIN
PLVtmr.set_factor (&1)
PLVtmr.capture;
FOR repind IN 1 .. &1 -- Number of iterations
LOOP
b := isnum ('&2'); -- The string to test
IF repind = 1
THEN
p.l (b);
END IF;
END LOOP;
PLVtmr.show_elapsed (`TO_NUMBER Version'); PLVtmr.set_factor (&1)
PLVtmr.capture;
FOR repind IN 1 .. &1
LOOP
b := isnum_translate ('&2');
PLVtmr.last_timing := 15;
IF repind = 1
THEN
p.l (b);
END IF;
END LOOP;
PLVtmr.show_elapsed (`TRANSLATE Version');
END;
/