Re: How to use Partition in Query

Hi Steve
Thanks a lot for taking time and giving me a detail reply eith example.I appreciate it.

Yes You are right , I am using only half of the data (infact half of the half) every time. Here in your example since there is only one partition, so in this case it will do full table scan as well as the partition pruning.But in my case I have more 10 partitions.If it uses full table scan then there is no use of all th epartitions.

Suppose if I want partition number 9 to fetched and optimizer is still using all partitions then all the partitions are useless.Is there any way that I can dynamically chose a particular partition at particular time.

Thanks alot for you help.
Regards

Lucky

stevedhoward_at_gmail.com wrote:
> If I am understanding you correctly, you are selecting half the data
> all the time. In this case. Oracle will still full scan the table (as
> we would expect), but it should also prune the partition, eliminating
> roughly half the I/O. See below for a simple test case.
>
> SQL> create table t0410(c number) partition by range(c)
> 2 (partition part_1 values less than (50000),
> 3 partition part_max values less than(maxvalue))
> 4 /
>
> Table created.
>
> SQL> create table t0410_a (c number);
>
> Table created.
>
> SQL> begin
> 2 for i in 1..100000 loop
> 3 insert into t0410 values(i);
> 4 end loop;
> 5 end;
> 6 /
>
> PL/SQL procedure successfully completed.
>
> SQL> begin
> 2 for i in 1..100000 loop
> 3 insert into t0410_a values(i);
> 4 end loop;
> 5 end;
> 6 /
>
> PL/SQL procedure successfully completed.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from t0410 where c= 20000;
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 136084398
> ------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time | Pstart| Pstop |
> ------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 13 | 20 (5)|
> 00:00:01 | | |
> | 1 | PARTITION RANGE SINGLE| | 1 | 13 | 20 (5)|
> 00:00:01 | 1 | 1 |
> |* 2 | TABLE ACCESS FULL | T0410 | 1 | 13 | 20 (5)|
> 00:00:01 | 1 | 1 |
> ------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("C"=20000)
>
> Note
> -----
> - dynamic sampling used for this statement
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 85 consistent gets
> 0 physical reads
> 0 redo size
> 404 bytes sent via SQL*Net to client
> 381 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL> select * from t0410_a where c= 20000;
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 1827387462
> -----------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
> -----------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 2 | 26 | 42 (3)|
> 00:00:01 |
> |* 1 | TABLE ACCESS FULL| T0410_A | 2 | 26 | 42 (3)|
> 00:00:01 |
> -----------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter("C"=20000)
>
> Note
> -----
> - dynamic sampling used for this statement
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 185 consistent gets
> 0 physical reads
> 0 redo size
> 404 bytes sent via SQL*Net to client
> 381 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL>
>
> In each case, a FTS occurs, but we do half the I/O when the partition
> is pruned 85 to 185), as measured by consistent gets.
>
> Regards,
>
> Steve
Received on Mon Apr 10 2006 - 19:03:30 CDT