1 2 Previous Next 27 Replies Latest reply on Jan 4, 2010 1:10 AM by 705031

    Can Oracle scan from one single data file only?

    705031
      Hi,

      Understand that we can use to get the data from a single data file only, but the following will indeed incur a full table scan for all the data files containing the data for the table.

      select * from <table_name> where dbms_rowid.rowid_relative_fno(rowid)=<file number>;

      Is there any way to ask Oracle to scan only the desired file only?

      Regards

      LIU GaoYuan
        • 1. Re: Can Oracle scan from one single data file only?
          Aman....
          LIU GaoYuan wrote:
          Hi,

          Understand that we can use to get the data from a single data file only, but the following will indeed incur a full table scan for all the data files containing the data for the table.

          select * from <table_name> where dbms_rowid.rowid_relative_fno(rowid)=<file number>;

          Is there any way to ask Oracle to scan only the desired file only?
          I may have not understood completely and if I didn't, apologies for it. But how are you passing the file number here? When you are using the rowid, that's already going to be a fastest access isn't it?

          Aman....
          • 2. Re: Can Oracle scan from one single data file only?
            Hemant K Chitale
            If you query by ROWID, you should be generating a list of ROWIDs for the specific file only that you want.
            • 3. Re: Can Oracle scan from one single data file only?
              705031
              The problem is that even I can get the data for a table from one data file only by using procedure, Oracle will still scan the whole table, which I am trying to avoid.

              The reason I am looking for this is that I am looking for some "manual parallelism" to speed up my table reorg process.

              If the table has a relevatively small primary key, I will be able to get all the ROWID very fast, then I can partition the ROWID into small groups, so that I can reorg the table by the groups of ROWID, which will be the fastest way to reorg, like what I had done once for one BLOB rebuild at 2007.

              However, the table I am studying now has a big primary key, hence I can not apply the same technique.

              For testing purpose, I am doing "manually parallelism" by partitioning the data into 32 groups using the primary key, so that I can still achieve much better performance - reducing the reorg time by more than 30% for a table with about 100GB.

              However, my monitoring shows three areas which may be improved further:
              - the 32 sessions are spending quite some elapsed time in waiting for other sessions to fetch the data - the physical storage is not always in-sync with the primary key sequence
              - the number of rows in the 32 sessions are not evenly distributed. It ends up different sessions can have very different elapsed time
              - the data has in fact spread across about 71 files, and the most data in one file is no more than 2GB. If there is a way to force Oracle to scan only one data file, there may be a chance to start 71 sessions concurrently, and the 71 sessions would have no data overlapped, and I will expect this to have the best performance.

              Regards

              LIU GaoYuan
              • 4. Re: Can Oracle scan from one single data file only?
                705031
                Hi,

                Thanks for reply.

                What I am looking for is whether there is a way to force Oracle to scan only one single data file.

                Regards

                LIU GaoYuan
                • 5. Re: Can Oracle scan from one single data file only?
                  705031
                  To get the complete list of ROWID may take almost the same amount of time as to scan the whole table, as the primary key itself is almost the same size as the table...
                  • 6. Re: Can Oracle scan from one single data file only?
                    Hemant K Chitale
                    The problem is that even I can get the data for a table from one data file only by using procedure, Oracle will still scan the whole table, which I am trying to avoid.
                    Why ?
                    OR
                    Why do you think so ?
                    • 7. Re: Can Oracle scan from one single data file only?
                      705031
                      I did the test by specifying:

                      where dbms_rowid.rowid_relative_fno(rowid)=15;

                      From v$session_wait, I could see the the data being scanned from file 15, 18, ... etc.

                      Also from v$session_longops, I could see that Oracle was scaning the whole table.
                      • 8. Re: Can Oracle scan from one single data file only?
                        Pavan Kumar
                        Hi,

                        AFAIK, You can't avoid it, since data is scattered across the data files.

                        - Pavan Kumar N
                        Oracle 9i/10g - OCP
                        http://oracleinternals.blogspot.com/
                        • 9. Re: Can Oracle scan from one single data file only?
                          705031
                          Another interesting observation is that by partitioning the primary key into 32 sessions, Oracle will do a range scan on the primary key, then sequencial scan the table.

                          I used the hint to force all the sessions to use full table scan since anyway I need to fetch the full table, the overall elapsed time is reduced by about 20%.

                          But just imaging, if we can have some pseudo column FILEID or EXTENT_ID like ROWID in a table, and if we specify FILEID or EXTENT_ID and Oracle knows exactly it needs to scan only that particular file, we can significantly improve the table rebuild performance by manual parallelism.
                          • 10. Re: Can Oracle scan from one single data file only?
                            sb92075
                            reducing the reorg time by more than 30% for a table with about 100GB.
                            What is the net result after doing table reorg?
                            • 11. Re: Can Oracle scan from one single data file only?
                              Hemant K Chitale
                              I had been thinking along the same lines.

                              1. Identify all the FILE_IDs (querying DBA_DATA_FILES for the Tablespace, as this would be faster -- it doesn't matter if one or more of the datafiles doesn't contain that table's extents)

                              2. Create a "Mapping" Table with two Columns FILE_ID and ROWID (type ROWID), preferably List Partitioned by FILE_ID (since you have identified all the possible FILE_IDs in advance)
                              create table mapping_table (file_id number, source_rowid rowid) (partition by file_id ......);
                              3. Use the Unique index on your large table to populate the Mapping Table. This relies on using the Primary Key (or NOT NULL Unique Key) on the table to extract the ROWIDs from the Index itself
                              insert into mapping_table 
                              select dbms_rowid.rowid_to_absolute_fno(rowid,'MYSCHEMA','MYLARGETABLE') , rowid
                              from myschema.mylargetable 
                              where primary_key_value > 0;
                              4. Now we have MAPPING_TABLE with 1 partition for each file_id containing all the rowids of MYLARGETABLE in that file_id alone.

                              5. Create the new reorg table
                              create my_new_table as select * from mylargetable where 1=2;
                              6. Insert into your new, reorg, table with one SQL Session per file_id --- thus running multiple sessions in parallel
                              insert into my_new_table
                              select * from mylargetable
                              where rowid in (select rowid from mapping_table where file_id=1);
                              The above is for file_id 1. Similarly, run a separate session for file_id 2 and so on -- you have already identified all the possible file_ids in Step 1.
                              Therefore, you can run, say, 36, different insert statements in manual parallelism.

                              You'd have to handle LONG and LOB columns and Indexes seperately.

                              This assumes that no new ROWIDs are loaded or ROWIDs are changed after you begin the process.

                              Hemant K Chitale
                              http://hemantoracledba.blogspot.com

                              Edited by: Hemant K Chitale on Jan 2, 2010 11:06 PM
                              1 person found this helpful
                              • 12. Re: Can Oracle scan from one single data file only?
                                Jonathan Lewis
                                Here's *[a link to something |http://www.jlcomp.demon.co.uk/big_upd.html]* I did more than 10 years ago with Oracle 7. The same idea may still work with the newest versions.

                                The trick is to remember that the extents of an object are file-based, so you can take a list of extents for an object and create first and last possible rowids for each extent by playing about with the file_id, block_id, and (since rowids changed after v7) object id. Then select where rowid between "first and last" for each extent that's in each file that you want. A /*+ rowid */ hint stops Oracle from switching to a full tablescan.

                                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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                1 person found this helpful
                                • 13. Re: Can Oracle scan from one single data file only?
                                  705031
                                  Step 1) This is the code I am using to partition the row ID into the number of concurrent sessions I intend to insert, where $NTILE defines the number of concurrent sessions.

                                  <pre>
                                  truncate table t_table_name_rowid;

                                  insert into t_table_name_rowid select rowid, rownum from t_table_name;

                                  truncate table t_table_name_rowid_ntile;

                                  insert into t_table_name_rowid_ntile
                                  select myrowid, myrownum, ntile($NTILE) over(order by myrowid) as myntile
                                  from t_table_name_rowid;
                                  </pre>

                                  Step 2) Below is the code I am using to start the different insert sessions:

                                  <pre>
                                  INSERT INTO T_TABLE_NAME_REORG tcn
                                  ( select *
                                  from t_table_name
                                  where rowid in (select myrowid from t_table_name_rowid_ntile where myntile=NTILE)
                                  ) ;
                                  </pre>

                                  The key factor is whether and how we can get the complete list of ROWID and partition the ROW ID as fast as possible. If the primary key size is small, in my successful case the primary is about 300MB while the table+BLOB is about 50GB, I can build the ROWID range(ntile) in less than three minutes, and completes the whole rebuild in about 20 minutes (HW is good, and Veritas ODM is enabled); If to run in single stream with either "alter table move" or "insert .. select", the process takes about 4 to 5 hours.

                                  However, when I try to deploy the same into another big table, which has primary key size of about 50GB, it takes about 10minutes to build up the ROW ID range in step 1. If I am using the first column of the primary key to partition the data into 32 sessions, the whole rebuild process takes about 22minutes (with full table scan hint) or about 32 minutes (range scan index then sequential scan the table data) - some sessions complete in about 15 minutes with 4m rows, while others may take 32 minutes with 40m+ rows.

                                  Another interesting point is about the parallel hint in such process. In all the test cases so far I have done with similar requirement (insert ... select /*+ parallel() */), I have not observed any improvement on the the elapsed time, although I do notice multiple oracle sessions are being initiated. That is why I am trying to build "manual parallelism".
                                  • 14. Re: Can Oracle scan from one single data file only?
                                    705031
                                    Thanks. This is a greate idea.

                                    In this case we can actual define the complete list of (partial artificial) ROWID range for each extent, and group ROWID by either extent or file_id.

                                    I will test out and post the performance result soon.
                                    1 2 Previous Next