Re: performance tuning questions: replace IN (values) by JOIN

From: Jérôme Lacoste - CoffeeBreaks <lacostej_at_coffebreaks.org>
Date: Sun, 31 Aug 2003 08:26:08 GMT
Message-ID: <Aii4b.17453$BD3.2774654@juliett.dax.net>

Bob Hairgrove wrote:
> On Sat, 30 Aug 2003 18:15:23 GMT,
> =?ISO-8859-1?Q?J=E9r=F4me_Lacoste_-_CoffeeBreaks?=
> <lacostej_at_coffebreaks.org> wrote:
>
> [snip]
>

>>But the index on the TIMESTAMP column doesn't seem to be used.

>
>
> did you try using a hint?

Bob,

I had rather not add an hint to the request, as I am supposed to be running my queries from Java throught JDBC and, afaik, I am not sure it will make my code cross-platform without adding DB detection, which I would rather avoid. I am testing this right now to find it out.

I did use the hint, and when used the query goes from 6 seconds to less than 0.02 sec.

I obviously need to use the HINT. Output:

Elapsed: 00:00:00.01

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=17 Bytes=321

           3)  

    1 0 SORT (ORDER BY) (Cost=22 Card=17 Bytes=3213)

    2 1 NESTED LOOPS (Cost=20 Card=17 Bytes=3213)

    3 2 TABLE ACCESS (BY INDEX ROWID) OF 'BAG_LOG' (Cost=3 Car

           d=17 Bytes=2261)  

    4 3 INDEX (RANGE SCAN) OF 'BAG_LOG_DTM_TIMESTAMP' (NON-U            NIQUE) (Cost=2 Card=31)  

    5 2 TABLE ACCESS (BY INDEX ROWID) OF 'BAGS' (Cost=1 Card=1

            Bytes=56)  

    6 5 INDEX (UNIQUE SCAN) OF 'PK_BAGS1' (UNIQUE) Statistics

---

           2 recursive calls

           0 db block gets

          33 consistent gets

           0 physical reads

           0 redo size

        2369 bytes sent via SQL*Net to client

         499 bytes received via SQL*Net from client

           2 SQL*Net roundtrips to/from client

           1 sorts (memory)

           0 sorts (disk)

          11 rows processed

I also found out that if I make a search using a period with a single bound, i.e. not using BETWEEN, but using:

        WHERE (BAGS.ID = BAG_LOG.BAG_ID AND
               BAG_LOG.DTM_TIMESTAMP >= (TO_DATE('2003-17-08 00:00:0

0', 'yyyy-dd-mm HH24:MI:SS'))

              ) ORDER BY BAGS.ID DESC, BAG_LOG.DTM_TIMESTAMP DESC; Oracle stops using the BAGS.ID primary key INDEX and makes a full scan on the BAGS TABLE (see log below). Something I am not happy with. I will probably find a way to force the use of a full period range in my code even if does not seem to affect the performance now.

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=448 Card=6981 Bytes=

           1319409)  

    1 0 SORT (ORDER BY) (Cost=448 Card=6981 Bytes=1319409)

    2 1 HASH JOIN (Cost=214 Card=6981 Bytes=1319409)

    3 2 TABLE ACCESS (BY INDEX ROWID) OF 'BAG_LOG' (Cost=3 Car

           d=6981 Bytes=928473)  

    4 3 INDEX (RANGE SCAN) OF 'BAG_LOG_DTM_TIMESTAMP' (NON-U            NIQUE) (Cost=2 Card=1257)  

    5 2 TABLE ACCESS (FULL) OF 'BAGS' (Cost=52 Card=42556 Byte

           s=2383136)

But I am wondering why Oracle doesn't use the hint by default. From the results it seems obvious it should :)

I will have to look at the oracle documentation to find this out. Jerome Received on Sun Aug 31 2003 - 03:26:08 CDT