On the use of DBMS_ROWID.ROWID_ROW_NUMBER function
Miles, February 03, 2003 - 3:06 pm UTC
Thanks for tip, Tom.
Still in connection to this topic, but what if there's only one relative file number for the table? Does this mean I need to break the data by EXTENTs Level? Can you please give me an example of breaking data by extent level?
February 03, 2003 - 3:51 pm UTC
Ok, it was fun to write - so here goes:
this is for a table in a SINGLE file, breaks it into 4 "more or less" equi ranges:
select relative_fno, min_block start_block, max_block+blocks-1 end_block,
sum_blocks,
dbms_rowid.rowid_create( 1, data_object_id, relative_fno, min_block, 0 )
min_rid,
dbms_rowid.rowid_create( 1, data_object_id, relative_fno,
max_block+blocks-1, 10000 ) max_rid
from (
select relative_fno,
block_id,
min(block_id) over (partition by grp) min_block,
max(block_id) over (partition by grp) max_block,
blocks,
sum(blocks) over (partition by grp) sum_blocks
from (
select relative_fno,
block_id,
blocks,
trunc( (sum(blocks) over (order by block_id)-0.01) /
(sum(blocks) over ()/4) ) grp
from dba_extents
where segment_name = 'BIG_TABLE'
and owner = user order by block_id
)
), (select data_object_id
from user_objects
where object_name = 'BIG_TABLE' )
where block_id = max_block
/
So, how does it work? we start from the middle:
big_table@ORA920> select relative_fno,
2 block_id,
3 blocks,
4 sum(blocks) over (order by block_id) cum_blocks,
5 sum(blocks) over () tot_blocks,
6 trunc( (sum(blocks) over (order by block_id)-0.01) /
7 (sum(blocks) over ()/4) ) grp
8 from dba_extents
9 where segment_name = 'BIG_TABLE'
10 and owner = user order by block_id
11 /
RELATIVE_FNO BLOCK_ID BLOCKS CUM_BLOCKS TOT_BLOCKS GRP
------------ ---------- ---------- ---------- ---------- ----------
11 9 8 8 13312 0
11 17 8 16 13312 0
11 25 8 24 13312 0
11 33 8 32 13312 0
....
11 10249 1024 11264 13312 3
11 11273 1024 12288 13312 3
11 12297 1024 13312 13312 3
84 rows selected.
We kept a running total of the blocks (cum_blocks) and had the tot number of blocks in the table (tot_blocks). Now, If I take the tot_blocks and divide by 4 -- i get the number of blocks I would like optimally in each group. We take that number and divide it into the running total number of blocks and assign a group id GRP of 0,1,2,3 to each group. Next we:
big_table@ORA920> select relative_fno,
2 block_id,
3 min(block_id) over (partition by grp) min_block,
4 max(block_id) over (partition by grp) max_block,
5 blocks,
6 sum(blocks) over (partition by grp) sum_blocks
7 from (
8 select relative_fno,
9 block_id,
10 blocks,
11 trunc( (sum(blocks) over (order by block_id)-0.01) /
12 (sum(blocks) over ()/4) ) grp
13 from dba_extents
14 where segment_name = 'BIG_TABLE'
15 and owner = user order by block_id
16 )
17 /
RELATIVE_FNO BLOCK_ID MIN_BLOCK MAX_BLOCK BLOCKS SUM_BLOCKS
------------ ---------- ---------- ---------- ---------- ----------
11 9 9 3209 8 3328
11 17 9 3209 8 3328
11 25 9 3209 8 3328
11 33 9 3209 8 3328
....
11 8073 6665 8201 128 2560
11 8201 6665 8201 1024 2560
11 9225 9225 12297 1024 4096
11 10249 9225 12297 1024 4096
11 11273 9225 12297 1024 4096
11 12297 9225 12297 1024 4096
84 rows selected.
Basically find the minimum block and maximum block per group. Here I compute sum_blocks as well -- just to see how well the data was distributed in the groups. It'll probably never be perfect (lmts with uniform extents and an evenly divisible number of extents would do that)...
Lastly we:
big_table@ORA920> select relative_fno, min_block start_block, max_block+blocks-1 end_block, sum_blocks,
2 dbms_rowid.rowid_create( 1, data_object_id, relative_fno, min_block, 0 ) min_rid,
3 dbms_rowid.rowid_create( 1, data_object_id, relative_fno, max_block+blocks-1, 10000 ) max_rid
4 from (
5 select relative_fno,
6 block_id,
7 min(block_id) over (partition by grp) min_block,
8 max(block_id) over (partition by grp) max_block,
9 blocks,
10 sum(blocks) over (partition by grp) sum_blocks
11 from (
12 select relative_fno,
13 block_id,
14 blocks,
15 trunc( (sum(blocks) over (order by block_id)-0.01) /
16 (sum(blocks) over ()/4) ) grp
17 from dba_extents
18 where segment_name = 'BIG_TABLE'
19 and owner = user order by block_id
20 )
21 ), (select data_object_id
22 from user_objects
23 where object_name = 'BIG_TABLE' )
24 where block_id = max_block
25 /
RELATIVE_FNO START_BLOCK END_BLOCK SUM_BLOCKS MIN_RID MAX_RID
------------ ----------- ---------- ---------- ------------------ ------------------
11 9 3336 3328 AAAHerAALAAAAAJAAA AAAHerAALAAAA0ICcQ
11 3337 6664 3328 AAAHerAALAAAA0JAAA AAAHerAALAAABoICcQ
11 6665 9224 2560 AAAHerAALAAABoJAAA AAAHerAALAAACQICcQ
11 9225 13320 4096 AAAHerAALAAACQJAAA AAAHerAALAAADQICcQ
that is -- we keep only the row where the block_id = MAX_BLOCK id, that keeps one row PER GROUP. From this - we now have the STARTING block, the ENDING BLOCK and are ready to generate the rowids for each range.
There you go ;)
Is this how PQ does it
Rob, February 03, 2003 - 8:54 pm UTC
Tom:
Is your approach the same approach taken by Parallel Query when it parallelizes a scan of a non-partitioned table.
February 04, 2003 - 7:25 am UTC
similar but they take a slightly more sophisticated approach. But it is fundementally based on rowid ranges.
Reader
A reader, February 03, 2003 - 10:51 pm UTC
RELATIVE_FNO BLOCK_ID MIN_BLOCK MAX_BLOCK BLOCKS SUM_BLOCKS
------------ ---------- ---------- ---------- ---------- ----------
11 9 9 3209 8 3328
For the same file 11, min_block is 9 and max_block is 3209
The total blocks = 3209 - 9 + 1 = 3201. Could you explain
how do you get total blocks = 3328
Thanks
February 04, 2003 - 7:44 am UTC
You did not have the entire report. The last extent has a starting block of 3209, but an ENDING block of 3209+128-1 or 3336. Now, from that we take away 8 blocks (since min_block is 9) and we get 3328...
Here is the bigger picture with GRP included:
RELATIVE_FNO BLOCK_ID MIN_BLOCK MAX_BLOCK BLOCKS SUM_BLOCKS GRP
------------ ---------- ---------- ---------- ---------- ---------- ----------
11 9 9 3209 8 3328 0
11 17 9 3209 8 3328 0
11 25 9 3209 8 3328 0
11 33 9 3209 8 3328 0
11 41 9 3209 8 3328 0
....
11 3081 9 3209 128 3328 0
11 3209 9 3209 128 3328 0
11 3337 3337 6537 128 3328 1
11 3465 3337 6537 128 3328 1
Miles, February 08, 2003 - 2:09 pm UTC
Excellent! This is just what we wanted to do. Thanks! Tom.
Great Start but...
Skip, September 03, 2003 - 5:35 pm UTC
I am really interested in this article because I need to do essentially the same thing (split a table for multiple processes). However, I have a couple of questions. (ENV 9.2.0.3EE db)
1) Why do counts of rowid ranges force you into single thread?
2) How can I split a table into 4 equal parts? When I use your technique above I got 25 parts, my system can only handle 4 processes running at once.
Thanks
Forced single thread
Skip, September 04, 2003 - 11:54 am UTC
What I mean by forced single thread is that no matter what parallel degree the table is or if I use a parallel hint the query is always run with one process showing in v$session.
September 05, 2003 - 1:26 pm UTC
big_table@ORA920> @split BIG_TABLE 4
GRP MIN_RID MAX_RID
---------- ------------------ ------------------
0 AAAJirAALAAAAAJAAA AAAJirAALAAADAICcQ
1 AAAJirAALAAADAJAAA AAAJirAALAAAGQICcQ
2 AAAJirAALAAAGQJAAA AAAJirAALAAAJQICcQ
3 AAAJirAALAAAJQJAAA AAAJirAALAAAMgICcQ
Elapsed: 00:00:01.88
big_table@ORA920> select /*+ full(big_table) parallel(big_table,8) */
2 count(*)
3 from big_table
4 where rowid between 'AAAJirAALAAAAAJAAA' and 'AAAJirAALAAADAICcQ';
COUNT(*)
----------
856998
Elapsed: 00:00:18.11
big_table@ORA920> set autotrace traceonly explain
big_table@ORA920> /
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=608 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q23000
3 2 TABLE ACCESS* (FULL) OF 'BIG_TABLE' (Cost=608 Card=8750 Bytes=61250) :Q23000
2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ NO_EXPAND ROWID(A2) */ A2.RO
WID C0 FROM "BIG_TABLE" PX_GRANULE(0, BLOCK_
RANGE, DYNAMIC) A2 WHERE A2.ROWID>='AAAJirA
ALAAAAAJAAA' AND A2.ROWID<='AAAJirAALAAADAIC
cQ') A1
3 PARALLEL_COMBINED_WITH_PARENT
that ran in parallel. not all access paths are parallelizable.
Robert, October 27, 2003 - 3:20 pm UTC
Most efficent method
Robert, October 27, 2003 - 5:55 pm UTC
Tom,
I ran the following query on a rowid range for a table which contains approx. 1/10 of the table.
The 'date_inserted' field in the second query is not indexed.
1. Can you explain the behavior why Oracle chose to scan the Primary Key index on the 'count(*)' in the first query instead of doing table access by rowid range like the 'count(date_inserted)' did?
(as you can see, the 'count(*)' query read approximatly 5 times as many blocks as the 'count(date_inserted)' did)
2. How can we use this rowid range to do parallel full table scans by rowid range so we can take advantage of the multi block read count of full table scans, while running multiple queries on the same table?
16:19:26 SQL> set autotrace on
16:19:33 SQL> select count(*) from accounts
16:19:37 2 where rowid between chartorowid('AAAC5rACwAABn0LAAA')
16:19:37 3 and chartorowid('AAAC5rACwAABq8KEJA')
16:19:38 4
16:19:38 SQL> /
COUNT(*)
---------
361519
real: 78342
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5056 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PK_ACCOUNTS' (UNIQUE) (Cost=5
056 Card=34449 Bytes=241143)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
69124 consistent gets
66643 physical reads
0 redo size
172 bytes sent via SQL*Net to client
293 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
16:20:57 SQL>
16:39:54 SQL>
16:39:56 SQL> edit
Wrote file afiedt.buf
1 select count(date_inserted) from accounts
2 where rowid between chartorowid('AAAC5rACwAABn0LAAA')
3* and chartorowid('AAAC5rACwAABq8KEJA')
16:40:05 SQL> /
COUNT(DATE_INSERTED)
--------------------
361519
real: 21050
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25217 Card=1 Bytes=1
4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY ROWID RANGE) OF 'ACCOUNTS' (Cost=25217
Card=34449 Bytes=482286)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
10480 consistent gets
10480 physical reads
0 redo size
211 bytes sent via SQL*Net to client
305 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
16:40:28 SQL>
Thanks,
Robert.
October 27, 2003 - 6:51 pm UTC
how up to date are the stats on that index?
are they accurate?
More info
Robert, October 27, 2003 - 6:54 pm UTC
Tom,
The table was analyzed one day ago, with 'analyze table...estimate statistics'... it would not have changed significantly since then.
Thanks,
Robert.
October 27, 2003 - 7:03 pm UTC
ok, are the INDEX stats accurate -- was the INDEX analyzed a day ago as well?
see how big that index is -- the index is 7 times the size of 1/10 of the table???
the index is about 70k blocks
1/10th of the table is 10k blocks
meaning the index is 70% the size of the table in real life?
that is leading me to think the optimizer is thinking the index is smaller then it really is.
'analyze table...'
Robert, October 28, 2003 - 9:53 am UTC
Tom,
Doesn't 'analyze table...estimate statistics' automatically analyze the index stats as well?
Thanks,
Robert.
October 28, 2003 - 10:27 am UTC
it'll get the table, the indexes and histograms:
ops$tkyte@ORA920PC> create table t as select object_id, object_name from all_objects;
Table created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create index t_idx on t(object_id);
Index created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> analyze table t
2 estimate statistics
3 /
Table analyzed.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select table_name, num_rows from user_tables where table_name = 'T';
TABLE_NAME NUM_ROWS
------------------------------ ----------
T 32778
ops$tkyte@ORA920PC> select index_name, num_rows from user_indexes where table_name = 'T';
INDEX_NAME NUM_ROWS
------------------------------ ----------
T_IDX 30207
ops$tkyte@ORA920PC> select count(*) from user_tab_histograms where table_name = 'T';
COUNT(*)
----------
4
ops$tkyte@ORA920PC>
...but back to the original question, please...
Robert, October 28, 2003 - 11:00 am UTC
Tom,
Hence the fact that the 'analyze table...estimate statistics' also calculates index stats, answers your concern that the index stats were out of date.
Assuming I am giving you accurate information (which I believe I am). And according to dba_tables and dba_indexes which shows this table and indexes to have been analyzed 2 days ago...
1. Why are the queries above generating those explain plans?
...and (perhaps more importantly)...
2. How can we use rowid ranges to run multiple programs simultaniously (i.e. parallel), each with a different rowid range...all accessing the table as a full table scan, thereby getting the speed of the multi block read count in parallel.
Thanks,
Robert.
October 28, 2003 - 2:09 pm UTC
1) the optimizer thinks the index is much smaller then it is. Can you peek at the stats and see if they are even anywhere CLOSE to reality.
2) this would be an appropriate time to consider using HINTS as you have a highly specialized piece of code here. You can hint this query.
More info......
Robert, October 28, 2003 - 2:57 pm UTC
Tom,
The stats appear to be accurate...
select count(*) from accounts;
COUNT(*)
---------
13811676
select num_rows
from dba_tables
where table_name = 'ACCOUNTS';
NUM_ROWS
---------
13779291
select distinct_keys
from dba_indexes
where table_name = 'ACCOUNTS'
and index_name = 'PK_ACCOUNTS';
DISTINCT_KEYS
-------------
13230309
Can you please give an example of using hints in order to achieve a full table scan on several jobs running in parallel, each running a different rowid range.
Each job would process a different rowid range on a big table and each job would do a full table scan in order to use multi block read count.
I have your new book "Effective Oracle by Design" and in Chapter 8 (I believe) you mention doing this.. but I can't find the example(s).
Thanks,
Robert
October 28, 2003 - 9:05 pm UTC
what are the index stats on this index? are they ACCURATE.
does it make sense to you that 70k blocks are in the index but only 100k blocks are in the table???
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
Purpose of Relative_fno
Sri, January 20, 2005 - 9:22 am UTC
Dear Tom,
what is the purpose of the column relative_fno
in dba_data_files?is rfile# is same as this?
if i can use dbms_rowid to get the relative_fno
what is the purpose of having a column in dba_data_files
thanx in advance
January 20, 2005 - 10:41 am UTC
it is used to support lots and lots of files, in most databases -- relative and "real" will be the same.
it lets us have a file number relative to a tablespace instead of the entire database, raising the over all number of possible files.
about rowid of views
Harry, January 24, 2005 - 5:24 am UTC
Greeting Tom,
I respect you. Here is my question.
Rowid for a row from a table is the physical address of a row.
How does Oracle construct the rowid for a view?
SQL> select d.rowid,e.ROWID from dept d, emp e where e.deptno=d.deptno
2 and e.empno=7369;
ROWID ROWID
------------------ ------------------
AAAHW5AABAAAMUSAAB AAAJ1pAABAAAP9KAAA
SQL> create or replace view v as select d.dname, e.ename, e.empno from dept d, emp e where e.deptno=d.deptno;
View created
SQL> select rowid, dname, ename from v where empno=7369;
ROWID DNAME ENAME
------------------ -------------- ----------
AAAKNAAABAAAMaKAAX RESEARCH SMITH
SQL>
January 24, 2005 - 8:38 am UTC
the rowid comes from the base table that is "key preserved", from the table that is "updatable"
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA9IR2> create table dept as select * from scott.dept;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v as select d.dname, e.ename, e.empno from dept d,
2 emp e where e.deptno=d.deptno;
View created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select rowid from v;
select rowid from v
*
ERROR at line 1:
ORA-01445: cannot select ROWID from a join view without a key-preserved table
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table dept add constraint deptno_pk primary key(deptno);
Table altered.
<b>emp is now key preserved -- it understands that the EMP rows will appear AT MOST ONCE in the view, so rowid comes from there</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select rowid from v;
ROWID
------------------
AAAJOQAAIAAAABSAAA
AAAJOQAAIAAAABSAAB
AAAJOQAAIAAAABSAAC
AAAJOQAAIAAAABSAAD
AAAJOQAAIAAAABSAAE
AAAJOQAAIAAAABSAAF
AAAJOQAAIAAAABSAAG
AAAJOQAAIAAAABSAAH
AAAJOQAAIAAAABSAAI
AAAJOQAAIAAAABSAAJ
AAAJOQAAIAAAABSAAK
AAAJOQAAIAAAABSAAL
AAAJOQAAIAAAABSAAM
AAAJOQAAIAAAABSAAN
14 rows selected.
Harry, January 31, 2005 - 10:11 am UTC
Thanks for your quick reply. But in my new schema that is true, in my old scott schema I can not get it.
sys@PROD> @conn a/a
a@PROD> select rowid from v where rowid in (select rowid from emp);
ROWID
------------------
AAAKVXAABAAAQ+6AAA
AAAKVXAABAAAQ+6AAB
AAAKVXAABAAAQ+6AAC
AAAKVXAABAAAQ+6AAD
AAAKVXAABAAAQ+6AAE
AAAKVXAABAAAQ+6AAF
AAAKVXAABAAAQ+6AAG
AAAKVXAABAAAQ+6AAH
AAAKVXAABAAAQ+6AAI
AAAKVXAABAAAQ+6AAJ
AAAKVXAABAAAQ+6AAK
AAAKVXAABAAAQ+6AAL
AAAKVXAABAAAQ+6AAM
AAAKVXAABAAAQ+6AAN
14 rows selected.
But in my old schema, I can not get it.
scott@PROD> desc emp;
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
ID NUMBER(38)
scott@PROD> desc dept
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
scott@PROD> desc v
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
DNAME VARCHAR2(14)
ENAME VARCHAR2(10)
EMPNO NOT NULL NUMBER(4)
scott@PROD> select text from user_views where view_name='V';
TEXT
-------------------------------------------------------------------------------
select d.dname, e.ename, e.empno from dept d, emp e where e.deptno=d.deptno
scott@PROD> select rowid from emp;
ROWID
------------------
AAAJ1pAABAAAP9KAAA
AAAJ1pAABAAAP9KAAB
AAAJ1pAABAAAP9KAAC
AAAJ1pAABAAAP9KAAD
AAAJ1pAABAAAP9KAAE
AAAJ1pAABAAAP9KAAF
AAAJ1pAABAAAP9KAAG
AAAJ1pAABAAAP9KAAH
AAAJ1pAABAAAP9KAAI
AAAJ1pAABAAAP9KAAJ
AAAJ1pAABAAAP9KAAK
AAAJ1pAABAAAP9KAAL
AAAJ1pAABAAAP9KAAM
AAAJ1pAABAAAP9KAAN
14 rows selected.
scott@PROD> select rowid from dept;
ROWID
------------------
AAAHW5AABAAAMUSAAA
AAAHW5AABAAAMUSAAB
AAAHW5AABAAAMUSAAC
AAAHW5AABAAAMUSAAD
scott@PROD> select rowid from v;
ROWID
------------------
AAAKNAAABAAAMaKAAX
AAAKNAAABAAAMaKAAY
AAAKNAAABAAAMaKAAZ
AAAKNAAABAAAMaKAAa
AAAKNAAABAAAMaKAAb
AAAKNAAABAAAMaKAAc
AAAKNAAABAAAMaKAAd
AAAKNAAABAAAMaKAAe
AAAKNAAABAAAMaKAAf
AAAKNAAABAAAMaKAAg
AAAKNAAABAAAMaKAAh
AAAKNAAABAAAMaKAAi
AAAKNAAABAAAMaKAAj
AAAKNAAABAAAMaKAAk
14 rows selected.
scott@PROD> select rowid from v where rowid in (select rowid from emp);
no rows selected
January 31, 2005 - 10:16 am UTC
umm, get "what"
look at your keys there -- do you see how the primary keys dictate "what is the key preserved table"
Harry, February 05, 2005 - 9:38 pm UTC
Sorry, in my schema, there is a MATERIALIZED VIEW EMP_MLV created from emp table. When I query the view v, oracle rewrite the query to get the rowid from the MATERIALIZED VIEW EMP_MLV.
SQL> select rowid from v where rowid in (select rowid from emp_mlv);
ROWID
------------------
AAAKNAAABAAAMaKAAX
AAAKNAAABAAAMaKAAY
AAAKNAAABAAAMaKAAZ
AAAKNAAABAAAMaKAAa
AAAKNAAABAAAMaKAAb
AAAKNAAABAAAMaKAAc
AAAKNAAABAAAMaKAAd
AAAKNAAABAAAMaKAAe
AAAKNAAABAAAMaKAAf
AAAKNAAABAAAMaKAAg
AAAKNAAABAAAMaKAAh
AAAKNAAABAAAMaKAAi
AAAKNAAABAAAMaKAAj
AAAKNAAABAAAMaKAAk
14 rows selected
Only one chunk!
Neil, November 16, 2005 - 7:19 am UTC
Tom - I am getting the following output from split:
@split original 4
GRP MIN_RID MAX_RID
---------- ------------------ ------------------
3 AAB/rsAA9AAAoARAAA AAB/rsAA9AAAwAQCcQ
1 row selected.
When I make a CTAS copy of this table and split that up, it works. It also works on most of my other tables.
This table has 1396200 rows.
The dba_extents view shows this:
select count(blocks), count(block_id) from dba_extents where segment_name = 'ORIGINAL';
COUNT(BLOCKS) COUNT(BLOCK_ID)
------------- ---------------
1 1
1 row selected.
select count(blocks), count(block_id) from dba_extents where segment_name = 'COPY';
COUNT(BLOCKS) COUNT(BLOCK_ID)
------------- ---------------
567 567
1 row selected.
The original table has one extent - but why is it only showing one block?
This appears to be the reason behind the single chunk returned by split (it contains all the rows, but that kind of defeats the object!)
Is the data in the view corrupt or am I missing something?
November 16, 2005 - 5:10 pm UTC
split can only work on extent boundaries.
the maximum number of chunks is the number of extents. It works on extents itself.
Re: Only one chunk!
Neil, November 16, 2005 - 10:11 am UTC
Tom,
The table is in a large tablespace and is inheriting its storage parameters from there. The copy went into the default tablespace which is considerably smaller. In the large tablespace, the table occupies only one extent (it is expected to grow), but in the smaller one it occupies lots.
Is there a workaround for this, apart from recreating the table in a smaller tablespace? The version is 8.1.7.
November 16, 2005 - 5:59 pm UTC
define "large tablespace" and such? do you mean a locally managed tablespace with huge extents?
Re: Only one chunk!
Neil, November 17, 2005 - 4:18 am UTC
Yes - The tablespace is locally managed and the initital and next extent sizes are 134,217,728 ...
November 17, 2005 - 8:15 am UTC
I didn't get this:
and the initital and next extent sizes
are 134,217,728 ...
is that bytes? then this is just a small 128mb table (or less)? Not really a candidate for parallelism??
Only one chunk!
Neil, November 17, 2005 - 9:34 am UTC
That figure is from dba_tablespaces and presumably it's in bytes. The table contains 1.3 million rows, and is not especially large at the moment, but it will grow. I guess I'm still asking if there's any way to split a table in this fashion if it only occupies one extent. Massive extents could in theory contain massive tables after all.
November 18, 2005 - 7:17 am UTC
yes, you could. No I do not have the query. You can use the same technique, dbms_rowid to create rowid ranges, you would break the block ranges up
for example, you discover the block range is (5,145) ... (5,245) -- file 5, blocks 145..245
You could create rowid ranges from
5,145 - 5,195
5,196 - 5,245
to split into two. and then use dbms_rowid like I did to create the actual rowid - to cover row 0 from 5,145 to row 10,000 on 5,195 and so on.
multiple ranges per extent
Scott, November 30, 2005 - 11:11 am UTC
Thanks Tom. This put me on the right track for fixing an issue I am facing.
The query below will break up the table into chunks of a given number of blocks. Just change the split_size column in the mblocks subquery to control the chunk size. You should make it a factor of two for most even splits, but any number works.
It is not an issue for my purposes but small segments each get their own chunk. It shouldn't be too hard for someone to consolidate these if they needed.
WITH blocks_0 AS (
SELECT relative_fno,
block_id min_block,
lead(block_id,1) over (ORDER BY block_id) max_block,
lead(blocks, 1) over (ORDER BY block_id) blocks
FROM dba_extents
WHERE segment_name = 'FIRST_REDEMPTIONS'
AND owner = 'SVC' ORDER BY block_id
) ,
blocks AS (
SELECT *
FROM blocks_0
WHERE max_block IS NOT NULL
),
oid AS (
SELECT data_object_id
FROM all_objects
WHERE object_name = 'FIRST_REDEMPTIONS'
AND owner='SVC'
),
mblocks AS
(
SELECT MAX(blocks) max_blocks, 64 split_size FROM blocks
),
splits AS (
SELECT ROWNUM * split_size bl FROM mblocks CONNECT BY LEVEL <= max_blocks/split_size
UNION SELECT NULL FROM dual
)
SELECT dbms_rowid.rowid_create( 1, data_object_id, relative_fno, min_block, 0 ) low,
dbms_rowid.rowid_create( 1, data_object_id, relative_fno, max_block+blocks-1, 10000 ) high,
blocks, NVL(bl, blocks)
FROM blocks, oid, splits
WHERE (bl < blocks OR bl IS NULL)
ORDER BY 1,4
scratch my last post
A reader, November 30, 2005 - 1:19 pm UTC
I don't know what I was thinking with my last post. If you can delete it please do.
This query is tested:
WITH blocks AS (
SELECT relative_fno,
block_id min_block,
blocks
FROM dba_extents
WHERE segment_name = 'FIRST_REDEMPTIONS'
AND owner = 'SVC' ORDER BY block_id
) ,
oid AS (
SELECT data_object_id
FROM all_objects
WHERE object_name = 'FIRST_REDEMPTIONS'
AND owner='SVC'
),
mblocks AS
(
SELECT MAX(blocks) max_blocks, 64 split_size FROM blocks
),
splits AS (
SELECT ROWNUM * split_size bl FROM mblocks CONNECT BY LEVEL <= max_blocks/split_size
UNION SELECT NULL FROM dual
),
split_segments AS (
SELECT data_object_id, relative_fno, min_block, blocks, NVL(bl, blocks) chunk_size,
min_block+GREATEST(0,NVL(bl,blocks)-split_size) STARTb,
min_block+NVL(bl,blocks)-1 ENDb
FROM blocks, oid, splits, mblocks
WHERE (bl < blocks OR bl IS NULL)
),
rids AS (
SELECT dbms_rowid.rowid_create( 1, data_object_id, relative_fno, startb, 0 ) low,
dbms_rowid.rowid_create( 1, data_object_id, relative_fno, endb , 10000 ) high
,s.*
FROM split_segments s
)
SELECT * FROM rids
ORDER BY relative_fno, min_block,startb
DBMS_ROWID
Aru, July 19, 2006 - 10:23 pm UTC
Hi Tom,
I am trying to read about dbms_rowid package from oracle docs but got stuck right at the begineing with not understanding the following:-
1 SELECT dbms_rowid.rowid_block_number(rowid)
2 FROM scott.emp
3* WHERE ename = 'KING'
SQL> /
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
32
This would mean that the particular row with ename as King recides in the block with block_no 32. Am I right in thinking that?
But when I do:-
SQL> select segment_name, segment_type from dba_extents where block_id = 32;
no rows selected.
SQL> select block_id from dba_extents where segment_name='EMP' and owner='SCOTT';
BLOCK_ID
----------
25
This would mean that the block_id and the rowid_block_number are not the same.
Please help explain...
Regards,
Aru.
July 22, 2006 - 4:30 pm UTC
ops$tkyte%ORA10GR2> select dbms_rowid.rowid_block_number( rowid) from scott.emp where ename = 'KING';
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
6256
ops$tkyte%ORA10GR2> select block_id, block_id+blocks-1 from dba_extents
2 where segment_name = 'EMP' and owner = 'SCOTT';
BLOCK_ID BLOCK_ID+BLOCKS-1
---------- -----------------
6249 6256
the single extent of my scott.emp table covers the block range above.
6256 is one of the blocks in that range.
block_id in dba_extents is the FIRST BLOCK of that extent. You have to add in the number of blocks to see what the start AND END blocks are.
A+++++++++++++++++++++
Aru, July 23, 2006 - 5:18 pm UTC
Thanks,
Regards,
ARU.
Rowid_block_number <> block_id
Hector Gabriel Ulloa ligarius, February 14, 2007 - 12:57 pm UTC
Hi people
--Tablespace
create tablespace orderby
datafile
'/<path>/orderby01.dbf' size 1M,
'/<path>/orderby02.dbf' size 1M
extent management local uniform size 128K
segment space management manual;
--Inserting data
insert into t1
SELECT ROWNUM,
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64)
FROM all_objects
WHERE ROWNUM < 21;
--Retrieve data of rowid_block_number
SQL> select
2 dbms_rowid.rowid_to_absolute_fno(rowid, 'SYSTEM', 'T1') ABS_FNO,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO, -- Last block used
5 dbms_rowid.rowid_row_number(rowid) ROWNO,
6 a
7 from t1 where a = 1;
ABS_FNO REL_FNO BLOCKNO ROWNO A
---------- ---------- ---------- ---------- ----------
25 25 10 0 1
--Consulting data of blocks
SQL> select block_id ,
block_id + blocks - 1
from dba_extents
where segment_name = 'T1';
BLOCK_ID BLOCK_ID+BLOCKS-1
---------- -----------------
9 24
The dba_extents.block_id = 9 not same with
dbms_rowid.rowid_row_number(rowid) = 10
Why?
Thank you very much....
Regards
Ligarius
htp://ligarius.wordpress.com
February 14, 2007 - 2:29 pm UTC
why do you say "last block used", I would presume that to be no the last block used.
I'm not really sure what you are looking for - but remember, the first bit of a segment is used by Oracle to manage the segment itself.
Findout rowids for extent grater than some number
A reader, July 26, 2007 - 9:25 am UTC
Tom,
Thanks for your help to Oracle professionals.
My question is:
Is there a way through which I can find out ROWIDs of records for a given table where blocks were allocated from a extent i.e. where extent number(extent_id) is 50 or whatever extent_id I provide?
In short, how can I find ROWID for records providing just extent_id in my sql query?
Can you please show an example if you have any?
Cheers,
July 27, 2007 - 8:49 am UTC
ops$tkyte%ORA10GR2> create table t as select * from all_objects;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select e.extent_id, e.block_id, e.block_id+blocks-1,
2 dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id, 0 ) min_rowid,
3 dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id+e.blocks-1, 10000 ) max_rowid
4 from dba_extents e,
5 user_objects o
6 where o.object_name = 'T'
7 and e.segment_name = 'T'
8 and e.owner = user
9 and e.segment_type = 'TABLE'
10 order by e.extent_id
11 /
EXTENT_ID BLOCK_ID E.BLOCK_ID+BLOCKS-1 MIN_ROWID MAX_ROWID
---------- ---------- ------------------- ------------------ ------------------
0 4129 4136 AAAh+UAAEAAABAhAAA AAAh+UAAEAAABAoCcQ
1 4137 4144 AAAh+UAAEAAABApAAA AAAh+UAAEAAABAwCcQ
2 4145 4152 AAAh+UAAEAAABAxAAA AAAh+UAAEAAABA4CcQ
3 4153 4160 AAAh+UAAEAAABA5AAA AAAh+UAAEAAABBACcQ
4 4161 4168 AAAh+UAAEAAABBBAAA AAAh+UAAEAAABBICcQ
5 4169 4176 AAAh+UAAEAAABBJAAA AAAh+UAAEAAABBQCcQ
6 4177 4184 AAAh+UAAEAAABBRAAA AAAh+UAAEAAABBYCcQ
7 4185 4192 AAAh+UAAEAAABBZAAA AAAh+UAAEAAABBgCcQ
8 4193 4200 AAAh+UAAEAAABBhAAA AAAh+UAAEAAABBoCcQ
9 4201 4208 AAAh+UAAEAAABBpAAA AAAh+UAAEAAABBwCcQ
10 4209 4216 AAAh+UAAEAAABBxAAA AAAh+UAAEAAABB4CcQ
11 4217 4224 AAAh+UAAEAAABB5AAA AAAh+UAAEAAABCACcQ
12 4225 4232 AAAh+UAAEAAABCBAAA AAAh+UAAEAAABCICcQ
13 4233 4240 AAAh+UAAEAAABCJAAA AAAh+UAAEAAABCQCcQ
14 4241 4248 AAAh+UAAEAAABCRAAA AAAh+UAAEAAABCYCcQ
15 4249 4256 AAAh+UAAEAAABCZAAA AAAh+UAAEAAABCgCcQ
16 4361 4488 AAAh+UAAEAAABEJAAA AAAh+UAAEAAABGICcQ
17 4489 4616 AAAh+UAAEAAABGJAAA AAAh+UAAEAAABIICcQ
18 4617 4744 AAAh+UAAEAAABIJAAA AAAh+UAAEAAABKICcQ
19 4745 4872 AAAh+UAAEAAABKJAAA AAAh+UAAEAAABMICcQ
20 4873 5000 AAAh+UAAEAAABMJAAA AAAh+UAAEAAABOICcQ
21 rows selected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count(*) from t;
COUNT(*)
----------
49916
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data as
2 (
3 select e.extent_id, e.block_id, e.block_id+blocks-1,
4 dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id, 0 ) min_rowid,
5 dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id+e.blocks-1, 10000 ) max_rowid
6 from dba_extents e,
7 user_objects o
8 where o.object_name = 'T'
9 and e.segment_name = 'T'
10 and e.owner = user
11 and e.segment_type = 'TABLE'
12 )
13 select extent_id, count(*) cnt
14 from data, t
15 where t.rowid between data.min_rowid and data.max_rowid
16 group by rollup (extent_id)
17 /
EXTENT_ID CNT
---------- ----------
0 437
1 654
2 566
3 647
4 592
5 677
6 563
7 646
8 545
9 635
10 564
11 620
12 543
13 625
14 541
15 575
16 8870
17 8854
18 8857
19 9147
20 4758
49916
22 rows selected.
Findout rowids for extent grater than some number
A reader, July 29, 2007 - 11:25 pm UTC
Thanks a lot Tom. Exactly what I was looking for!
Cheers,
Map dbms_rowid.rowid_block_number(rowid) to Dba_extents
Snehasish Das, December 21, 2012 - 1:49 am UTC
Hi Tom,
How do we map dbms_rowid.rowid_block_number(rowid) to the DBA_EXTENTS view. The DBA_EXTENTS view has block_id but that is not the same as the block_number returned by
dbms_rowid.rowid_block_number(rowid).
What I practically wanted to see was what data is stored in the extents (extend_id). Like
select T.*,dbms_rowid.rowid_block_number(rowid) from TABL T where dbms_rowid.rowid_block_number(rowid) = XXXX
Regards,
Snehasish Das
January 02, 2013 - 12:32 pm UTC
dba_extents has the starting block number and number of blocks - so when you join to it, you need to use >= and <
something like this:
ops$tkyte%ORA11GR2> create table t
2 as
3 select * from all_objects;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> with data
2 as
3 (
4 select dbms_rowid.rowid_block_number(rowid) bno,
5 dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfno,
6 object_id
7 from t
8 where mod(object_id,100) = 0
9 )
10 select d.object_id, d.bno, de.extent_id
11 from data d, dba_extents de
12 where d.bno >= de.block_id
13 and d.bno < de.block_id+de.blocks
14 and de.relative_fno = d.rfno
15 and de.owner = user
16 and de.segment_name = 'T'
17 /
OBJECT_ID BNO EXTENT_ID
---------- ---------- ----------
100 110524 0
300 110526 0
400 110527 0
500 110528 1
600 110529 1
700 110530 1
800 110531 1
900 110532 1
1000 110533 1
1100 110534 1
1200 110537 2
1300 110538 2
1400 110539 2
1500 110540 2
1600 110541 2
1700 110543 2
1800 110544 3
1900 110545 3
2000 110547 3
2100 110548 3
...