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@xxxxxxxxxxxx]=20 Sent: Thursday, March 03, 2005 5:28 PM To: Khedr, Waleed; oracle-l@xxxxxxxxxxxxx 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 http://www.db-nemec.com ---- some test -- 1) c2 out of range - instance responce 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 ----- Original Message -----=20 From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx> To: "jaromir nemec" <jaromir@xxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx> Sent: Thursday, March 03, 2005 3:37 AM Subject: RE: Anyway to optimize the optimizer -- //www.freelists.org/webpage/oracle-l