RE: Function - Based Indexes
- From: Muqthar Ahmed <Muqthar.Ahmed@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Mar 2004 14:43:38 -0500
Thanks to Kevin and Mark.
Muqthar
-----Original Message-----
From: Kevin Toepke [mailto:ktoepke@xxxxxxxxxxxxxx]
Sent: Monday, March 01, 2004 1:53 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Function - Based Indexes
Check the dba_ind_expressions table.
-----Original Message-----
From: Muqthar Ahmed [mailto:Muqthar.Ahmed@xxxxxxxxxxxxxxxxx]
Sent: Monday, March 01, 2004 1:40 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxx]
Sent: Monday, March 01, 2004 1:28 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx>
Reply-To: oracle-l@xxxxxxxxxxxxx
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
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.
_________________________________________________________________
Stay informed on Election 2004 and the race to Super Tuesday.
http://special.msn.com/msn/election2004.armx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
* * * * * * * *
*
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.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
* * * * * * * *
*
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.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
