Oracle optimization-table optimization (row link generation experiment)

First, create a piece of data with row links but no row migration by inserting data larger than the basic block size (8K) at a time

SQL > insert into t_pctfree values (10002) dbmsroom.string (2000), dbms_random.string (2000))

1 row created.

SQL > commit

Commit complete.

SQL > alter system switch logfile

System altered.

SQL > /

System altered.

SQL > /

System altered.

Use dump to make sure that this row of data has a row link but no row migration

SQL > select dbms_rowid.rowid_block_number (rowid) block_id from t_pctfree where id=10002

BLOCK_ID

-

51036

SQL > alter system dump datafile 5 block 51036

System altered.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25082.trc

[oracle@localhost ~] $more / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25082.trc

Block_row_dump:

Tab 0, row 0, @ 0xfe4

Tl: 4020 fb:-- Hmurf Fmuri-lb: 0x1 cc: 3

Nrid: 0x0140c758.1

Col 0: [4] c3 02 01 03

Col 1: [2000]

55 51 46 4b 57 4c 4a 48 4b 47 4e 43 43 59 42 46 56 44 54 48 54 50 42 51 47

58 59 54 54 41 48 45 4e 5a 56 4c 47 44 4e 55 43 53 43 51 4d 57 4e 54 59 5a

48 48 4c 48 41 41 4e 44 45 51 4c 51 4c 58 47 4a 4f 4a 42 4f 41 59 57 57 58

46 4d 50 4a 45 57 46 42 45 48 48 46 41 44 5a 43 53 55 59 53 4a 42 52 5a 4f

4b 4f 52 56 56 46 4e 58 57 4a 46 4b 4a 46 56 54 59 51 59 4a 49 51 42 56 4e

53 48 59 4e 58 55 57 44 41 41 53 4c 4f 43 45 52 4b 4c 4d 46 58 4d 53 50 51

Third, confirm whether column 1 is the data just inserted

SQL > var n number

SQL > exec dbms_stats.convert_raw_value ('c3020103)

PL/SQL procedure successfully completed.

SQL > print: n

N

-

10002

Therefore, it can be confirmed that this block (51036) stores 1 and 2 columns of information, and the rest is listed on another block, unlike when rows are migrated, there is only nrid in the block information

4. View the information of linked blocks

SQL > select dbms_utility.DATA_BLOCK_ADDRESS_FILE (to_number ('0140c758)) file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK (to_number (' 0140c758)) block# from dual

FILE# BLOCK#

--

5 51032

SQL > alter system dump datafile 5 block 51032

System altered.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug tracefile_name

/ u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25101.trc

Tl: 6012 fb:-Lmuri-lb: 0x2 cc: 3

Col 0: [2000]

4e 49 59 52 43 52 47 41 44 44 4e 54 43 5a 4d 4d 53 47 55 55 4d 56 55 44 55

4f 46 53 50 47 4d 50 52 43 45 51 57 52 41 44 4c 55 49 55 59 4c 57 58 5a 52

44 50 41 45 52 48 57 45 4b 53 58 54 50 56 47 52 4b 5a 52 4d 50 44 42 43 50

4b 48 51 41 54 47 49 48 50 4c 45 57 4f 48 4f 4a 55 4c 50 59 52 4d 53 55 50

59 4d 41 41 55 53 5a 48 56 52 41 44 4e 53 49 4a 58 4c 4a 4e 47 54 55 44 43

4f 56 4f 55 4a 41 46 44 42 55 41 42 48 58 47 56 57 52 55 47 52 44 4a 42 48