Categories
dba fun sql xml

high cost

What’s wrong with this query ?


select
(t6.column_value).getstringval() t6
from
table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1,
table(xmlsequence(t1.column_value))t2,
table(xmlsequence(t2.column_value))t3,
table(xmlsequence(t3.column_value))t4,
table(xmlsequence(t4.column_value))t5,
table(xmlsequence(t5.column_value))t6;
T6
————————
<x/>

Elapsed: 00:00:00.01

Well, let’s check the plan :

--------------------------------------------------------------------
| Id | Operation | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | 18E| 15E| 18E (0)|999:59:59 |
| 1 | NESTED LOOPS | 18E| 15E| 18E (0)|999:59:59 |
| 2 | NESTED LOOPS | 18E| 15E| 99P (3)|999:59:59 |
| 3 | NESTED LOOPS | 4451T| 31P| 12T (3)|999:59:59 |
| 4 | NESTED LOOPS | 544G| 3045G| 1490M (3)|999:59:59 |
| 5 | NESTED LOOPS | 66M| 254M| 182K (3)| 00:36:31 |
| 6 | COLLECTION I | | | | |
| 7 | COLLECTION I | | | | |
| 8 | COLLECTION IT | | | | |
| 9 | COLLECTION ITE | | | | |
| 10 | COLLECTION ITER | | | | |
| 11 | COLLECTION ITERA | | | | |
--------------------------------------------------------------------

It is returning 18 quadrillions of rows, 15 exabytes, the cost is 1.8E19 and the time is about one month :mrgreen: