Re: Optimizing union based view

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 3 Mar 2008 07:09:25 -0800 (PST)
Message-ID: <8127b08a-28bb-44d4-8769-2321097e1b61@8g2000hse.googlegroups.com>


On Mar 3, 8:48 am, Mathias Waack <M.Wa..._at_gmx.de> wrote:
> Hi all,
>
> I'm having a performance problem with a view based on the union of two
> tables:
>
> create table t1(c1 varchar(10), recno number);
> alter table t1 add primary key (recno);
> insert into t1old values ('r1', 1);
> insert into t1old values ('r2', 2);
> create table t1old(c1 varchar(10), recno number);
> alter table t1old add primary key (recno);
> insert into t1old values ('r0', 0);
> create view t1view as select * from t1 union all select * from t1old;
>
> The application using this schema accesses records by its 'recno', so a
> common query is:
>
> select /*+ first_rows(1) */ * from t1 where recno >= 1 order by recno;
>
> Some tables growing very fast, so I've started to expire old records into
> backup tables, thats why we have t1 and t1old. Thus t1 union all t1old
> gives us the original tables.
>
> Now starts my problem: if the application runs the same query as above on
> the view:
>
> select /*+ first_rows(1) */ * from t1view where recno >= 1 order by recno;
>
> Oracle performs a full table scan over both tables after applying the
> filter, thus we get something like this:
>
> sort
>   union all
>     select * from t1 where recno >= 1
>     select * from t1old where recno >= 1.
>
> Usually the application fetches only one record (thats why the optimizer
> hint), but in a very few cases it fetches more than one row. In the common
> case the query is very slow. I know, that
>
> select count(*) from t1view = select count(*) from t1 + select count(*) from
> t1old
>
> and
>
> select max(recno) from t1old < select min(recno) from t1
>
> I think Oracle needs both information to perform better - but how to tell
> it?
>
> Mathias

Thanks for posting the DDL and DML for the test case. Since you indicated that there are many rows involved, I did not use your insert statement. Instead, I used: INSERT INTO
  T1OLD
SELECT
  'R'||TO_CHAR(ROWNUM-1),
  ROWNUM-1
FROM
  DUAL
CONNECT BY
  LEVEL<=200000;

INSERT INTO
  T1
SELECT
  'R'||TO_CHAR(ROWNUM+200000-1),
  ROWNUM+200000-1
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

COMMIT; The above inserted 200,000 rows into T1OLD with values from 0 through 199,999, and 100,000 rows into T1 with values from 200,000 through 299,999.

Now, collect table and index stats:
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE); EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1OLD',CASCADE=>TRUE); Let's try to set up the query without using the view (on Oracle 11.1.0.6), specifying that RECNUM must be at least 250,000: SELECT
  *
FROM
(SELECT /*+ INDEX(T1) */
  C1,
  RECNO
FROM
  T1
UNION ALL
SELECT /*+ INDEX(T1OLD) */
  C1,
  RECNO
FROM
  T1OLD
ORDER BY
  RECNO)
WHERE
  RECNO>=250000
  AND ROWNUM<=1;


| Id  | Operation                       | Name         | Starts | E-
Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
|*  1 |  COUNT STOPKEY                  |              |      1
|        |      1 |00:00:00.59 |     223 |       |       |          |
|   2 |   VIEW                          |              |      1 |
3 |      1 |00:00:00.59 |     223 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY        |              |      1
|        |      1 |00:00:00.59 |     223 |  2048 |  2048 | 2048  (0)|
|   4 |     UNION-ALL                   |              |      1
|        |  50000 |00:00:00.50 |     223 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| T1           |      1 |
50000 |  50000 |00:00:00.20 |     221 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | SYS_C0013568 |      1 |
50000 |  50000 |00:00:00.05 |      96 |       |       |          |
|   7 |      TABLE ACCESS BY INDEX ROWID| T1OLD        |      1 |
1 |      0 |00:00:00.01 |       2 |       |       |          |
|*  8 |       INDEX RANGE SCAN          | SYS_C0013569 |      1 |
1 |      0 |00:00:00.01 |       2 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   6 - access("RECNO">=250000)
   8 - access("RECNO">=250000)

Note that in the above, I am controlling the number of rows returned by the ROWNUM<=1 clause, and Oracle is pushing that restriction into the two halves of the UNION ALL, and it is also pushing in the RECNO restriction into the two halves of the UNION ALL.

Trying again, this time attempting to force Oracle to recognize the MIN/MAX relationship:
SELECT
  *
FROM
(SELECT /*+ INDEX(T1) */
  C1,
  RECNO
FROM
  T1
WHERE
  RECNO>=(
    SELECT
      MIN(RECNO)
    FROM
      T1)
UNION ALL
SELECT /*+ INDEX(T1OLD) */
  C1,
  RECNO
FROM
  T1OLD
WHERE
  RECNO<=(
    SELECT
      MAX(RECNO)
    FROM
      T1)
ORDER BY
  RECNO)
WHERE
  RECNO>=250000
  AND ROWNUM<=1;

The DBMS Xplan:


| Id  | Operation                        | Name         | Starts | E-
Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
|*  1 |  COUNT STOPKEY                   |              |      1
|        |      1 |00:00:00.60 |     320 |       |       |          |
|   2 |   VIEW                           |              |      1
|      3 |      1 |00:00:00.60 |     320 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY         |              |      1
|      3 |      1 |00:00:00.60 |     320 |  2048 |  2048 | 2048  (0)|
|   4 |     UNION-ALL                    |              |      1
|        |  50000 |00:00:00.51 |     320 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID | T1           |      1
|      2 |  50000 |00:00:00.21 |     316 |       |       |          |
|*  6 |       INDEX RANGE SCAN           | SYS_C0013568 |      1 |
450 |  50000 |00:00:00.06 |     191 |       |       |          |
|   7 |        SORT AGGREGATE            |              |      1
|      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   8 |         INDEX FULL SCAN (MIN/MAX)| SYS_C0013568 |      1 |
100K|      1 |00:00:00.01 |       2 |       |       |          |
|   9 |      TABLE ACCESS BY INDEX ROWID | T1OLD        |      1
|      1 |      0 |00:00:00.01 |       4 |       |       |          |
|* 10 |       INDEX RANGE SCAN           | SYS_C0013569 |      1 |
10000 |      0 |00:00:00.01 |       4 |       |       |          |
|  11 |        SORT AGGREGATE            |              |      1
|      1 |      1 |00:00:00.01 |       2 |       |       |          |
|  12 |         INDEX FULL SCAN (MIN/MAX)| SYS_C0013568 |      1 |
100K|      1 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   6 - access("RECNO">=)
       filter("RECNO">=250000)

  10 - access("RECNO">=250000 AND "RECNO"<=)

This time the plan is a bit more complicated, and it takes 0.01 seconds longer to complete.

Maybe, forcing the use of indexes with only two columns in the tables is actually slowing down performance:
SELECT
  *
FROM
(SELECT
  C1,
  RECNO
FROM
  T1
UNION ALL
SELECT
  C1,
  RECNO
FROM
  T1OLD
ORDER BY
  RECNO)
WHERE
  RECNO>=250000
  AND ROWNUM<=1;


| Id  | Operation                       | Name         | Starts | E-
Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
|*  1 |  COUNT STOPKEY                  |              |      1
|        |      1 |00:00:00.43 |     258 |       |       |          |
|   2 |   VIEW                          |              |      1 |
3 |      1 |00:00:00.43 |     258 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY        |              |      1
|        |      1 |00:00:00.43 |     258 |  2048 |  2048 | 2048  (0)|
|   4 |     UNION-ALL                   |              |      1
|        |  50000 |00:00:00.35 |     258 |       |       |          |
|*  5 |      TABLE ACCESS FULL          | T1           |      1 |
50000 |  50000 |00:00:00.05 |     256 |       |       |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| T1OLD        |      1 |
1 |      0 |00:00:00.01 |       2 |       |       |          |
|*  7 |       INDEX RANGE SCAN          | SYS_C0013569 |      1 |
1 |      0 |00:00:00.01 |       2 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   5 - filter("RECNO">=250000)
   7 - access("RECNO">=250000)

The above completed 0.16 second faster. In the above, Oracle automatically used the index on the T1OLD table to determine that no rows would be returned from that table.

A performance improvement that I was not expecting by using the view (this may be specific to Oracle 11.1.0.6): SELECT
  *
FROM
  (SELECT
    *
  FROM
    T1VIEW
  WHERE
    RECNO>=250000)
WHERE
  ROWNUM<=1;


| Id  | Operation                      | Name         | Starts | E-
Rows | A-Rows | A-Time | Buffers |
|*  1 |  COUNT STOPKEY                 |              |      1
|        |      1 |00:00:00.01 |     128 |
|   2 |   VIEW                         | T1VIEW       |      1 |
3 |      1 |00:00:00.01 |     128 |
|   3 |    UNION-ALL                   |              |      1
|        |      1 |00:00:00.01 |     128 |
|*  4 |     TABLE ACCESS FULL          | T1           |      1 |
50000 |      1 |00:00:00.01 |     128 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T1OLD        |      0 |
1 |      0 |00:00:00.01 |       0 |
|*  6 |      INDEX RANGE SCAN          | SYS_C0013569 |      0 |
1 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 - filter(ROWNUM<=1)
   4 - filter("RECNO">=250000)
   6 - access("RECNO">=250000)

The above completed in 0.01 seconds.

I would suggest avoiding the use of the static view, if possible (even though in the above case it seems to have helped).

You might need to perform occasional maintenance on the indexes if you are periodically deleting from the T1 table and adding those rows to the T1OLD table. See the following thread for the symptoms of what may happen if you do not maintain the indexes: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/79d0a3d85381dc64

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Mar 03 2008 - 09:09:25 CST

Original text of this message