regexp_replace(xml ....) vs regexp_replace(substr(xml .... on 10.2.0.4

Red Hat 5.1  

CREATE TABLE R_MONITOR.TBL_XMLDATA (

  QUERYID     NUMBER(19)                        NOT NULL,

  XML         CLOB,

  LASTUPDATE  DATE                              NOT NULL

)  

~ 350 000 rows

CLOB is 1700 MB

Table data is 568 MB  

Two separate queries  

Query A  

select regexp_replace(xml, '^..*<DigiTekGenericOrder>..$', 'DTO', 1, 1, 'n') from tbl_xmldata where rownum < 101;  

A level 8 trace shows

1114507 direct path reads

It takes about 4 minutes  

STAT #2 id=1 cnt=100 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=18 pr=0 pw=0 time=707 us)'

STAT #2 id=2 cnt=100 pid=1 pos=1 obj=110365 op='TABLE ACCESS FULL TBL_XMLDATA (cr=18 pr=0 pw=0 time=505 us)'  

Query B  

select regexp_replace(substr(xml,1,length(xml)), '^..*<DigiTekGenericOrder>..$', 'DTO', 1, 1, 'n') from tbl_xmldata where rownum < 101;

Note: I'm substr to the length of the clob (which is called xml)  

A level 8 trace shows

148 direct path reads

It takes about 20 seconds if data is not in the cache.  

STAT #2 id=1 cnt=100 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=18 pr=0 pw=0 time=702 us)'

STAT #2 id=2 cnt=100 pid=1 pos=1 obj=110365 op='TABLE ACCESS FULL TBL_XMLDATA (cr=18 pr=0 pw=0 time=500 us)'  

That's about the only differences I see in the trace, just the amount of direct path reads. I thought it was scanning through the whole table for the query without the substr but I doubled the data and still came up with the same numbers. It's also not the regexp_replace as I changed it to an upper and got the same results.  

Any ideas on why the difference in direct path reads? I thought there might be some type of implicit conversion going on but the docs show that clob is acceptable for these functions and a clob is returned.  

Thanks

Mike

--
http://www.freelists.org/webpage/oracle-l