Re: tune between query/join

  • From: Timo Raitalaakso <rafu@xxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 24 Oct 2011 15:14:44 +0300

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
--
//www.freelists.org/webpage/oracle-l


Other related posts: