newbie SQL question

I have 2 different queries which are using different methods to perform a join of tables, one of which seems very inefficient:

The good one:

SQL> select handle, password
  2 from users u, profiles p
  3 where u.user_id = p.user_id
  4 and email = 'mizukami_at_NOSPAMfoxproductions.com';

HANDLE PASSWORD

--------------- -------------------------
############      #########

 real: 340

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=86)    1 0 NESTED LOOPS (Cost=6 Card=1 Bytes=86)

   2    1     TABLE ACCESS (BY ROWID) OF 'USERS' (Cost=3 Card=1 By
          tes=64)

   3    2       INDEX (UNIQUE SCAN) OF 'SYS_C00459' (UNIQUE)
   4    1     TABLE ACCESS (BY ROWID) OF 'PROFILES'
   5    4       INDEX (RANGE SCAN) OF 'PROF_USER_ID_INDEX' (NON-UNIQUE
          )

--------------------------------------------------------------------

The bad one:

SQL> select country, count(*) x
  2 from users u, profiles p
  3 where p.user_id = u.user_id
  4 and p.approved = '1'
  5 and p.category LIKE '%1%'
  6 group by country;

COUNTRY                                X
------------------------------ ---------
                                       1
Afghanistan                            6
Algeria                                2
Argentina                              2
Australia                             21


	.

	.

	.

55 rows selected.

 real: 203383

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=570 Card=302 Bytes=2
          6878)

   1    0   SORT (GROUP BY)
   2    1     HASH JOIN (Cost=570 Card=302 Bytes=26878)
   3    2       TABLE ACCESS (FULL) OF 'PROFILES' (Cost=260 Card=3
          02 Bytes=12684)

   4    2       TABLE ACCESS (FULL) OF 'USERS' (Cost=238 Card=3016
          8 Bytes=1417896)

-----------------------------------------------------------------

It looks like the NESTED LOOPS join in the first query has a cost of 6, and the HASH JOIN which is being performed in the 2nd query a cost of 570.

Is there a reason that the HASH JOIN is being chosen for the 2nd query (eg, does the GROUP BY clause cause this?).

Also, does every column in the WHERE clause (approved, category, country) need to be indexed to avoid the full table scans?

Thanks in advance!

Tony Mizukami Received on Sat Nov 22 1997 - 00:00:00 CST