| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle8 partitions and joins question.
A couple of guide-line experiments:
a) create a partitioned table, with partition key column
the same as primary key column. Select a single row from the table using a literal primary key value, a bind variable primary key value, and then repeat specifying the correct partition. Check the logical I/O by setting sql_trace true before the start of the experiment.
create table t1 (
n1 number,
n2 number,
v1 varchar2(200)
)
partition by range (n1)
(
partition p1 values less than (1), partition p2 values less than (2), partition p3 values less than (3), partition p4 values less than (4), partition p5 values less than (5), partition p6 values less than (6)
create unique index i1 on t1(n1) local; alter table t1 add constraint t1_pk primary key (n1);
column num new_value m_num;
select count(*) num from all_objects;
insert into t1 select 0+(rownum-1)/&m_num, rownum, object_name from
all_objects;
insert into t1 select 1+(rownum-1)/&m_num, rownum, object_name from
all_objects;
insert into t1 select 2+(rownum-1)/&m_num, rownum, object_name from
all_objects;
insert into t1 select 3+(rownum-1)/&m_num, rownum, object_name from
all_objects;
insert into t1 select 4+(rownum-1)/&m_num, rownum, object_name from
all_objects;
insert into t1 select 5+(rownum-1)/&m_num, rownum, object_name from
all_objects;
analyze table t1 compute statistics;
declare
v_n1 number := 4.5;
v_n2 number;
begin
select count(*) into v_n2
from t1
where n1 = 4.5;
end;
/
declare
v_n1 number := 4.5;
v_n2 number;
begin
select count(*) into v_n2
from t1
where n1 = v_n1;
end;
/
declare
v_n1 number := 4.5;
v_n2 number;
begin
select count(*) into v_n2
from t1 partition (p5)
where n1 = v_n1;
end;
/
To my surprise, the logical I/O was identical in all 3 cases. I was aware that the optimizer could detect that the query was for a single row, but it must be checking the actual input value very early and avoiding ALL index probes to do this. You should repeat the test on a much large scale - With a large number of partitions you may find a CPU cost of checking the incoming value against the list of partition boundaries - on just 6 partitions the difference is too small to see.
b) testing the effects of joins. Create a small driving
table that will join a few rows into the partiiton table, then check (autotrace on) a join condition.
create table t2 as
select * from t1 where rownum <= 40;
rem
rem Muck about a bit to clone more rows viz:
rem multiple rows by 4
rem add 4 to n1 to target partition 5
rem
rem
rem Select from the table
rem
select /*+ ordered use_nl index(t1) */
sum(t1.n2)
from t2,t1 t1
where t2.n2 between 27 and 28
and t1.n1 = t2.n1
/
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=676) 1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=15 Card=13 Bytes=676)
3 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=13 Bytes=338)
4 2 PARTITION (SINGLE)
5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T1' (Cost=1
Card=7458 Bytes=193908)
6 5 INDEX (UNIQUE SCAN) OF 'I1' (UNIQUE)
Statistics
33 consistent gets
rem
rem Select from the targetted partition
rem
1 select /*+ ordered use_nl index(t1) */
2 sum(t1.n2)
3 from t2,t1 partition (p5) t1
4 where t2.n2 between 27 and 28
5* and t1.n1 = t2.n1
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=676) 1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=15 Card=13 Bytes=676)
3 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=13 Bytes=338)
4 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T1' (Cost=1 Ca
rd=1243 Bytes=32318)
5 4 INDEX (UNIQUE SCAN) OF 'I1' (UNIQUE)
Statistics
27 consistent gets
Conclusion:
I have shown the execution plans (which show a key difference
when the partition is specified) and just the logical I/O from
the stats. As you can see oracle has to do some trial and
error work in the join when the partition is NOT specified.
The scan cost 9 consistent gets, so the difference in the
logical I/O cost between specified partition and general
table join was actually 18 blocks against 24 blocks.
Again you should test this on a much larger scale.
--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk
Prasanth Duvvur wrote in message <373C56C4.16C030C4_at_ncs.com>...
>>I interpret this as being: the first (one or two) columns >>of the primary key are being used as the partition key. > >Yes actually the primary key is only one column so, partition key = primary >key. > >>But will they be prefixed (start with the partition key) or non-prefixed, >>or a mixture of both. Pre-fixed can be undesirably large but >>may give you partition elimination in circumstances that would >>otherwise require you to check every partition. > >Yes they will be prefixed. > >>Will the queries include literal values for the columns that define >>the partitions, will they use bind variables, will the partition key >>columns only appear as join columns in the WHERE clause. >>Will you use queries that join on the secondary indexes and do >>not use the partition key columns at all ? Will your use of the >>partition key columns in join queries be equality, bounded >>range scan (BETWEEN) or unbounded range scan (<= ,>=). >>Are typical queries supposed to collect a few rows, or lots of >>rows. > >most queries will look like: >from table >table.partition_key = :value (only returning one record) > >there will be a report running once a night that will join with another >table: >from table, other_table >table.partition_key=other_table.field (returning several records) > >What do you think, about using local prefixed indexes for this situation? I >tried a sample query (the join query) , and it appeared that the optimizer >did a partition concatenation (explain plan output), with local prefixed >indexes. > >Thanks. >Received on Fri May 14 1999 - 15:42:18 CDT
 |
 |