SQL tuning

[bolero]

Can someone suggest me how to speed up the below SQL.


SQL> ed
Wrote file afiedt.buf

1 SELECT distinct rev_voh.moc_acc_no, rev_voh.rv_ser, rev_voh.ds_ser,
2 bk_dslip.sor_ser, bk_dslip.type_type, rev_voh.depos_amt, rev_voh.amt,
3 cash.moc_rcpt, rev_voh.ca_cod, cash.rc_ser, nvl(chrt_of_acc.ca_cod_p,
4 rev_voh.ca_cod) ca_cod_p
5 FROM ait.rev_voh, ait.bk_dslip, ait.chrt_of_acc, ait.cash
6 WHERE rev_voh.ds_ser = bk_dslip.ds_ser
7 AND chrt_of_acc.ca_cod = rev_voh.ca_cod
8 AND to_char(rev_voh.tdate, 'DD/MM/YYYY') >= '22-MAY-2004'
9 AND to_char(rev_voh.tdate, 'DD/MM/YYYY') <= '01-SEP-2004'
10 AND cash.ca_ser = bk_dslip.sor_ser
11 AND bk_dslip.type_type = 1
12 AND rev_voh.rev_st = 1
13* ORDER BY 8 ASC, 1 ASC, 11 ASC, moc_rcpt
14 .
SQL> set autot traceonly explain
SQL> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=839 Card=270 Bytes=2
4300)

1 0 SORT (UNIQUE) (Cost=833 Card=270 Bytes=24300)
2 1 NESTED LOOPS (Cost=826 Card=270 Bytes=24300)
3 2 NESTED LOOPS (Cost=799 Card=270 Bytes=21600)
4 3 NESTED LOOPS (Cost=772 Card=270 Bytes=17550)
5 4 TABLE ACCESS (FULL) OF 'REV_VOH' (Cost=745 Card=27
0 Bytes=14040)

6 4 TABLE ACCESS (BY INDEX ROWID) OF 'BK_DSLIP' (Cost=
1 Card=63935 Bytes=831155)

7 6 INDEX (UNIQUE SCAN) OF 'DSLP_PK' (UNIQUE)
8 3 TABLE ACCESS (BY INDEX ROWID) OF 'CASH' (Cost=1 Card
=139054 Bytes=2085810)

9 8 INDEX (UNIQUE SCAN) OF 'APAY_PK' (UNIQUE)
10 2 TABLE ACCESS (BY INDEX ROWID) OF 'CHRT_OF_ACC' (Cost=1
Card=151139 Bytes=1511390)

11 10 INDEX (UNIQUE SCAN) OF 'CHRT_PK' (UNIQUE)


I can see FULL table scan on REV_VOH table.Even though i know somecase FTS is not harmfull.I need suggestions to speed up this SQL.

The index on REV_VOH is
------------------------
SQL> select column_name from dba_ind_columns where index_name='RVOC_PK';

COLUMN_NAME
------------
DS_SER
RV_SER

Thanks in advance

[mike9]

8 AND to_char(rev_voh.tdate, 'DD/MM/YYYY') >= '22-MAY-2004'
9 AND to_char(rev_voh.tdate, 'DD/MM/YYYY') <= '01-SEP-2004'

Please have a look at your code, that's nonsense.
Also when comparing a date column with a string always transforme the string into a date but NEVER the date column in a string.

[DaPi]

When you have sorted out your date comparisons as mike9 suggested, then perhaps an index on tdate or (rev_st, tdate) will help.

As a general principle, check that you really need DISTINCT - maybe the data structure forces the rows to be unique. (Not that it looks like a big overhead in this case.)

[Dilippatel]

Bolero,

Regarding your statelemt:
"I can see FULL table scan on REV_VOH table.Even though i know somecase FTS is not harmfull."

The FTS is not harmful if you are selecting almost all rows in the resultset, if your result set (No of records) are very less then you must use Index scan to avoide FTS.

Dilip