메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

Quick tip on Function Based Indexes

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

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 12
27 V$EVENT_NAME 뷰의 Name 컬럼에 정의된 event name에서 오는 오해 명품관 2017.03.08 50
26 Can I apply a BP on top of a PSU? Or vice versa? 명품관 2016.06.01 113
25 How to change the database name in 12c 명품관 2016.05.31 46
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 86
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 77
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 99
21 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 85
» Quick tip on Function Based Indexes 명품관 2016.04.19 35
19 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 99
18 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 71
17 Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 240
16 Hybrid Columnar Compression Common Questions 명품관 2016.03.04 34
15 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 48
14 Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 58
13 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 39
12 (유투브) KISS series on Analytics: Dealing with null - Connor McDonald 명품관 2016.01.05 28
11 Upgrade a Pluggable Database in Oracle 12c 명품관 2015.12.30 11
10 Oracle 12c SQL – Using JSON 명품관 2015.12.29 148
9 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 60
8 Top 5 SQL Monitor Features file 명품관 2015.12.01 17
위로