Solved

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

Posted on 2014-07-28
6
1,163 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 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

26 Experts available now in Live!

Get 1:1 Help Now