Re: Optimizing union based view

On Mar 4, 5:17 am, Mathias Waack <M.Wa...@xxxxxx> 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.
.

Relevant Pages

  • Re: Attn: Mr. Mook
    ... energy into the atmosphere, so as to maintain an artificially inflated ... dramatically reducing the cost of PV materials. ... the PV cell at a rate of 110 watts per square centimeter. ...
    (sci.energy.hydrogen)
  • Attn: Mr. Mook / rogue green energy Czar + Brad Guth
    ... dramatically reducing the cost of PV materials. ... the PV cell at a rate of 110 watts per square centimeter. ... solar disk with an accuracy of a few degrees. ...
    (sci.energy.hydrogen)
  • Re: Attn: Mr. Mook
    ... dramatically reducing the cost of PV materials. ... the PV cell at a rate of 110 watts per square centimeter. ... solar disk with an accuracy of a few degrees. ...
    (sci.energy.hydrogen)
  • Re: energy need to produce one litre of hydrogen?
    ...   That's 10/147th the mass of a liter of air. ... liters of AIR per second - so this is the air fuel mix by volume. ... sufficiently low cost. ...
    (sci.energy.hydrogen)
  • Re: Parameter to influence Oracles Idea of IO Cost?
    ... Does my memory fail me? ... This parameter does not affect the cost of a single table ... Oracle docs are a bit sparse IMHO. ... SYSTEM STATISTICS INFORMATION ...
    (comp.databases.oracle.server)
Loading