Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 389 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
How to compare index names and columns from different user?

I am using below query to compare two indexes from 2 different users but even though index name and columns are same... result shows me they are different.. what I am doing wrong? Thanks
WITH t AS
(SELECT COUNT (DISTINCT index_owner || index_name || indexed_cols)
cnt
FROM ( SELECT index_owner,
index_name,
listagg (column_name, ',')
WITHIN GROUP (ORDER BY column_position)
indexed_cols
FROM dba_ind_columns
WHERE index_name='XPKTBL_A'
GROUP BY index_owner, index_name))
SELECT CASE
WHEN cnt > 1 THEN 'Indexes are different'
WHEN cnt = 0 THEN 'Indexes dont exist'
WHEN cnt > 1 THEN 'Indexes are identical'
END
commnt
FROM t
Result:
Indexes are different
but Actually if you check below they are same After when I run this query:
SELECT index_owner,
index_name,
listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position)
indexed_cols
FROM dba_ind_columns
WHERE index_name='XPKTBL_A'
GROUP BY index_owner, index_name;
Result:
Index_owner | Index_name | Index_cols |
USER1 | XPKTBL_A | FIELD_A1 |
USER2 | XPKTBL_A | FIELD_A1 |
Answers
-
Hi,
Erhan_toronto wrote: I am using below query to compare two indexes from 2 different users but even though index name and columns are same... result shows me they are different.. what I am doing wrong? Thanks WITH t AS (SELECT COUNT (DISTINCT index_owner || index_name || indexed_cols) ...
So index_owner is 'USER1' in one case, and 'USER2' in the other; right?
A string that starts with 'USER1' will be distinct from a string that starts with 'USER2', no matter what the rest of the string contains. Maybe you don't want to compare the owners, or maybe you meant to use some other column (such as table_name) instead of index_owner).
.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE, CREATE INDEX and CONNECT statements), and also post the results you want from that data.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using (e.g., 11.2.0.2.0).See the forum FAQ: https://forums.oracle.com/message/9362002
-
You might also want to check your case condition as well. I think that you need cnt = 1 THEN 'Indexes are identical' in the last case.
John
-
Thanks for your reply Frank,
Hope this help,
I am using 11gR2...
UserA:
create table TBL_A ( FIELD_A1 number not null,
FIELD_A2 varchar2(50),
FIELD_A3 date,
FIELD_A4 number(5,2) default 0,
FIELD_A5 varchar2(10) not null
);
create unique index XPKTBL_A on TBL_A (FIELD_A1);
UserB:
create table TBL_A ( FIELD_A1 number not null,
FIELD_A2 varchar2(20),
FIELD_A4 number(5,2) not null,
FIELD_A5 varchar2(10),
FIELD_A6 number(2) not null
);
create unique index XPKTBL_A on TBL_A (FIELD_A1, FIELD_A6);
Like to see the result if same index name also has same columns... if yes than index is identical else they are different...
-
I did notice that when I remove index_owner from my query it shows as index are identical even though they are not!
-
DUPLICATE THREAD!
This is the THIRD thread that asks the same question. Please don't duplicate threads. People can't follow what is happening.
https://forums.oracle.com/thread/2563449?start=15&tstart=0
https://forums.oracle.com/thread/2563840
Mark two of these threads ANSWERED and use the remaining thread for your question.