Re: DML too slow on some tables

On Mar 29, 6:42 am, "Andrea" <netsecur..._at_tiscali.it> wrote:
> On 29 Mar, 12:51, "gazzag" <gar..._at_jamms.org> wrote:
>
>
>
>
>
> > On 29 Mar, 11:25, "Andrea" <netsecur..._at_tiscali.it> wrote:
>
> > > Hi,
> > > i've a 9i DB for OLTP database type. Some tables is quiet large (about
> > > 3/4 million of rows, size 500/600Mb) and when i launch query or DML
> > > transactions it is too slow.
>
> > > I've already analyzed schema with dbms_stats.schema.. , check chained
> > > row, tuning OS, but is all ok.
> > > I would like to know if there is a method for improving performance of
> > > big table, some tips or advise :)
>
> > > thanks very much.
> > > bye
> > > --
> > > Andrea
>
> > To get help, provide the following information:
>
> > 1. Oracle and operating system versions.
> > 2. Details of the structure of the table(s) involved.
> > 3. Examples of the DML causing you problems.
> > 4. An explain plan if the above DML.
>
> 1. Oracle 9.0.2 SE, Windows 2000 Srv
>
> 2.
> PCT_FREE INITIAL_EXTENT NEXT_EXTENT NUM_ROWS AVG_ROW_LEN
> -------- -------------- -----------
> ---------- -----------
> 10 356678656 77528320
> 1253021 193
>
> 3.
> SELECT SBI_ID FROM TRANSIT WHERE CARD_NUMBER LIKE '%193';
>
> 1102 rows selected.
>
> Elapsed: 00:00:12.47
>
> 4.
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3170 Card=46788 Byte
> s=655032)
>
> 1 0 TABLE ACCESS (FULL) OF 'TRANSIT' (Cost=3170 Card=4
> 6788 Bytes=655032)
>
> Statistics
> ----------------------------------------------------------
> 29 recursive calls
> 4 db block gets
> 20957 consistent gets
> 10496 physical reads
> 0 redo size
> 44994 bytes sent via SQL*Net to client
> 8528 bytes received via SQL*Net from client
> 150 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1102 rows processed- Hide quoted text -
>
> - Show quoted text -

You need a lesson in how the optimizer operates; *ANY* conditional starting with a wildcard will *not* use an index. This is precisely what you have, a condition where you're looking for specific data at the end of a string. Oracle cannot use an index to speed access with such a condition. One possibility would be to store that column data in a new column in reverse order, index that column and change your condition:

SELECT SBI_ID FROM TRANSIT WHERE REV_CARD_NUMBER LIKE '391%'; Such a query can use an available index.

David Fitzjarrell Received on Thu Mar 29 2007 - 07:02:21 CDT