Hi, Tom

That hint (/*+ MATERIALIZE */) is new to me. Is it documented?

I can't find it in the list of hints in the 10g docs - but maybe it's just because I'm nearsighted :-)

Is it an alternative method of using "where rownum > 0" or something similar to force materializing an inline view?


Thanks for your help as always...

While on the subject of hints (see how I did that), can you clarify the purpose and mechanism of the /*+ NESTED_TABLE_FAST_INSERT */ hint which is referred to in the 10g documentation for DBMS_FREQUENT_ITEMSET?

"If you want to use an insert statement to load frequent itemsets into a nested table, it is better to use the NESTED_TABLE_FAST_INSERT hint for performance:

CREATE TABLE fq_nt (coll FI_VARCHAR_NT) NESTED TABLE coll STORE AS
coll_nest;
INSERT /*+ NESTED_TABLE_FAST_INSERT */ INTO fq_nt
SELECT cast(itemset as FI_VARCHAR_NT)
FROM table(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL(
cursor(SELECT tid, iid FROM tranx_table_in), 0.6, 2, 5,
NULL, NULL));"

I think I will use the your second method.

my original method runtime: ~60sec
your method 1 runtime: ~31sec
your method 2 runtime: ~27sec

BTW, where can I find the documentation about the MATERIALIZE hint?

Thanks

In answer to the original question, rownum has been used to number the lines in the file. For this solution to work correctly, rownum value should match the actual line number of each line in the file. Is this a deterministic way to get the position of a line in the file ? If it is deterministic, is it documented anywhere ?

1. Is it a good practice to use rownum as the line number of lines in an external table. Even though results might indicate that rownum matches the line number in current versions of Oracle, since it is not documented, Oracle might treat external tables like heap tables in future versions resulting in rownum no longer matching the line number

2. If the external table is accessed in parallel (for files with fixed width records), then rownum may not reflect the line number even in current versions of Oracle

Thanks for the alternative approach to get the line number of the records in the external table using RECNUM.

Searched the 9iR2 and 10gR2 DB utilities guide in the external table section - but could not find any mention of RECNUM.

Is the use of RECNUM as external table field datatype undocumented ?

If it is indeed undocumented - which is the lesser of 2 evils - use of rownum or RECNUM to get the line number ?

P.S: Tested in 9iR2 and RECNUM does indeed returns the line number of the records in an external table

Hi Tom,

If materialize hint is undocumented, then we shouldn't be using it. Right......?

If RECNUM can be used as an external table field datatype (even though currently undocumented) - don't you think this should be logged as a documentation bug ?
Otherwise how would anyone know that RECNUM can be used, since what can be carried over from "sqlldr" is also not documented.

the materialize hint seems to have a dependency to global names.
can you explain this?

this well intentioned design attempted to have one copy of a package of business gic routines the functions needed to be called in views across dblinks. some of these routines take no arguments and return not-often-changed values. the functions take some time to execute. so when selected in a view it was desired to call them once for the view and not for every row. I know there are much better ways to accomplish this - syscontext, but they weren't ready for that. so here is an example of my attempt to get this done.

--on system 1
set autotrace trace
set timing on

create or replace package foo as
function bar return number;
end;
/

show errors

create or replace package body foo as
function bar return number is
begin
dbms_lock.sleep(1);
return to_number(to_char(sysdate,'YYYY'));
end bar;
end foo;
/

show errors

create or replace view foo_a as
with a as (select foo.bar bar from dual)
select a.bar foo_bar, object_name from user_objects,a where rownum < 10;

select * from foo_a;
--works

create or replace view foo_a as
with a as (select /*+MATERIALIZE*/foo.bar bar from dual)
select a.bar foo_bar, object_name from user_objects,a where rownum < 10;

select * from foo_a;
--works

-- global names is set to false
--on system 2
set autotrace trace
set timing on
select foo.bar@sys1 from dual;
-- works

create or replace view foo_a as
with a as (select foo.bar@sys1 bar from dual)
select a.bar foo_bar, object_name from user_objects,a where rownum < 10;
--works

select * from foo_a;
--works

create or replace view foo_a as
with a as (select /*+MATERIALIZE*/foo.bar@sys1 bar from dual)
select a.bar foo_bar, object_name from user_objects,a where rownum < 10;

--ORA-02069: global_names parameter must be set to TRUE for this operation

when a materialize hinted view is granted to another schema in the same instance this is what I get.


create or replace package foo as
function bar return number;
end;
/

show errors

create or replace package body foo as
function bar return number is
begin
dbms_lock.sleep(1);
return to_number(to_char(sysdate,'YYYY'));
end bar;
end foo;
/

show errors


create or replace view foo_a as
with a as (select /*+MATERIALIZE*/foo.bar bar from dual)
select a.bar foo_bar, object_name from user_objects,a where rownum < 10;

grant select on foo_a to user2;

--connect user2

DMS-DM> select * from user1.foo_a;
select * from user1.foo_a
*
ERROR at line 1:
ORA-03001: unimplemented feature

A situation where I couldn't get rownum > 0 to work, but the materialize hint seems to work

I have an update stmt, and the updated data comes from a subquery. I wanted to materialize the subquery. Couldn't get rownum > 0 to work, but the materialize hint seems to work (after a lot of help from here - </code> http://dizwell.com/main/component/option,com_smf/Itemid,114/topic,168.0 <code>):

------------------------------------------------------------
-- rownum > 0
------------------------------------------------------------
UPDATE rahul_temp_sr s
SET site_key_qy =
(WITH q1 AS
(SELECT COUNT (DISTINCT (site_key)) qy,
a2s.sr_id
FROM rahul_temp_acct a,
rahul_temp_acct2sr a2s
WHERE a.acct_id = a2s.acct_id
AND ROWNUM >= 0
GROUP BY a2s.sr_id)
SELECT x.qy
FROM q1 x
WHERE x.sr_id = s.sr_id)

--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | RAHUL_TEMP_SR |
| 2 | TABLE ACCESS FULL | RAHUL_TEMP_SR |
| 3 | VIEW | |
| 4 | SORT GROUP BY | |
| 5 | COUNT | |
| 6 | FILTER | |
| 7 | MERGE JOIN | |
| 8 | SORT JOIN | |
| 9 | TABLE ACCESS FULL| RAHUL_TEMP_ACCT2SR |
| 10 | SORT JOIN | |
| 11 | TABLE ACCESS FULL| RAHUL_TEMP_ACCT |
--------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("X"."SR_ID"=:B1)
6 - filter(ROWNUM>=0)
10 - access("A"."ACCT_ID"="A2S"."ACCT_ID")
filter("A"."ACCT_ID"="A2S"."ACCT_ID")

Note: cpu costing is off

------------------------------------------------------------
-- with materialize hint
------------------------------------------------------------
UPDATE rahul_temp_sr s
SET site_key_qy =
(WITH q1 AS
(SELECT /*+ materialize */
COUNT (DISTINCT (site_key)) qy,
a2s.sr_id
FROM rahul_temp_acct a,
rahul_temp_acct2sr a2s
WHERE a.acct_id = a2s.acct_id
-- AND ROWNUM >= 0
GROUP BY a2s.sr_id)
SELECT x.qy
FROM q1 x
WHERE x.sr_id = s.sr_id)

--------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | RAHUL_TEMP_SR |
| 2 | TABLE ACCESS FULL | RAHUL_TEMP_SR |
| 4 | TEMP TABLE TRANSFORMATION | |
| 3 | RECURSIVE EXECUTION | SYS_LE_4_0 |
| 0 | INSERT STATEMENT | |
| 1 | LOAD AS SELECT | |
| 2 | SORT GROUP BY | |
| 3 | MERGE JOIN | |
| 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL | RAHUL_TEMP_ACCT2SR |
| 6 | SORT JOIN | |
| 7 | TABLE ACCESS FULL | RAHUL_TEMP_ACCT |
| 5 | VIEW | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_10B3AF26 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("A"."ACCT_ID"="A2S"."ACCT_ID")
filter("A"."ACCT_ID"="A2S"."ACCT_ID")
5 - filter("X"."SR_ID"=:B1)

Note: cpu costing is off

Running 9i on HP-UX
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

Hi Tom,

I am replacing a data warehouse load process and plan to use external tables. Not all data is loaded and a user may need to get to see the original line from the file. My plan is to store the line# and, on the rare occasion the user needs to see the original, call a procedure supplying the line# to retrieve it. One file could be 600K records with 300 plus attributes and many files are loaded per month.

You have mentioned that using rownum as the line# (with external tables) is not documented and I was wondering if I am better off reading the file into a CLOB and parsing it in PL code. This should guarantee that the first line parsed is the first line.

I was doing an insert /*+ append */ into mytable selecting from a pipelined table function. This funtion selected from the external table, datatyped, looked up foreign keys (...) and piped rows.

My change would be to have the function open the CLOB, parse, datatype, look up foreign keys and pipe rows. Do you think I would take a performance hit with this plan?

Browsing at lunch, have/am using external tables. Title caught my eye, thanks.
Targeting alert logs very useful and tuning the access, sorry to admit using external tables when I got the rows I was done - will apply more diligence.
What knocked me out - at the risk of confessing to not knowing it - was this:
"with
a as (select rownum line#, a.* from alert_log a ),
s as (select * from a where a.line1 like '% LOG_ARCHIVE_DEST_1%')
select distinct a.*...... "

I have never seen this construct 'with' before!
Where do I read more on this? Searching on 'with' gets a lot of unwanted prepostions. Thanks so much. CJL

Tom, I did not paste the complete scripts since I thought it too large to be a followup too. The link I posted has the table creation, population scripts complete with a small picture of the data model at the original forum though :). Here are the relevant details:

We have a Many to Many relationship between Sales Resources and Accounts resolved into 3 tables. Accounts have an attribute "Site Key" which may be repeated for different accounts. We need to have a denormalised count of distinct site keys (of accounts) linked to a Sales Resource.

-- Create Tables
create table rahul_temp_sr
(sr_id varchar2(10),
site_key_qy number)
/

create table rahul_temp_acct
(acct_id varchar2(30),
site_key varchar2(14))
/

create table rahul_temp_acct2sr
(acct_id varchar2(30),
sr_id varchar2(10))
/

-- Populate data
insert into rahul_temp_sr
select l, 0 from (select level l from dual connect by level <= 15000)
/

insert /*+ append */ into rahul_temp_acct
select l, -l from (select level l from dual connect by level <= 10000000)

insert /*+ append */ into rahul_temp_acct2sr
select acct_id, mod(rownum,15000) + 1
from rahul_temp_acct

-- Create Constraints/Indexes
alter table rahul_temp_sr add constraint rahul_temp_sr_pk primary key (sr_id)
/
alter table rahul_temp_acct add constraint rahul_temp_acct_pk primary key (acct_id) novalidate
/
alter table rahul_temp_acct2sr add (constraint acct2sr_acct_fk foreign key (acct_id) references rahul_temp_acct(acct_id) novalidate,
constraint acct2sr_sr_fk foreign key (sr_id) references rahul_temp_sr(sr_id) novalidate)
/
create index rahul_temp_acct2sr_idx on rahul_temp_acct2sr(acct_id,sr_id)
/

-- Analyze
analyze table rahul_temp_sr compute statistics for table for all indexed columns for all indexes
/
analyze table rahul_temp_acct compute statistics for table for all indexed columns for all indexes
/
analyze table rahul_temp_acct2sr compute statistics for table for all indexed columns for all indexes
/


-- The update statement in question is:
UPDATE rahul_temp_sr s
SET site_key_qy =
(SELECT x.qy
FROM (SELECT COUNT (DISTINCT (site_key)) qy,
a2s.sr_id
FROM rahul_temp_acct a,
rahul_temp_acct2sr a2s
WHERE a.acct_id = a2s.acct_id
AND ROWNUM >= 0
GROUP BY a2s.sr_id) x
WHERE x.sr_id = s.sr_id)
/

I only realized that the subquery was not being materialized when I saw the repeated full table scans on the tables in the subquery while watching v$session_longops. After seeing that, I looked back at the plan and assumed that is how I was supposed to interpret the predicate information in the plan. Specifically: 3 - filter("X"."SR_ID"=:B1)

--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | RAHUL_TEMP_SR |
| 2 | TABLE ACCESS FULL | RAHUL_TEMP_SR |
| 3 | VIEW | |
| 4 | SORT GROUP BY | |
| 5 | COUNT | |
| 6 | FILTER | |
| 7 | MERGE JOIN | |
| 8 | SORT JOIN | |
| 9 | TABLE ACCESS FULL| RAHUL_TEMP_ACCT2SR |
| 10 | SORT JOIN | |
| 11 | TABLE ACCESS FULL| RAHUL_TEMP_ACCT |
--------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("X"."SR_ID"=:B1)
6 - filter(ROWNUM>=0)
10 - access("A"."ACCT_ID"="A2S"."ACCT_ID")
filter("A"."ACCT_ID"="A2S"."ACCT_ID")

The query where the rownum is applied is not correlated (thought the surrounding subquery is):

SELECT COUNT (DISTINCT (site_key)) qy,
a2s.sr_id
FROM rahul_temp_acct a,
rahul_temp_acct2sr a2s
WHERE a.acct_id = a2s.acct_id
AND ROWNUM >= 0
GROUP BY a2s.sr_id

When I use the materialize hint as follows, it actually does execute the innermost subquery only once (by creating a temporary table for the result set).

UPDATE rahul_temp_sr s
SET site_key_qy =
(WITH q1 AS
(SELECT /*+ materialize */
COUNT (DISTINCT (site_key)) qy,
a2s.sr_id
FROM rahul_temp_acct a,
rahul_temp_acct2sr a2s
WHERE a.acct_id = a2s.acct_id
GROUP BY a2s.sr_id)
SELECT x.qy
FROM q1 x
WHERE x.sr_id

Or am I getting this completely wrong and comparing apples to oranges.

thought the surrounding subquery is
=
though the surrounding subquery is