Re: tune between query/join

A short example with 8000 records in account and 150 records in tmp_account:
 
SQL> 
SQL> create table account(
  2        account_number number not null,
  3        id      varchar2(10) not null,
  4        lowest_amount number(22,4),
  5        highest_amount number(22,4)
  6  );
 
Table created.
 
SQL> 
SQL> create table tmp_account(
  2        account_number number not null,
  3        amount  number(22,4),
  4        descr   varchar2(40)
  5  );
 
Table created.
 
SQL> 
SQL> begin
  2        for i in 1..8000 loop
  3         insert into account
  4         (account_number, id, lowest_amount, highest_amount)
  5         values
  6         (i, 'Ex '||i, mod(i, 337), mod(i, 93));
  7         if i <= 150 then
  8          insert into tmp_account
  9          (account_number, amount, descr)
 10          values
 11          (i, mod(i, 43), 'Example record '||i);
 12         end if;
 13        end  loop;
 14  
 15        commit;
 16  
 17  end;
 18  /
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> set autotrace on
SQL> 
SQL> select a.account_number, a.id, b.*
  2    from account a, tmp_account b
  3  where b.amount between a.lowest_amount and a.highest_amount;
 
ACCOUNT_NUMBER ID         ACCOUNT_NUMBER     AMOUNT DESCR
-------------- ---------- -------------- ---------- 
----------------------------------------
          6782 Ex 6782                42         42 Example record 42
          1390 Ex 1390                42         42 Example record 42
          7793 Ex 7793                42         42 Example record 42
          7119 Ex 7119                42         42 Example record 42
          5097 Ex 5097                42         42 Example record 42
          4423 Ex 4423                42         42 Example record 42
          6108 Ex 6108                42         42 Example record 42
          4086 Ex 4086                42         42 Example record 42
          2401 Ex 2401                42         42 Example record 42
          1727 Ex 1727                42         42 Example record 42
          3412 Ex 3412                42         42 Example record 42
           716 Ex 716                 42         42 Example record 42
            42 Ex 42                  42         42 Example record 42
          6781 Ex 6781                42         42 Example record 42
          1389 Ex 1389                42         42 Example record 42
          7792 Ex 7792                42         42 Example record 42
          7118 Ex 7118                42         42 Example record 42
          5096 Ex 5096                42         42 Example record 42
...
          1685 Ex 1685                86          0 Example record 86
          2022 Ex 2022                86          0 Example record 86
          2359 Ex 2359                86          0 Example record 86
          4044 Ex 4044                86          0 Example record 86
          5392 Ex 5392                86          0 Example record 86
          6066 Ex 6066                86          0 Example record 86
          5729 Ex 5729                86          0 Example record 86
          4381 Ex 4381                86          0 Example record 86
          4718 Ex 4718                86          0 Example record 86
          5055 Ex 5055                86          0 Example record 86
          7414 Ex 7414                86          0 Example record 86
          7751 Ex 7751                86          0 Example record 86
          6403 Ex 6403                86          0 Example record 86
          6740 Ex 6740                86          0 Example record 86
           337 Ex 337                 86          0 Example record 86
 
52108 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3631114592
--------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost 
(%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  3000 |   275K|       |   110   
(4)| 00:00:02 |
|   1 |  MERGE JOIN          |             |  3000 |   275K|       |   110   
(4)| 00:00:02 |
|   2 |   SORT JOIN          |             |   150 |  7200 |       |     4  
(25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | TMP_ACCOUNT |   150 |  7200 |       |     3   
(0)| 00:00:01 |
|*  4 |   FILTER             |             |       |       |       |            
|          |
|*  5 |    SORT JOIN         |             |  8000 |   359K|   952K|   105   
(2)| 00:00:02 |
|   6 |     TABLE ACCESS FULL| ACCOUNT     |  8000 |   359K|       |     9   
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("B"."AMOUNT"<="A"."HIGHEST_AMOUNT")
   5 - 
access(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST_AMOUNT"
              ))
       
filter(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST_AMOUNT"
              ))
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
        152  recursive calls
          0  db block gets
         92  consistent gets
          0  physical reads
          0  redo size
    1324733  bytes sent via SQL*Net to client
      38588  bytes received via SQL*Net from client
       3475  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      52108  rows processed
 
SQL> 
SQL> select a.account_number, a.id, b.*
  2    from account a, tmp_account b
  3  where b.account_number = a.account_number
  4  and b.amount between a.lowest_amount and a.highest_amount;
 
ACCOUNT_NUMBER ID         ACCOUNT_NUMBER     AMOUNT DESCR
-------------- ---------- -------------- ---------- 
----------------------------------------
             1 Ex 1                    1          1 Example record 1
             2 Ex 2                    2          2 Example record 2
             3 Ex 3                    3          3 Example record 3
             4 Ex 4                    4          4 Example record 4
             5 Ex 5                    5          5 Example record 5
             6 Ex 6                    6          6 Example record 6
             7 Ex 7                    7          7 Example record 7
             8 Ex 8                    8          8 Example record 8
             9 Ex 9                    9          9 Example record 9
            10 Ex 10                  10         10 Example record 10
            11 Ex 11                  11         11 Example record 11
            12 Ex 12                  12         12 Example record 12
            13 Ex 13                  13         13 Example record 13
            14 Ex 14                  14         14 Example record 14
            15 Ex 15                  15         15 Example record 15
            16 Ex 16                  16         16 Example record 16
            17 Ex 17                  17         17 Example record 17
            18 Ex 18                  18         18 Example record 18
            19 Ex 19                  19         19 Example record 19
            20 Ex 20                  20         20 Example record 20
            21 Ex 21                  21         21 Example record 21
            22 Ex 22                  22         22 Example record 22
            23 Ex 23                  23         23 Example record 23
            24 Ex 24                  24         24 Example record 24
            25 Ex 25                  25         25 Example record 25
            26 Ex 26                  26         26 Example record 26
            27 Ex 27                  27         27 Example record 27
            28 Ex 28                  28         28 Example record 28
            29 Ex 29                  29         29 Example record 29
            30 Ex 30                  30         30 Example record 30
            31 Ex 31                  31         31 Example record 31
            32 Ex 32                  32         32 Example record 32
            33 Ex 33                  33         33 Example record 33
            34 Ex 34                  34         34 Example record 34
            35 Ex 35                  35         35 Example record 35
            36 Ex 36                  36         36 Example record 36
            37 Ex 37                  37         37 Example record 37
            38 Ex 38                  38         38 Example record 38
            39 Ex 39                  39         39 Example record 39
            40 Ex 40                  40         40 Example record 40
            41 Ex 41                  41         41 Example record 41
            42 Ex 42                  42         42 Example record 42
 
42 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1664268811
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| 
Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    94 |    13   (8)| 
00:00:01 |
|*  1 |  HASH JOIN         |             |     1 |    94 |    13   (8)| 
00:00:01 |
|   2 |   TABLE ACCESS FULL| TMP_ACCOUNT |   150 |  7200 |     3   (0)| 
00:00:01 |
|   3 |   TABLE ACCESS FULL| ACCOUNT     |  8000 |   359K|     9   (0)| 
00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ACCOUNT_NUMBER"="A"."ACCOUNT_NUMBER")
       filter("B"."AMOUNT">="A"."LOWEST_AMOUNT" AND
              "B"."AMOUNT"<="A"."HIGHEST_AMOUNT")
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         81  consistent gets
          0  physical reads
          0  redo size
       2401  bytes sent via SQL*Net to client
        407  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         42  rows processed
SQL> 
SQL> set autotrace off
SQL> 
SQL> create index account_amt_idx
  2  on account(lowest_amount, highest_amount);
 
Index created.
 
SQL> 
SQL> exec dbms_stats.gather_schema_stats('BING');
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> set autotrace on
SQL> 
SQL> select a.account_number, a.id, b.*
  2    from account a, tmp_account b
  3  where b.amount between a.lowest_amount and a.highest_amount;
 
ACCOUNT_NUMBER ID         ACCOUNT_NUMBER     AMOUNT DESCR
-------------- ---------- -------------- ---------- 
----------------------------------------
          6782 Ex 6782                42         42 Example record 42
          1390 Ex 1390                42         42 Example record 42
          7793 Ex 7793                42         42 Example record 42
          7119 Ex 7119                42         42 Example record 42
          5097 Ex 5097                42         42 Example record 42
          4423 Ex 4423                42         42 Example record 42
          6108 Ex 6108                42         42 Example record 42
          4086 Ex 4086                42         42 Example record 42
          2401 Ex 2401                42         42 Example record 42
          1727 Ex 1727                42         42 Example record 42
          3412 Ex 3412                42         42 Example record 42
           716 Ex 716                 42         42 Example record 42
            42 Ex 42                  42         42 Example record 42
          6781 Ex 6781                42         42 Example record 42
...
          7751 Ex 7751                86          0 Example record 86
          6403 Ex 6403                86          0 Example record 86
          6740 Ex 6740                86          0 Example record 86
           337 Ex 337                 86          0 Example record 86
 
52108 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3631114592
------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| 
Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  3000 |   123K|    16  (25)| 
00:00:01 |
|   1 |  MERGE JOIN          |             |  3000 |   123K|    16  (25)| 
00:00:01 |
|   2 |   SORT JOIN          |             |   150 |  3600 |     4  (25)| 
00:00:01 |
|   3 |    TABLE ACCESS FULL | TMP_ACCOUNT |   150 |  3600 |     3   (0)| 
00:00:01 |
|*  4 |   FILTER             |             |       |       |            
|          |
|*  5 |    SORT JOIN         |             |  8000 |   140K|    11  (19)| 
00:00:01 |
|   6 |     TABLE ACCESS FULL| ACCOUNT     |  8000 |   140K|     9   (0)| 
00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("B"."AMOUNT"<="A"."HIGHEST_AMOUNT")
   5 - access(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST
              _AMOUNT"))
       filter(INTERNAL_FUNCTION("B"."AMOUNT")>=INTERNAL_FUNCTION("A"."LOWEST
              _AMOUNT"))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         38  consistent gets
          0  physical reads
          0  redo size
    1324733  bytes sent via SQL*Net to client
      38588  bytes received via SQL*Net from client
       3475  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      52108  rows processed
 
SQL> 
SQL> select a.account_number, a.id, b.*
  2    from account a, tmp_account b
  3  where b.account_number = a.account_number
  4  and b.amount between a.lowest_amount and a.highest_amount;
 
ACCOUNT_NUMBER ID         ACCOUNT_NUMBER     AMOUNT DESCR
-------------- ---------- -------------- ---------- 
----------------------------------------
             1 Ex 1                    1          1 Example record 1
             2 Ex 2                    2          2 Example record 2
             3 Ex 3                    3          3 Example record 3
             4 Ex 4                    4          4 Example record 4
             5 Ex 5                    5          5 Example record 5
             6 Ex 6                    6          6 Example record 6
             7 Ex 7                    7          7 Example record 7
             8 Ex 8                    8          8 Example record 8
             9 Ex 9                    9          9 Example record 9
            10 Ex 10                  10         10 Example record 10
            11 Ex 11                  11         11 Example record 11
            12 Ex 12                  12         12 Example record 12
            13 Ex 13                  13         13 Example record 13
            14 Ex 14                  14         14 Example record 14
            15 Ex 15                  15         15 Example record 15
            16 Ex 16                  16         16 Example record 16
            17 Ex 17                  17         17 Example record 17
            18 Ex 18                  18         18 Example record 18
            19 Ex 19                  19         19 Example record 19
            20 Ex 20                  20         20 Example record 20
            21 Ex 21                  21         21 Example record 21
            22 Ex 22                  22         22 Example record 22
            23 Ex 23                  23         23 Example record 23
            24 Ex 24                  24         24 Example record 24
            25 Ex 25                  25         25 Example record 25
            26 Ex 26                  26         26 Example record 26
            27 Ex 27                  27         27 Example record 27
            28 Ex 28                  28         28 Example record 28
            29 Ex 29                  29         29 Example record 29
            30 Ex 30                  30         30 Example record 30
            31 Ex 31                  31         31 Example record 31
            32 Ex 32                  32         32 Example record 32
            33 Ex 33                  33         33 Example record 33
            34 Ex 34                  34         34 Example record 34
            35 Ex 35                  35         35 Example record 35
            36 Ex 36                  36         36 Example record 36
            37 Ex 37                  37         37 Example record 37
            38 Ex 38                  38         38 Example record 38
            39 Ex 39                  39         39 Example record 39
            40 Ex 40                  40         40 Example record 40
            41 Ex 41                  41         41 Example record 41
            42 Ex 42                  42         42 Example record 42
 
42 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1664268811
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| 
Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    42 |    13   (8)| 
00:00:01 |
|*  1 |  HASH JOIN         |             |     1 |    42 |    13   (8)| 
00:00:01 |
|   2 |   TABLE ACCESS FULL| TMP_ACCOUNT |   150 |  3600 |     3   (0)| 
00:00:01 |
|   3 |   TABLE ACCESS FULL| ACCOUNT     |  8000 |   140K|     9   (0)| 
00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ACCOUNT_NUMBER"="A"."ACCOUNT_NUMBER")
       filter("B"."AMOUNT">="A"."LOWEST_AMOUNT" AND
              "B"."AMOUNT"<="A"."HIGHEST_AMOUNT")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         41  consistent gets
          0  physical reads
          0  redo size
       2401  bytes sent via SQL*Net to client
        407  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         42  rows processed
SQL> 


The original 'join' produces useless noise along with some valid data buried 
deep within.  Even creating the suggested index doesn't do any good (although 
that may be due to how I configured the data).
 
David Fitzjarrell
--
http://www.freelists.org/webpage/oracle-l


Other related posts: