ORACLE DB - INDEX

 
/***********************************************
  Oracle DB 인덱스 생성
   Oracle 19c 기준으로 작성되었습니다. 
***********************************************/



/***********************************************
 CUST, CUST_DTL 테이블, 인덱스 상태 조회
   STR 계정 접속
***********************************************/
SELECT
    OBJECT_TYPE,
    OBJECT_NAME,
    CREATED
FROM USER_OBJECTS
ORDER BY 2,1;



/***********************************************
 통계 정보 생성
   STR 계정 접속
***********************************************/
SELECT
    TABLE_NAME,
    TABLESPACE_NAME,
    LAST_ANALYZED
FROM USER_TABLES
ORDER BY 1;
-- 통계 정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'STR', TABNAME=>'CUST');
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'STR', TABNAME=>'CUST_DTL');
-- 통계 정보 삭제
EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'STR', TABNAME=>'CUST');
EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'STR', TABNAME=>'CUST_DTL');



/***********************************************
 인덱스 추가 생성
  테이블명: CUST 
  인덱스명: CUST_X1(CUST_NM)
   STR 계정 접속
***********************************************/

SELECT *
FROM STR.CUST
WHERE CUST_NO = '20221125TKDN00000007';

-- CUST_NM 조회
-- 김영칠 3건, 김일영 2건, 김일삼 1건
SELECT *
FROM STR.CUST
WHERE CUST_NM = '김영칠';

CREATE INDEX STR.CUST_X1 ON STR.CUST
(CUST_NM)
TABLESPACE TS_STR_I;


SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    COLUMN_POSITION
FROM USER_IND_COLUMNS
ORDER BY 1, 2;


/***********************************************
 FBI 인덱스 추가 생성
  테이블명: CUST 
  인덱스명: CUST_X1(CUST_NM)
   STR 계정 접속
***********************************************/
SELECT * 
FROM STR.CUST_DTL
WHERE MBL_NO = '015-0101-0101';
SELECT * 
FROM STR.CUST_DTL
WHERE MBL_NO = '01501010101';
SELECT
  MBL_NO,
  REPLACE(MBL_NO,'-','')
FROM STR.CUST_DTL;
SELECT * 
FROM STR.CUST_DTL
WHERE REPLACE(MBL_NO,'-','') = '01501010101';

CREATE INDEX STR.CUST_DTL_X1 ON STR.CUST_DTL
(REPLACE(MBL_NO,'-',''))
TABLESPACE TS_STR_I;
SELECT
    A.TABLE_NAME,
    A.INDEX_NAME,
    B.INDEX_TYPE,
    A.COLUMN_NAME,
    A.COLUMN_POSITION
FROM 
    USER_IND_COLUMNS A,
    USER_INDEXES B
WHERE A.TABLE_NAME = B.TABLE_NAME 
AND A.INDEX_NAME = B.INDEX_NAME
ORDER BY 1, 2;



-- 인덱스 삭제
DROP INDEX STR.CUST_X1;
DROP INDEX STR.CUST_DTL_X1;

[출처] DB보다

MSSQL - INDEX 재 정리

SQL Server Management Studio 사용하여 인덱스 유지 관리

인덱스를 다시 구성하거나 다시 작성하려면

1. 개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.

2. 테이블 폴더를 확장합니다.

3. 인덱스를 다시 구성할 테이블을 확장합니다.

4. 인덱스 폴더를 확장합니다.

5. 다시 구성할 인덱스를 마우스 오른쪽 단추로 클릭하고 다시 구성을 선택합니다.

6. 인덱스 다시 구성 대화 상자에서 다시 구성할 인덱스 표에 올바른 인덱스가 있는지 확인한 다음 확인을 클릭합니다.

7. 큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.

8. 확인.

테이블의 모든 인덱스를 다시 구성하려면

1. 개체 탐색기에서 인덱스를 다시 구성할 테이블이 포함된 데이터베이스를 확장합니다.

2. 테이블 폴더를 확장합니다.

3. 인덱스를 다시 구성할 테이블을 확장합니다.

4. 인덱스 폴더를 마우스 오른쪽 단추로 클릭하고 모두 다시 구성을 선택합니다.

5. 인덱스 다시 구성 대화 상자에서 다시 구성할 인덱스에 올바른 인덱스가 있는지 확인합니다. 다시 구성할 인덱스 표에서 인덱스를 제거하려면 인덱스를 선택한 다음 Delete 키를 누릅니다.

6. 큰 개체 열 데이터 압축 확인란을 선택하여 LOB(Large Object) 데이터가 포함된 모든 페이지도 압축되도록 지정합니다.

7. 확인.

[1] 특정 인덱스를 다시 구성하려면

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode

    ON HumanResources.Employee

    REORGANIZE;

[2] 테이블의 모든 인덱스를 다시 구성하려면 (인덱스를 다시 작성하는 것보다 리소스가 덜 사용됩니다. 기본 인덱스 유지 관리 방법.)

ALTER INDEX ALL ON HumanResources.Employee 

REORGANIZE;

[3] 인덱스를 다시 작성하려면 (인덱스가 삭제된 다음 다시 생성됩니다. 작업 시 잠금 및 DB성능 저하.)

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee 

REBUILD;

[4] 테이블에서 모든 인덱스를 다시 작성하려면 (서비스 내리고 진행해야 함. 데이터의 크기에 따라 시간이 오래 걸릴 수 있음.)

ALTER INDEX ALL ON Production.Product

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

              STATISTICS_NORECOMPUTE = ON);

DB - MSSQL - 테이블 인덱스 재정리

-----------------------------------------------------------------------------------
해당 데이터베이스의 모든 테이블의 모든 인덱스를 재정리
-----------------------------------------------------------------------------------

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor





-----------------------------------------------------------------------------------
특정한 테이블 인텍스 재정리
-----------------------------------------------------------------------------------

USE DB명

SP_HELPINDEX table명



--조회
DBCC SHOWCONTIG (table명) WITH TABLERESULTS

/*

Scan Density(logical extent density) 와 Avg Page Density(Avg free bytes/page) 확인.

30~50% under이면 Reorg가 필요

*/

--index defrag

DBCC INDEXDEFRAG(DB명, TABLE명, INDEX명)
--혹은 dbcc dbreindex(TABLE명, '', 100)

--reindex 후 다시 조회
DBCC SHOWCONTIG (SC_TRAN) WITH TABLERESULTS