Solved

Best method to load 50Mb xml file into an Oracle table to search on?

Posted on 2014-07-28
6
1,149 Views
Last Modified: 2014-07-28
What is the best method (binary, lob, clob, external table, etc) to load this 50Mb xml file into an Oracle table to search on?

I've been reading through the Oracle XML DB Developer's Guide11g Release 2 (11.2). I've tried various methods of loading a very large 50mb XML file of XMLTYPE  into a table (CLOB, LOB, varchar2, etc) so I can perform a SQL query on the table to load Latitude and longitude values into an Oracle geospatial procedure I have set up.  The next method I want to try is Binary XML Storage,

create table XMLBIN of XMLType
XMLTYPE store as BINARY XML;

but I wanted to check here first since I've spend a lot of time on this already and I don't want to damage our heavily used test db.

I'm trying to use sqlldr to load a 50 mb xml file into an Oracle table. For each method I use, the locally managed tablespace grows over 10GB before stopping after 300 records loaded.  The error messages vary between method used but they all consume of 10GB of tablespace before stopping.  I had 16GB allocated but this seems like way too much memory being used.



CREATE TABLE TABLE_XML_DATA
(
  COL_XML_DATA  XMLTYPE
)
TABLESPACE ASM_LIMIT_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

---------------

sqlldr control file,

load data
infile 'c:\Geospat.xml'
into table TEST_USER.TABLE_XML_DATA
REPLACE
FIELDS TERMINATED BY ','
(
COL_XML_DATA lobfile (CONSTANT' c:\Geospat.xml' ) terminated by eof
)
0
Comment
Question by:talahi
  • 3
  • 3
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40224471
I would first try BFILE and parse the XML from there.

Then I would probably load the XML file into a temp table using dbms_lob.loadclobfromfile:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#i998978

Once you can see the XML, I would use XMLTABLE to parse it.  The extract and extractvalue functions are deprecated and not very efficient.  XMLTABLE seems to be pretty decent.
0
 

Author Comment

by:talahi
ID: 40224490
Should I use a locally managed tablespace and what size memory would I expect it to use to store this in an XMLTABLE?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40224508
XMLTABLE is a function that parses an XMLTYPE data type and returns data as a 'normal' table.

When storing the file in an XMLTYPE data type locally or dictionary managed really shouldn't matter.

As far as the amount of memory to provide for the SGA, I really can't say.  I would start with whatever you have and see if Oracle complains that it cannot allocate more memory.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:talahi
ID: 40224516
ok thanks will give it a try.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40224524
Don't you want to leave this open until you have a working solution?

If it doesn't work, you'll end up with a duplicate question.
0
 

Author Comment

by:talahi
ID: 40224559
I won't get back to this until later and by then there might be others responding.  I don't want to waste anyone's time while I'm busy on other things.  You've given me enough to keep busy with for now and it confirms some things I've thought about.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now