Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Oracle optimization-table optimization (row link generation experiment)

2025-04-03 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

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

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report