Home » SQL & PL/SQL » SQL & PL/SQL » Efficient SQL
Efficient SQL [message #332220] Mon, 07 July 2008 23:32 Go to next message
petec
Messages: 11
Registered: July 2008
Junior Member
Hi,

I have a huge production table TBLXXX that I need to check for and count duplicates if the primary key is changed. I've been an sql specialist for a while, but just want to check if this is right (or maybe there's a better way).

If the primary key is Field1, Field2, Field3, is the following sql the most efficient if I want to remove Field1 from the key:

Select Sum(Count(Field3))From TBLXXX Group By Field2, Field3 Having Count(Field3) > 1;
Re: Efficient SQL [message #332226 is a reply to message #332220] Mon, 07 July 2008 23:51 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Define Efficient first??

That depends on many factors.

Regards,
Rajat
Re: Efficient SQL [message #332234 is a reply to message #332226] Tue, 08 July 2008 00:12 Go to previous messageGo to next message
petec
Messages: 11
Registered: July 2008
Junior Member
Thanks for speedy reply.

The query has been running for 5 hours on a test box that has no other activity and has been utilising 99% CPU.

The table has about 4,000,000 rows and I thought this was way too long.
Re: Efficient SQL [message #332241 is a reply to message #332234] Tue, 08 July 2008 00:25 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Have you checked the Explain Plan for your query.

The reason why this query takes time is depends on following things:-

1) You can have Full access of this big table.
2) Indexes are not there.
3) Stats are missing.

Post your explain plan.

Regards,
Rajat
Re: Efficient SQL [message #332249 is a reply to message #332234] Tue, 08 July 2008 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Something went amiss when simplifying your query. At some part you mixed up field1 and field 3.

I can't think of a Business Case for removing a column from a PK..

@rajatratewal:
It is not very likely that there is an index on the second plus third field of a primary key, since access would normally be on either the complete key or the key is configured in an order that at least field1 is always used.

Re: Efficient SQL [message #332250 is a reply to message #332220] Tue, 08 July 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If the primary key is Field1, Field2, Field3, is the following sql the most efficient if I want to remove Field1 from the key:

You can't remove a field from a primary key as it will no more be a primary key.
But you can create a new index on field2 and field3 and it will likely be faster... for this query... assuming you have sufficient number of rows.

Regards
Michel
Re: Efficient SQL [message #332261 is a reply to message #332250] Tue, 08 July 2008 01:06 Go to previous messageGo to next message
petec
Messages: 11
Registered: July 2008
Junior Member
Sorry, I should have explained better.

Field1 of the primary key is a "District Code".
They want to amalgamate different districts to the one district.

The task is to identify and count the records that would cause a primary key constraint if Field1 was the same for all records.

I'll do an explain plan if I can get it going. We are on 8i Sad
Re: Efficient SQL [message #332268 is a reply to message #332261] Tue, 08 July 2008 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The task is to identify and count the records that would cause a primary key constraint if Field1 was the same for all records.

I don't understand this sentence and I don't see the relation with your first query.

Regards
Michel
Re: Efficient SQL [message #332526 is a reply to message #332268] Tue, 08 July 2008 16:45 Go to previous messageGo to next message
petec
Messages: 11
Registered: July 2008
Junior Member
The primary key of table TBLXXX is composed of Field1, Field2, Field3. If Field1 is updated to the same value for all records, then this may cause duplicate primary keys.

The query is to count the possible duplicates if the update was done.
Re: Efficient SQL [message #332527 is a reply to message #332220] Tue, 08 July 2008 16:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL aboveIf

Select Field2, Field3, count(*)
From TBLXXX
Group By Field2, Field3
Having Count(*) > 1;



Re: Efficient SQL [message #332528 is a reply to message #332527] Tue, 08 July 2008 17:20 Go to previous messageGo to next message
petec
Messages: 11
Registered: July 2008
Junior Member
Thanks Ana, but this will return a row for each duplicate.
My original question was "is the following query efficient"?

Select Field2, Field3, sum(count(*))
From TBLXXX
Group By Field2, Field3
Having Count(*) > 1;
Re: Efficient SQL [message #332530 is a reply to message #332527] Tue, 08 July 2008 17:48 Go to previous messageGo to next message
petec
Messages: 11
Registered: July 2008
Junior Member
STOP PRESS - The query is efficient. It took only a minute after they found a rogue process on the box that had stalled my query.

Thanks to everyone's help. I hope I didn't waste anybody's time too much. What a great forum.

As I'm only new, how do I cleanly wrap up a topic to say I'm finished? Do I just just click the [ Unsubscribe from this topic ] link in the email?

Thanks all again. Very much appreciated.



Re: Efficient SQL [message #332545 is a reply to message #332530] Tue, 08 July 2008 23:11 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks for the follow-up.
Actually there is no way to mark a topic [SOLVED] or anything. The unsubcribe is purely for yourself, so you will not get any email when a reply is added.
Previous Topic: difference in records [merged with] OR problem
Next Topic: Oracle Index
Goto Forum:
  


Current Time: Fri Dec 09 03:55:16 CST 2016

Total time taken to generate the page: 0.14910 seconds