Oracle intermediate join table size

Question

When we join more than 2 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 identify the intermediate join size. I am particularly interested in oracle. One solution I know is to use Autotrace in sqldeveloper which has the column LAST_OUTPUT_ROWS. But for queries executed by pl/sql and other means does oracle record the intermediate join size in some table?

I am asking this because recently we had a problem as someone dropped the statistics and failed to regenerate it and when traced through we found that oracle formed an intermediate table of 180 million rows before arriving at the final result of 6 rows and the query was quite slow.

Answer 1

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

Since it's a one-off table that is deleted after the query is complete, its statistics are not stored.

However, you can estimate its size by building a plan for the query and looking at ROWS parameters of the appropriate 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 |
---------------------------------------------------------------------------------------------------------

Here, the materialized table is called SYS_TEMP_0FD9D6604_2660595 and the estimated record count is 100T (100,000,000,000,000 records)