Re: Optimizer change from 8i-9i

From: zhu chao <zhuchao_at_gmail.com>
Date: Tue, 10 May 2005 13:08:56 +0800
Message-ID: <962cf44b05050922087fc03783@mail.gmail.com>

Hi,=20
  About the RBO change in 9i, can you show me a test case? I did some test, I can't prove it changed, but I can't prove it unchanged, as it is more difficult.
Test SQL:
select * from=20
(select id,name from test where id<1000) where id>10000
/

In RBO, oracle always run the inner subquery then filter with ID>10000. The same behavior in 8i/9i.
But in 9i if I convert to CBO, there is no lio, as oracle CBO seems pre-evaluate the subquery and merge the view.

For point 2, we are using migration script to do upgrade, we don't use exp/imp, so there is no such kind of concern.

THanks
Chao

SQL> @testcase
SQL> CREATE TABLE TEST(ID NUMBER, NAME VARCHAR2(300)); Table created.

SQL> begin for i in 1..10000 loop insert into test values(i,'aa'); end loop; end ;
  2 /

PL/SQL procedure successfully completed.

SQL> COMMIT; Commit complete.

SQL>=20
SQL> SET AUTOTRACE ON
SQL>=20
SQL> select * from

  2 (select id,name from test where id<1000)   3 where id>10000
  4 /

no rows selected

Execution Plan

---

   0 SELECT STATEMENT Optimizer=3DCHOOSE    1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics

---

          0  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>=20
SQL> ANALYZE TABLE TEST COMPUTE STATISTICS; Table analyzed.

SQL> select * from
  2 (select id,name from test where id<1000)   3 where id>10000
  4 /

no rows selected

Execution Plan

---

   0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D4 Card=3D1 Bytes=3D5)    1 0 FILTER
   2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=3D4 Card=3D1 Bytes=3D5)

Statistics

---

          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

On 5/10/05, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
>=20

> >>1. For SQLs using RBO, the plan for the existing SQL won''t change,
> >>right? As oracle says no change for RBO since oracle 7.3.
> >
> >Wrong. You could see some differences.

>=20

> Let me precise this point...

>=20

> There are two reasons that lead to different execution plans with the RBO= :
> 1) Differences in the optimizer itself. > 2) The indexes of the new database are not created in the same order as t= he indexes of the old one.
>=20
>=20

--=20
Regards
Zhu Chao
www.cnoug.org

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 10 2005 - 01:13:26 CDT