Home » RDBMS Server » Performance Tuning » Oracle 9i (SQL not using MV.)
Oracle 9i [message #377738] Wed, 24 December 2008 14:05 Go to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I had a view Items_Sales_V with the same SQL as in the MV below. I created a MV now when I do a select * from my view above. The explain plan does'nt show that it using the MV. It is doing a full table scan of both the tables in the SQL.

CREATE MATERIALIZED VIEW Items_sales_MV
TABLESPACE DATA
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT
trim(r.frm) as sub_group
,i.grp as Group
,r.vend as vendor
,r.itm as ITM
,r.FRCST_YR as FSCL_YR
,sum(r.mon1/1000) as JAN
,sum(r.mon2/1000) as FEB
,sum(r.mon3/1000) as MAR
,sum(r.mon4/1000) as APR
,sum(r.mon5/1000) as MAY
,sum(r.mon6/1000) as JUN
,sum(r.mon7/1000) as JUL
,sum(r.mon8/1000) as AUG
,sum(r.mon9/1000) as SEP
,sum(r.mon10/1000) as OCT
,sum(r.mon11/1000) as NOV
,sum(r.mon12/1000) as DEC
From Item_Master i, Receipt_itm r
Where i.active = 'A'
and i.Item = r.Itm
Group By
r.frm
,i.grp
,r.vend
,r.itm
,r.FRCST_YR;

Can somebody help why my view doesn't use MV
Re: Oracle 9i [message #377739 is a reply to message #377738] Wed, 24 December 2008 14:16 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


What's you tried??

Post all information. (your view, plan output etc...)

Babu
Re: Oracle 9i [message #377742 is a reply to message #377739] Wed, 24 December 2008 16:18 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
My explain plan as below. It is using the tables instead MV I have created.

SELECT STATEMENT CHOOSE
Cost: 19,526 Bytes: 51,994,613 Cardinality: 776,039
4 SORT GROUP BY
Cost: 19,526 Bytes: 51,994,613 Cardinality: 776,039
3 HASH JOIN
Cost: 10,908 Bytes: 51,994,613 Cardinality: 776,039
1 TABLE ACCESS FULL ITEM_MASTER
Cost: 8,355 Bytes: 5,006,066 Cardinality: 385,082
2 TABLE ACCESS FULL RECEIPT_ITM
Cost: 1,338 Bytes: 57,494,556 Cardinality: 1,064,714


The SQL for my view is same as SQL for my MV I have posted above.

Thanks
Re: Oracle 9i [message #377744 is a reply to message #377739] Wed, 24 December 2008 16:34 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
The explain plan shows that the SQL uses the table instead of the MV I have created.

SELECT STATEMENT CHOOSE
Cost: 19,526 Bytes: 51,994,613 Cardinality: 776,039
4 SORT GROUP BY
Cost: 19,526 Bytes: 51,994,613 Cardinality: 776,039
3 HASH JOIN
Cost: 10,908 Bytes: 51,994,613 Cardinality: 776,039
1 TABLE ACCESS FULL ITEM_MASTER
Cost: 8,355 Bytes: 5,006,066 Cardinality: 385,082
2 TABLE ACCESS FULL RECEIPT_ITM
Cost: 1,338 Bytes: 57,494,556 Cardinality: 1,064,714

I am doing a select * from the view and SQL for my view is same as SQL for MV I have created as above.
Re: Oracle 9i [message #377777 is a reply to message #377738] Thu, 25 December 2008 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

- OraFAQ Forum Guide, Performance Tuning section
- Performances sticky
- OraFAQ Performance Tuning


Regards
Michel
Re: Oracle 9i [message #377801 is a reply to message #377777] Thu, 25 December 2008 03:49 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I make an example for you.
In this example, I create 2 MVIEW, one is refresh complete, one is query rewrite
C:\>sqlplus metatest/metatest@meta

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Dec 25 16:07:46 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing o

metatest@META> alter user hr account unlock;

User altered.

metatest@META> alter user hr identified by hr;

User altered.

metatest@META> create materialized view log on hr.employees;

Materialized view log created.

metatest@META> create materialized view log on hr.departments
  2  /

Materialized view log created.

metatest@META> create materialized view meta_hr_mview
  2  refresh complete
  3  next sysdate+3/(24*60)
  4  as
  5  select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SAL,
  6  d.department_name DEPT_NAME
  7  from hr.employees E, hr.departments D
  8  where E.department_id=D.department_id
  9  group by e.employee_id, e.last_name,d.department_name
 10  /

Materialized view created.

metatest@META> create materialized view hr_meta_mview_equery
  2  enable query rewrite
  3  as
  4  select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SAL,
  5  d.department_name DEPT_NAME
  6  from hr.employees E, hr.departments D
  7  where E.department_id=D.department_id
  8  group by e.employee_id, e.last_name,d.department_name
  9  /

Materialized view created.


Now, I recheck if the query revokes MView or not
metatest@META> set autotrace traceonly
metatest@META> select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SA
L,
  2  d.department_name DEPT_NAME
  3  from hr.employees E, hr.departments D
  4  where E.department_id=D.department_id
  5  group by e.employee_id, e.last_name,d.department_name
  6  /

106 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=106 Bytes=6042)
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'HR_META_MVIEW_EQUERY' (MAT_VIEW R
EWRITE) (C
          ost=3 Card=106 Bytes=6042)





Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       3791  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        106  rows processed


Said it as MAT_VIEW in the statistics. Now, I do not want retrieve from MVIEW
metatest@META> alter session set query_rewrite_enabled=false;
alter session set query_rewrite_enabled=false
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION


metatest@META> show parameter query_
metatest@META> host sqlplus sys/tuananhtran@meta as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Dec 25 16:33:57 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@META> grant query rewrite to metatest;

Grant succeeded.

sys@META> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

metatest@META> alter session set query_rewrite_enabled=false;

Session altered.

metatest@META> select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SAL,
  2  d.department_name DEPT_NAME
  3  from hr.employees E, hr.departments D
  4  where E.department_id=D.department_id
  5  group by e.employee_id, e.last_name,d.department_name
  6  /

106 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=106 Bytes=3710)
   1    0   HASH (GROUP BY) (Cost=7 Card=106 Bytes=3710)
   2    1     MERGE JOIN (Cost=6 Card=106 Bytes=3710)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (TABLE) (Cost=2 C
ard=27 Byt
          es=432)

   4    3         INDEX (FULL SCAN) OF 'DEPT_ID_PK' (INDEX (UNIQUE)) (Cost=1 Car
d=27)
   5    2       SORT (JOIN) (Cost=4 Card=107 Bytes=2033)
   6    5         TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=3 Card=107 By
tes=2033)




Statistics
----------------------------------------------------------
         53  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       3791  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        106  rows processed

metatest@META> alter session set query_rewrite_enabled=true;

Session altered.

metatest@META> select e.employee_id ID, e.last_name NAME, sum(E.salary) TOTAL_SA
L,
  2  d.department_name DEPT_NAME
  3  from hr.employees E, hr.departments D
  4  where E.department_id=D.department_id
  5  group by e.employee_id, e.last_name,d.department_name
  6  /

106 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=106 Bytes=6042)
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'HR_META_MVIEW_EQUERY' (MAT_VIEW R
EWRITE) (C
          ost=3 Card=106 Bytes=6042)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       3791  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        106  rows processed

metatest@META>


The above code, you will see, if I disable query_rewrite_enabled, the statement retrieved data from master tables (EMPLOYEES and DEPARTMENTS) instead of revoking MVIEW.

At the end, I will refresh the MVIEW when I update one row in HR.EMPLOYEES
metatest@META> set autotrace off
metatest@META> select employee_id, last_name
  2  from hr.employees
  3  where employee_id=165
  4  /

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        165 Lee

metatest@META>

metatest@META> update hr.employees
  2  set last_name='LEE'
  3  where employee_id=165
  4  /

1 row updated.

metatest@META> commit;

Commit complete.

metatest@META> select employee_id,last_name
  2  from hr.employees
  3  where employee_id=165
  4  /

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        165 LEE

metatest@META> execute dbms_refresh.refresh('META_HR_MVIEW');

PL/SQL procedure successfully completed.

metatest@META> select * from meta_hr_mview
  2  where id=165
  3  /

        ID NAME                       TOTAL_SAL DEPT_NAME
---------- ------------------------- ---------- ----------
       165 LEE                             6800 Sales

metatest@META>

[Updated on: Thu, 25 December 2008 03:54]

Report message to a moderator

Re: Oracle 9i [message #377816 is a reply to message #377801] Thu, 25 December 2008 12:46 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
trantuananh24hg that was a nice example.

But I noticed you have created 2 materialized. Can you please tell why?
My Materialized view definition is different from yours. But I have also enabled query rewrite in MV definition. I pasting it again.
I also don't have Materialized view logs on the table. I don't think we need MV logs for refresh on demand MVs.

NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
Re: Oracle 9i [message #377836 is a reply to message #377816] Thu, 25 December 2008 20:37 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I do not often use MView enable query, but I will re-create an example like yours
metatest@META> select * from tab;

no rows selected

metatest@META> CREATE MATERIALIZED VIEW customers_orders_mv
  2  TABLESPACE RSMGR
  3  NOCACHE
  4  LOGGING
  5  NOPARALLEL
  6  BUILD IMMEDIATE
  7  REFRESH FORCE ON DEMAND
  8  WITH PRIMARY KEY
  9  ENABLE QUERY REWRITE
 10  AS
 11  SELECT   c.customer_id ID, c.cust_first_name NAME,
 12            o.order_date ord_date,
 13           o.order_status ord_status,SUM(o.order_total) ord_total
 14      FROM customers@metadwh_oe c, orders@metadwh_oe o
 15     WHERE c.customer_id = o.customer_id
 16  GROUP BY c.customer_id,
 17           c.cust_first_name,
 18           o.order_date,
 19           o.order_status
 20  order by o.order_date desc
 21  /

Materialized view created.

metatest@META> set autotrace traceonly
metatest@META> select * from customers_orders_mv
  2  /

105 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=105 Bytes=6720)
   1    0   MAT_VIEW ACCESS (FULL) OF 'CUSTOMERS_ORDERS_MV' (MAT_VIEW) (Cost=3 Card=105 Bytes=6720)


Statistics
----------------------------------------------------------
        234  recursive calls
          0  db block gets
         73  consistent gets
          0  physical reads
          0  redo size
       5000  bytes sent via SQL*Net to client
        578  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
        105  rows processed

Re: Oracle 9i [message #377839 is a reply to message #377836] Thu, 25 December 2008 21:10 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
You did select from the MV itself. Please do a select as in the definition of MV. Would you suspect MV logs are required as in my case.
Re: Oracle 9i [message #377842 is a reply to message #377839] Thu, 25 December 2008 21:30 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Oh, as my understanding, you wonder that, you created a MView like that
metatest@META> CREATE MATERIALIZED VIEW customers_orders_mv_meta
  2  TABLESPACE RSMGR
  3  NOCACHE
  4  LOGGING
  5  NOPARALLEL
  6  BUILD IMMEDIATE
  7  REFRESH FORCE ON DEMAND
  8  WITH PRIMARY KEY
  9  ENABLE QUERY REWRITE
 10  AS
 11  SELECT   c.customer_id ID, c.cust_first_name NAME,
 12            o.order_date ord_date,
 13           o.order_status ord_status,SUM(o.order_total) ord_total
 14      FROM oe.customers c, oe.orders o
 15     WHERE c.customer_id = o.customer_id
 16  GROUP BY c.customer_id,
 17           c.cust_first_name,
 18           o.order_date,
 19           o.order_status
 20  order by o.order_date desc
 21  /

Materialized view created.


But when you select from OE.Customers & OE.Orders, you'd like to navigate the statement which retrieved data from MView, not from master tables like here
metatest@META> SELECT   c.customer_id ID, c.cust_first_name NAME,
  2            o.order_date ord_date,
  3           o.order_status ord_status,SUM(o.order_total) ord_total
  4      FROM oe.customers c, oe.orders o
  5     WHERE c.customer_id = o.customer_id
  6  GROUP BY c.customer_id,
  7           c.cust_first_name,
  8           o.order_date,
  9           o.order_status
 10  order by o.order_date desc
 11  /

105 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=105 Bytes=3675)
   1    0   SORT (GROUP BY) (Cost=9 Card=105 Bytes=3675)
   2    1     HASH JOIN (Cost=8 Card=105 Bytes=3675)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ORDERS' (TABLE) (Cost=2 Card=105 Bytes=2520)
   4    3         INDEX (RANGE SCAN) OF 'ORD_CUSTOMER_IX' (INDEX) (Cost=1 Card=1
05)
   5    2       TABLE ACCESS (FULL) OF 'CUSTOMERS' (TABLE) (Cost=5 Card=319 Bytes=3509)

Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
       5000  bytes sent via SQL*Net to client
        578  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        105  rows processed

metatest@META>
Re: Oracle 9i [message #377849 is a reply to message #377842] Thu, 25 December 2008 22:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Check the following link to see if any of the rewrite restrictions apply. http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref511

Try removing the ORDER BY from the MV.

Also try using the DBMS_MVIEW.EXPLAIN_REWRITE() procedure to find out why it is not using rewrite.

Ross Leishman
Re: Oracle 9.2.0.8.0 [message #377938 is a reply to message #377849] Fri, 26 December 2008 09:54 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
the two tables in my materialized are in 2 different schemas.
Re: Oracle 9.2.0.8.0 [message #377939 is a reply to message #377849] Fri, 26 December 2008 10:14 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I don't have an order by in my MV
Re: Oracle 9.2.0.8.0 [message #378267 is a reply to message #377801] Mon, 29 December 2008 11:07 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Please create a same simple view with the same SQL as in the Materialized. Then when you select from the view. It does not use the Materialized view but the tables.

When I use dbms_mview.Explain_Rewrite
I get following 2 messages in my tables.

QSM-01063: query has a dictionary table or view
QSM-01019: no suitable materialized view found to rewrite this query

Does that mean I can't use a table or view in my SQL where I expect MV to be used.
Re: Oracle 9.2.0.8.0 [message #378301 is a reply to message #378267] Mon, 29 December 2008 20:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
QSM-01063: query has a dictionary table or view

Sounds like your SQL references a table owned by SYS.

Are you selecting from DUAL?

Ross Leishman
Re: Oracle 9.2.0.8.0 [message #378457 is a reply to message #378301] Tue, 30 December 2008 09:03 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I create a MV then a View with the same SQL. Then I do a select * from view. It does not use MV. I am not using dual or any other sys owned objects.
Re: Oracle 9.2.0.8.0 [message #378519 is a reply to message #378457] Tue, 30 December 2008 18:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What happens if you just run the SQL that is in the view, rather than selecting from the view?

Ross Leishman
Re: Oracle 9.2.0.8.0 [message #378522 is a reply to message #378519] Tue, 30 December 2008 19:21 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
When I directly use the SQL in the view it uses the Materialized View. My SQL, View SQL and MV SQL are all same.
Re: Oracle 9.2.0.8.0 [message #378525 is a reply to message #378522] Tue, 30 December 2008 20:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I just did a quick test myself:
- Create a table
- Create an aggregate MV
- Select from the table using same SQL as the aggregate MV - did not rewrite
- Repeated SELECT with /*+ REWRITE*/ hint - worked
- Created VIEW with same SQL - no hint
- SELECT * FROM View - didn't rewrite
- SELECT /*+REWRITE*/ * from View - worked.

This tells me that it is possible to get REWRITE working when selecting from a view that has the same SQL as the MV, although I needed to use the REWRITE hint.

Try the REWRITE hint. If that doesn't work, try a much simpler test case and see if that works. My test case was a copy of DBA_TABLES and a view/MV/SQL of SELECT owner, count(*) FROM tbl GROUP BY owner.

Note that I am using 10.2. It may be a limitation on v9

Ross Leishman
Re: Oracle 9.2.0.8.0 [message #379979 is a reply to message #378301] Thu, 08 January 2009 16:14 Go to previous messageGo to next message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
I still couldn't get it working. I guess it probably has got to do something with the type of view and Materialized View I have. Also my 2 tables are in different schemas.

One other question I have related it

********This MV is from Oracle doc******************It is a fast refresh***********************

CREATE MATERIALIZED VIEW cust_dly_sales_mv
BUILD IMMEDIATE REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE AS
SELECT s.time_id, p.prod_id, p.prod_name, COUNT(*),
SUM(s.quantity_sold), SUM(s.amount_sold),
COUNT(s.quantity_sold), COUNT(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id
GROUP BY s.time_id, p.prod_id, p.prod_name;

But when I try to create a fast refresh MV I get the
ORA-12015: cannot create a fast refresh materialized view from a complex query.

I am trying to figure what is the difference between the SQLs in the MVs.
Both the SQL have aggregate, group by, where clause and 2 or more tables.
So what makes the second one complex SQL and first not(on the top not a complex SQL)

CREATE MATERIALIZED VIEW Items_sales_MV
TABLESPACE DATA
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT
trim(r.frm) as sub_group
,i.grp as Group
,r.vend as vendor
,r.itm as ITM
,r.FRCST_YR as FSCL_YR
,sum(r.mon1/1000) as JAN
,sum(r.mon2/1000) as FEB
,sum(r.mon3/1000) as MAR
,sum(r.mon4/1000) as APR
,sum(r.mon5/1000) as MAY
,sum(r.mon6/1000) as JUN
,sum(r.mon7/1000) as JUL
,sum(r.mon8/1000) as AUG
,sum(r.mon9/1000) as SEP
,sum(r.mon10/1000) as OCT
,sum(r.mon11/1000) as NOV
,sum(r.mon12/1000) as DEC
From Item_Master i, Receipt_itm r
Where i.active = 'A'
and i.Item = r.Itm
Group By
r.frm
,i.grp
,r.vend
,r.itm
,r.FRCST_YR;


Re: Oracle 9.2.0.8.0 [message #380009 is a reply to message #379979] Thu, 08 January 2009 19:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
DBMS_MVIEW.EXPLAIN_MVIEW() should tell you why it won't work.

It may be because the SELECT column trim(r.frm) is different to the GROUP BY column r.frm

Ross Leishman
Re: Oracle 9.2.0.8.0 [message #380238 is a reply to message #380009] Fri, 09 January 2009 11:54 Go to previous message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
rleishman thanks a lot.

I think most of my problem was due to "SELECT column trim(r.frm) is different to the GROUP BY column r.frm"

I appreciate your help and insight.
Previous Topic: tuning sql query
Next Topic: Purge recyclebin
Goto Forum:
  


Current Time: Sun Dec 04 20:51:45 CST 2016

Total time taken to generate the page: 0.07735 seconds