Oracle Intermediate Size

Question

When we join more than two tables, oracle or, for that matter, any database decides to join 2 tables and use the result to join with subsequent tables. Is there a way to determine the size of the intermediate compound. I am particularly interested in the oracle. One solution that I know is to use Autotrace in sqldeveloper, which has a LAST_OUTPUT_ROWS column. But for queries executed by pl / sql and other means, does the oracle write the intermediate size of the join in some table?

I ask this because lately we had a problem when someone dumped statistics and couldn’t restore it, and when we traced it, we found that the oracle formed an intermediate table of 180 million rows until the final result from 6 lines and the query was rather slow.

Answer 1

Oracle can materialize the intermediate results of joining a table in the time segment set for your session.

Since this is a one-time table that is deleted after the query is completed, its statistics are not saved.

However, you can estimate its size by building a query plan and looking at the parameters of the ROWScorresponding operation:

EXPLAIN PLAN FOR
WITH    q AS
        (
        SELECT  /*+ MATERIALIZE */
                e1.value AS val1, e2.value AS val2
        FROM    t_even e1, t_even e2
        )
SELECT  COUNT(*)
FROM    q

SELECT  *
FROM    TABLE(DBMS_XPLAN.display())

Plan hash value: 3705384459

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |     1 |       |    43G  (5)|999:59:59 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   2 |   LOAD AS SELECT           |                            |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN    |                            |   100T|   909T|    42G  (3)|999:59:59 |
|   4 |     TABLE ACCESS FULL      | T_ODD                      |    10M|    47M|  4206   (3)| 00:00:51 |
|   5 |     BUFFER SORT            |                            |    10M|    47M|    42G  (3)|999:59:59 |
|   6 |      TABLE ACCESS FULL     | T_ODD                      |    10M|    47M|  4204   (3)| 00:00:51 |
|   7 |   SORT AGGREGATE           |                            |     1 |       |            |          |
|   8 |    VIEW                    |                            |   100T|       |  1729M (62)|999:59:59 |
|   9 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6604_2660595 |   100T|   909T|  1729M (62)|999:59:59 |
---------------------------------------------------------------------------------------------------------

SYS_TEMP_0FD9D6604_2660595, - 100T (100,000,000,000,000 records)