Steve Robin wrote:
> Sorting is taking time in Materialized view.
> You can see below, here sorting is taking time. Is there any way I can
> stop sorting or make it fast.
>
> SQL> CREATE MATERIALIZED VIEW "MCC_CATALOG_2"."REL_SYS_NAME_IPPSYSNAM"
> 2 BUILD IMMEDIATE
> 3 REFRESH FORCE with rowid
> 4 ENABLE QUERY REWRITE
> 5 AS
> 6 SELECT IPP_SYSTEM_NAME FROM MCC_CATALOG_2.RELEASE_SYSTEM_NAME
> where IPP_SYSTEM_NAME is not null;
>
> Materialized view created.
>
> Elapsed: 00:00:10.09
> SQL> explain plan for
> 2 select distinct IPP_SYSTEM_NAME FROM
> MCC_CATALOG_2.RELEASE_SYSTEM_NAME;
>
> Explained.
>
> Elapsed: 00:00:00.00
> SQL> @?\rdbms\admin\utlxplp.sql
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------
>
> -----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost |
> -----------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 319 | 2871 |
> 1425 |
> | 1 | SORT UNIQUE | | 319 | 2871 |
> 1425 |
> | 2 | TABLE ACCESS FULL | RELEASE_SYSTEM_NAME | 525K|
> 4617K| 706 |
> -----------------------------------------------------------------------------
>
> Note: cpu costing is off
>
> 10 rows selected.
>
> SQL> explain plan for
> 2 select distinct IPP_SYSTEM_NAME FROM
> MCC_CATALOG_2.RELEASE_SYSTEM_NAME where IPP_SYSTEM_NAME is not null;
>
> Explained.
>
> Elapsed: 00:00:00.06
> SQL> @?\rdbms\admin\utlxplp.sql
>
> PLAN_TABLE_OUTPUT
> ----------------------------------------------------------------------------------------------------------------
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes
> |TempSpc| Cost |
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 475K|
> 9M| | 2389 |
> | 1 | SORT UNIQUE | | 475K|
> 9M| 25M| 2389 |
> | 2 | TABLE ACCESS FULL | REL_SYS_NAME_IPPSYSNAM | 475K|
> 9M| | 232 |
> ----------------------------------------------------------------------------------------
>
> Note: cpu costing is off
>
> 10 rows selected.
>
> Elapsed: 00:00:00.00
Take out the DISTINCT keyword or enable query rewrite and build another
mv with the DISTINCT IPP_SYSTEM_NAMEs. Both probably being bad suggestions.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Mar 12 2007 - 11:01:38 CDT
