| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anyway to optimize the optimizer
I thought about the partitioned view, but it will be ugly plus Oracle is
not planning to continue to support partitioned views.
Thanks
Waleed
-----Original Message-----
From: jaromir nemec [mailto:jaromir_at_db-nemec.com]=20
Sent: Thursday, March 03, 2005 5:28 PM
To: Khedr, Waleed; oracle-l_at_freelists.org
Subject: Re: Anyway to optimize the optimizer
Hi Waleed,
> Oracle does not substitute the value of C2 in the expression.
this could be achieved with two nested views using an expression e.g.
(c2 -=20
10) in the inner view and referencing this new column in query of the
outer.=20
But this is apparently not the desired solution.
I'd see a possible solution of this problem in a combination of
partitioned=20
views and partitioned tables (Jonathan mentioned it in his book, if I
recall=20
it correctly).
Something like this:
create or replace view v3
as
select c1,c2,c3
from test_p_v
where c2 >=3D 210 and c2 <=3D 309 and c1 >=3D 200
union all
select c1,c2,c3
from test_p_v
where c2 between 110 and 209 and c1 between 100 and 209
union all
select c1,c2,c3
from test_p_v
where c2 < 110 and c1 <=3D 99
;
So each UNION ALL subquery access at most 2 partitions. Note that this
is=20
only simplified solution to demonstrate the principle.
select * from v3
where c2 =3D 150;
Explaining this simple query you get following execution plan
(10.1.0.2.0):
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)|=20
Time | Pstart| Pstop | ------------------------------------------------------------------------ ------------------------------- | 0 | SELECT STATEMENT | | 483 | 14490 | 342
(21)|=20
00:00:01 | | | | 1 | VIEW | V3 | 483 | 14490 | 342
(21)|=20
00:00:01 | | | | 2 | UNION-ALL PARTITION | | | | |=20 | | | |* 3 | FILTER | | | | |=20 | | | | 4 | PARTITION RANGE SINGLE | | 1 | 27 | 7622
(6)|=20
00:00:07 | 3 | 3 | |* 5 | TABLE ACCESS FULL | TEST_P_V | 1 | 27 | 7622
(6)|=20
00:00:07 | 3 | 3 | | 6 | PARTITION RANGE ITERATOR| | 60 | 1740 | 18148
(21)|=20
00:00:17 | 2 | 3 | |* 7 | TABLE ACCESS FULL | TEST_P_V | 60 | 1740 | 18148
(21)|=20
00:00:17 | 2 | 3 | |* 8 | FILTER | | | | |=20 | | | | 9 | PARTITION RANGE SINGLE | | 1 | 26 | 7193
(6)|=20
00:00:07 | 1 | 1 | |* 10 | TABLE ACCESS FULL | TEST_P_V | 1 | 26 | 7193
(6)|=20
00:00:07 | 1 | 1 | ------------------------------------------------------------------------ -------------------------------
Predicate Information (identified by operation id):
3 - filter(210<=3D150)
5 - filter("C2"=3D150 AND "C2">=3D210 AND "C2"<=3D309)
7 - filter("C2"=3D150 AND "C2">=3D110 AND "C2"<=3D209 AND = "C1"<=3D209)
8 - filter(110>150)
10 - filter("C2"=3D150 AND "C2"<110 AND "C1"<=3D99)
A good question is if the obviously false filter (e.g. 210<=3D150) does =
prohibit the underlying table access. Some simple tests shows that this
is=20
OK and at most one union branch is executed if a equi-predicate on c2 is
defined. See below.
You may wont to verify some more complex queries on this view, usage of
bind=20
variables etc.
HTH Jaromir D.B. Nemec
1 select count(*) from v3
2* where c2 =3D 550
SQL> / Elapsed: 00:00:00.01
2) test partition pruning, only the 1st partition should be accessed --
>=20
OK, consistent gets corresponds to blocks of the partition
1 select count(*) from v3
2* where c2 =3D 109
SQL> / Elapsed: 00:00:04.32
Statistics
8 recursive calls
0 db block gets
18778 consistent gets
18758 physical reads
0 redo size
392 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> select partition_name,
2 blocks
3 from user_tab_partitions a
4 where
5 table_name =3D upper('test_p_v');
PARTITION_NAME BLOCKS ------------------------------ ---------- P1 18765 P2 19785 P3 19913
Elapsed: 00:00:03.34
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 03 2005 - 17:36:32 CST
 |
 |