¡á¿¹ 6_1_1 || Å×À̺íÀÇ Á¶È¸
SELECT * FROM INSA
WHERE SALARY >= 3000;
¡á¿¹ 6_1_2 || À妽º»ý¼º
CREATE INDEX INSA.INSA_SAL
ON INSA.INSA (SALARY);
¡á¿¹ 6_1_3 || À妽º »ý¼º ÈÄ Á¶È¸
SELECT /*+ INDEX (INSA INSA_SAL) */
* FROM INSA
WHERE SALARY >= 3000;
¡á¿¹ 6_1_4 || PK Á¦¾àÁ¶°Ç UNIQUE INDEX »ý¼ºÀÇ ¿¹
ALTER TABLE INSA.INSA ADD
(
CONSTRAINT INSA_PK
PRIMARY KEY ( SABUN )
);
¡á¿¹ 6_1_5 || UNIQUE INDEX »ç¿ë ¿¹
SELECT SABUN, ENG_NAME, HP, JOIN_GBN_CODE, SALARY
FROM INSA
WHERE SABUN = '2013022601';
¡á¿¹ 6_1_6 || INDEX »ý¼º°ú »ç¿ë ¿¹
CREATE INDEX À妽º¸í
ON Å×À̺í¸í(Ä÷³,..n);
CREATE INDEX INSA_HP
ON INSA(HP);
¡á¿¹ 6_1_7 || À妽º È®ÀÎ
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES -- À¯ÀúÀÇ »ý¼ºµÈ À妽º¸¦ º¼ ¼ö ÀÖ´Â µñ¼Å³Ê¸®Å×À̺í
WHERE TABLE_NAME = 'INSA';
¡á¿¹ 6_2_1 || Å×ÀÌºí »ý¼º
____CREATE TABLE____ INSA_TE_IN(
SABUN VARCHAR2(10) CONSTRAINT INSA_TE_IN_PK PRIMARY KEY,
HP VARCHAR2(13)
);
¡á[SQL 6_2_1] Å×À̺í È®ÀÎ
DESC INSA_TE_IN;
¡á¿¹ 6_2_2 || UNIQUE INDEX »ý¼º
CREATE UNIQUE INDEX IN_HP
ON INSA_TE_IN(HP);
¡á[SQL 6_2_2] À妽º È®ÀÎ
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES -- À¯ÀúÀÇ »ý¼ºµÈ À妽º¸¦ º¼ ¼ö ÀÖ´Â µñ¼Å³Ê¸®Å×À̺í
WHERE TABLE_NAME = 'INSA_TE_IN';
¡á¿¹ 6_2_3 || NUNUNIQUE INDEX »ý¼º
CREATE INDEX INSA_SAL
ON INSA(SALARY);
¡á[SQL 6_2_3] À妽º È®ÀÎ
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES -- À¯ÀúÀÇ »ý¼ºµÈ À妽º¸¦ º¼ ¼ö ÀÖ´Â µñ¼Å³Ê¸®Å×À̺í
WHERE TABLE_NAME = 'INSA';
¡á¿¹ 6_2_4 || COMPOSITE INDEX »ý¼º
CREATE INDEX INSA_GBN_SABUN
ON INSA(JOIN_GBN_CODE, SABUN);
¡á[SQL 6_2_4] À妽º È®ÀÎ
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES -- À¯ÀúÀÇ »ý¼ºµÈ À妽º¸¦ º¼ ¼ö ÀÖ´Â µñ¼Å³Ê¸®Å×À̺í
WHERE TABLE_NAME = 'INSA';
¡á¿¹ 6_2_5 || COMPOSITE INDEXÀÇ Á¶È¸
SELECT SABUN, NAME, SALARY, JOIN_GBN_CODE
FROM INSA
WHERE SABUN LIKE '2013%'
AND JOIN_GBN_CODE = 'RGL';
¡á[SQL 6_2_5] È®ÀÎ SQL
SELECT /*+ INDEX_DESC(INSA INSA_SAL) */
SABUN, ENG_NAME, JOIN_GBN_CODE, SALARY
FROM INSA
WHERE TO_CHAR(SALARY) LIKE '30%'
AND JOIN_GBN_CODE = 'RGL';
¡á¿¹ 6_2_6 || ÇÔ¼ö»ç¿ë INDEX »ý¼º
CREATE INDEX INSA_SAL_F
ON INSA(TO_CHAR(SALARY));
¡á[SQL 6_2_6] ÇÔ¼ö»ç¿ë INDEX È®ÀÎ SQL
SELECT /*+ INDEX_DESC(INSA INSA_SAL_F) */
SABUN, ENG_NAME, JOIN_GBN_CODE, SALARY
FROM INSA
WHERE SALARY LIKE '30%'
AND JOIN_GBN_CODE='RGL';
¡á¿¹ 6_2_7 || PK(UNIQUE SCAN)°ªÀ¸·Î ã¾Æ °¡±â
SELECT SABUN, ENG_NAME, JOIN_GBN_CODE, SALARY
FROM INSA
WHERE SABUN = '2013022603';
¡á¿¹ 6_2_8 || PK(RANGE SCAN)°ªÀ¸·Î ã¾Æ °¡±â
SELECT SABUN, ENG_NAME, JOIN_GBN_CODE, SALARY
FROM INSA
WHERE SABUN BETWEEN '2013011501' AND '2013022603';
¡á¿¹ 6_2_9 || INDEX¿¡ ÀÖ´Â Ä®·³ °ªÀÇ Á¶È¸
SELECT SABUN
FROM INSA;