Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how would i compare if two indexes are identical?

Erhan_torontoJul 24 2013 — edited Jul 31 2013

I like to compare if two indexes from both users are identical. Is there any way to to that. I need similer to below query:

select CASE (2*(select count(*) from (select * from XPKTBL_A INTERSECT select * from XPKTBL_A )) - (select count(*) from XPKTBL_A ) - (select count(*) from XPKTBL_A )) WHEN 0 THEN 'Indexes are identical' ELSE 'Indexes are different' END "Result" from  DUAL;

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);

This post has been answered by Erhan_toronto on Jul 30 2013
Jump to Answer

Comments

Sdhamoth-Oracle
Answer

OCIServerVersion() will help you to get the server version and banner.

Marked as Answer by user11763611 · Sep 27 2020
user11763611

Thanks for your quick answer!

I see there is also an OCIServerRelease() function...

Can someone explain what is done by these functions?

If internally OCI executes SQL to get the information, it will not help much.

Seb

Sdhamoth-Oracle

It won't execute that SQL.

user11763611

Understand: I am looking for the fastest solution to get the server version and session id...

If it takes a client-server round-trip it's a pity... we are already connected...

Linux strace shows some activity on the socket to the server so I suspect OCI asks the server:

write(2, ">> MARK-1\n", 10>> MARK-1

)             = 10

write(6, "\0\0\0<\6\0\0\0\0\0\21k\4\"\0\0\0\201\275\0\0\1\0\0\0\3;\5\376\377\377\377"..., 60) = 60

read(6, "\0\0\0\272\6\0\0\0\0\0\10\241\0\241Oracle Database 12"..., 8208) = 186

write(2, ">> MARK-2\n", 10>> MARK-2

:-(

Sdhamoth-Oracle

OCIServerRelease() may not make a server round trip. Please try that.

user11763611

I have already checked: The trace output I provided was produced from OCIServerRelease(), which makes a server round trip.

It is still an option for us, if it takes less time than a SELECT to execute.

Seb

user11763611

After some testing (executing 100 processes in sequence, using client/server config over TCP):

1) Regular OCIStmtPrepare + OCIStmtExecute + OCIDefineByPos +  OCIStmtFetch of SELECT to get Oracle version: 3.7 secs

2) Using OCIServerRelease(): 3.3 secs.

A little better but if all information would be available on client side after connection that would certainly be faster.

Using OCIServerRelease() simplifies the code, thought.

Seb

Sdhamoth-Oracle

Have you tried fetching the details about database using OCIDescribeAny() with type OCI_PTYPE_DATABASE?

DB Version will can be read using OCIAttrGet() API from attribute OCI_ATTR_VERSION.

user11763611

Nope, will try OCIDescribeAny(), thanks for the tip!

Seb

1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 28 2013
Added on Jul 24 2013
25 comments
1,894 views