1 2 Previous Next 27 Replies Latest reply: Jan 3, 2010 7:10 PM by 705031 Go to original post RSS
      • 15. Re: Can Oracle scan from one single data file only?
        Hemant K Chitale
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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