Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Optimizing union based view

75 views
Skip to first unread message

Mathias Waack

unread,
Mar 3, 2008, 8:48:30 AM3/3/08
to
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

Charles Hooper

unread,
Mar 3, 2008, 10:09:25 AM3/3/08
to

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.

Mathias Waack

unread,
Mar 4, 2008, 5:17:37 AM3/4/08
to
Hi Charles,

Charles Hooper wrote:

this works for me - as long as both tables are in the same schema. After
creating a schema 'old' and moving t1old into old, the query becomes:

SELECT
*
FROM
(SELECT /*+ INDEX(T1) */
C1,
RECNO
FROM
T1
UNION ALL

SELECT /*+ INDEX(old.T1OLD) */
C1,
RECNO
FROM
old.T1OLD
ORDER BY
RECNO)

Now oracle again uses a full table scan for old.t1old. Is this a permissions
problem? Of course I'm able to read old.t1old, but why doesn't oracle use
the index in this case?

Mathias

Charles Hooper

unread,
Mar 4, 2008, 8:42:59 AM3/4/08
to
> DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1OLD',CASCADE=>TRUE)­;
>

In the above, replace USER with 'OLD' when gathering statistics for
objects owned by the OLD user.

I believe that the second index hint is invalid. The index hint
should specify the table's aliased name in the SQL statement, in this
case just T1OLD. The second index hint is being ignored, so Oracle is
using the normal execution plan for the second half of the UNION ALL
using the calculated cost to determine the predicted least expensive
execution plan, which to Oracle appears to be a full table scan.

(Tests performed on 11.1.0.6 - your results may be a bit different
based on initialization parameters, CPU costing, and version).
In the following tests, table T1 is in one schema, and the table and
primary key index were analyzed using DBMS_STATS. Table T1OLD is in
another schema, and that table and its primary key index were also
analyzed using DBMS_STATS. The SQL statements and execution plans
were gathered by the user owning the schema with the T1 table.
First, the SQL statement with the invalid index hint:


SELECT
*
FROM
(SELECT /*+ INDEX(T1) */
C1,
RECNO
FROM
T1
UNION ALL

SELECT /*+ INDEX(OLD.T1OLD) */
C1,
RECNO
FROM
OLD.T1OLD
ORDER BY
RECNO)

The execution plan (predicted, with expected costs identified):
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| | 1951 (100)| |
| 1 | VIEW | | 300K|
5859K| | 1951 (71)| 00:00:24 |
| 2 | SORT ORDER BY | | 300K|
3515K| 11M| 575 (24)| 00:00:07 |
| 3 | UNION-ALL | | |
| | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 100K|
1171K| | 440 (1)| 00:00:06 |
| 5 | INDEX FULL SCAN | SYS_C0013575 | 100K|
| | 190 (1)| 00:00:03 |
| 6 | TABLE ACCESS FULL | T1OLD | 200K|
2343K| | 135 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------------------

The execution plan (actual, with timings):


------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-
Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------

| 1 | VIEW | | 1 |
300K| 300K|00:00:04.15 | 925 | | | |
| 2 | SORT ORDER BY | | 1 |
300K| 300K|00:00:03.24 | 925 | 9266K| 1184K| 8236K (0)|
| 3 | UNION-ALL | | 1
| | 300K|00:00:02.40 | 925 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 |
100K| 100K|00:00:00.40 | 438 | | | |
| 5 | INDEX FULL SCAN | SYS_C0013575 | 1 |
100K| 100K|00:00:00.10 | 189 | | | |
| 6 | TABLE ACCESS FULL | T1OLD | 1 |
200K| 200K|00:00:00.20 | 487 | | | |
------------------------------------------------------------------------------------------------------------------------------------

In the above, note the predicted cost of 1951 with the invalid index
hint (full table scan of T1OLD has a cost of 135).

Now with the fixed index hint:


SELECT
*
FROM
(SELECT /*+ INDEX(T1) */
C1,
RECNO
FROM
T1
UNION ALL
SELECT /*+ INDEX(T1OLD) */
C1,
RECNO
FROM

OLD.T1OLD
ORDER BY
RECNO)

The execution plan (predicted, with expected costs identified):
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| | 2674 (100)| |
| 1 | VIEW | | 300K|
5859K| | 2674 (52)| 00:00:33 |
| 2 | SORT ORDER BY | | 300K|
3515K| 11M| 1298 (67)| 00:00:16 |
| 3 | UNION-ALL | | |
| | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 100K|
1171K| | 440 (1)| 00:00:06 |
| 5 | INDEX FULL SCAN | SYS_C0013575 | 100K|
| | 190 (1)| 00:00:03 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1OLD | 200K|
2343K| | 858 (1)| 00:00:11 |
| 7 | INDEX FULL SCAN | SYS_C0013577 | 200K|
| | 377 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------

The execution plan (actual, with timings):


------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-
Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------

| 1 | VIEW | | 1 |
300K| 300K|00:00:04.79 | 1292 | | | |
| 2 | SORT ORDER BY | | 1 |
300K| 300K|00:00:03.88 | 1292 | 9266K| 1184K| 8236K (0)|
| 3 | UNION-ALL | | 1
| | 300K|00:00:03.00 | 1292 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 |
100K| 100K|00:00:00.40 | 438 | | | |
| 5 | INDEX FULL SCAN | SYS_C0013575 | 1 |
100K| 100K|00:00:00.10 | 189 | | | |


| 6 | TABLE ACCESS BY INDEX ROWID| T1OLD | 1 |

200K| 200K|00:00:00.80 | 854 | | | |
| 7 | INDEX FULL SCAN | SYS_C0013577 | 1 |
200K| 200K|00:00:00.20 | 375 | | | |
------------------------------------------------------------------------------------------------------------------------------------

In the above, note that the predicted cost of 2674 is greater than the
predicted cost of 1951 with the invalid index hint (index access of
T1OLD has a cost of 854 compared with the full table scan cost of
135). That is why Oracle selected to perform a full table scan,
rather than use the index on the T1OLD table. The actual time did
increase with the corrected index hint, so it looks like Oracle's
prediction was correct.

Now, let's go back to the original SQL statement with the invalid
index hint, and this time add a restriction to the RECNO column, such
that Oracle is 99% (or more) certain that the T1OLD table will be
excluded. This will help us see if Oracle switches from a full table
scan on the T1OLD table to an index scan on that table:


SELECT
*
FROM
(SELECT /*+ INDEX(T1) */
C1,
RECNO
FROM
T1
UNION ALL

SELECT /*+ INDEX(OLD.T1OLD) */
C1,
RECNO
FROM
OLD.T1OLD
ORDER BY
RECNO)
WHERE RECNO>250000

The execution plan (predicted, with expected costs identified):
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 218 (100)| |
| 1 | VIEW | | 49999 |
634K| 218 (1)| 00:00:03 |
| 2 | SORT ORDER BY | | |
| | |
| 3 | UNION-ALL | | |
| | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 49999 |
585K| 221 (1)| 00:00:03 |
|* 5 | INDEX RANGE SCAN | SYS_C0013575 | 49999 |
| 96 (2)| 00:00:02 |
| 6 | TABLE ACCESS BY INDEX ROWID| T1OLD | 1 | 12
| 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | SYS_C0013577 | 1 |
| 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("RECNO">250000)
7 - access("RECNO">250000)

The execution plan (actual, with timings):


------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-
Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------

| 1 | VIEW | | 1 |
49999 | 49999 |00:00:00.79 | 223 | | | |
| 2 | SORT ORDER BY | | 1
| | 49999 |00:00:00.64 | 223 | 1612K| 624K| 1432K (0)|
| 3 | UNION-ALL | | 1
| | 49999 |00:00:00.50 | 223 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 |
49999 | 49999 |00:00:00.20 | 221 | | | |
|* 5 | INDEX RANGE SCAN | SYS_C0013575 | 1 |
49999 | 49999 |00:00:00.05 | 96 | | | |


| 6 | TABLE ACCESS BY INDEX ROWID| T1OLD | 1 |
1 | 0 |00:00:00.01 | 2 | | | |

|* 7 | INDEX RANGE SCAN | SYS_C0013577 | 1 |
1 | 0 |00:00:00.01 | 2 | | | |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("RECNO">250000)
7 - access("RECNO">250000)

In the above, the cost for checking the T1OLD table dropped from 854
(when forcing the index access) before the RECNO restriction to 3
(down from 135 when Oracle performed a full table scan).

So, Oracle decides whether or not to use an index or full table scan
based on the predicted cost. Examination of a 10053 trace file will
confirm that this is the case.

I noticed that you dropped the AND ROWNUM<=1 restriction. That
restriction does help reduce the amount of time, if you know that the
user is only interested in one row. But, without the RECNO
retriction, Oracle still needs to perform the UNION ALL on the two
tables in order to sort the rows by RECNO to determine the first row.

Mathias Waack

unread,
Mar 10, 2008, 9:17:59 AM3/10/08
to
Hi Charles,

> > > 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;

> I noticed that you dropped the AND ROWNUM<=1 restriction.  That


> restriction does help reduce the amount of time, if you know that the
> user is only interested in one row.  But, without the RECNO
> retriction, Oracle still needs to perform the UNION ALL on the two
> tables in order to sort the rows by RECNO to determine the first row.

I had to remove the ROWNUM restriction because the application sometimes
fetches more records. But after understanding your comments about the
possible optimization I've finally managed it to get everything running
with a passable performance.

Thank you!

Mathias

0 new messages