Oracle PL/SQL Tutorial/System Packages/dbms rowid
Содержание
- 1 Avg # Rows/Block
- 2 DBMS_ROWID.ROWID_BLOCK_NUMBER and DBMS_ROWID.ROWID_ROW_NUMBER
- 3 DBMS_ROWID.ROWID_RELATIVE_FNO(RowID)
- 4 DBMS_ROWID.ROWID_RELATIVE_FNO(RowID),DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
- 5 DBMS_ROWID.ROWID_ROW_NUMBER(RowID)
- 6 DBMS_ROWID.ROWID_TO_RESTRICTED
- 7 DBMS_ROWID.ROWID_TO_RESTRICTED(RowID,0)
- 8 Distinct Blocks
- 9 Max # Rows/Block
- 10 Min # Rows/BLock
- 11 Use dbms_rowid.rowid_block_number
Avg # Rows/Block
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H","Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL> SELECT AVG(COUNT(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)))
2 "Avg # Rows/Block"
3 FROM employee
4 GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);
10
SQL>
SQL>
SQL> drop table employee;
Table dropped.
DBMS_ROWID.ROWID_BLOCK_NUMBER and DBMS_ROWID.ROWID_ROW_NUMBER
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10,"H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL> SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) "Block Number",
2 DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) "Row in Block",
3 employee_id
4 FROM employee;
42570 0 1
42570 1 2
42570 2 3
42570 3 4
42570 4 5
42570 5 6
42570 6 7
42570 7 8
42570 8 9
42570 9 10
10 rows selected.
SQL>
SQL> drop table employee;
Table dropped.
DBMS_ROWID.ROWID_RELATIVE_FNO(RowID)
SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID)
2 from dual
3 where RowNum=1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
1
DBMS_ROWID.ROWID_RELATIVE_FNO(RowID),DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID),
2 DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
3 from dual
4 where RowNum=1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1 2082
DBMS_ROWID.ROWID_ROW_NUMBER(RowID)
SQL> select DBMS_ROWID.ROWID_ROW_NUMBER(RowID)
2 from dual;
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
----------------------------------
0
SQL>
SQL>
DBMS_ROWID.ROWID_TO_RESTRICTED
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT ROWID "V8 ROWID",
2 DBMS_ROWID.ROWID_TO_RESTRICTED(ROWID,0) "V7 ROWID",
3 employee_id, last_name
4 FROM employee
5 WHERE ROWNUM < 3;
AAAFEtAABAAAKZKAAA 0000A64A.0000.0001 1 V
AAAFEtAABAAAKZKAAB 0000A64A.0001.0001 2 N
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
DBMS_ROWID.ROWID_TO_RESTRICTED(RowID,0)
SQL> select DBMS_ROWID.ROWID_TO_RESTRICTED(RowID,0)
2 from dual;
DBMS_ROWID.ROWID_T
------------------
00000822.0000.0001
Distinct Blocks
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H","Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL> SELECT COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
2 "Distinct Blocks"
3 FROM employee
4 GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);
1
SQL>
SQL> drop table employee;
Table dropped.
Max # Rows/Block
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H","Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL> SELECT MAX(COUNT(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)))
2 "Max # Rows/Block"
3 FROM employee
4 GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);
10
SQL>
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.
Min # Rows/BLock
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H","Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL> SELECT MIN(COUNT(DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)))
2 "Min # Rows/BLock"
3 FROM employee
4 GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);
10
SQL>
SQL>
SQL> drop table employee;
Table dropped.
Use dbms_rowid.rowid_block_number
SQL>
SQL> set echo on
SQL> set serveroutput on
SQL>
SQL> create table t
2 ( a int,
3 b varchar2(4000) default rpad("*",4000,"*"),
4 c varchar2(3000) default rpad("*",3000,"*" )
5 )
6 /
Table created.
SQL>
SQL> insert into t(a) select rownum from all_users;
15 rows created.
SQL> insert into t(a) select rownum+1000 from all_users;
15 rows created.
SQL>
SQL> select dbms_rowid.rowid_block_number(rowid), a from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) A
------------------------------------ ----------
43410 1
43411 2
43412 3
43413 4
43414 5
43415 6
43416 7
43417 8
43418 9
43419 10
43420 11
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) A
------------------------------------ ----------
43421 12
43422 13
43423 14
43424 15
43425 1001
43426 1002
43427 1003
43428 1004
43429 1005
43430 1006
43431 1007
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) A
------------------------------------ ----------
43432 1008
43433 1009
43434 1010
43435 1011
43436 1012
43437 1013
43438 1014
43439 1015
30 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>