| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Function - Based Indexes
Try DBA_IND_EXPRESSIONS.
-----Original Message-----
From: Muqthar Ahmed [mailto:Muqthar.Ahmed_at_decoratetoday.com]
Sent: Monday, March 01, 2004 1:40 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Function - Based Indexes
Rafiq,
May be I did not explain properly. If I use the following query, I will get index names and column names:
select index_name, column_name, column_position
from all_ind_columns
where table_name = upper('&tab')
and index_owner = upper('&owner')
order by index_name, column_position
INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------------------- --------------- TBLORD_IDX1 OOOID 1 TBLORD_IDX1 SYS_NC00081$ 2 TBLORD_IDX1 OOODERNO 3 TBLORD_IDX2 OOOID 1 TBLORD_IDX2 OOOTOTAL 2 TBLORD_IDX2 OOODATE 3 TBLORD_IDX3 OOOID 1 TBLORD_IDX3 OOOUBMITDATE 2 TBLORD_IDX4 SYS_NC00064$ 1 TBLORD_IDX5 SYS_NC00066$ 1
But it will not display function-based information. In the above example, it is showing only:
SYS_NC00081$ SYS_NC00064$ SYS_NC00066$
Thanks
Muqthar
-----Original Message-----
From: M Rafiq [mailto:rafiq9857_at_hotmail.com]
Sent: Monday, March 01, 2004 1:28 PM
To: oracle-l_at_freelists.org
Subject: RE: Function - Based Indexes
Run this script
SELECT index_name, table_name, status FROM dba_indexes
WHERE FUNCIDX_STATUS is not null
/
Regards
Rafiq
From: Muqthar Ahmed <Muqthar.Ahmed_at_decoratetoday.com>
Reply-To: oracle-l_at_freelists.org
To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org>
Subject: Function - Based Indexes
Date: Mon, 1 Mar 2004 13:05:45 -0500
Hi,
If an Index is created on a column using Function then how do you find out this information using Data Dictionary.
For example:
create index tab1_idx1 on tab1(trim(lower(billaddress))) tablespace users;
Usually I export table without DATA to see Create Index syntax.
Please let me know if I can use any Data Dictionary.
Thanks
Muqthar Ahmed
The information contained in this E-mail message is privileged,
confidential, and may be protected from disclosure; please be aware that any
other use, printing,copying,
disclosure or dissemination of this communication may be subject to legal
restriction or sanction. If you think that you have received this E-mail
message in error, please
reply to the sender and delete it from your computer. Thank you.
The information contained in this E-mail message is privileged, confidential, and may be protected from disclosure; please be aware that any other use, printing,copying, disclosure or dissemination of this communication may be subject to legal restriction or sanction. If you think that you have received this E-mail message in error, please reply to the sender and delete it from your computer. Thank you.
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |