데이터베이스 질문과 답변
테이블 사이즈는 작은데 빈번한 입력/수정/삭제가 일어나야 하는 경우 테이블에 인덱스를 만들어주는것이 유리할지 고민입니다.
혹시 테이블에 인덱스가 설정되어있는 경우 Lock 의 범위가 Table 전체가 아니고 해당 Row 단위로 잡힐수 있는지요?
퍼포먼스 측면에서 어떤것이 유리할지 조언 부탁드립니다.
데이터 변경 시 테이블에 대한 TM LOCK이 걸릴 것이고 변경되는 데이터에 대해서만 TX LOCK이 걸릴 것 같은데요.
실제로 변경되는 데이터에 대해서만 LOCK이 걸리는 것은 동일하고 SCAN하는 범위에만 차이가 나므로 제 생각에는 테이블 사이즈가 충분히 작다면
굳이 인덱스를 생성하지 않더라도 크게 지장이 없을 것 같습니다만.. 충분히 작은 테이블이라는 기준이 어느 정도인지는 판단하기 어렵네요.
다만 입력/수정/삭제가 빈번하게 일어난다면 DML마다 인덱스 변경도 일어나야 할 것이기에 DML에 대한 성능 저하는 확실히 늘어나겠죠.
입력할때는 인덱스가 부담이 될수도 있으나
수정과 삭제에 있어서 인덱스는 필수라고 할 수 있습니다.
변경 대상을 인덱스를 통해 빠르게 찾아야죠.
매번 풀스캔 하면서 변경 대상을 찾는다면 엄청난 비효율입니다.
제가 읽은 책에서
인덱스 만들 때의 기준이라고 해서
크기가 작은 테이블에는 애초에 인덱스도 풀스캔도 큰 차이가 없고 따라서 작은 테이블은
인덱스를 굳이 만들지 않아도 괜찮다고 적혀있었는데..
생각해보니 속도적 측면에서는 큰 차이가 없겠지만 버퍼캐시 효율성 차원에서는
작은 테이블도 인덱스가 있는 것이 나을 수 있겠네요.
답변 감사드립니다.
데이터 총 건수가 3000건 미만이고, 인덱스를 생성해도 동일하게 3000건일것으로 추정됩니다.
그래도 역시 인덱스를 만들어주는것이 효율적인가요?
https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_in.htm
https://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes002.htm
여기 링크를 보시면
Use the following guidelines for determining when to create an index:
Create an index if you frequently want to retrieve less than about 15% of the rows in a large table. This threshold percentage varies greatly, however, according to the relative speed of a table scan and how clustered the row data is about the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.
Index columns that are used for joins to improve join performance.
Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key; see Chapter 3, "Maintaining Data Integrity Through Constraints" for more information.
Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large.
라고 되어있는데 이유는 설명하지 않고 있어요.
제 추측은 작은 테이블에 인덱스가 많을 경우에 파싱할 때 여러 케이스가 발생할 것이고 여러 케이스가 발생하면
파싱 비용이 늘어나서 그런 게 아닐까.. 생각하는데요.
인덱스를 많이 만들어야 해서 파싱비용이 부담된다 => 인덱스 미생성
인덱스 적게 만들 것이어서 파싱비용보다 캐시 블럭 활용 효율성을 올리겠다 => 인덱스 생성
이렇게 접근해야 하는 게 아닌가 생각됩니다. 이마저도 테이블 크기가 작다면 의미가 많이 없긴하겠지만요.
다시 한번 오라클 성능 고도화 저자이신 조시형님께 염치 불구하고 다시 여쭤봤고 질문 드린 게 죄송할 정도로 많은 테스트와 함께 답변을 주셨습니다.
http://cafe.naver.com/dbian/212
결론적으로는 상황에 따라 다르나 만들어주는 것이 좋을 것 같습니다.
간단히 요약해드리면
1.작은 테이블도 PK인덱스는 자동으로 생성이 되며, 보통 소형 테이블은 마스터성 테이블이고 NL 조인 방식에서 룩업되고 이때 PK 인덱스만으로도 충분하다.
2.NL 조인에서 소형 테이블이 드라이빙 테이블로 사용되거나 해시조인 또는 소트머지 조인할 때 초소형(100~200)건 정도의 테이블이면 굳이 인덱스를 만들 필요가 없다. 다만 1~2만건 정도의 테이블이라면 해당 컬럼이 변별력이 좋은지 수행빈도가 높은지에 따라 판단해야 한다.
-변별력이 좋을 경우 인덱스를 만드는 것이 좋다.(수행 빈도가 매우 낮다면 만들지 않아도 무방)
-다만 변별력이 좋은 경우에도 해당 컬럼에대한 INSERT, DELETE, UPDATE가 엄청 많은 경우라면 TPS(Transaction Per Second)에 영향을 줄 수 있고 인덱스 생성에 신중을 기해야 한다.
-변별력이 나쁠 경우 인덱스를 만들지 않는 것이 좋다.
책에 있는 몇줄의 문구와 오라클 문서를 바탕으로 왜 소형 테이블에 인덱스가 필요가 없다고 한 것인지 의아했는데.. 덕분에 좋은 내용을 알게 되었네요.
) 버튼을 클릭하여 작성 하시면 됩니다.