Re: tune between query/join

From: Timo Raitalaakso <rafu_at_iki.fi>
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:

> 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
Received on Mon Oct 24 2011 - 07:14:44 CDT

Original text of this message