Re: tune between query/join
Date: Mon, 24 Oct 2011 15:14:44 +0300
Message-ID: <4EA556B4.5010906_at_iki.fi>
With similar range join problem I have used sdo_join to reduce execution time from days to minutes. With this example data sdo_join is slower.
A basic example of using function based r-tree indexing http://rafudb.blogspot.com/2010/04/dark-side-of-easter.html
And for this issue an SDO_JOIN example:
select
min(lowest_amount),max(lowest_amount),min(highest_amount),max(highest_amount)from
account;
0 92
select min(amount),max(amount)from tmp_account;
0 42
CREATE OR REPLACE FUNCTION TF(FRO number, TIL number) RETURN
SDO_GEOMETRY deterministic as
BEGIN
RETURN case when fro <= til then MDSYS.SDO_GEOMETRY(2002,NULL, NULL,
SDO_ELEM_INFO_ARRAY (1,2,1),
SDO_ORDINATE_ARRAY(FRO,0,TIL,0)) end;
END;
/
CREATE OR REPLACE FUNCTION P(A number) RETURN SDO_GEOMETRY deterministic as
BEGIN
RETURN MDSYS.SDO_GEOMETRY(2001,NULL, SDO_POINT_TYPE(A, 0, NULL), null,
null);
END;
/
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'ACCOUNT';
INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
VALUES (
'ACCOUNT',
'RAFU.TF(LOWEST_AMOUNT,HIGHEST_AMOUNT)',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('X', 0, 92, 0.5), SDO_DIM_ELEMENT('Y', 0, 0, 0.5) )
)
;
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TMP_ACCOUNT';
INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO)
VALUES (
'TMP_ACCOUNT',
'RAFU.P(AMOUNT)',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('X', 0, 42, 0.5), SDO_DIM_ELEMENT('Y', 0, 0, 0.5) )
)
;
COMMIT; DROP INDEX A_IDX; CREATE INDEX A_IDX ON ACCOUNT(TF(lowest_amount,highest_amount))
INDEXTYPE IS mdsys.spatial_index;
DROP INDEX AT_IDX; CREATE INDEX AT_IDX ON TMP_ACCOUNT(P(amount))
INDEXTYPE IS mdsys.spatial_index;
select /*+ordered*/a.account_number, a.id, b.*
from
table(sdo_join('TMP_ACCOUNT','RAFU.P(AMOUNT)','ACCOUNT','RAFU.TF(LOWEST_AMOUNT,HIGHEST_AMOUNT)','mask=anyinteract'))
sj
, tmp_account b , account a where sj.rowid1=b.rowid and sj.rowid2=a.rowid;
-- Timo Raitalaakso http://rafudb.blogspot.com On 21.10.2011 12:06, David Fitzjarrell wrote:Received on Mon Oct 24 2011 - 07:14:44 CDT
> A short example with 8000 records in account and 150 records in tmp_account:
>
> SQL>
> SQL> create table account(
> 2 account_number number not null,
> 3 id varchar2(10) not null,
> 4 lowest_amount number(22,4),
> 5 highest_amount number(22,4)
> 6 );
>
> Table created.
>
> SQL>
> SQL> create table tmp_account(
> 2 account_number number not null,
> 3 amount number(22,4),
> 4 descr varchar2(40)
> 5 );
>
> Table created.
>
> SQL>
> SQL> begin
> 2 for i in 1..8000 loop
> 3 insert into account
> 4 (account_number, id, lowest_amount, highest_amount)
> 5 values
> 6 (i, 'Ex '||i, mod(i, 337), mod(i, 93));
> 7 if i<= 150 then
> 8 insert into tmp_account
> 9 (account_number, amount, descr)
> 10 values
> 11 (i, mod(i, 43), 'Example record '||i);
> 12 end if;
> 13 end loop;
> 14
> 15 commit;
> 16
> 17 end;
> 18 /
>
> PL/SQL procedure successfully completed.
> ... >
> David Fitzjarrell
-- http://www.freelists.org/webpage/oracle-l