Re: Partitioned View??

   We use some partitioned views ourselves in Production databases all around and it works fine.

   How did you create the view. Did you specify a check constraint on the DATE column you partitioning on ? The SELECT statements of the view are using exactly the same WHERE clause as the CHECK CONSTRAINT ? Are you using to_char or to_date for date conversion on the WHERE clause/CHECK CONSTRAINT condition ?

--

   Aram Meguerian
   LHS Communications Systems
   ameguerian_at_us.lhsgroup.com
   ameguerian_at_yahoo.com

   Phone #: +1 305 894 4705
ccdicky in hotmail wrote in message <80c90d$6cc1_at_imsp212.netvigator.com>...
>Hi ther
>
>Anyone can tell me how to make use of partitioned views? I have a table
>with more than 10 millions records. One of the column in this table is
>"DATE" type; therefore, I have divided the big table into 12 smaller tables
>by month and group these 12 tables into a partitioned view following the
>steps mentioned in Oracle Tuning Guide.
>
>All my queries can seems this partitioned view just equvalent to the big
>table; however, the queries need more time to run if I use the partitioned
>view instead of the big table. For example,
>
>select * from paritioned_view where date >= '10-JAN-99' and date
><='25-FEB-99';
>
>Note that the 'DATE" column has been indexed.
>
>It doesn't make any sense to me since I guess the excution plan for above
>query first is to select two corresponding month tables (JAN,FEB), and then
>retrieve the needed records from these two tables in parallel. Therefore,
>the run time for above query is somehow faster than the one using the big
>table instead.
>
>Thanks in advance.
>
>Dicky
>
>
Received on Wed Nov 10 1999 - 09:30:27 CST