메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

Quick tip on Function Based Indexes

명품관 2016.04.19 11:30 조회 수 : 38

Quick tip on Function Based Indexes

 

 

For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes.

SQL> create table blah ( x varchar2(30));

Table created.

SQL> create index blah_ix on blah ( upper(x));

Index created.

SQL> select column_name from user_ind_columns
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_NAME
------------------------------
SYS_NC00002$

 

Don’t panic. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.

SQL> select column_expression from user_ind_expressions
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
UPPER("X")


 

Easy peasy

 

출처 : https://connormcdonald.wordpress.com/2016/04/17/quick-tip-on-function-based-indexes/

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 13
27 Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 267
26 Oracle 12c SQL – Using JSON 명품관 2015.12.29 172
25 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 169
24 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 132
23 Can I apply a BP on top of a PSU? Or vice versa? 명품관 2016.06.01 122
22 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 111
21 How to Recover Data (Without a Backup!) 명품관 2016.05.11 92
20 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 88
19 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 82
18 NOUG Session: How Cache Fusion Works file ecrossoug 2015.11.18 68
17 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 64
16 How many checkpoints in Oracle database ? [1] file 명품관 2015.11.20 63
15 V$EVENT_NAME 뷰의 Name 컬럼에 정의된 event name에서 오는 오해 명품관 2017.03.08 60
14 Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 58
13 How to change the database name in 12c 명품관 2016.05.31 54
12 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 49
11 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 41
» Quick tip on Function Based Indexes 명품관 2016.04.19 38
9 Hybrid Columnar Compression Common Questions 명품관 2016.03.04 38
8 Parallel Execution 12c New Features Overview file 명품관 2015.11.23 31
위로