Re: Optimizer change from 8i-9i
- From: zhu chao <zhuchao@xxxxxxxxx>
- To: Christian Antognini <Christian.Antognini@xxxxxxxxxxxx>
- Date: Tue, 10 May 2005 13:08:56 +0800
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@xxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
