You Asked

Hi

I do like to use 'with' syntax (common table expressions) in long select statements.
To me they make statements much more readable.

While looking at that execution plans one notices that sometimes these statements are using internal tables.

What these tables actually are ? Global temporary tables ?
Would they benefit from using TEMP_UNDO_ENABLED initialization parameter ?

lh

and Connor said...

Maybe a little but not as much as you might think. We'll look at 'redo size' for before and after executions of a sample statement


SQL> connect mcdonac/alicat1
Connected.

mcdonac@np12
SQL> alter session set temp_undo_enabled = false;

Session altered.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                                 924

SQL>
SQL> with bigtab as
  2   ( select /*+ materialize */ d.*
  3     from dba_objects d,
  4        ( select 1 from dual connect by level <= 20 )
  5    )
  6  select max(object_id) from bigtab;

MAX(OBJECT_ID)
--------------
        101352

1 row selected.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                                2304

SQL>
SQL> connect mcdonac/alicat1
Connected.

SQL> alter session set temp_undo_enabled = true;

Session altered.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                                 940

SQL>
SQL> with bigtab as
  2   ( select /*+ materialize */ d.*
  3     from dba_objects d,
  4        ( select 1 from dual connect by level <= 20 )
  5    )
  6  select max(object_id) from bigtab;

MAX(OBJECT_ID)
--------------
        101352

1 row selected.

SQL>
SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                                2080

SQL>

You can a tiny bit of difference, but the main thing here is there was not much redo at all. The loads are like "create table as select", rather then something like "insert into" which *would* use a lot of redo.

For example:

SQL> connect mcdonac/alicat1
Connected.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                                 940

SQL>
SQL> create table bigtab as select * from dba_objects where 1=0;

Table created.

SQL> insert into bigtab
  2  select d.*
  3  from dba_objects d,
  4        ( select 1 from dual connect by level <= 20 );

1862280 rows created.

SQL>
SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           255080176

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank You very much for Your prompt and detailed answer.

lh, March 15, 2017 - 9:48 am UTC

Thank You very much for Your prompt and detailed answer.

lh

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thank You very much for Your prompt and detailed answer.

More to Explore

Hints

TEMP_UNDO_ENABLED and with clause

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thank You very much for Your prompt and detailed answer.

More to Explore

Hints