how to take particial xml data out of a huge table

[sfgaldba]

I need to take 3 months worth of data from the table contains xml type. The table is very large. Just 3 months has close to 150000 rows. For some reason when I try to write out to a table, it is doing full scan even if I had a primary key. Anyways, my question is to all experts, is there a way to easily take part of the data out and load to another database? i can not take the whole table. It needs to pick up just the 3 months I need. Thanks.

Just wonder if there is other tools/utility to get xml out other than just sql.

[PAVB]

Could you please post your query as well as your PK specs?
Total row count for your table wouldn't hurt either.
Also... are performance stats up-to-date for that particular table?

[PAVB]

The production table is about a million rows. I need 3 months.The pk is the application_id. When I try to create a new table to just collect the 2 tables, I got performance hit. When I try to run the following it took long time like 30 mins so I cancelled. Not sure why other than the xml type. I wonder if I have to do one day at a time? or if there is other tools? Thanks. Here is the sql and explain plan I got:

explain plan set statement_id = 'MINE' for
select b.application_id,b.xml_data from EHTEMP.application_id_sub a, EHADMIN.application_xml_data b where
a.application_id = b.application_id;
Operation
Object
------------------------------
SELECT STATEMENT ()
NESTED LOOPS ()
INDEX (FULL SCAN)
XPK_APPLICATION_SUB
Operation
-------------------------------------
Object
------------------------------
TABLE ACCESS (BY GLOBAL INDE
APPLICATION_XML_DATA
INDEX (UNIQUE SCAN)
XPK_APPLICATION_XML_DATA

There are two things that caught my attention...

1-- Your WHERE clause has no mention of any date column thay might help you to select the three months of data you are supposed to extract.

2-- I'm not sure why are you including table application_id_sub in your query, only reason could be you have a date column in there but you forgot to mention it in your WHERE clause.

Do you have any date column that might help you to filter the data?

[sfgaldba]

The xml table has no date, but application_id and the xml_data. I have to find the application_id for the 3 months and put them in a sub table. Both table application_id is the PK. I was hoping to find the rows by application_id to another new table. There were some details to what to get out of the 3 months. I have tried one day it ran pretty fast. But this is production, I need a better way to get the data out. Thanks.

[sfgaldba]

I think I maybe running into the problem with different version of exp. I need to exp from 10.1.0.4 and import to 9.2.0.1 . I am getting
sjprmktdb01 > Import: Release 9.2.0.2.1 - Production on Wed Apr 11 14:27:16 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

[PAVB]

You are right, Oracle does not allows it.

"Export dump files, however, are not downward compatible with the Import utilities of previous Oracle Database releases. That is, exported data cannot be imported by the Import utilities of previous Oracle Database releases"

http://www.stanford.edu/dept/itss/do...763/expimp.htm

[sfgaldba]

My script got the following error when try to write out or insert to a table:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00216: invalid character 146 (0x92)
Error at line 87

the select contains xml type reading in and write out to varchar 4000, how can skip the bad rows and write the good rows out in my code?

select c.1,c.2,p.1,x.1 '//app:coverage/'||m.xml_name||'/@answer' as xpath,,extractvalue(x.xml_data,'//app:coverage/'||m.xml_name||'/@answer','xmlns:app="http://www.xyz.com/application/data"') as COVER from c,p,x