Have Geek Brain

Select individual ... inner join versus select ... where id in (...)

I am trying to create a subset of a table (as a materialized view) defined as those records that have a corresponding record in a different materialized view.

For example, let's say I have a Users table with user_id and name columns, and a log table with entry_id, user_id, activity, and timestamp columns.

First I create a materialized view of the log table by selecting only those rows with a timestamp> some_date. Now I want to look at Materliazed for the users mentioned in my snapshot of the Log table. I can create it like

select * from Users where user_id in (select user_id from Log_mview)

or i can do

select distinct u.* from Users u inner join Log_mview l on u.user_id = l.user_id

(needed to avoid multiple hits from users with multiple log entries).

The former seems cleaner and more elegant, but takes much longer. Am I missing something? Is there a better way to do this?

Edit: The suggestion where exists

helped a lot, except in the case where the condition uses OR

. For example, let's say the log table above also had a user_name column, and the correct way to map a log entry to a "Users" entry is when one of the columns (user id or username) matches. I find that

select distinct u.* from Users u
    inner join Log_mview l
        on u.user_id = l.user_id or u.name = l.user_name

much faster than

select * from Users u where exists
    (select id from Log_mview l 
        where l.user_id = u.user_id or l.user_name = u.name)

Any help?

(As for the outline of the explanation ... Lemme is working on disinfecting them, or rather ... I'll post them in a while.)

Edit: explain plans: For an inner join query:

Plan hash value: 436698422

-------------------------------------------------- -------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
-------------------------------------------------- -------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 4539K | 606M | | 637K (3) | 02:07:25 |
| 1 | HASH UNIQUE | | 4539K | 606M | 3201M | 637K (3) | 02:07:25 |
| 2 | CONCATENATION | | | | | | |
| * 3 | HASH JOIN | | 4206K | 561M | 33M | 181K (4) | 00:36:14 |
| 4 | BITMAP CONVERSION TO ROWIDS | | 926K | 22M | | 2279 (1) | 00:00:28 |
| 5 | BITMAP INDEX FAST FULL SCAN | I_M_LOG_MVIEW_4 | | | | | |
| * 6 | TABLE ACCESS FULL | USERS | 15M | 1630M | | 86638 (6) | 00:17:20 |
| * 7 | HASH JOIN | | 7646K | 1020M | 33M | 231K (4) | 00:46:13 |
| 8 | BITMAP CONVERSION TO ROWIDS | | 926K | 22M | | 2279 (1) | 00:00:28 |
| 9 | BITMAP INDEX FAST FULL SCAN | I_M_LOG_MVIEW_4 | | | | | |
| 10 | TABLE ACCESS FULL | USERS | 23M | 2515M | | 87546 (7) | 00:17:31 |
-------------------------------------------------- -------------------------------------------------- -----------

Predicate Information (identified by operation id):
-------------------------------------------------- -

   3 - access ("U". "NAME" = "L". "USER_NAME")
   6 - filter ("U". "NAME" IS NOT NULL)
   7 - access ("U". "USER_ID" = TO_NUMBER ("L". "USER_ID"))
       filter (LNNVL ("U". "NAME" = "L". "USER_NAME") OR LNNVL ("U". "NAME" IS NOT NULL))

Note
-----
   - dynamic sampling used for this statement

For those who use where exists

:

Plan hash value: 2786958565

-------------------------------------------------- -------------------------------------------------- -
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- -------------------------------------------------- -
| 0 | SELECT STATEMENT | | 1 | 114 | 21M (1) | 70:12:13 |
| * 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | USERS | 23M | 2515M | 87681 (7) | 00:17:33 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 7062 | 179K | 1 (0) | 00:00:01 |
| * 4 | BITMAP INDEX FAST FULL SCAN | I_M_LOG_MVIEW_4 | | | | |
-------------------------------------------------- -------------------------------------------------- -

Predicate Information (identified by operation id):
-------------------------------------------------- -

   1 - filter (EXISTS (SELECT / * + * / 0 FROM "MYSCHEMA". "LOG_MVIEW" 
              "LOG_MVIEW" WHERE ("USER_NAME" =: B1 OR TO_NUMBER ("USER_ID") =: B2) AND 
              ("USER_NAME" =: B3 OR TO_NUMBER ("USER_ID") =: B4) AND ("USER_NAME" =: B5 OR 
              TO_NUMBER ("USER_ID") =: B6)))
   4 - filter ("USER_NAME" =: B1 OR TO_NUMBER ("USER_ID") =: B2)

Note
-----
   - dynamic sampling used for this statement

DB object names have been changed to protect the innocent .: P

+2


a source to share


3 answers


try it

select * from Users u
where exists 
   ( select user_id 
     from Log_mview l
     where l.user_id = u.user_id )
/


If the subquery returns a large number of rows, it WHERE EXISTS

can be significantly faster than WHERE ... IN

.

+1


a source


It will depend on your data, but using Distinct

inside a connection can improve your performance:



Select u.*
From Users u
Join ( Select Distinct user_id
       From log_mview ) l On u.user_id = l.user_id
+1


a source


The second query probably works more hard disk than the first query (join + distinc).

The first request will probably translate to something like:

for each row in the Log table, find the corresponding row in the User table (in memory). The database is probably smart enough to create User tables in memory structures, which are probably much smaller than the log table. I believe the query one (join + distinct) will only require one pass in the Log table. The differences are likely to be done in memory.

The second query is probably causing the database to do multiple filled spaces in the Log table.

So in your second request, you probably get:

For each row in the table, the user reads all the rows in the Log table (from disk) to match the condition.

You should also take into account that some queries can have drastic differences in speed due to changes in memory availability, table loading and growing.

+1


a source






More articles:


All Articles