11.0.1.2467

I'm coding a web-based application where a lot of selects typically go like this=

select cols from work_table bt 
      inner join this_user_table user1 on user1.col1 = bt.col1
      inner join this_language_table l on l.col = bt.col2
      inner join this_workflow_table w on w.col = bt.col3
      left outer join this_user_table user2 on user2.col2 = bt.col4

I only need one column from each of the joined tables.

Most work_tables have typically less than 5000 rows

• this_user_table: about 10.000 rows
• this_language_table: about 7500 rows
• this_workflow_table < 10 rows

The columns in the join conditions are all primary or foreign keys, so they're indexed.

From what I gather based on incomplete production data, the values in the join conditions tend to repeat themselves quite a bit. For instance, there may be 10.000 users, but only 100 or so satisfy the condition, and about a dozen users cover about 80% of all cases. The same applies to the language table.

I need to generate database views with the selects above for each of my work tables.

In terms of overall performance, what's better in your expert opinion?

Views with joins as above or inlined deterministic functions on the work table to retrieve the column I need from each of the other three tables?