null 값으로 날짜 열을 인덱싱하는 방법은 무엇입니까?

null 값으로 날짜 열을 인덱싱하는 방법은 무엇입니까?

일부 행에 null 값이 있는 경우 날짜 열을 인덱싱하려면 어떻게 해야 합니까?날짜 범위와 null 날짜가 있는 행 사이에서 행을 선택해야 합니다.

Oracle 9.2 이상을 사용합니다.

찾은 옵션

1. 날짜 열에 비트맵 색인 사용
2. 날짜가 null일 때 값이 1인 상태 필드의 인덱스 및 날짜에 인덱스 사용
3. 날짜 열에 인덱스 사용 및 null이 아닌 다른 허용 열

옵션에 대한 제 생각은 다음과 같습니다.

to 1: 비트맵 인덱스를 사용하기 위해 테이블에 너무 많은 다른 값이 있습니다.
to 2: 이 목적으로만 필드를 추가하고 null 날짜 행을 검색하려면 쿼리를 변경해야 합니다.
to 3: 실제로 필요하지 않은 필드를 인덱스에 추가하기 위해 까다로운 잠금

이 경우에 가장 좋은 방법은 무엇입니까?잘 부탁드립니다.

제가 읽은 몇 가지 정보:

오라클 날짜 인덱스
오라클 인덱스 열 값이 null인 경우는 언제입니까?

편집

저희 테이블에는 30만 개의 레코드가 있습니다. 매일 1,000개에서 10,000개의 레코드가 삽입되고 삭제됩니다.280,000개의 레코드가 날짜에 null로 전달되었습니다.그것은 일종의 피킹 버퍼입니다.

당사의 구조(영어로 번역)는 다음과 같습니다.

create table orders
(
  orderid              VARCHAR2(6) not null,
  customerid           VARCHAR2(6) not null,
  compartment          VARCHAR2(8),
  externalstorage      NUMBER(1) default 0 not null,
  created_at           DATE not null,
  last_update          DATE not null,
  latest_delivery      DATE not null,
  delivered_at         DATE,
  delivery_group       VARCHAR2(9),
  fast_order           NUMBER(1) default 0 not null,
  order_type           NUMBER(1) default 0 not null,
  produkt_group        VARCHAR2(30)
)

Tony의 훌륭한 조언 외에도 쿼리를 조정할 필요가 없는 방식으로 열을 인덱싱하는 옵션도 있습니다.비결은 인덱스에만 상수 값을 추가하는 것입니다.

시연:

10,000개의 행이 있는 테이블을 만들고 그 중 6개에만 a_date 열에 대한 NULL 값이 포함됩니다.

SQL> create table mytable (id,a_date,filler)
  2  as
  3   select level
  4        , case when level < 9995 then date '1999-12-31' + level end
  5        , lpad('*',1000,'*')
  6     from dual
  7  connect by level <= 10000
  8  /

Table created.

먼저 a_date 열에 인덱스만 만들면 "여기서 a_date가 null"이라는 술어를 사용할 때 인덱스가 사용되지 않는다는 것을 보여드리겠습니다.

SQL> create index i1 on mytable (a_date)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select id
  2       , a_date
  3    from mytable
  4   where a_date is null
  5  /

        ID A_DATE
---------- -------------------
      9995
      9996
      9997
      9998
      9999
     10000

6 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6 Bytes=72)
   1    0   TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=72 Card=6 Bytes=72)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        720  consistent gets
          0  physical reads
          0  redo size
        285  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

720개의 일관된 가져오기 및 전체 테이블 스캔.

이제 상수 1을 포함하도록 인덱스를 변경하고 테스트를 반복합니다.

SQL> set autotrace off
SQL> drop index i1
  2  /

Index dropped.

SQL> create index i1 on mytable (a_date,1)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true)

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select id
  2       , a_date
  3    from mytable
  4   where a_date is null
  5  /

        ID A_DATE
---------- -------------------
      9995
      9996
      9997
      9998
      9999
     10000

6 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=6 Bytes=72)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLE' (Cost=2 Card=6 Bytes=72)
   2    1     INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=2 Card=6)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        285  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

6개의 일관된 가져오기 및 인덱스 범위 검색.

안녕, 롭.

"우리 테이블에는 30만 개의 레코드가 있습니다.280,000개의 레코드가 날짜에 null로 전달되었습니다."

즉, 거의 전체 테이블이 DELIED_AT가 null인 위치를 검색하는 쿼리를 충족합니다.인덱스는 해당 검색에 완전히 적합하지 않습니다.전체 테이블 스캔이 가장 좋은 방법입니다.

Enterprise Edition 라이센스가 있고 여유 CPU가 있는 경우 병렬 쿼리를 사용하면 시간이 단축됩니다.

당신의 질문이 이와 같을 것이라는 뜻입니까?

select ...
from mytable
where (datecol between :from and :to
       or datecol is null);

null이 테이블에 상대적으로 적을 경우에만 인덱싱할 수 있습니다. 그렇지 않으면 전체 테이블 검색이 Null을 찾는 가장 효율적인 방법일 수 있습니다.인덱싱할 가치가 있다고 가정하면 다음과 같은 기능 기반 인덱스를 만들 수 있습니다.

create index mytable_fbi on mytable (case when datecol is null then 1 end);

그런 다음 쿼리를 다음으로 변경합니다.

select ...
from mytable
where (datecol between :from and :to
       or case when datecol is null then 1 end = 1);

케이스를 더 매끄럽게 만드는 기능으로 포장할 수 있습니다.

create or replace function isnull (p_date date) return varchar2
DETERMINISTIC
is
begin
    return case when p_date is null then 'Y' end;
end;
/

create index mytable_fbi on mytable (isnull(datecol));

select ...
from mytable
where (datecol between :from and :to
       or isnull(datecol) = 'Y');

나는 null이 아닐 때 함수가 NULL을 반환하여 null 날짜만 인덱스에 저장되도록 했습니다.또한 저는 기능을 Deterministic으로 선언해야 했습니다. (저는 단지 "isnull"이라는 이름이 저에게 제안하기 때문에 1이 아닌 'Y'를 반환하도록 변경했습니다. 제 선호를 무시하세요!)

테이블 검색을 피하고 다음과 같은 인덱스를 만듭니다.

create index i1 on mytable (a_date,id) ;

언급URL : https://stackoverflow.com/questions/3068730/how-to-index-a-date-column-with-null-values