Shulou(Shulou.com)06/01 Report--

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)

-

0

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

SQL > select count (0) from TMP_TEST

COUNT (0)

-

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.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

0 000 LOAD TABLE CONVENTIONAL (cr=8 pr=0 pw=0 time=548 us)

14 14 14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=64 us cost=3 size=1218 card=14)

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

14 rows created.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

0 000 LOAD TABLE CONVENTIONAL (cr=8 pr=0 pw=0 time=566 us)

14 14 14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=63 us cost=3 size=1218 card=14)

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

14 rows created.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

0 000 LOAD TABLE CONVENTIONAL (cr=11 pr=2 pw=0 time=784 us)

14 14 14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=31 us cost=3 size=1218 card=14)

SQL > delete from SCOTT.TMP_TEST where SAL delete from SCOTT.TMP_TEST1 where SAL delete from SCOTT.TMP_TEST2 where SAL update SCOTT.TMP_TEST set sal=sal+100

5 rows updated.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

0 000 UPDATE TMP_TEST (cr=3 pr=0 pw=0 time=132 us)

5 55 TABLE ACCESS FULL TMP_TEST (cr=3 pr=0 pw=0 time=46 us cost=2 size=65 card=5)

SQL > update SCOTT.TMP_TEST1 set sal=sal+100

5 rows updated.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

0 000 UPDATE TMP_TEST1 (cr=3 pr=0 pw=0 time=132 us)

5 55 TABLE ACCESS FULL TMP_TEST1 (cr=3 pr=0 pw=0 time=34 us cost=2 size=65 card=5)

SQL > update SCOTT.TMP_TEST2 set sal=sal+100

5 rows updated.

Rows (1st) Rows (avg) Rows (max) Row Source Operation

0 000 UPDATE TMP_TEST2 (cr=7 pr=0 pw=0 time=69 us)

5 55 TABLE ACCESS FULL TMP_TEST2 (cr=7 pr=0 pw=0 time=15 us cost=3 size=65 card=5)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.