Forum Stats

  • 3,784,147 Users
  • 2,254,897 Discussions
  • 7,880,711 Comments

Discussions

Categories

Is there any way to compare index by columns and column orders in oracle?

1028309
1028309 Member Posts: 5
edited Jul 26, 2013 3:11AM in SQL & PL/SQL

create table student_A(      

id number not null,

name varchar2(50),

lastname varchar2(50));

                               

create unique index intstd_A on student  (id);

create table student_B(      

id number not null,

name varchar2(50),

lastname varchar2(50));

                               

create unique index intstd_A on student_B (id , lastname );

What would be the query to compare this two?

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Unknown
    Unknown
    edited Jul 25, 2013 10:01PM

    What problem are you trying to solve?


    You can use the ALL_IND_COLS view to get info about TABLE_NAME, COLUMN_NAME, COLUMN_POSITION, etc

    http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1090.htm

    >

    ALL_IND_COLUMNS

    ALL_IND_COLUMNS describes the columns of indexes on all tables accessible to the current user.

    >

    · Share on TwitterShare on Facebook
  • 1028309
    1028309 Member Posts: 5

    I am new in oracle and like to write to query to compare two index columns and columns order. I have no idea have to do that???

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown 
    e27d74c6-5fff-404b-9698-78029354fb67 wrote:

I am new in oracle and like to write to query to compare two index columns and columns order. I have no idea have to do that???

    What problem are you trying to solve? What do you plan to do with the information when you have it?

    And why are you the one trying to solve it if you have no experience in SQL?

    · Share on TwitterShare on Facebook
  • 1028309
    1028309 Member Posts: 5

    I like to run this query and depends on this query result I like to do some programming in my C# application. If I would get the query result like yes or no, it would be enough for me to use it in my C# code.

    · Share on TwitterShare on Facebook
  • 1028309
    1028309 Member Posts: 5

    I used but not worked

    select

       table_name,

       index_name,

       column_name

    from

       dba_ind_columns

    where

       table_owner='XXXX'

    order by

       table_name,

       column_position


    minus


    select

       table_name,

       index_name,

       column_name

    from

       dba_ind_columns

    where

       table_owner='XXXX'

    order by

       table_name,

       column_position


    · Share on TwitterShare on Facebook
  • Unknown
    Unknown

    All you are saying is 'I want to do something based on the result'.

    Well, DOH! It's obvious you want to do 'something'. I've never needed to compare indexes like that in over 25 years of SQL.

    Since it is clear you don't want to tell us WHAT PROBLEM you have and why you need to do this you'll need to wait for someone else to try to help you.

    · Share on TwitterShare on Facebook
  • 1028309
    1028309 Member Posts: 5

    you don't need to shout out "WHAT PROBLEM" please!.  I already know that you voluntarily helping me out.  Obviously I need it for something...But Instead of learning why I need it isn't be nice to help. Thanks anyway.

    · Share on TwitterShare on Facebook
  • Umesh P
    Umesh P Member Posts: 26

    As per your explanation it seems that you want to compare data for two indexes,if all the records for two index matches or not .

    Using simple sql query it seems tedious, your index are not of on identical schema. One index made up of composite keys other is not.

    One scenario of comparison possible is when one out of two columns in composite key index is empty.

    At the same time there could be index with more than two columns as you have shown above.

    Index are nothing but set of columns . So comparison will be possible if all index have similar schema.

    · Share on TwitterShare on Facebook
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown

    Maybe (if column names might be different)

    select *

      from (select t.owner,t.table_name,i.index_name,i.column_name,i.column_position,

                   i.descend,t.data_type,t.data_length,t.data_precision,t.data_scale,t.nullable

              from all_ind_columns i,

                   all_tab_columns t

             where t.table_name = 'STUDENT_A'

               and t.table_name = i.table_name

               and t.owner = i.table_owner

               and t.column_name = i.column_name

           ) a

           full outer join

           (select t.owner,t.table_name,i.index_name,i.column_name,i.column_position,

                   i.descend,t.data_type,t.data_length,t.data_precision,t.data_scale,t.nullable

              from all_ind_columns i,

                   all_tab_columns t

             where t.table_name = 'STUDENT_B'

               and t.table_name = i.table_name

               and t.owner = i.table_owner

               and t.column_name = i.column_name

           ) b

        on a.column_position = b.column_position

       and a.data_type = b.data_type

       and a.data_length = b.data_length

       and lnnvl(a.data_precision != b.data_precision)

       and lnnvl(a.data_scale != b.data_scale)

       and a.nullable = b.nullable

       and a.descend = b.descend

    Regards

    Etbin

    · Share on TwitterShare on Facebook
This discussion has been closed.