Thanks for the question, Girish.
Asked: April 15, 2014 - 4:55 am UTC
Last updated: April 17, 2014 - 6:01 pm UTC
Version: 11.2
Viewed 1000+ times
You Asked
Hi Tom,
I have questions on table partitioning in Oracle
1) What is a bloom filter and how it works.I see them in execution plan as BF000 are these bloom filters ?
2) I have a table tab_p with columns as given below
create table tab_p(
c1 number,
c2 number,
c3 number,
c4 number ,
CONSTRAINT tab_p_pk PRIMARY KEY (c1,c2) local
(
partition pi1,
partition pi2,
partition pi3,
partition pi4
)
partition by hash(c1)
(
partition pt1,
partition pt2,
partition pt3,
partition pt4
);
Here my index column combination(c1,c2) is different from partition key c1
in such cases is it going to use partition pruning
if (a) I have a filter condition c2=10
(b) I have a filter condition c2=10 and c1=30
all partition and tablespaces are available and online
3)
In this table
create table tab_p(
c1 number,
c2 number,
c3 number,
c4 number )
partition by hash(c1)
(
partition pt1,
partition pt2,
partition pt3,
partition pt4
);
create index tab_p_gidx on tab_p(c1,c2) global;
Is query is going to use partition pruning
if (a) I have a filter condition c2=10
(b) I have a filter condition c2=10 and c1=30
all partition and tablespaces are available and online
Thanks,
Girish
and Tom said...
1)
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4226505600346291174 2) you could easily answer these questions yourself - using a simple "autotrace" or dbms_xplan.display_cursor call!!! unfortunately, you didn't give me a create table that actually creates :( I'll use a hash partitioned table with 4 partitions and a locally partitioned primary key index... your create index for the global index cannot coexist with the locally partitioned one.
2a) since you have not partitioned by C2, a where clause solely on "where c2=10" could not possibly use partition pruning since c2=10 could be in ALL partitions.
2b) since you include "c1=30" and you have partitioned on c1, then we would be able to partition prune, yes. Either on the index (assuming you meant to use a locally partitioned unique index on (c1,c2) or the table partition. In this case - we would probably do an index partition prune to get to c2=10 and c1=30 and then using the local rowid we got - an index access to the single table partition