{"id":2649,"date":"2018-02-02T18:46:28","date_gmt":"2018-02-02T09:46:28","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2649"},"modified":"2018-02-02T18:59:38","modified_gmt":"2018-02-02T09:59:38","slug":"invisible-index","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2649","title":{"rendered":"Invisible Index"},"content":{"rendered":"
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
1. Invisible Index\ub780<\/p>\n

Invisible Index\uae30\ub2a5\uc740 Optimizer\uac00 Execution Plan\uc744 \uc0dd\uc131 \uc2dc\uc5d0 Invisible Index\uc778 Index\ub4e4\uc744 \ubb34\uc2dc \ud558\uac8c \ub418\ub294 \uae30\ub2a5\uc785\ub2c8\ub2e4.
\nSession\uc774\ub098 System\ubcc4\ub85c OPTIMIZER_USE_INVISIBLE_INDEXES Parameter\ub97c True\ub85c \uc124\uc815\ud558\uac8c \ub418\uba74 Optimizer\uac00 Invisible Index\ub77c\uace0 \ud560\uc9c0\ub77c\ub3c4 \ubb34\uc2dc\ud558\uc9c0 \uc54a\uace0 Execution Plan\uc744 \uc791\uc131\ud558\uac8c \ub429\ub2c8\ub2e4.
\nUnusable Index\uc640\ub294 \ub2e4\ub974\uac8c DML \uc791\uc5c5\uc744 \ud558\uba74 Invisible Index\ub4e4\uc740 \uacc4\uc18d \uc720\uc9c0\uac00 \ub429\ub2c8\ub2e4.
\nInvisible Index\uc758 \uae30\ub2a5\uc744 \uc774\uc6a9\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uc7a5\uc810\uc744 \uc774\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

1) Index\ub97c Drop\ud558\uae30 \uc804\uc5d0 Execution Plan\uc758 \ubcc0\ud654\ub97c \ubbf8\ub9ac Test\ud574 \ubcf4\uc2e4 \uc218 \uc788\uc2b5\ub2c8\ub2e4.
\n2) \uc804\uccb4 Application\uc758 \uc601\ud5a5\uc744 \uc8fc\uc9c0 \uc54a\uace0 \ud2b9\uc815 Application\uc5d0\uc11c\ub9cc Temporary\ud558\uac8c Index\ub97c \uc0ac\uc6a9\ud558\uac8c \ud558\uc2e4 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

2. Syntax<\/p>\n

1) Index\ub97c Invisible\ud558\uac8c Create<\/p>\n

CREATE INDEX emp_deptno ON emp(deptno)
\nTABLESPACE users
\nSTORAGE (INITIAL 20K
\nNEXT 20k
\nPCTINCREASE 75)
\nINVISIBLE; < ———————— Invisible Option\uc744 \uc0ac\uc6a9\ud558\uc5ec Create \ud55c\ub2e4.<\/p>\n

2) Index\ub97c Invisible \ud639\uc740 Visible\ud558\uac8c \ub9cc\ub4e4\uae30<\/p>\n

ALTER INDEX index_name INVISIBLE;
\nALTER INDEX index_name VISIBLE;<\/p>\n

3. Test<\/p>\n

1) Invisible Index \uc0dd\uc131 \ud6c4\uc5d0 \ud655\uc778<\/p>\n

SQL> CREATE INDEX emp_deptno ON emp(deptno)
\nTABLESPACE users
\nSTORAGE (INITIAL 20K
\nNEXT 20k
\nPCTINCREASE 75)
\nINVISIBLE;<\/p>\n

Index created.<\/p>\n

SQL> select index_name, visibility from user_indexes;<\/p>\n

INDEX_NAME VISIBILIT
\n—————————— ———
\nPK_EMP VISIBLE
\nEMP_DEPTNO INVISIBLE
\nPK_DEPT VISIBLE<\/p>\n

2) PK_EMP\ub97c Invisible\ub85c \ubc14\uafbc \ud6c4\uc758 \ubcc0\ud654<\/p>\n

SQL> ALTER INDEX PK_EMP INVISIBLE;<\/p>\n

Index altered.<\/p>\n

SQL> set autotrace on
\nSQL> select * from scott.emp where empno=7369;<\/p>\n

EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
\n———- ——— ———- ——— ———- ———- ———-
\n7369 CLERK 7902 17-DEC-80 800 20<\/p>\n

Execution Plan
\n———————————————————-
\nPlan hash value: 3956160932<\/p>\n

————————————————————————–
\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
\n————————————————————————–
\n| 0 | SELECT STATEMENT | | 1 | 33 | 3 (0)| 00:00:01 |
\n|* 1 | TABLE ACCESS FULL| EMP | 1 | 33 | 3 (0)| 00:00:01 |
\n————————————————————————–<\/p>\n

3) PK_EMP\ub97c \ub2e4\uc2dc Visible\ub85c \ubc14\uafbc \ud6c4\uc758 \ubcc0\ud654<\/p>\n

SQL> ALTER INDEX PK_EMP VISIBLE;<\/p>\n

Index altered.<\/p>\n

SQL> set autot on
\nSQL> select * from scott.emp where empno=7369;<\/p>\n

EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
\n———- ——— ———- ——— ———- ———- ———-
\n7369 CLERK 7902 17-DEC-80 800 20<\/p>\n

Execution Plan
\n———————————————————-
\nPlan hash value: 2949544139
\n————————————————————————————–
\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
\n————————————————————————————–
\n| 0 | SELECT STATEMENT | | 1 | 33 | 1 (0)| 00:00:01 |
\n| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 1 (0)| 00:00:01 |
\n|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
\n————————————————————————————–<\/p>\n

4) \uc804\uccb4 Application\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce58\uc9c0 \uc54a\uace0 \ud2b9\uc815 Query\uc5d0\uc11c\ub9cc Invisible Index \uc0ac\uc6a9\ud558\uae30<\/p>\n

– Session A<\/p>\n

* Invisible Index\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d OPTIMIZER_USE_INVISIBLE_INDEXES Parameter\ub97c True\ub85c \uc124\uc815\ud569\ub2c8\ub2e4.<\/p>\n

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES = true;<\/p>\n

Session altered.<\/p>\n

* Invisible Index\uac00 \uc788\ub294\uc9c0 \ud655\uc778 \ud569\ub2c8\ub2e4.<\/p>\n

SQL> select index_name, visibility from user_indexes;<\/p>\n

INDEX_NAME VISIBILIT
\n—————————— ———
\nPK_EMP VISIBLE
\nEMP_DEPTNO INVISIBLE
\nPK_DEPT VISIBLE<\/p>\n

* Index\ub97c \uc0ac\uc6a9\ud558\ub294 \ubd80\ubd84\uc744 \ud655\uc778 \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

SQL> set autot on
\nSQL> select * from scott.emp where deptno=20;<\/p>\n

EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
\n———- ——— ———- ——— ———- ———- ———-
\n7369 CLERK 7902 17-DEC-80 800 20
\n7566 MANAGER 7839 02-APR-81 2975 20
\n7788 ANALYST 7566 19-APR-87 3000 20
\n7876 CLERK 7788 23-MAY-87 1100 20
\n7902 ANALYST 7566 03-DEC-81 3000 20<\/p>\n

Execution Plan
\n———————————————————-
\nPlan hash value: 1182541070
\n——————————————————————————————
\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
\n——————————————————————————————
\n| 0 | SELECT STATEMENT | | 5 | 165 | 2 (0)|00:00:01 |
\n| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 165 | 2 (0)|00:00:01 |
\n|* 2 | INDEX RANGE SCAN | EMP_DEPTNO | 5 | | 1 (0)|00:00:01 |
\n——————————————————————————————<\/p>\n

– Session B<\/p>\n

SQL> connect \/ as sysdba
\nConnected.<\/p>\n

* OPTIMIZER_USE_INVISIBLE_INDEXES\uac00 Default\ub85c False\uc784\uc744 \ud655\uc778 \ud569\ub2c8\ub2e4.<\/p>\n

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES<\/p>\n

NAME TYPE VALUE
\n———————————— ———– ——————————
\noptimizer_use_invisible_indexes boolean FALSE<\/p>\n

SQL> connect scott\/tiger
\nConnected.
\nSQL> set autot on<\/p>\n

* OPTIMIZER_USE_INVISIBLE_INDEXES\uac00 False\uc77c \ub54c Invisible Index\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uc74c\uc744 \ud655\uc778 \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

SQL> select * from scott.emp where deptno=20;<\/p>\n

EMPNO JOB MGR HIREDATE SAL COMM DEPTNO
\n———- ——— ———- ——— ———- ———- ———-
\n7369 CLERK 7902 17-DEC-80 800 20
\n7566 MANAGER 7839 02-APR-81 2975 20
\n7788 ANALYST 7566 19-APR-87 3000 20
\n7876 CLERK 7788 23-MAY-87 1100 20
\n7902 ANALYST 7566 03-DEC-81 3000 20<\/p>\n

Execution Plan
\n———————————————————-
\nPlan hash value: 3956160932<\/p>\n

————————————————————————–
\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
\n————————————————————————–
\n| 0 | SELECT STATEMENT | | 5 | 165 | 3 (0)| 00:00:01 |
\n|* 1 | TABLE ACCESS FULL| EMP | 5 | 165 | 3 (0)| 00:00:01 |
\n————————————————————————–<\/p><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

1. Invisible Index\ub780 Invisible Index\uae30\ub2a5\uc740 Optimizer\uac00 Execution Plan\uc744 \uc0dd\uc131 \uc2dc\uc5d0 Invisible Index\uc778 Index\ub4e4\uc744 \ubb34\uc2dc \ud558\uac8c \ub418\ub294 \uae30\ub2a5\uc785\ub2c8\ub2e4. Session\uc774\ub098 System\ubcc4\ub85c OPTIMIZER_USE_INVISIBLE_INDEXES Parameter\ub97c True\ub85c \uc124\uc815\ud558\uac8c \ub418\uba74 Optimizer\uac00 Invisible Index\ub77c\uace0 \ud560\uc9c0\ub77c\ub3c4 \ubb34\uc2dc\ud558\uc9c0 \uc54a\uace0 Execution Plan\uc744 \uc791\uc131\ud558\uac8c \ub429\ub2c8\ub2e4. Unusable Index\uc640\ub294 \ub2e4\ub974\uac8c DML \uc791\uc5c5\uc744 \ud558\uba74 Invisible Index\ub4e4\uc740 \uacc4\uc18d \uc720\uc9c0\uac00 \ub429\ub2c8\ub2e4. Invisible Index\uc758 \uae30\ub2a5\uc744 \uc774\uc6a9\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uc7a5\uc810\uc744 \uc774\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. […]<\/p>\n","protected":false},"author":1,"featured_media":2660,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[860,859,861],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/oracle-invisible-index-11g.jpg?fit=210%2C196","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2649"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2649"}],"version-history":[{"count":3,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2649\/revisions"}],"predecessor-version":[{"id":2661,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2649\/revisions\/2661"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2660"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2649"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}