深入瞭解oracle的高水位(HWM)

(B)建立一個表,注意,此表的第二個欄位我故意設成是CHAR(100),以讓此表在插入1千萬條記錄後,空間有足夠大:

CREATE TABLE TEST_TAB(C1 NUMBER(10),C2 CHAR(100)) TABLESPACE RAINNY;

插入記錄DECLARE
I NUMBER(10);BEGIN
FOR I IN 1..10000000 LOOP
INSERT INTO TEST_TAB VALUES(I,'TESTSTRING');
END LOOP;
COMMIT;END ;

(C)我們來查詢一下,看在插入一千萬條記錄後所訪問的塊數和查詢所用時間:

SQL> SET TIMING ON

SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT COUNT(*) FROM TEST_TAB;

ELAPSED: 00:01:03.05

EXECUTION PLAN

----------------------------------------------------------

0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=15056 CARD=1)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST_TAB' (COST=15056 CARD=10000

000)

STATISTICS

----------------------------------------------------------

0 RECURSIVE CALLS

0 DB BLOCK GETS

156310 CONSISTENT GETS

154239 PHYSICAL READS

0 REDO SIZE

379 BYTES SENT VIA SQL*NET TO CLIENT

503 BYTES RECEIVED VIA SQL*NET FROM CLIENT

2 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

1 ROWS PROCESSED

SQL>

我們來看上面的執行計劃,這句SQL總供耗時是:1分3秒.訪問方式是採用全表掃描方式(FTS),邏輯讀了156310個BLOCK,物理讀了154239個BLOCK.

我們來分析一下這個表:

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST',
TABNAME=> 'TEST_TAB',
PARTNAME=> NULL);END;

發現這個表目前使用的BLOCK有: 156532,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數為(NUM_ROWS):1000 0000

(D)接下來我們把此表的記錄用DELETE方式刪掉,然後再來看看SELECT COUNT(*) FROM TEST_TAB所花的時間:

DELETE FROM TEST_TAB;

COMMIT;

SQL> SELECT COUNT(*) FROM TEST_TAB;

ELAPSED: 00:01:04.03

EXECUTION PLAN

----------------------------------------------------------

0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=15056 CARD=1)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST_TAB' (COST=15056 CARD=1)

STATISTICS

----------------------------------------------------------

0 RECURSIVE CALLS

0 DB BLOCK GETS

156310 CONSISTENT GETS

155565 PHYSICAL READS

0 REDO SIZE

378 BYTES SENT VIA SQL*NET TO CLIENT

503 BYTES RECEIVED VIA SQL*NET FROM CLIENT

2 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

1 ROWS PROCESSED

SQL>