Re: tune between query/join
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Fri, 21 Oct 2011 02:06:18 -0700 (PDT)
Message-ID: <1319187978.49033.YahooMailNeo_at_web65403.mail.ac4.yahoo.com>
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.
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
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 |
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
42 rows selected.
Plan hash value: 1664268811
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
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
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
2 on account(lowest_amount, highest_amount);
Index created.
SQL>
SQL> exec dbms_stats.gather_schema_stats('BING');
PL/SQL procedure successfully completed.
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
...
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.
Plan hash value: 3631114592
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
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
42 rows selected.
Plan hash value: 1664268811
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
1 - access("B"."ACCOUNT_NUMBER"="A"."ACCOUNT_NUMBER")
filter("B"."AMOUNT">="A"."LOWEST_AMOUNT" AND
"B"."AMOUNT"<="A"."HIGHEST_AMOUNT")
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
Date: Fri, 21 Oct 2011 02:06:18 -0700 (PDT)
Message-ID: <1319187978.49033.YahooMailNeo_at_web65403.mail.ac4.yahoo.com>
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
-------------- ---------- -------------- ---------- ----------------------------------------7118 Ex 7118 42 42 Example record 42
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
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
-------------- ---------- -------------- ---------- ----------------------------------------6781 Ex 6781 42 42 Example record 42
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
...
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-lReceived on Fri Oct 21 2011 - 04:06:18 CDT