[MYSQL] INDEX 확인 및 생성 스크립트

1. 특정 Database 및 Table의 Index 확인 쿼리 SELECT A.TABLE_SCHEMA "DB", A.TABLE_NAME "테이블ID", A.TABLE_COMMENT "테이블명", B.INDEX_NAME "인덱스명", B.COLUMN_NAME "컬럼ID", C.DATA_TYPE "데이터타입", CASE WHEN C.NUMERIC_SCALE IS NULL OR C.NUMERIC_SCALE = '0' THEN CASE WHEN C.NUMERIC_PRECISION IS NOT NULL THEN C.NUMERIC_PRECISION ELSE C.CHARACTER_MAXIMUM_LENGTH END ELSE CONCAT(C.NUMERIC_PRECISION, ',', C.NUMERIC_SCALE) EN..

  • format_list_bulleted Database/MYSQL
  • · 2023. 5. 4.
  • textsms

[TIBERO] 인덱스 추가 시, Plan변화 TEST

1. Table 생성 create tablespace USER_TEST01 datafile 'TS_USER_TEST_01.dtf' size 2G; create user test_user01 identified by "test01#$" default tablespace USER_TEST01; grant connect, resource to test_user01; --drop table test_user01.test01; create table test_user01.test01 (c1 number, c2 number, c3 varchar2(10) ) nologging; 2. 50만건 insert DECLARE TYPE tbl_ins IS TABLE OF test_user01.test01%ROWTYPE IND..

  • format_list_bulleted Database/Tibero
  • · 2023. 5. 4.
  • textsms

[Index Tunning] Random Access 제거

1. 실습환경 생성 Linux oracle1 3.10.0-514.el7.x86_64 #1 SMP Tue Nov 22 16:42:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux Memory 4G 1-1. Tablespace 및 계정생성 create tablespace dbms_data datafile '/app/oradata/dbms_data001.dbf' size 4G autoextend off; create temporary tablespace dbms_temp tempfile '/app/oracle/cfgtoollogs/orcl/dbms_temp001.dbf' size 1G autoextend off; create user dbms_test identified by "or..

  • format_list_bulleted Database/Tuning
  • · 2022. 4. 25.
  • textsms