This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Aug 10, 2009 6:46 AM by 716323 RSS

External Tables

716323 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    PavanKumar Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Answered

    Edited by: user8707806 on Aug 10, 2009 5:33 AM
  • 11. Re: External Tables
    26741 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    answered
1 2 Previous Next

Legend

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