CODEWITHSUNDEEP
sqloracle-database
c12
c12

Reputation: 9827

Oracle DISTINCT Ignoring Primary Key Index Causing Performance Issue

When I include distinct in my query below a full table scan is occurring on GROUP_WIDGET table and my query below is very slow. When I remove distinct it is fast. Any ideas why the performance suffers when using distinct?

Tables:
COMPONENT
ID (primary key)

GROUP
ID (primary key)

WIDGET
ID (primary key)
COMPONENT_ID (foreign key to COMPONENT TABLE)

GROUP_WIDGET (join table between GROUP AND WIDGET)
ID (primary key)
GROUP_ID (foreign key to GROUP table)
WIDGET_ID (foreign key to WIDGET table)
***all foreign keys are indexed

Very Slow (30 seconds):

SELECT DISTINCT GROUP_WIDGET.ID FROM GROUP_WIDGET GW, WIDGET W WHERE W.COMPONENT_ID=12345 AND W.ID=GW.WIDGET_ID

Very Fast (1 second):

SELECT GROUP_WIDGET.ID FROM GROUP_WIDGET GW, WIDGET W WHERE  W.COMPONENT_ID=12345 AND W.ID=GW.WIDGET_ID

Upvotes: 1

Views: 293

Answers (1)

David Faber
David Faber

Reputation: 12485

Rather than this:

SELECT GW.ID
  FROM GROUP_WIDGET GW, WIDGET W
 WHERE W.COMPONENT_ID=12345 AND W.ID=GW.WIDGET_ID

I think you might try using WHERE EXISTS:

SELECT gw.id
  FROM group_widget gw
 WHERE EXISTS ( SELECT 1 FROM widget w
                 WHERE w.id = gw.widget_id
                   AND w.component_id = 12345 )

This way you don't have to use DISTINCT at all.

Upvotes: 1

Related Questions