Re: Query Tuning

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 12 Apr 2003 06:34:49 -0700
Message-ID: <WBUla.5647$%X5.882_at_fed1read03>


Tom wrote:
> I have 2 tables, one with 80,000 records and the other with 132,000
> records. Its not a huge amount of data but still query takes around 5
> mins…am I doing something wrong ?
> Also I cannot remove LIKE . Could you please suggest me to make is
> any FASTER ?
>
> TABLE 1 (80k rows)
>
> COMP_ID,
> BANK_ID,
> LEGAL_NAME,
> DBA_NAME,
> Adress
>
> TABLE 2 (135K rows)
>
> COMP_ID
> BANK_ID
> ACCT_NUM
>
>
> The query Iam running is
>
> SELECT a.bank_id,a.legal_name,a.dba_name,a.comp_id , b.acct_num
> FROM table1 a, table2 b
> WHERE (UPPER(b.acct_num) like '%1595%' ESCAPE '/')
> AND (a.bank_id=b.bank_id)
> AND (a.comp_id=b.comp_id)
> AND (ROWNUM < 52)
>
> Takes appox. 5mins
>
> There is INDEX on comp_id, bank_id on both the tables, also Acct_num
> is indexed
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24669 Card=1 Bytes=9
> 6)
>
> 1 0 COUNT (STOPKEY)
> 2 1 HASH JOIN (Cost=24669 Card=1 Bytes=96)
> 3 2 TABLE ACCESS (FULL) OF 'TABLE2' (Cost=586 Card=1
> 2296 Bytes=356584)
>
> 4 2 VIEW OF 'CVW_COMP' (Cost=19717 Card=74607 Bytes=499866
> 9)
>
> 5 4 SORT (UNIQUE) (Cost=19717 Card=74607 Bytes=14240185)
> 6 5 UNION-ALL
> 7 6 TABLE ACCESS (FULL) OF 'TABLE1' (Cost=837 Car
> d=73388 Bytes=14017108)
>
> 8 6 TABLE ACCESS (FULL) OF 'TABLE1' (Cost=837 Car
> d=1219 Bytes=223077)
>
> Statistics
> ----------------------------------------------------------
> 22 recursive calls
> 699 db block gets
> 14887 consistent gets
> 30985 physical reads
> 0 redo size
> 6443 bytes sent via SQL*Net to client
> 758 bytes received via SQL*Net from client
> 5 SQL*Net roundtrips to/from client
> 4 sorts (memory)
> 1 sorts (disk)
> 51 rows processed
>
>
> SELECT /*+RULE*/ a.bank_id,a.legal_name,a.dba_name,a.comp_id ,
> b.acct_num
> FROM table1 a, table2 b
> WHERE (UPPER(b.acct_num) like '%1595%' ESCAPE '/')
> AND (a.bank_id=b.bank_id)
> AND (a.comp_id=b.comp_id)
> AND (ROWNUM < 52)
>
> Takes appox. 5mins, no change using rulebase.
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: RULE
> 1 0 COUNT (STOPKEY)
> 2 1 NESTED LOOPS
> 3 2 VIEW OF 'CVW_COMP'
> 4 3 SORT (UNIQUE)
> 5 4 UNION-ALL
> 6 5 TABLE ACCESS (FULL) OF 'TABLE1'
> 7 5 TABLE ACCESS (FULL) OF 'TABLE1'
> 8 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2'
> 9 8 INDEX (RANGE SCAN) OF 'IDX_COMP_BANK' (NON-UNIQUE)
You might be able to do something along the lines of

SELECT a.bank_id,a.legal_name,a.dba_name,a.comp_id , b.acct_num FROM table1 a, table2 b
WHERE (ROWNUM < 52)

AND (a.bank_id=b.bank_id)
AND (a.comp_id=b.comp_id)
AND (b.rowid exists (SELECT '1'
                      FROM table2 b2
                      WHERE (UPPER(b2.acct_num) like '%1595%' ESCAPE '/')
                        and  b2.rowid = b.rowid
                      );


I'm not sure how much, if any, this will speed up the results. Alternatively if you created a PL/SQL temporary table which contains the rowids of B
WHERE (UPPER(b2.acct_num) like '%1595%' ESCAPE '/') then you'd make only a single TS of B PLUS the query could make use of the indexes.

HTH & YMMV HAND! Received on Sat Apr 12 2003 - 15:34:49 CEST

Original text of this message