Oracle : SQL Optimization

Question

I have two tables, TableA and TableB.

TableA is a large dataset with thousands/millions of record and has a unique key (ID) and 3 alternate keys (NAME,SCHOOL,DATE). Table B is a smaller dataset with thousands of record and has no unique keys.

TableA
---------
ID
NAME
SCHOOL
DATE
RECORD
STATUS

TableB
---------
NAME
SCHOOL

Now, I need to join these two tables to get the list of unique IDs to be use in another query. Below is the sample query, but this took so long more hours to run.

SELECT a.ID AS ID_key, a.school, a.name
   FROM TableA a, TableB b
 WHERE a.name = b.name
  AND a.school = b.school
  AND a.status = 'Active'

Is there any way to optimize this query? Thanks in advance.

paxdiablo · Accepted Answer · 2011-10-13 01:00:47Z

1

Why are you joining the tables at all if all the information required is in table A?

The only possible reason I can think of is if you want to exclude rows in A that don't have a corresponding row in B but, if your schema is set up properly, you would probably have a foreign key constraint to enforce that.

If that's not the case, get rid of the join altogether:

SELECT a.ID AS ID_key,
       a.school,
       a.name
FROM   TableA
WHERE  status = 'Active'

If there is a valid reason for the join (and, from your comment, that seems to be the case), make sure that you have indexes on name and school in both tables, and that your runstats (or whatever Oracle calls its query optimisation data) are up to date.

Also run a query analysis (explain) on that query to find out where the problem lies. Once you have that as a baseline, you can begin modifying things (like adding indexes) to see if the query improves. Remember the #1 mantra for optimisation: measure, don't guess!

edited Oct 13, 2011 at 1:00

answered Oct 12, 2011 at 9:28

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ianthe Over a year ago

TableB is an input table or the info need to search from TableA

ypercubeᵀᴹ Over a year ago

@paxdiablo: Don't you mean a compound index on (name, school) for the join?

paxdiablo Over a year ago

@ypercube, possibly. It would depend on other queries as well. If, as seems likely, table B is used only for white-list selection, the composite index would be better. You'd still have to measure to be certain.

schurik · Accepted Answer · 2011-10-12 09:41:06Z

1

is there any indexes defined?

you can try this:

 SELECT a.ID AS ID_key, a.school, a.name
   FROM TableA a
 WHERE (a.name, a.school ) in (select b.name, b.school from tableB b)
  AND a.status = 'Active'

answered Oct 12, 2011 at 9:41

schurik

7,9582 gold badges26 silver badges30 bronze badges

Comments

sikrip · Accepted Answer · 2011-10-12 09:44:40Z

0

If it is not a problem for you(privileges etc), add an index on TableB.name and TableB.school columns.

edited Oct 12, 2011 at 9:44

answered Oct 12, 2011 at 9:39

sikrip

6818 silver badges10 bronze badges

Collectives™ on Stack Overflow

Oracle : SQL Optimization

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related