Materiazed View Sorting cost increasing

From: Steve Robin <ocmaman_at_gmail.com>
Date: 11 Mar 2007 21:48:01 -0700
Message-ID: <1173674881.505959.287690@q40g2000cwq.googlegroups.com>

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 Received on Sun Mar 11 2007 - 23:48:01 CDT