SQL & PL/SQL

I am confused by this one. For some reason I keep performing a full table scan on a table returning only 1 record.

select a.user_id, a.username, b.organization_id
FROM users a, user_profile b
WHERE a.user_id = b.user_id
AND upper(a.username)=upper('student100210')

Explained.


ID PARENT_ID Query Plan
---------- ---------- -----------------------------------------------------------
0 SELECT STATEMENT (Cost=30330)
1 0 HASH JOIN (SubCost:30330)
2 1 TABLE ACCESS BY INDEX ROWID USERS (SubCost:4299)
3 2 INDEX RANGE SCAN IDX_U_USERNAME_UPPER (SubCost:4)
4 1 TABLE ACCESS FULL USER_PROFILE (SubCost:15776)


I put in an index hint and reran.

Explained.


ID PARENT_ID Query Plan
---------- ---------- -----------------------------------------------------------
0 SELECT STATEMENT (Cost=114248)
1 0 MERGE JOIN (SubCost:114248)
2 1 TABLE ACCESS BY INDEX ROWID USER_PROFILE (SubCost:10944
3 2 INDEX FULL SCAN IDX_UP_USER_ID (SubCost:10767)
4 1 SORT JOIN (SubCost:4803)
5 4 TABLE ACCESS BY INDEX ROWID USERS (SubCost:4299)
6 5 INDEX RANGE SCAN IDX_U_USERNAME_UPPER (SubCost:4)

The issue is that the user_id column on the user_profile table has unique values for every record. The index is non unique because there is the potential for a user with multiple profiles but currently all records are unique. I have updated statistics on both tables and indexes and I have even tried rebuilding the index, once as a unique index, all to no avail.

Anybody have any suggestions? This query should be able to do a rowid lookup based on the user_id found in the index.

Thanks