...does what it says on the tin. TMBSNMOTW"second"OWIWPU : comments.
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
|---|---|---|---|---|---|---|
|
1
|
2
|
3
|
4
|
|||
|
5
|
6
|
7
|
8
|
9
|
10
|
11
|
|
12
|
13
|
14
|
15
|
16
|
17
|
18
|
|
19
|
20
|
21
|
22
|
23
|
24
|
25 |
|
26
|
27
|
28
|
29
|
30
|
31
|
![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
(no subject)
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3571711743 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 113K| 883K| | 1008K (19)| 03:21:42 | | 1 | SORT GROUP BY | | 113K| 883K| 102M| 1008K (19)| 03:21:42 | | 2 | VIEW | | 6580K| 50M| | 1075K (24)| 03:35:03 | | 3 | SORT UNIQUE | | 6580K| 652M| 734M| 1075K (17)| 03:35:03 | | 4 | CONCATENATION | | | | | | | |* 5 | HASH JOIN | | 3290K| 326M| 22M| 505K (18)| 01:41:12 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- | 6 | TABLE ACCESS FULL | LOCATION | 749K| 14M| | 2502 (7)| 00:00:31 | |* 7 | HASH JOIN | | 3290K| 263M| 414M| 486K (18)| 01:37:19 | |* 8 | HASH JOIN | | 6581K| 338M| | 185K (23)| 00:37:11 | |* 9 | TABLE ACCESS FULL | LOCATION | 1164 | 27936 | | 2859 (19)| 00:00:35 | |* 10 | TABLE ACCESS FULL | MOVEMENT | 38M| 1112M| | 179K (21)| 00:35:53 | |* 11 | TABLE ACCESS FULL | MOVEMENT | 38M| 1112M| | 179K (21)| 00:35:53 | | 12 | NESTED LOOPS | | 473 | 49192 | | 487K (18)| 01:37:30 | |* 13 | HASH JOIN | | 473 | 39732 | 414M| 486K (18)| 01:37:19 | |* 14 | HASH JOIN | | 6581K| 338M| | 185K (23)| 00:37:11 | |* 15 | TABLE ACCESS FULL | LOCATION | 1164 | 27936 | | 2859 (19)| 00:00:35 | |* 16 | TABLE ACCESS FULL | MOVEMENT | 38M| 1112M| | 179K (21)| 00:35:53 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- |* 17 | TABLE ACCESS FULL | MOVEMENT | 38M| 1112M| | 179K (21)| 00:35:53 | | 18 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 | 20 | | 2 (0)| 00:00:01 | |* 19 | INDEX UNIQUE SCAN | LOCATION_KEY | 1 | | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("A"."LKEY"="LOCATION"."KEY") 7 - access("A"."AKEY"="B"."AKEY" AND "A"."MDAT"="B"."MDAT") 8 - access("KEY"="B"."LKEY") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- 9 - filter("PTYPE"='AH' AND REGEXP_LIKE ("POSTC",'HS[1-2]','i') AND "DDATE" IS NULL) 10 - filter("B"."MDIR"=2 AND "B"."CSTAT"='L') 11 - filter("A"."MDIR"=1 AND "A"."CSTAT"='L') 13 - access("A"."AKEY"="B"."AKEY" AND "A"."MDAT"="B"."MDAT") filter(LNNVL("KEY"="B"."LKEY")) 14 - access("KEY"="A"."LKEY") 15 - filter("PTYPE"='AH' AND REGEXP_LIKE ("POSTC",'HS[1-2]','i') AND "DDATE" IS NULL) 16 - filter("A"."MDIR"=1 AND "A"."CSTAT"='L') 17 - filter("B"."MDIR"=2 AND "B"."CSTAT"='L') 19 - access("A"."LKEY"="LOCATION"."KEY") 43 rows selected.(no subject)
Are the row counts vaguely accurate, or are they way out too? If they're also way out then it might be one of the annoying cases where EXPLAIN PLAN isn't giving you the execution plan that's being used. Beyond that, I'm afraid I don't really know.
No chance of using some indexes? If you're doing a lot of the same sort of thing, think about function based indexes "CREATE INDEX foo ON movement (regexp_substr(postc,'HS[1-2]'));" (or whatever the exact function is). Saves having to compute the function each time for every row on the table.