This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Jan 3, 2010 5:10 PM by 705031 Go to original post RSS
  • 15. Re: Can Oracle scan from one single data file only?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    This :
     insert into t_table_name_rowid select rowid, rownum from t_table_name;
    requires a Full Table Scan.

    However, if you query based on the Primary Key / NOT NULL Unique Key alone, Oracle would read the ROWIDs from the Index.


    Hemant K Chitale
  • 16. Re: Can Oracle scan from one single data file only?
    705031 Newbie
    Currently Being Moderated
    In the test cases I have run, I have been carefully tested and monitored and Oracle has only scaned the primary key.
  • 17. Re: Can Oracle scan from one single data file only?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    If you want to parallelise the insert .. select, ensure that you

    a) ALTER SESSION ENABLE PARALLEL DML;

    b) Put a PARALLEL hint in both the INSERT and the SELECT clauses.


    Hemant K Chitale
  • 18. Re: Can Oracle scan from one single data file only?
    705031 Newbie
    Currently Being Moderated
    It seems the trick does not work on my version, which is running 10.2.0.4 on Solaris 10. It seems the ROWID in the last row not always greater than the ROWID of the first row, as shown below. So I can not use a range of ROWID to determine all the rows for belonging to one single extent.

    Later I will test out whether I can generate a complete list of ROWID (for 723,100,541 rows?), including some artificial ones, and groups evenly. It will then depend on how efficient the list of ROWID can be generated and partitioned, compared to the mere grouping by the first column of the primary key.

    <pre>
    SQL> with my_obj as (select '<table name>' as object_name, 100 as max_row_num, 1 as rowid_type from dual)
    2 select extent_id, file_id, block_id, blocks, object_id, block_id+blocks-1 as last_block_id,
    3 dbms_rowid.rowid_create(rowid_type,object_id,file_id,block_id,0) as first_rowid,
    4 dbms_rowid.rowid_create(rowid_type,object_id,file_id,block_id+blocks-1,max_row_num) as last_rowid
    5 from dba_extents a, user_objects b, my_obj c
    6 where a.segment_name=c.object_name and b.object_name=c.object_name
    7 and a.extent_id=1277;

    EXTENT_ID FILE_ID BLOCK_ID BLOCKS OBJECT_ID LAST_BLOCK_ID FIRST_ROWID LAST_ROWID
    ---------- ---------- ---------- ---------- ---------- ------------- ------------------ ------------------
    1277 7 507913 3968 7502 511880 AAAB1OAAHAAB8AJAAA AAAB1OAAHAAB8+IABk

    SQL> select sysdate from dual where 'AAAB1OAAHAAB8AJAAA'<'AAAB1OAAHAAB8+IABk';

    no rows selected

    SQL>
    </pre>

    The last select statement is trying to show that the ROWID in the first row is somehow greater than the ROWID in the last row, but the last ROWID not shown properly.

    Thanks for the idea.

    Edited by: LIU GaoYuan on Jan 3, 2010 12:11 AM
  • 19. Re: Can Oracle scan from one single data file only?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    In the test cases I have run, I have been carefully tested and monitored and Oracle has only scaned the primary key.
    Ah yes. As it is a Primary Key, Oracle intelligently scans only the index without you having to specify the key columns -- akin to how it does a count(*) without reading the table.
    However, you can still parallelise the index read and insert into your rowids table.

    Hemant K Chitale
  • 20. Re: Can Oracle scan from one single data file only?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    LIU GaoYuan wrote:
    It seems the trick does not work on my version, which is running 10.2.0.4 on Solaris 10. It seems the ROWID in the last row not always greater than the ROWID of the first row, as shown below. So I can not use a range of ROWID to determine all the rows for belonging to one single extent.
    It seems to work okay for me (on 10.2.0.3). [+*I ran up a quick test*+|http://jonathanlewis.wordpress.com/2010/01/03/pseudo-parallel/] to check it.

    A couple of points - if you want to use dbms_rowid() the calls take the data_object_id as a parameter, not the object_id, and the highest rowid in in a block is (currently 4095.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 21. Re: Can Oracle scan from one single data file only?
    705031 Newbie
    Currently Being Moderated
    Thanks, Jonathan.

    My previous response this afternoon may be due to some misunderstanding of the ROWID in Oracle.

    Below is my test, which should have explained why the method you suggest would work. However, I have not found out why the select by ROWID behaves in this way.

    <pre>
    -- Create the view to get the range of ROWID for individual extent:

    create view raw_rowid as
    with my_obj as (select '<owner>' as owner, '<table name>' as object_name, 4095 as max_row_num, 1 as rowid_type from dual)
    select extent_id, file_id, block_id, blocks, object_id, block_id+blocks-1 as last_block_id,
    dbms_rowid.rowid_create(rowid_type,data_object_id,file_id,block_id,0) as first_rowid,
    dbms_rowid.rowid_create(rowid_type,data_object_id,file_id,block_id+blocks-1,max_row_num) as last_rowid
    from dba_extents a, user_objects b, my_obj c
    where a.owner=c.owner and a.segment_name=c.object_name and b.object_name=c.object_name

    -- Create another view to get all the ROWIDs
    create view rowid_list as
    select extent_id, first_rowid as myrowid, 1 as src from raw_rowid union all
    select extent_id, last_rowid, 2 as src from raw_rowid

    -- To be able to identify the extents by range of ROWID, two assumptions would be met:
    -- 1) The ROWID of the first row in the extent must be less than the ROWID of the last possible row in the extent
    -- 2) The ROWID range for one extent should not include other rows from another extent

    -- To test the first condition:

    SQL> select count(*) from raw_rowid where first_rowid>last_rowid;

    COUNT(*)
    ----------
    101

    SQL> select * from raw_rowid where extent_id=1485 and first_rowid>last_rowid;

    EXTENT_ID FILE_ID BLOCK_ID BLOCKS OBJECT_ID LAST_BLOCK_ID FIRST_ROWID LAST_ROWID
    ---------------------------------------------------------------------------------------------------
    1485 129 775049 8192 7502 783240 AAAB1OACBAAC9OJAAA AAAB1OACBAAC/OIA//

    SQL> select /*+ rowid(t) */ count(*) from t_ext_samples_raw t where rowid between 'AAAB1OACBAAC9OJAAA' and 'AAAB1OACBAAC/OIA//';

    COUNT(*)
    ----------
    1756877

    SQL>

    -- The above query shows although literally the first ROWID in extent 1485 is greater than the last ROWID in the same extent, Oracle would still be able to select the data by specifying the ROWID range.

    -- Notice from the test that although first_rowid>last_rowid, Oracle is smart enough to get the right data with a "between" condition?

    -- To test the second condition:

    1 select count(*)
    2 from (
    3 select extent_id, max(seq), min(seq)
    4 from (
    5 select extent_id, myrowid, src, row_number()over(order by myrowid) as seq
    6 from rowid_list)
    7 group by extent_id
    8* having max(seq)-min(seq)!=1)
    SQL> /

    COUNT(*)
    ----------
    158

    -- To verify the second condition:

    SQL> select * from raw_rowid where extent_id=1275;

    EXTENT_ID FILE_ID BLOCK_ID BLOCKS OBJECT_ID LAST_BLOCK_ID FIRST_ROWID LAST_ROWID
    ---------------------------------------------------------------------------------------------------
    1275 7 500105 7808 7502 507912 AAAB1OAAHAAB6GJAAA AAAB1OAAHAAB8AIA//

    SQL> select sysdate from dual where 'AAAB1OAAHAAB6GJAAA'<'AAAB1OAAHAAB8AIA//';

    SYSDATE
    --------------------
    04-Jan-2010 00:00:23

    SQL> select * from raw_rowid where first_rowid between 'AAAB1OAAHAAB6GJAAA' and 'AAAB1OAAHAAB8AIA//'
    2 or last_rowid between 'AAAB1OAAHAAB6GJAAA' and 'AAAB1OAAHAAB8AIA//';

    EXTENT_ID FILE_ID BLOCK_ID BLOCKS OBJECT_ID LAST_BLOCK_ID FIRST_ROWID LAST_ROWID
    ---------------------------------------------------------------------------------------------------
    1275 7 500105 7808 7502 507912 AAAB1OAAHAAB6GJAAA AAAB1OAAHAAB8AIA//
    1277 7 507913 3968 7502 511880 AAAB1OAAHAAB8AJAAA AAAB1OAAHAAB8+IA//

    -- Get the data from both extents:
    SQL> create table mytab as select PKC1, PKC2, 1 as src from t_table_name where 1=0;

    Table created.

    SQL> insert into mytab select /*+ rowid(t) */ PKC1, PKC2, 1 as src from t_table_name t where rowid between 'AAAB1OAAHAAB6GJAAA' and 'AAAB1OAAHAAB8AIA//';

    1378189 rows created.

    SQL> commit;

    Commit complete.

    SQL> insert into mytab select /*+ rowid(t) */ PKC1, PKC2, 2 as src from t_table_name t where rowid between 'AAAB1OAAHAAB8AJAAA' and 'AAAB1OAAHAAB8+IA//';

    687660 rows created.

    SQL> commit;

    Commit complete.

    SQL> select PKC1, PKC2 from mytab group by PKC1, PKC2 having count(*)>1;

    no rows selected

    -- The above query shows there is no overlapping data from the two extents.

    -- Notice that Oracle will not pull the data from the second extent, although the first_rowid of extent 1277 is literally between the first and last rowid of extent 1275

    </pre>

    Edited by: LIU GaoYuan on Jan 3, 2010 8:34 AM

    Edited by: LIU GaoYuan on Jan 3, 2010 8:39 AM
  • 22. Re: Can Oracle scan from one single data file only?
    705031 Newbie
    Currently Being Moderated
    I just finished the very first test by dividing into 32 ROWID ranges, and the whole process completes in about 19 minutes for 723m rows, compared to 22/23 minutes with the primary key partitioning.

    Some sessions complete in a few minutes as the extent size is quite small.

    Now the main wait event is on "log file switch (checkpoint incomplete)", which is expected although I have created total 10 groups with 200MB each.

    The number of rows inserted tally with the previous tests.

    Thanks to Jonathan.
  • 23. Re: Can Oracle scan from one single data file only?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    LIU GaoYuan wrote:{quote
    SQL> select * from raw_rowid where extent_id=1485 and first_rowid>last_rowid;

    EXTENT_ID FILE_ID BLOCK_ID BLOCKS OBJECT_ID LAST_BLOCK_ID FIRST_ROWID LAST_ROWID
    ---------------------------------------------------------------------------------------------------
    1485 129 775049 8192 7502 783240 AAAB1OACBAAC9OJAAA AAAB1OACBAAC/OIA//
    My first guess here was that the character representation of a rowid may not sort in the same order as the rowid, and that somehow this query is comparing character strings rather than real rowid types. Simple check:
    set linesize 50
    
    select
         dump(chartorowid('AAAB1OACBAAC9OJAAA'),16) lowrid,
         dump(chartorowid('AAAB1OACBAAC/OIA//'),16) highrid
    from
    dual
    ;
    
    LOWRID
    ----------------------------------------
    HIGHRID
    -----------------------------------------
    Typ=69 Len=10: 0,0,1d,4e,20,4b,d3,89,0,0
    Typ=69 Len=10: 0,0,1d,4e,20,4b,f3,88,f,ff
    QED.

    Alternatively:
    select
         user 
    from
         dual
    where
         chartorowid('AAAB1OACBAAC9OJAAA') < chartorowid('AAAB1OACBAAC/OIA//')
    ;
    
    
    select
         user 
    from
         dual
    where
         'AAAB1OACBAAC9OJAAA' < {noformat}'AAAB1OACBAAC/OIA//'{noformat}
    ;
    One of these queries returns a row, the other doesn't.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 24. Re: Can Oracle scan from one single data file only?
    705031 Newbie
    Currently Being Moderated
    Thanks Jonathan for all your input.

    Just to share more information as why I am doing such odd thing. Below are some tests I have conducted to rebuild a table with huge BLOB data. The parallel DML version is still running, but I don't see any better performance than a normal rebuild without any parallelism. The reason maybe the table contains a huge BLOB column.

    <pre>

    -- Rebuild performance with "insert .. select .." only, without any parallelism
    -- Finish in 4h15m

    SNAP_TIME SEGMENT_NAME MB
    ----------------------------------------------------------------------
    30-Dec-2009 21:29:32 CKC_BLOB 768
    30-Dec-2009 21:34:32 CKC_BLOB 1344
    30-Dec-2009 21:39:33 CKC_BLOB 1920
    30-Dec-2009 21:44:33 CKC_BLOB 2496
    30-Dec-2009 21:49:33 CKC_BLOB 3136
    30-Dec-2009 21:54:33 CKC_BLOB 3776
    30-Dec-2009 22:04:33 CKC_BLOB 4928
    30-Dec-2009 22:09:33 CKC_BLOB 5504
    30-Dec-2009 22:14:33 CKC_BLOB 6080
    30-Dec-2009 22:19:36 CKC_BLOB 6656
    30-Dec-2009 22:24:33 CKC_BLOB 7232
    30-Dec-2009 22:29:33 CKC_BLOB 7808
    30-Dec-2009 22:34:32 CKC_BLOB 8384
    30-Dec-2009 22:39:33 CKC_BLOB 8896
    ...

    -- Rebuild performance with "manual parallelism"
    -- Finish in less than 20m

    SNAP_TIME SEGMENT_NAME MB
    ----------------------------------------------------------------------
    31-Dec-2009 22:03:59 CKC_BLOB 64
    31-Dec-2009 22:04:32 CKC_BLOB 768
    31-Dec-2009 22:09:32 CKC_BLOB 10368
    31-Dec-2009 22:14:32 CKC_BLOB 20224
    31-Dec-2009 22:19:32 CKC_BLOB 30208
    31-Dec-2009 22:23:53 CKC_BLOB 35968

    -- Rebuild performance with Oracle DML parallelism
    -- Don't observe better performance than without parallelism

    SQL> alter table t_table_name_reorg nologging;

    Table altered.

    Elapsed: 00:00:00.00
    SQL> ALTER SESSION ENABLE PARALLEL DML;

    Session altered.

    Elapsed: 00:00:00.00
    SQL> insert /*+ parallel(t1 32) */ into t_table_name_reorg t1 select /*+ parallel(t2 32) */ * from t_table_name t2;


    SNAP_TIME SEGMENT_NAME MB
    ----------------------------------------------------------------------
    04-Jan-2010 02:40:34 CKC_BLOB 64
    04-Jan-2010 02:44:31 CKC_BLOB 384
    04-Jan-2010 02:49:31 CKC_BLOB 832
    04-Jan-2010 02:54:31 CKC_BLOB 1344
    ..still running
    </pre>

    I have just tested using Oracle Parallel DML for a table without BLOB, and the performance was much better than my expectation.
  • 25. Re: Can Oracle scan from one single data file only?
    601262 Explorer
    Currently Being Moderated
    FYI - making a table nologging but not using
    insert /*+ append */
    is still a logging insert.

    What is the reasoning against using PDML and doing this manually? Seems to me to be a bit like reinventing the wheel, no?

    --
    Regards,
    Greg Rahn
    http://structureddata.org
  • 26. Re: Can Oracle scan from one single data file only?
    705031 Newbie
    Currently Being Moderated
    If /*+ append */ is specified, all the parallel sessions would be having HW contention.

    Just ignore the nologging in this case
  • 27. Re: Can Oracle scan from one single data file only?
    705031 Newbie
    Currently Being Moderated
    The parallel running completed after 6h25m. The key seems to the BLOB fetching consumes most of the elapsed time.

    <pre>
    SNAP_TIME SEGMENT_NAME MB
    ----------------------------------------------------------------------
    04-Jan-2010 02:40:34 CKC_BLOB 64
    04-Jan-2010 02:44:31 CKC_BLOB 384
    04-Jan-2010 02:49:31 CKC_BLOB 832
    ...
    04-Jan-2010 09:04:30 CKC_BLOB 35712
    04-Jan-2010 09:06:24 CKC_BLOB 35840

    Elapsed: 06:25:44.13
    </pre>
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points