SLTechnology News&Howtos › Database ›

Oracle optimization-table optimization (temporary table)

Shulou Source: shulou.com Published: 2022-06-01 06:58:55 10月01日 Update

First, create temporary tables and regular tables

SQL > CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS AS SELECT * FROM emp

Table created.

SQL > select count (0) from TMP_TEST

COUNT (0)

fourteen

SQL > CREATE GLOBAL TEMPORARY TABLE TMP_TEST1 ON COMMIT DELETE ROWS AS SELECT * FROM emp

Table created.

SQL > select count (0) from TMP_TEST1

COUNT (0)

fourteen

SQL > create table tmp_test2 as select * from emp

Table created.

SQL > select count (0) from TMP_TEST2

COUNT (0)

fourteen

-- TMP_TEST1 data is emptied because the tmp_test2 is created as ddl

SQL > select count (0) from TMP_TEST1

COUNT (0)

-- close the session and reopen, and the TMP_TEST data is emptied

SQL > select count (0) from TMP_TEST

COUNT (0)

Second, add, delete and modify the copy table respectively, and compare the performance, you can find the following differences:

New: cr=8 of temporary table, cr=11 of regular table

When modified: cr=3,cost=2; normal table cr=7,cost=3 of temporary table

When deleted: cr=3,cost=2; normal table cr=7,cost=3 of temporary table

Therefore, the performance of the temporary table is higher than that of the ordinary table if it is used as an intermediate table to add, delete and modify.

SQL > insert into SCOTT.TMP_TEST SELECT * FROM SCOTT.emp

14 rows created.