| 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: VLDBA's: gather stats on a large table
Hi Zoran
>My understanding is a little bit contradictory with
>your last sentence because whenever Oracle at parse
>time does not know exactly the one partition it will
>operate on (e.g. when you have from KEY to KEY or you
>have from 1 to 10) Oracle optimizer will use global
>statistics.
>
>Only if you have fixed one partition used in the
>execution plan Oracle is going to use partition
>statistics of that particular partition, like from 10
>to 10.
If Oracle has to read more than one partition it is true that global = statistics are used.
But if a single partition is used (according to your example, from 10 to = 10), partition-level statistics are used if the partition key is know. = Therefore, if partition pruning is used, partition-level statistics are = important.
HTH
Chris
PS: an example... (notice that I manually removed some columns (Bytes, = Cost and Time) from the autotrace output)
SQL> create table t (c1 number(10))
2 partition by range (c1)
3 (
4 partition t_p1 values less than (10), 5 partition t_p2 values less than (20), 6 partition t_p3 values less than (30), 7 partition t_p4 values less than (40), 8 partition t_p5 values less than (50)9 );
SQL> insert into t select mod(rownum,10) from dba_objects where rownum =
<=3D 5000;
SQL> insert into t select mod(rownum,20) from dba_objects where rownum =
<=3D 4000;
SQL> insert into t select mod(rownum,30) from dba_objects where rownum =
<=3D 3000;
SQL> insert into t select mod(rownum,40) from dba_objects where rownum =
<=3D 2000;
SQL> insert into t select mod(rownum,50) from dba_objects where rownum =
<=3D 1000;
SQL> exec dbms_stats.gather_table_stats(user,'T')
SQL> select partition_name, sum(num_rows)
2 from user_tab_partitions
3 where table_name =3D 'T'
4 group by rollup(partition_name);
PARTITION_NAME SUM(NUM_ROWS)
------------------------------ -------------
T_P1 8700
T_P2 3700
T_P3 1700
T_P4 700
T_P5 200
15000
SQL> set autotrace trace exp
SQL> select * from t where c1 in (29);
Execution Plan
| 0 | SELECT STATEMENT | | 170 | | | | 1 | PARTITION RANGE SINGLE| | 170 | 3 | 3 | |* 2 | TABLE ACCESS FULL | T | 170 | 3 | 3 | ---------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("C1"=3D29)
=3D=3D=3D =3D=3D=3D single partition, therefore partition-level stats are used =3D=3D=3D Card=3Dpart_num_rows/part_distinct_keys=3D1700/10=3D170 =3D=3D=3D
SQL> select * from t where c1 in (30);
Execution Plan
| 0 | SELECT STATEMENT | | 70 | | | | 1 | PARTITION RANGE SINGLE| | 70 | 4 | 4 | |* 2 | TABLE ACCESS FULL | T | 70 | 4 | 4 | ---------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("C1"=3D30)
=3D=3D=3D =3D=3D=3D single partition, therefore partition-level stats are used =3D=3D=3D Card=3Dpart_num_rows/part_distinct_keys=3D700/10=3D70 =3D=3D=3D
SQL> select * from t where c1 in (29,30);
Execution Plan
| 0 | SELECT STATEMENT | | 600 | | | | 1 | PARTITION RANGE INLIST| | 600 |KEY(I) |KEY(I) | |* 2 | TABLE ACCESS FULL | T | 600 |KEY(I) |KEY(I) | ---------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("C1"=3D29 OR "C1"=3D30)
=3D=3D=3D =3D=3D=3D more than one partition, therefore global stats are used =3D=3D=3D Card=3D2*tab_num_rows/tab_distinct_keys=3D2*15000/50=3D600 =3D=3D=3D
SQL> variable b1 number
SQL> exec :b1 :=3D 29;
PL/SQL procedure successfully completed.
SQL> select * from t where c1 in (:b1);
Execution Plan
| 0 | SELECT STATEMENT | | 300 | | | | 1 | PARTITION RANGE SINGLE| | 300 | KEY | KEY | |* 2 | TABLE ACCESS FULL | T | 300 | KEY | KEY | ---------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("C1"=3DTO_NUMBER(:B1))
=3D=3D=3D
=3D=3D=3D single partition but partition key not know, therefore global =
stats are used
=3D=3D=3D Card=3Dtab_num_rows/tab_distinct_keys=3D15000/50=3D300
=3D=3D=3D
SQL> drop table t purge;
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 12 2005 - 07:57:52 CDT
 |
 |