Shulou(Shulou.com)05/31 Report--

This article mainly introduces the statistical information of how to achieve the global temporary table GTT in the database, which is very detailed and has a certain reference value. Interested friends must read it!

As we all know, the global temporary table GTT is divided into two types, one is transaction level, the other is session level

It is implemented through on commit delete rows/preserve rows, where session level is expressed in this sessoin.

The data is valid. Within the same session, the data of previous transaction operations is visible for subsequent operations, while the transaction-level GTT represents

Once the transaction ends (commit), then immediately delete, subsequent operations of the same session can not see the previous transaction operation.

In the 9i phase, you can use the GATHER_TABLE_STATS call to collect statistics. The parameter GATHER_TEMP is TRUE.

Starting from 10g, oracle has no special treatment for collecting statistics from regular tables and GTT, all through GATHER_TABLE_STATS.

Stored procedures to collect, but because of the particularity of the above two kinds of GTT, the collection statistics are special:

1. For session level, because GTT data is not persistent and session isolation exists, it needs to be collected in the current session.

Collecting statistics through another window (new session) will not be successful because the session that collects statistics does not have data

Of course, no statistics can be collected.

two。 For transaction level, even if it is the current session collection, because GATHER_TABLE_STATS performs the default commit first

So the data is deleted automatically, and naturally there is no data to collect. So in response to this situation, oracle has an official introduction of note 403587.1.

Here are the steps to collect transaction-level GTT

1. Create a PRESERVE ROWS table

SQL > create global temporary table TT (I number) on commit preserve rows

2. Populate with representative data

SQL > insert into TT select rownum from dba_objects where rownum exec dbms_stats.gather_table_stats (null,'TT')

4. Create a STAT table

SQL > exec dbms_stats.create_stat_table (null,'TTSTATS')

5. Export the stats from the PRESERVE ROWS table

SQL > exec dbms_stats.export_table_stats (null,'TT',null,'TTSTATS',null,true)

6. Truncate then drop the PRESERVE ROWS table

SQL > truncate table TT

SQL > drop table TT

7. Now create the real temporary table defined using DELETE ROWS-the default)

SQL > create global temporary table TT (I number)

8. Finally import the stats exported from the STAT table

SQL > exec dbms_stats.import_table_stats (null,'TT',null,'TTSTATS',null,true)

3. In version 12c, oracle has improved the collection of statistics for this transaction level GTT

In other words, GATHER_TABLE_STATS will not initiate commit by default when collecting statistics, so it will not

The stored procedure that destroys the transaction integrity of the current session and collects statistics can see the current session's

Data and collect statistics.

Here is a simple test process:

3.1. Create transaction level GTT

Create Global Temporary Table maob_temp (a number,b varchar2 (100)) On Commit delete Rows

COUNT (*)

-

nine hundred and ninety nine

3.5. Check to see if statistics have been collected successfully

SQL > select TABLE_NAME,NUM_ROWS,BLOCKS,SCOPE from DBA_TAB_STATISTICS where owner='MAOB' AND TABLE_NAME='MAOB_TEMP'

TABLE_NAME NUM_ROWS BLOCKS SCOPE

-

MAOB_TEMP 0 0 SHARED

MAOB_TEMP 999 4 SESSION

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.