Solved

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

Posted on 2014-07-28
6
1,222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 77

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 77

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

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

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

691 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