Re: Optimizing union based view
Date: Tue, 4 Mar 2008 05:42:59 -0800 (PST)
Message-ID: <952e1604-84da-4ac2-93be-e88365d5b059@e23g2000prf.googlegroups.com>
On Mar 4, 5:17 am, Mathias Waack <M.Wa..._at_gmx.de> wrote:
> Hi Charles,
> Charles Hooper wrote:
> > 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);
>
In the above, replace USER with 'OLD' when gathering statistics for objects owned by the OLD user.
>
> > 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;
>
> 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
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.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Mar 04 2008 - 07:42:59 CST