Re: Optimizing union based view

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);
>
> 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 Received on Tue Mar 04 2008 - 04:17:37 CST