데이터베이스 설계: 부울 열을 타임스탬프 열로 바꿉니다.

데이터베이스 설계: 부울 열을 타임스탬프 열로 바꿉니다.

이전에 이런 식으로 테이블을 만들었습니다.

create table workflow (
    id number primary key,
    name varchar2(100 char) not null,
    is_finished number(1) default 0 not null,
    date_finished date
);

열 is_finished는 워크플로우가 완료되었는지 여부를 나타냅니다.열 date_finished는 워크플로우가 완료된 시점입니다.

그런 다음 "I need need is_finished as I can't say: where data_finished is null"이라는 아이디어를 얻었고 is_finished 열 없이 설계했습니다.

create table workflow (
    id number primary key,
    name varchar2(100 char) not null,
    date_finished date
);

(Oracle 10 사용)

좋은 생각일까요, 나쁜 생각일까요?NULL 값을 가진 열에는 인덱스를 가질 수 없다고 들었습니다.where data_finished is not null큰 테이블에서는 매우 느릴 것입니다.

좋은 생각일까요, 나쁜 생각일까요?

좋은 생각이에요.

중복된 열에 의해 사용된 공간을 없앴습니다. DATE 열은 이중 작업을 수행합니다. 작업이 완료된 시점과 시기를 알 수 있습니다.

NULL 값이 있는 열에는 인덱스를 가질 수 없으므로 큰 테이블에서는 "data_finished가 null이 아닌 경우"가 매우 느릴 것이라고 들었습니다.

틀렸습니다.Oracle 인덱스는 NULL 값을 무시합니다.

인덱스가 생성되지 않는 NULL 값을 피하기 위해 함수 기반 인덱스를 생성할 수 있지만, 제가 만난 대부분의 DBA들은 이 값들을 정말 좋아하지 않기 때문에 싸움에 대비해야 합니다.

null 값을 색인하는 올바른 방법이 있고 FBI를 사용하지 않습니다.Oracle은 null 값을 인덱싱하지만 트리의 null LEAF 값을 인덱싱하지 않습니다.그래서 당신은 그 열을 제거할 수 있습니다.is_finished이렇게 인덱스를 만들어주세요.

CREATE INDEX ON workflow (date_finished, 1);

그런 다음 이 쿼리에 대한 설명 계획을 확인하면 다음과 같습니다.

SELECT count(*) FROM workflow WHERE date_finished is null;

(최적화 도구가 행복한 경우) 사용 중인 인덱스를 볼 수 있습니다.

다시 원래 질문으로 돌아가: 여기에 있는 다양한 답들을 보면, 정답은 없다고 생각합니다.저는 불필요한 경우 칼럼을 삭제하는 것을 개인적으로 선호할 수도 있지만 칼럼의 의미를 지나치게 강조하는 것도 좋아하지 않습니다.여기에는 두 가지 개념이 있습니다.

1. 레코드가 끝났습니다.is_finished
2. 레코드는 특정 날짜에 마쳤습니다.date_finished

이것들을 따로 보관해야 할 수도 있고, 그렇지 않을 수도 있습니다.제가 생각할 때는.is_finished칼럼, 신경 쓰입니다.앞으로 기록이 끝난 곳에서 상황이 발생할 수도 있지만 정확히 언제인지는 알 수 없습니다.다른 출처에서 데이터를 가져와야 하는데 날짜를 알 수 없습니다.물론이죠, 지금은 비즈니스 요구사항이 아니지만 상황이 바뀝니다.그럼 어떻게 합니까?음, 당신은 그 안에 어떤 더미 값을 넣어야 합니다.date_finished열, 그리고 이제 데이터를 조금 손상시켰군요.끔찍하진 않지만, 거기에 문질러져 있습니다.내 머리 속의 작은 목소리는 내가 그런 일을 할 때 당신이 잘못하고 있다고 소리치고 있습니다.

내 충고는 따로따로 해요.아주 작은 칼럼과 아주 마른 인덱스를 말씀하시는 겁니다.여기서 스토리지는 문제가 되지 않습니다.

대표 규칙:프로그램 로직이 어리석고 강력할 수 있도록 지식을 데이터에 통합합니다.

-에릭 S.레이먼드

칼럼이 공간 낭비라고 말한 모든 사람들에게:

더블 듀티는 데이터베이스에서 좋은 것이 아닙니다.당신의 주된 목표는 명확해야 합니다.많은 시스템, 도구, 사람들이 당신의 데이터를 사용할 것입니다.다른 열 안에 의미를 숨겨서 값을 위장하면 다른 시스템이나 사용자가 잘못 이해하기를 간청하는 것입니다.

그리고 그것이 공간을 절약한다고 생각하는 사람은 완전히 잘못된 것입니다.

해당 날짜 열에 두 개의 인덱스가 필요합니다.하나는 OMG가 제안하는 것처럼 기능 기반이 될 것입니다.다음과 같이 나타납니다.

NVL(Date_finished, TO_DATE('01-JAN-9999')

따라서 완료되지 않은 작업을 찾으려면 where 절을 정확하게 작성해야 합니다.

다음과 같이 나타납니다.

여기서 NVL(Date_finished, TO_DATE('01-JAN-9999')) = TO_DATE('01-JAN-9999')

네, 분명하죠그것보다 완전히 낫습니다.

WHERE IS_미완성 = 'YES'

같은 열에 두 번째 인덱스를 사용하려는 이유는 해당 날짜의 다른 모든 쿼리에 대한 것입니다.날짜별로 직업을 찾는 데 그 지수를 사용하고 싶지 않을 것입니다.

그렇다면 OMG의 제안 등을 통해 어떤 성과를 거두었는지 살펴보겠습니다.

공간을 더 많이 사용하고, 데이터의 의미를 혼란스럽게 하고, 오류를 범할 가능성이 높아졌습니다.위너!

때로는 프로그래머들이 아직도 70년대에 살고 있는 것처럼 보이는데, 그 때는 MB 용량의 하드 드라이브 공간이 주택 계약금이었습니다.

많은 명확성을 포기하지 않고 공간을 효율적으로 활용할 수 있습니다.Is_unfinished를 Y 또는 NULL로 설정...만약 당신이 '할 일'을 찾을 때만 그 열을 사용할 것입니다.이렇게 하면 해당 인덱스가 압축됩니다.이 값은 미완성된 행의 크기만큼만 됩니다(이런 방식으로 색인화되지 않은 널을 이용하는 대신).)테이블에 약간의 공간을 두셨지만, 전반적으로 FBI보다는 적습니다.열에 1바이트가 필요하고, 완료되지 않은 행만 색인화하므로 작업의 작은 부분일 뿐이며 아마도 꽤 일정하게 유지될 것입니다.FBI는 당신이 그들을 찾으려고 하든 하지 않든 모든 행에 7바이트가 필요할 것입니다.이 지수는 미완성된 작업의 크기뿐만 아니라 표의 크기와도 보조를 맞출 것입니다.

OMG의 코멘트에 대한 답변

그/그녀는 그/그녀의 논평에서 당신이 완성되지 않은 일자리를 찾기 위해서는 당신이 그냥 사용하면 된다고 말합니다.

WHERE date_finished IS NULL

하지만 그의 대답에서 그는 말합니다.

인덱싱되지 않는 NULL 값을 우회하기 위해 함수 기반 인덱스를 생성할 수 있습니다.

그가 가리키는 링크를 따라가면 NVL을 사용하여 null 값을 다른 임의의 값으로 대체하면 다른 설명이 무엇인지 잘 모르겠습니다.

좋은 생각일까요, 나쁜 생각일까요?NULL 값이 있는 열에는 인덱스를 가질 수 없으므로 큰 테이블에서는 "data_finished가 null이 아닌 경우"가 매우 느릴 것이라고 들었습니다.

Oracle은 nullable 필드를 인덱싱하지만 값을 인덱싱하지는 않습니다.

이는 표시된 필드에 색인을 작성할 수 있음을 의미합니다.NULL, 하지만 기록이 남아있는 건NULL이 분야는 지수에 들지 못할 겁니다

이것은, 차례대로, 만일 당신이 그들을date_finished NULL, 지수는 크기가 적을 것입니다.NULL값은 인덱스에 저장되지 않습니다.

그래서 범위의 동일성 검색을 포함한 쿼리는date_finished더 좋은 성과를 낼 수 있을 겁니다.

물론 이 솔루션의 단점은 다음과 관련된 쿼리입니다.NULL의 가치.date_finished전체 테이블 스캔으로 돌아가야 합니다.

두 가지 인덱스를 만들어 이 문제를 해결할 수 있습니다.

CREATE INDEX ON mytable (date_finished)
CREATE INDEX ON mytable (DECODE(date_finished, NULL, 1))

다음 쿼리를 사용하여 미완성 작업을 찾습니다.

SELECT  *
FROM    mytable
WHERE   DECODE(date_finished, NULL, 1) = 1

이는 분할된 인덱스와 같이 동작합니다. 전체 작업은 첫 번째 인덱스에 의해 인덱싱되고, 불완전한 작업은 두 번째 인덱스에 의해 인덱싱됩니다.

완료되거나 완료되지 않은 작업을 검색할 필요가 없는 경우 언제든지 적절한 인덱스를 제거할 수 있습니다.

테이블 디자인 면에서, 당신이 그 부분을 제거한 것은 좋은 일이라고 생각합니다.is_finished칼럼은 불필요하다고 말씀하신 것과 같습니다(중복입니다).필요하지 않은 경우에는 데이터를 추가로 저장할 필요가 없습니다. 공간만 낭비하게 됩니다.성능적인 측면에서 NULL 값이 문제가 되지 않는다고 봅니다.그들은 무시당해야 합니다.

다른 답변에서 이미 언급한 것처럼 "WHERE date_finished IS NULL"을 제외한 모든 쿼리에 대해 null을 인덱스가 작동하기 때문에 null을 사용합니다(따라서 해당 쿼리를 사용해야 하는지에 따라 다릅니다).저는 9999년과 같은 이상치는 절대로 사용하지 않을 것입니다.

미완성 워크플로우의 date_finished 값으로 "dummy" 값(예: 9999년 12월 31일)을 사용할 수도 있습니다.

9999년과 같은 특이치는 성능에 영향을 미치는데, 그 이유는 다음과 같습니다(http://richardfoote.wordpress.com/2007/12/13/outlier-values-an-enemy-of-the-index/) :

범위 스캔의 선택도는 기본적으로 CBO에 의해 관심 범위에 있는 값의 수를 가능한 값의 전체 범위로 나눈 값으로 계산됩니다(즉, 최대값에서 최소값을 뺀 값).

9999와 같은 값을 사용하면 DB는 필드에 저장되는 값의 범위를 실제 2008-2010이 아닌 2008-9999로 간주합니다. 따라서 모든 범위 쿼리(예: "2008-2009 사이")는 실제로 범위의 약 절반을 포함하는 것과 비교하여 가능한 값 범위의 아주 작은 %를 포함하는 것으로 보입니다.이 통계를 사용하여 적용 가능한 값의 %가 높을 경우 많은 행이 일치할 수 있으며 인덱스 검색보다 전체 테이블 검색이 더 빠를 수 있습니다.데이터에 특이치가 있으면 이 작업을 제대로 수행하지 않습니다.

다른 사람들이 말한 것처럼 도출 가능한 값 열을 제거하는 것이 좋습니다.

한 가지 더 생각하면 열을 제거하면 is_day = No 및 finished_date = 어제의 경우와 같이 코드화해야 하는 역설적인 조건을 피할 수 있습니다.기타.

색인/비 색인 열을 해결하려면 다음과 같이 두 개의 테이블을 간단히 조인하는 것이 더 쉬운 방법이 아닐까요?

-- PostgreSQL
CREATE TABLE workflow(
    id SERIAL PRIMARY KEY
  , name VARCHAR(100) NOT NULL
);

CREATE TABLE workflow_finished(
    id INT NOT NULL PRIMARY KEY REFERENCES workflow
  , date_finished date NOT NULL
);

따라서 workflow_finished에 레코드가 있으면 이 워크플로가 완료되고 그렇지 않으면 완료되지 않습니다.제가 보기에는 이것은 꽤 간단한 것 같습니다.

완료되지 않은 워크플로우를 쿼리할 때 쿼리는 다음과 같습니다.

-- Only unfinished workflow items
SELECT workflow.id
FROM workflow
WHERE NOT EXISTS(
  SELECT 1
  FROM workflow_finished
  WHERE workflow_finished.id = workflow.id);

원본 쿼리를 원하시나요?깃발이랑 날짜랑?그러면 다음과 같이 쿼리합니다.

-- All items, with the flag and date
SELECT
    workflow.id
  , CASE
    WHEN workflow_finished.id IS NULL THEN 'f'
    ELSE                                   't'
    END AS is_finished
  , workflow_finished.date_finished
FROM
            workflow
  LEFT JOIN workflow_finished USING(id);

데이터를 사용하는 소비자의 경우, 필요에 따라 뷰를 생성할 수 있고 생성해야 합니다.

함수 기반 인덱스 대신 "더미" 값(예: 9999년 12월 31일 또는 가장 이른 예상 date_finished 값 하루 전)을 미완성 워크플로우의 date_finished 값으로 사용할 수도 있습니다.

EDIT: 주석 다음과 같은 대체 더미 날짜 값입니다.

저는 단일 컬럼 솔루션을 선호합니다.

그러나 제가 가장 자주 사용하는 데이터베이스에서는 NULL이 인덱스에 포함되어 있기 때문에 개방형 워크플로우를 검색하는 일반적인 경우는 빠른 반면에 사용자의 경우는 느린 편입니다.개방형 워크플로우를 검색하는 경우가 가장 일반적인 작업 중 하나일 가능성이 높기 때문에 단순히 해당 검색을 지원하기 위해서는 중복 열이 필요할 수도 있습니다.

성능 테스트를 통해 더 나은 솔루션을 성능적으로 사용할 수 있는지 확인한 다음, 필요한 경우 덜 좋은 솔루션으로 되돌아갑니다.

언급URL : https://stackoverflow.com/questions/3916847/database-design-replace-a-boolean-column-with-a-timestamp-column