Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
materialized view via link slow after upgrade from 8i to 9i

materialized view via link slow after upgrade from 8i to 9i

2005-11-04       - By Terry Sutton

Reply:     1     2     3     4     5  

Barbara,

I can't tell you the specific action to take to fix the process, but it's root
cause is probably because of the different default values in 9i vs. 8i for many
underscore parameters related to the optimizer.  These particularly affect
subqueries.  Take a look in Metalink for terms like "unnest".

--Terry
 I'm testing an upgrade of a database from version 8.1.7.4 to version 9.2.0.4
   Test db  LEGS        Oracle version 9.2.0.4  just created
   Prod db  ARMS       Oracle version 8.1.7.4
   Prod db  AMPROD  Oracle version 9.2.0.4

 Test (LEGS) is creating a materialized view via a link from the AMPROD
database.  
 This mview is created in less than 2 minutes when run from the prod ARMS 8i
database.  
 The same mview takes 40 minutes in the newly-upgraded test LEGS 9i database.  
 IDENTICAL MVIEW; only difference is running out of 8i database versus 9i
database.

 All 3 databases are on the same VMS node.  (OpenVMS 7.3-1)

 Google/Metalink searches like "materialized view slow upgrade" yielded no
useful info.

 The mview from the 8i database is gathering the data in a different manner.  
 For example, v8i:

 SELECT "ACCT_NBR", . . . more stuff
 FROM "ADMARC"."NAD" "NAD" WHERE "ACCT_NBR"=:1

 call     count       cpu    elapsed       disk      query       rows
 -- ---- -- ---  -- ----- -- ---- -- -- ---- -- -- ---- --   -- ---- --
 Parse        1      0.00       0.00          0          0          0
 Execute    516      0.30       0.26          0          0          0
 Fetch      516      0.18       0.65         73       2066        516
 -- ---- -- ---  -- ----- -- ---- -- -- ---- -- -- ---- --   -- ---- --
 total     1033      0.48       0.92         73       2066        516


 v9i:  (I don't know what that sys_alias_1 thing is)

 SELECT "ACCT_NBR, . . .  more stuff
 FROM "ADMARC"."NAD" "SYS_ALIAS_1"

 call     count       cpu    elapsed       disk      query    rows
 -- ---- -- ---  -- ----- -- ---- -- -- ---- -- -- ---- --  -- ---
 Parse        0      0.00       0.00          0          0       0
 Execute      0      0.00       0.00          0          0       0
 Fetch     8914     31.31      74.21      44894      63033  463481
 -- ---- -- ---  -- ----- -- ---- -- -- ---- -- -- ---  -- ---- --
 total     8914     31.31      74.21      44894      63033  463481


 version 8i is doing this:

 SELECT "ACCT_KEY","END_ISS","ACT_DATE" FROM
 "ADMARC"."CNR" "CNR" WHERE "ACT_DATE">=TRUNC(:1-1) AND "END_ISS">=TRUNC(:2-1)

 call     count       cpu    elapsed       disk      query       rows
 -- ---- -- ---  -- ----- -- ---- -- -- ---- -- -- ---- -- -- ---- --
 Parse        1      0.00       0.00          0          0          0
 Execute      1      0.01       0.00          0          0          0
 Fetch        1     13.04      24.60      17226      93473        547
 -- ---- -- ---  -- ----- -- ---- -- -- ---- -- -- ---- -- -- ---- --
 total        3     13.05      24.61      17226      93473        547


 version 9i is doing this:

 SELECT "ACCT_NBR","ACT_DATE" FROM
 "ADMARC"."NAD" "NAD" WHERE "ACCT_NBR"=:1 AND "ACT_DATE">=TRUNC(:2-1)

 call     count       cpu    elapsed       disk      query       rows
 -- ---- -- ---  -- ----- -- ---- -- -- ---- -- -- ---- -- -- ---- --
 Parse        0      0.00       0.00          0          0          0
 Execute 463500    244.01     262.98          0          0          0
 Fetch   463769    109.76     130.75      20767    1854059        269
 -- ---- -- ---  -- ----- -- ---- -- -- ---- -- -- ---- -- -- ---- --
 total   927269    353.77     393.74      20767    1854059        269
   

 9i is clearly choosing a different method, but I don't know why.

 Here's the mview creation:

 CREATE MATERIALIZED VIEW barb.MV_barb_CDT
 TABLESPACE ARMS_DATA LOGGING BUILD IMMEDIATE
 REFRESH FORCE ON DEMAND
 AS
 select * FROM admarc.nad@(protected)
 where acct_nbr in
         ( select acct_nbr
           from admarc.nad@(protected)
           where  act_date  >= trunc(sysdate-1)
     union select acct_key
           from admarc.cnr@(protected)
           where   act_date >= trunc(sysdate-1)
           and     end_iss  >= trunc(sysdate-1) )

 I will be grateful for any ideas.
 Thanks so much!
 Barb

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1498" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Barbara,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>I can't tell you the specific action to take to
fix
the process, but it's root cause is probably because of the different default
values in 9i vs. 8i for many underscore parameters related to the
optimizer.&nbsp; These particularly affect subqueries.&nbsp; Take a look in
Metalink for terms like "unnest".</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>--Terry</FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT:
#000000 2px solid; MARGIN-RIGHT: 0px">
 <DIV style="FONT: 10pt arial">I'm testing an upgrade of a database from
 version <A href="http://8.1.7.4">8.1.7.4</A> to version <A
 href="http://9.2.0.4">9.2.0.4</A><BR>&nbsp;&nbsp;Test db&nbsp;&nbsp;LEGS&nbsp
;
 &nbsp; &nbsp;&nbsp;&nbsp; Oracle version <A
 href="http://9.2.0.4">9.2.0.4</A>&nbsp;&nbsp;just created <BR>&nbsp;&nbsp
;Prod
 db&nbsp;&nbsp;ARMS&nbsp; &nbsp; &nbsp;&nbsp; Oracle version <A
 href="http://8.1.7.4">8.1.7.4</A><BR>&nbsp;&nbsp;Prod
 db&nbsp;&nbsp;AMPROD&nbsp; Oracle version <A
 href="http://9.2.0.4">9.2.0.4</A><BR><BR>Test (LEGS) is creating a
 materialized view via a link from the AMPROD database.&nbsp; <BR>This mview
is
 created in less than 2 minutes when run from the prod ARMS 8i database.&nbsp;
 <BR>The same mview takes 40 minutes in the newly-upgraded test LEGS 9i
 database.&nbsp; <BR>IDENTICAL MVIEW; only difference is running out of 8i
 database versus 9i database.<BR><BR>All 3 databases are on the same VMS
 node.&nbsp;&nbsp;(OpenVMS 7.3-1)<BR><BR>Google/Metalink searches like
 "materialized view slow upgrade" yielded no useful info. <BR><BR>The mview
 from the 8i database is gathering the data in a different manner.&nbsp;
 <BR>For example, v8i:<BR><BR><SPAN
 style="FONT-FAMILY: courier new,monospace">SELECT "ACCT_NBR", . . . more
 stuff</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">FROM "ADMARC"."NAD" "NAD" WHERE
 "ACCT_NBR"=:1</SPAN><BR style="FONT-FAMILY: courier new,monospace"><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">call&nbsp;&nbsp;&nbsp;&nbsp;
 count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 cpu&nbsp;&nbsp;&nbsp;&nbsp;elapsed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 disk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;query&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
 rows</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">-- ---- -- ---&nbsp;&nbsp;-- -----
 -- ---- -- -- ---- -- -- ---- --&nbsp;&nbsp; -- ---- --</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Parse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Execute&nbsp;&nbsp;&nbsp;&nbsp;516
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 0.26&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Fetch&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;516&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
 0.65&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 73&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 2066&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;516</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">-- ---- -- ---&nbsp;&nbsp;-- -----
 -- ---- -- -- ---- -- -- ---- --&nbsp;&nbsp; -- ---- --</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">total&nbsp;&nbsp;&nbsp;&nbsp;
 1033&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.48&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
 0.92&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 73&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 2066&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;516</SPAN><BR><BR><BR>v9i
:&nbsp;&nbsp;(I
 don't know what that sys_alias_1 thing is)<BR><BR>S<SPAN
 style="FONT-FAMILY: courier new,monospace">ELECT "ACCT_NBR, . .
 .&nbsp;&nbsp;more stuff</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">FROM "ADMARC"."NAD"
 "SYS_ALIAS_1"</SPAN><BR><BR><SPAN
 style="FONT-FAMILY: courier new,monospace">call&nbsp;&nbsp;&nbsp;&nbsp;
 count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 cpu&nbsp;&nbsp;&nbsp;&nbsp;elapsed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 disk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;query&nbsp;&nbsp;&nbsp;&nbsp;rows<
/SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">-- ---- -- ---&nbsp;&nbsp;-- -----
 -- ---- -- -- ---- -- -- ---- --&nbsp;&nbsp;-- ---</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Parse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
 0</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Execute&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;
 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
 0</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Fetch&nbsp;&nbsp;&nbsp;&nbsp;
 8914&nbsp;&nbsp;&nbsp;&nbsp;
 31.31&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;74.21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;44894&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;63033&nbsp;&nbsp;463481</SPAN><BR

 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">-- ---- -- ---&nbsp;&nbsp;-- -----
 -- ---- -- -- ---- -- -- ---&nbsp;&nbsp;-- ---- --</SPAN> <BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">total&nbsp;&nbsp;&nbsp;&nbsp;
 8914&nbsp;&nbsp;&nbsp;&nbsp;
 31.31&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;74.21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;44894&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;63033&nbsp;&nbsp;463481</SPAN><BR
><BR><BR>version
 8i is doing this:<BR><BR>SELECT "ACCT_KEY","END_ISS","ACT_DATE"
 FROM<BR>"ADMARC"."CNR" "CNR" WHERE "ACT_DATE"&gt;=TRUNC(:1-1) AND
 "END_ISS"&gt;=TRUNC(:2-1) <BR><BR><SPAN
 style="FONT-FAMILY: courier new,monospace">call&nbsp;&nbsp;&nbsp;&nbsp;
 count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 cpu&nbsp;&nbsp;&nbsp;&nbsp;elapsed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 disk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;query&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
 rows</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">-- ---- -- ---&nbsp;&nbsp;-- -----
 -- ---- -- -- ---- -- -- ---- -- -- ---- --</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Parse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Execute&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.01&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;
 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Fetch&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;
 13.04&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;24.60&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;17226&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;93473&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;547</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">-- ---- -- ---&nbsp;&nbsp;-- -----
 -- ---- -- -- ---- -- -- ---- -- -- ---- -- </SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;
 13.05&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;24.61&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;17226&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;93473&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;547</SPAN><BR><BR><BR>version
 9i is doing this:<BR><BR>SELECT "ACCT_NBR","ACT_DATE" FROM<BR>"ADMARC"."NAD"
 "NAD" WHERE "ACCT_NBR"=:1 AND "ACT_DATE"&gt;=TRUNC(:2-1) <BR><BR>c<SPAN
 style="FONT-FAMILY: courier new,monospace">all&nbsp;&nbsp;&nbsp;&nbsp;
 count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 cpu&nbsp;&nbsp;&nbsp;&nbsp;elapsed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 disk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;query&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;
 rows</SPAN><BR style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">-- ---- -- ---&nbsp;&nbsp;-- -----
 -- ---- -- -- ---- -- -- ---- -- -- ---- --</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Parse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0.00&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;
 0.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Execute
 463500&nbsp;&nbsp;&nbsp;&nbsp;244.01&nbsp;&nbsp;&nbsp;&nbsp;
 262.98&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">Fetch&nbsp;&nbsp;
 463769&nbsp;&nbsp;&nbsp;&nbsp;109.76&nbsp;&nbsp;&nbsp;&nbsp;
 130.75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20767&nbsp;&nbsp;&nbsp;&nbsp
;1854059&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;269</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">-- ---- -- ---&nbsp;&nbsp;-- -----
 -- ---- -- -- ---- -- -- ---- -- -- ---- --</SPAN><BR
 style="FONT-FAMILY: courier new,monospace"><SPAN
 style="FONT-FAMILY: courier new,monospace">total&nbsp;&nbsp;
 927269&nbsp;&nbsp;&nbsp;&nbsp;353.77&nbsp;&nbsp;&nbsp;&nbsp;
 393.74&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20767&nbsp;&nbsp;&nbsp;&nbsp
;1854059&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;269</SPAN><BR>&nbsp;
&nbsp;<BR><BR>9i
 is clearly choosing a different method, but I don't know why.<BR><BR>Here's
 the mview creation:<BR><BR>CREATE MATERIALIZED VIEW
 barb.MV_barb_CDT<BR>TABLESPACE ARMS_DATA LOGGING BUILD IMMEDIATE <BR>REFRESH
 FORCE ON DEMAND<BR>AS<BR>select * FROM admarc.nad@(protected)<BR>where acct_nbr
 in<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;( select
 acct_nbr<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from
 admarc.nad@(protected)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;where&nbsp;&nbsp;act_date&nbsp;&nbsp;&gt;=
 trunc(sysdate-1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;union select acct_key
 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from
 admarc.cnr@(protected)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;where&nbsp;&nbsp;
 act_date &gt;=
 trunc(sysdate-1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;and&nbsp;&nbsp;&nbsp;&nbsp;
 end_iss&nbsp;&nbsp;&gt;= trunc(sysdate-1) )<BR><BR>I will be grateful for any
 ideas.<BR>Thanks so much!<BR>Barb<BR></DIV></BLOCKQUOTE></BODY></HTML>