Die Gesamtkosten des Oracle-Plans sind wirklich geringer als einige seiner Bestandteile
Ich kann nicht herausfinden, warum manchmal die Gesamtkosten eines Plans eine sehr kleine Zahl sein können, wohingegen wir bei Betrachtung des Plans enorme Kosten finden können. (In der Tat ist die Abfrage sehr langsam).
Kann mir das jemand erklären?
Hier ist ein Beispiel. Anscheinend stammt der kostspielige Teil aus einem Feld in der Hauptauswahl, das eine Listagg für eine Unteransicht ausführt, und die Verknüpfungsbedingung mit dieser Unteransicht enthält eine komplexe Bedingung (wir können auf dem einen oder anderen Feld verknüpfen).
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 875 | 20 |
| 1 | SORT GROUP BY | | 1 | 544 | |
| 2 | VIEW | | 1 | 544 | 3 |
| 3 | SORT UNIQUE | | 1 | 481 | 3 |
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | 3 | 1443 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID | | 7 | 140 | 1 |
| 7 | INDEX RANGE SCAN | | 7 | | 1 |
| 8 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 9 | TABLE ACCESS BY INDEX ROWID | | 1 | 461 | 1 |
| 10 | SORT GROUP BY | | 1 | 182 | |
| 11 | NESTED LOOPS | | | | |
| 12 | NESTED LOOPS | | 8 | 1456 | 3 |
| 13 | NESTED LOOPS | | 8 | 304 | 2 |
| 14 | TABLE ACCESS BY INDEX ROWID | | 7 | 154 | 1 |
| 15 | INDEX RANGE SCAN | | 7 | | 1 |
| 16 | INDEX RANGE SCAN | | 1 | 16 | 1 |
| 17 | INDEX RANGE SCAN | | 1 | | 1 |
| 18 | TABLE ACCESS BY INDEX ROWID | | 1 | 144 | 1 |
| 19 | SORT GROUP BY | | 1 | 268 | |
| 20 | VIEW | | 1 | 268 | 9 |
| 21 | SORT UNIQUE | | 1 | 108 | 9 |
| 22 | CONCATENATION | | | | |
| 23 | NESTED LOOPS | | | | |
| 24 | NESTED LOOPS | | 1 | 108 | 4 |
| 25 | NESTED LOOPS | | 1 | 79 | 3 |
| 26 | NESTED LOOPS | | 1 | 59 | 2 |
| 27 | TABLE ACCESS BY INDEX ROWID | | 1 | 16 | 1 |
| 28 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 29 | TABLE ACCESS BY INDEX ROWID | | 1 | 43 | 1 |
| 30 | INDEX RANGE SCAN | | 1 | | 1 |
| 31 | TABLE ACCESS BY INDEX ROWID | | 1 | 20 | 1 |
| 32 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 33 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 34 | TABLE ACCESS BY INDEX ROWID | | 1 | 29 | 1 |
| 35 | NESTED LOOPS | | | | |
| 36 | NESTED LOOPS | | 1 | 108 | 4 |
| 37 | NESTED LOOPS | | 1 | 79 | 3 |
| 38 | NESTED LOOPS | | 1 | 59 | 2 |
| 39 | TABLE ACCESS BY INDEX ROWID | | 4 | 64 | 1 |
| 40 | INDEX RANGE SCAN | | 2 | | 1 |
| 41 | TABLE ACCESS BY INDEX ROWID | | 1 | 43 | 1 |
| 42 | INDEX RANGE SCAN | | 1 | | 1 |
| 43 | TABLE ACCESS BY INDEX ROWID | | 1 | 20 | 1 |
| 44 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 45 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 46 | TABLE ACCESS BY INDEX ROWID | | 1 | 29 | 1 |
| 47 | SORT GROUP BY | | 1 | 330 | |
| 48 | VIEW | | 1 | 330 | 26695 |
| 49 | SORT UNIQUE | | 1 | 130 | 26695 |
| 50 | CONCATENATION | | | | |
| 51 | HASH JOIN ANTI | | 1 | 130 | 13347 |
| 52 | NESTED LOOPS | | | | |
| 53 | NESTED LOOPS | | 1 | 110 | 4 |
| 54 | NESTED LOOPS | | 1 | 81 | 3 |
| 55 | NESTED LOOPS | | 1 | 61 | 2 |
| 56 | TABLE ACCESS BY INDEX ROWID | | 1 | 16 | 1 |
| 57 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 58 | TABLE ACCESS BY INDEX ROWID | | 1 | 45 | 1 |
| 59 | INDEX RANGE SCAN | | 1 | | 1 |
| 60 | TABLE ACCESS BY INDEX ROWID | | 1 | 20 | 1 |
| 61 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 62 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 63 | TABLE ACCESS BY INDEX ROWID | | 1 | 29 | 1 |
| 64 | VIEW | | 164K| 3220K| 13341 |
| 65 | NESTED LOOPS | | | | |
| 66 | NESTED LOOPS | | 164K| 11M| 13341 |
| 67 | NESTED LOOPS | | 164K| 8535K| 10041 |
| 68 | TABLE ACCESS BY INDEX ROWID | | 164K| 6924K| 8391 |
| 69 | INDEX SKIP SCAN | | 2131K| | 163 |
| 70 | INDEX UNIQUE SCAN | | 1 | 10 | 1 |
| 71 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 72 | TABLE ACCESS BY INDEX ROWID | | 1 | 20 | 1 |
| 73 | HASH JOIN ANTI | | 2 | 260 | 13347 |
| 74 | NESTED LOOPS | | | | |
| 75 | NESTED LOOPS | | 2 | 220 | 4 |
| 76 | NESTED LOOPS | | 2 | 162 | 3 |
| 77 | NESTED LOOPS | | 2 | 122 | 2 |
| 78 | TABLE ACCESS BY INDEX ROWID | | 4 | 64 | 1 |
| 79 | INDEX RANGE SCAN | | 2 | | 1 |
| 80 | TABLE ACCESS BY INDEX ROWID | | 1 | 45 | 1 |
| 81 | INDEX RANGE SCAN | | 1 | | 1 |
| 82 | TABLE ACCESS BY INDEX ROWID | | 1 | 20 | 1 |
| 83 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 84 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 85 | TABLE ACCESS BY INDEX ROWID | | 1 | 29 | 1 |
| 86 | VIEW | | 164K| 3220K| 13341 |
| 87 | NESTED LOOPS | | | | |
| 88 | NESTED LOOPS | | 164K| 11M| 13341 |
| 89 | NESTED LOOPS | | 164K| 8535K| 10041 |
| 90 | TABLE ACCESS BY INDEX ROWID | | 164K| 6924K| 8391 |
| 91 | INDEX SKIP SCAN | | 2131K| | 163 |
| 92 | INDEX UNIQUE SCAN | | 1 | 10 | 1 |
| 93 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 94 | TABLE ACCESS BY INDEX ROWID | | 1 | 20 | 1 |
| 95 | NESTED LOOPS OUTER | | 1 | 875 | 20 |
| 96 | NESTED LOOPS OUTER | | 1 | 846 | 19 |
| 97 | NESTED LOOPS OUTER | | 1 | 800 | 18 |
| 98 | NESTED LOOPS OUTER | | 1 | 776 | 17 |
| 99 | NESTED LOOPS OUTER | | 1 | 752 | 16 |
| 100 | NESTED LOOPS OUTER | | 1 | 641 | 15 |
| 101 | NESTED LOOPS OUTER | | 1 | 576 | 14 |
| 102 | NESTED LOOPS OUTER | | 1 | 554 | 13 |
| 103 | NESTED LOOPS OUTER | | 1 | 487 | 12 |
| 104 | NESTED LOOPS OUTER | | 1 | 434 | 11 |
| 105 | NESTED LOOPS | | 1 | 368 | 10 |
| 106 | NESTED LOOPS | | 1 | 102 | 9 |
| 107 | NESTED LOOPS OUTER | | 1 | 85 | 8 |
| 108 | NESTED LOOPS | | 1 | 68 | 7 |
| 109 | NESTED LOOPS | | 50 | 2700 | 6 |
| 110 | HASH JOIN | | 53 | 1696 | 5 |
| 111 | INLIST ITERATOR | | | | |
| 112 | TABLE ACCESS BY INDEX ROWID| | 520 | 10400 | 3 |
| 113 | INDEX RANGE SCAN | | 520 | | 1 |
| 114 | INLIST ITERATOR | | | | |
| 115 | TABLE ACCESS BY INDEX ROWID| | 91457 | 1071K| 1 |
| 116 | INDEX UNIQUE SCAN | | 2 | | 1 |
| 117 | TABLE ACCESS BY INDEX ROWID | | 1 | 22 | 1 |
| 118 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 119 | TABLE ACCESS BY INDEX ROWID | | 1 | 14 | 1 |
| 120 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 121 | TABLE ACCESS BY INDEX ROWID | | 1 | 17 | 1 |
| 122 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 123 | TABLE ACCESS BY INDEX ROWID | | 1 | 17 | 1 |
| 124 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 125 | TABLE ACCESS BY INDEX ROWID | | 1 | 266 | 1 |
| 126 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 127 | TABLE ACCESS BY INDEX ROWID | | 1 | 66 | 1 |
| 128 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 129 | TABLE ACCESS BY INDEX ROWID | | 1 | 53 | 1 |
| 130 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 131 | TABLE ACCESS BY INDEX ROWID | | 1 | 67 | 1 |
| 132 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 133 | INDEX RANGE SCAN | | 1 | 22 | 1 |
| 134 | TABLE ACCESS BY INDEX ROWID | | 1 | 65 | 1 |
| 135 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 136 | TABLE ACCESS BY INDEX ROWID | | 1 | 111 | 1 |
| 137 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 138 | TABLE ACCESS BY INDEX ROWID | | 1 | 24 | 1 |
| 139 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 140 | TABLE ACCESS BY INDEX ROWID | | 1 | 24 | 1 |
| 141 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 142 | TABLE ACCESS BY INDEX ROWID | | 1 | 46 | 1 |
| 143 | INDEX UNIQUE SCAN | | 1 | | 1 |
| 144 | TABLE ACCESS BY INDEX ROWID | | 1 | 29 | 1 |
| 145 | INDEX UNIQUE SCAN | | 1 | | 1 |
----------------------------------------------------------------------------------------------------------