1 2 Previous Next 16 Replies Latest reply: Aug 10, 2009 8:46 AM by 716323 RSS

    External Tables

    716323
      Hi,

      Iam using external tables to reda my external file,I have more than 5000 records in it,
      Now I want to read only my first 1000 records in external table,
      how shoul I do it ? and if do not know how many records I have in my data file and there is a page break in my file
      will exernal table understand the page break?

      Please assist

      Yazhini.T
        • 1. Re: External Tables
          sb92075
          Now I want to read only my first 1000 records in external table,
          Do it the same as you would do for a regular table.
          will exernal table understand the page break?
          A page break is unknown to RDBMS.
          A page break meaningful for Report Generator.
          Rows in a table have NO inherent order.
          • 2. Re: External Tables
            EdStevens
            user8707806 wrote:
            Hi,

            Iam using external tables to reda my external file,I have more than 5000 records in it,
            Now I want to read only my first 1000 records in external table,
            how shoul I do it ? and if do not know how many records I have in my data file and there is a page break in my file
            will exernal table understand the page break?

            Please assist

            Yazhini.T
            Page break? I would be highly surprised if that didn't cause problems. As part of defining the external table you have to describe the data in sqlloader terms. How would you account that every so many records you have this extra control character?

            Have you actually tried it? If not, why?

            Also, as already pointed out, tables have no inherent order in a relational database. Even external tables. There is no concept of "first" in the table.
            • 3. Re: External Tables
              Hoek
              Hi and welcome to the forum!
              Now I want to read only my first 1000 records in external table, how shoul I do it?
              In case the already provided answers aren't specific enough:
              select *
              from   your_external_table
              where  rownum <= 1000;
              http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#SQLRF00255
              • 4. Re: External Tables
                EdStevens
                hoek wrote:
                Hi and welcome to the forum!
                Now I want to read only my first 1000 records in external table, how shoul I do it?
                In case the already provided answers aren't specific enough:
                select *
                from   your_external_table
                where  rownum <= 1000;
                http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#SQLRF00255
                The OP needs to understand that ROWNUM refers to the row number within the result set, not within the table.
                • 5. Re: External Tables
                  Hoek
                  Hi Ed,

                  wow, you're fast ;)

                  OK, I think I get your point, thanks for reminding that sometimes things are a bit more 'nuanced' on second look.


                  To Yazhini:

                  If you don't care at all which 1000 rows you need to get from your external table, then the rownum example should fulfill your requirement.
                  On the other hand, if that's not the case: you'll need to identify one or more columns to sort on and use an ORDER BY.
                  That would be a TOP-N query.
                  First you select your 5000+ records sorted, and from that set you pick your desired 1000 records.

                  In short: there's no 'sorted data guarantee' at all, unless you use an ORDER BY

                  You can read more insights/examples here:
                  http://tkyte.blogspot.com/2005/08/order-in-court.html
                  http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html
                  • 6. Re: External Tables
                    26741
                    Yes, ROWNUM is applied after the WHERE predicates (other than ROWNUM).

                    Let's say you have a query on a (regular) table as
                    select * from regular_table;
                    would it be guaranteed to always return rows in the same order ? NO, because rowids, positions of rows in blocks etc can change with INSERT/ row migration/ block coalescion / DELETE (and, of course, Export-Import).

                    BUT say that the table is an EXTERNAL Table. An External table (at least in current versions !) undergoes no DML. Would rows always be returned in the same order if NO other predicates are supplied in the WHERE clause ?

                    Then, again, think of another complication in FullTableScans. In a FullTableScan, Oracle may even split a multiblock read into smaller multiblock reads if some blocks are present in the buffer cache.
                    How would this work if the table is an EXTERNAL Table and some rows have been retrieved earlier ?

                    Hemant K Chitale
                    http://hemantoracledba.blogspot.com
                    • 7. Re: External Tables
                      EdStevens
                      Hemant K Chitale wrote:
                      Yes, ROWNUM is applied after the WHERE predicates (other than ROWNUM).

                      Let's say you have a query on a (regular) table as
                      select * from regular_table;
                      would it be guaranteed to always return rows in the same order ? NO, because rowids, positions of rows in blocks etc can change with INSERT/ row migration/ block coalescion / DELETE (and, of course, Export-Import).

                      BUT say that the table is an EXTERNAL Table. An External table (at least in current versions !) undergoes no DML. Would rows always be returned in the same order if NO other predicates are supplied in the WHERE clause ?
                      That thought had crossed my mind, that due to the physical storage of the external table perhaps one could consider a 'natural' ordering of rows, but ...
                      Then, again, think of another complication in FullTableScans. In a FullTableScan, Oracle may even split a multiblock read into smaller multiblock reads if some blocks are present in the buffer cache.
                      How would this work if the table is an EXTERNAL Table and some rows have been retrieved earlier ?
                      There's the rub .. multiple selective reads of the table, some rows already in buffers from previous reads, some not .. I'd just take the approach that you can never depend on row ordering without an ORDER BY clause. Even if testing on the current system seems to indicate otherwise, who'd know when some patch or upgrade would change that behavior. Too many people get bit on all sorts of stuff because they design around observed behavior of current systems, even when that observed behavior is stated to be outside of the system specs.
                      Hemant K Chitale
                      http://hemantoracledba.blogspot.com
                      BTW, it's Saturday. I'm babysitting an installation with more "wait" time than "keyboard" time. What's your excuse? We should both be somewhere else, having a beer and thinking about anything <i>but</i> oracle! ;-)
                      • 8. Re: External Tables
                        Pavan Kumar
                        Hi Hemant,

                        BUT say that the table is an EXTERNAL Table. An External table (at least in current versions !) undergoes no DML. Would rows always be returned in the same order if NO other predicates are supplied in the WHERE clause ?

                        Come on Sir..By default they are "read-only".. !!

                        Then, again, think of another complication in FullTableScans. In a FullTableScan, Oracle may even split a multiblock read into smaller multiblock reads if some blocks are present in the buffer cache.
                        How would this work if the table is an EXTERNAL Table and some rows have been retrieved earlier ?

                        IMO, as we are dealng with multiblock reads - Basic thing is that's depends on sizing of extents and how we are creating a schema objects. So I think Analyzing the data is more important and based on the working "Database File Multiblock Read Count" will help us out..

                        Let me know .. your comments...

                        EdStevens,

                        That's a nice thoughts too.!! :-) Can I Join...!!


                        - Pavan Kumar N
                        • 9. Re: External Tables
                          26741
                          By default they are "read-only".. !!
                          Yes, what is the difference between "by default they are 'read-only'" and "An External table (at least in current versions !) undergoes no DML." ?
                          based on the working "Database File Multiblock Read Count" will help us out..
                          and you need to know how multiblock reads work if some blocks are already present in the buffer cache.
                          • 10. Re: External Tables
                            716323
                            Answered

                            Edited by: user8707806 on Aug 10, 2009 5:33 AM
                            • 11. Re: External Tables
                              26741
                              External tables are similar to SQLLoader input files.

                              You can't have titles and certainly not repeating titles !! The files contain only data. The structure of the data is defined in your CREATE TABLE definition.

                              Start your understanding of External Tables with
                              http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2445

                              Edited by: Hemant K Chitale on Aug 9, 2009 6:42 PM
                              • 12. Re: External Tables
                                Hans Forbrich
                                Hemant K Chitale wrote:
                                External tables are similar to SQLLoader input files.

                                You can't have titles and certainly not repeating titles !! The files contain only data. The structure of the data is defined in your CREATE TABLE definition.
                                This implies that the 'load format info' clause and the 'LOAD WHEN' subclause are not used in External Tables.

                                If the pattern can be determined, there is no reason why we can not reject (ignore) specific records. The code in the LOAD WHEN subclause may become attrocious, but it is perfectly plausible to skip page breaks and titles if they can be defined.



                                As an example, using [René Nyffenegger's |http://www.adp-gmbh.ch/ora/misc/ext_table.html] ext table demo as a basis, I created a file with octothorpes (#) in column 1 to give me a pattern to skip. The skip can be done either by 'bad line' handling (bad data type) or by 'discarded line' (lod when) or both.
                                C:\Temp\test>type file.csv
                                1,one,first
                                2,two,second
                                #
                                3,three,third
                                4,four,fourth
                                #
                                #
                                Then set up the external table
                                C:\Temp\test>sqlplus  / as sysdba
                                
                                SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 9 08:51:17 2009
                                
                                Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                                
                                
                                Connected to:
                                Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                                
                                SQL> create or replace directory ext_dir as 'C:\Temp\test';
                                
                                Directory created.
                                
                                SQL> grant read, write on directory ext_dir to test;
                                
                                Grant succeeded.
                                
                                SQL> connect test
                                Enter password:
                                Connected.
                                SQL> create table ext_table_csv (
                                  2    i   Number,
                                  3    n   Varchar2(20),
                                  4    m   Varchar2(20)
                                  5  )
                                  6  organization external (
                                  7    type              oracle_loader
                                  8    default directory ext_dir
                                  9    access parameters (
                                 10      records delimited  by newline
                                 11      fields  terminated by ','
                                 12      missing field values are null
                                 13    )
                                 14    location ('file.csv')
                                 15  )
                                 16  reject limit unlimited;
                                
                                Table created.
                                and the result is that
                                SQL> select * from ext_table_csv;
                                
                                         I N                    M
                                ---------- -------------------- --------------------
                                         1 one                  first
                                         2 two                  second
                                         3 three                third
                                         4 four                 fourth
                                
                                SQL>
                                whch ignores the bad lines since they are not numeric. Those lines are indeed listed in the '.bad' file.

                                I then replaced the external table definition with one to skip periodic repeated information (in this case a line starting with #)
                                create table ext_table_csv (
                                  i   Varchar2(20),
                                  n   Varchar2(20),
                                  m   Varchar2(20)
                                )
                                organization external (
                                  type              oracle_loader
                                  default directory ext_dir
                                  access parameters (
                                    records delimited  by newline load when (i != '#')
                                    fields  terminated by ','
                                    missing field values are null
                                  )
                                  location ('file.csv')
                                )
                                reject limit unlimited;
                                and the result is that the records starting with # are skipped and NOT put into the .bad file (but into the discard file instead)
                                SQL> select * from ext_table_csv;
                                
                                I                    N                    M
                                -------------------- -------------------- --------------------
                                1                    one                  first
                                2                    two                  second
                                3                    three                third
                                4                    four                 fourth
                                
                                SQL> host dir
                                 Volume in drive C is FCC_C
                                 Volume Serial Number is 8C75-879B
                                
                                 Directory of C:\Temp\test
                                
                                09/08/2009  09:08 AM    <DIR>          .
                                09/08/2009  09:08 AM    <DIR>          ..
                                09/08/2009  09:08 AM                 9 EXT_TABLE_CSV_1848_3984.dsc
                                09/08/2009  09:08 AM             2,295 EXT_TABLE_CSV_1848_3984.log
                                09/08/2009  09:08 AM                66 file.csv
                                               3 File(s)          2,370 bytes
                                               2 Dir(s)   4,610,854,912 bytes free
                                /Hans
                                • 13. Re: External Tables
                                  EdStevens
                                  user8707806 wrote:
                                  Thanks to all.

                                  I have attached the content of my file here, can someone suggest me how to use external tables and use it please copy paste in note pad for the better view,but the allianment is the same.

                                  TBR00001 1410 08/118 141094@h US 22222-0001
                                  RSI(94@h) 28
                                  Unit: 90999 90998 FUNP

                                  CITIBANK
                                  GOVERNMENT CARD SERVICES
                                  P O BOX 6125
                                  SIOUX FALLS SD 57117-6125
                                  <snip>
                                  I hope this is dummy test data ...
                                  • 14. Re: External Tables
                                    716323
                                    answered
                                    1 2 Previous Next