Início / dba / Perguntas / 42084
Accepted
BBlake
Asked: 2013-05-14 09:47:26 +0800 CST

Maneira mais eficiente e prática de armazenar valores de tempo (HH:MM) no Oracle que o torna facilmente pesquisável

  • 772

Eu tenho um conjunto de horários de início e término que preciso armazenar em um banco de dados Oracle. Eu preciso armazená-los de uma maneira que os torne facilmente pesquisáveis ​​(ou seja, uma pesquisa típica é encontrar todas as linhas em que um valor de tempo, como 9h30, fica entre o horário de início e o horário de término). No SQL Server, eu usaria apenas um campo de tipo de dados TIME, mas o Oracle não parece ter um equivalente.

Já vi vários blogs e fóruns que recomendam apenas usar um campo DATE e fazer conversões com TO_CHAR e depois comparar ou armazenar os valores de tempo como campos varchar(4 / HHMM) ou varchar(6 / HHMMSS). Ambos parecem desnecessariamente ineficientes.

Existe uma maneira melhor ou mais eficiente de fazer isso no Oracle?

oracle

4 respostas

  1. Best Answer

    Se você não precisa armazenar o componente de data, então an interval day(0) to secondé a abordagem mais prática. A maior vantagem vem se você precisar fazer cálculos para descobrir quanto tempo se passou entre duas vezes, pois não precisa fazer nenhum processamento extra fora da caixa.

    Por exemplo:

    create table times ( t1 interval day(0) to second, t2 interval day(0) to second);

insert into times values ( interval '0 09:30:00' day(0) to second, interval '0 12:30:00' day(0) to second);
insert into times values ( interval '0 10:30:00' day(0) to second, interval '0 13:45:00' day(0) to second);
insert into times values ( interval '0 11:30:00' day(0) to second, interval '0 12:05:01' day(0) to second);

commit;

SELECT * FROM times
where  t1 between interval '0 10:00:00' day to second and interval '0 11:00:00' day to second ;

T1          T2        
----------- -----------
0 10:30:0   0 13:45:0   

SELECT max(t2-t1) FROM times; 

MAX(T2-T1)
-----------
0 3:15:0.0

    Você também tem funções padrão para extrair os vários componentes da hora (hora, minuto, etc.):

    SELECT extract(hour from t1) hr, extract(minute from t1) mn, extract(second from t1) sec
FROM times; 

        HR         MN        SEC
---------- ---------- ----------
         9         30          0 
        10         30          0 
        11         30          0
    • 5

  2. Tente armazenar como número. O pior caso é 3 bytes para HHMM e 4 para HHMMSS. 01:00 levaria 1 byte.

    • 0

  3. Fiz alguns testes usando o oracle 11g e obtive isso:

    SQL*Plus: Release 11.2.0.1.0 Production on Mon May 13 22:40:32 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Sessie: 44,8525
Eventueel trace-bestand: c:\app\administrator\diag\rdbms\sm10\sm10\trace\sm10_ora_5748.trc

sys@SM10> set timing on;
sys@SM10> set autotrace off;
sys@SM10> drop table timing;

Table dropped.

Elapsed: 00:00:00.07
sys@SM10> create table timing ( hoursDate date , hoursText varchar2(5) , hoursNumber number(4) ) nologging;

Table created.

Elapsed: 00:00:00.01
sys@SM10> insert into timing (select to_date(trunc(dbms_random.value(0,23)) || ':' || trunc(dbms_random.value(0,59)),'HH24:MI'),to_char(trunc(dbms_random.value(0,23)) || ':' || tru
nc(dbms_random.value(0,59)) ), trunc(dbms_random.value(0,23)) ||  trunc(dbms_random.value(0,59)) from all_objects );

72514 rows created.

Elapsed: 00:00:02.72
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

72514 rows created.

Elapsed: 00:00:00.03
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

145028 rows created.

Elapsed: 00:00:00.05
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

290056 rows created.

Elapsed: 00:00:00.08
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

580112 rows created.

Elapsed: 00:00:00.15
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

1160224 rows created.

Elapsed: 00:00:00.28
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.01
sys@SM10> insert /*+ append */ into timing select * from timing;

2320448 rows created.

Elapsed: 00:00:00.51
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> select count (*) from timing;

  COUNT(*)
----------
   4640896

1 row selected.

Elapsed: 00:00:00.15
sys@SM10> create index normal_hourdate on timing(hoursDate) nologging;

Index created.

Elapsed: 00:00:04.54
sys@SM10> create index normal_hourtext on timing(hoursText) nologging;

Index created.

Elapsed: 00:00:14.21
sys@SM10> create index normal_hournumber on timing(hoursNumber) nologging;

Index created.

Elapsed: 00:00:10.33
sys@SM10> analyze table timing compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Elapsed: 00:00:31.40
sys@SM10> set autotrace traceonly;
sys@SM10> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.02
sys@SM10> select        * from timing where hoursDate between to_date('12:00','HH24:MI') and to_date('13:00','HH24:MI') ;

201152 rows selected.

Elapsed: 00:00:01.81

Execution Plan
----------------------------------------------------------
Plan hash value: 3409064691

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   196K|  2879K|  4084   (6)| 00:00:50 |
|*  1 |  FILTER            |        |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TIMING |   196K|  2879K|  4084   (6)| 00:00:50 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('12:00','HH24:MI')<=TO_DATE('13:00','HH24:MI'))
   2 - filter("HOURSDATE">=TO_DATE('12:00','HH24:MI') AND
              "HOURSDATE"<=TO_DATE('13:00','HH24:MI'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      27682  consistent gets
      14311  physical reads
          0  redo size
    5421569  bytes sent via SQL*Net to client
     147925  bytes received via SQL*Net from client
      13412  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     201152  rows processed

sys@SM10> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
sys@SM10> select        * from timing where to_date(hoursText,'HH24:MI') between to_date('12:00','HH24:MI') and to_date('13:00','HH24:MI') ;

207104 rows selected.

Elapsed: 00:00:04.27

Execution Plan
----------------------------------------------------------
Plan hash value: 3409064691

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        | 11602 |   169K|  4148   (7)| 00:00:50 |
|*  1 |  FILTER            |        |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TIMING | 11602 |   169K|  4148   (7)| 00:00:50 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('12:00','HH24:MI')<=TO_DATE('13:00','HH24:MI'))
   2 - filter(TO_DATE("HOURSTEXT",'HH24:MI')>=TO_DATE('12:00','HH24:MI')
               AND TO_DATE("HOURSTEXT",'HH24:MI')<=TO_DATE('13:00','HH24:MI'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      28078  consistent gets
      14311  physical reads
          0  redo size
    5667161  bytes sent via SQL*Net to client
     152281  bytes received via SQL*Net from client
      13808  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     207104  rows processed

sys@SM10> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
sys@SM10> select        * from timing where hoursNumber between 1200 and 1300 ;

171968 rows selected.

Elapsed: 00:00:01.38

Execution Plan
----------------------------------------------------------
Plan hash value: 4284679420

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   135K|  1988K|  3940   (2)| 00:00:48 |
|*  1 |  TABLE ACCESS FULL| TIMING |   135K|  1988K|  3940   (2)| 00:00:48 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("HOURSNUMBER">=1200 AND "HOURSNUMBER"<=1300)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      25746  consistent gets
      14311  physical reads
          0  redo size
    4654857  bytes sent via SQL*Net to client
     126519  bytes received via SQL*Net from client
      11466  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171968  rows processed

    Então eu iria para a DATA ou NÚMERO se eu puder. É mais rápido e menor. Apenas certifique-se de ignorar a parte DAY do seu encontro, se não precisar.

    • 0

  4. Eu estava procurando o mesmo cenário de armazenar hh:mm (ou hhmm) em uma coluna do oráculo. Eu tentei o exemplo acima (de carregar dados na tabela de tempo) e descobri que, como usamos o gerador de valor aleatório, ele está armazenando dados 12.0 em vez de 12.00 na coluna hoursText (que é varchar). Também acho que as consultas não estão usando índices, pois temos funções definidas na coluna no

    select ... where to_date(hoursText) between ...

    Acho que não precisamos dessa conversão para to_date. Por que não:

    select  * from timing where hoursText between '12:0' and '13:00' ;

    Acho que essa consulta usará o índice e retornará os resultados rapidamente.

    • -1

relate perguntas