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

From: Jérôme Lacoste - CoffeeBreaks <lacostej_at_coffebreaks.org>
Date: Sat, 30 Aug 2003 18:15:23 GMT
Message-ID: <%Q54b.17419$BD3.2770389@juliett.dax.net>

>>Q: I was wondering what kind of query should I use in order to manage to 
>>improve the design of my query (1) especially removing its limitations 
>>without introducing the performance loss as with my second query. Should 
>>I use a temporary table?

>
>
> I would forget about the strategy you applied in 1. It sucks. It
> doesn't exploit the capabilities of sql. Your reference to using a
> temporary table shows you have brainwashed by B Gates and his Evil
> Empire.

OK.

> The second variant doesn't perform for the following reasons
> - the distinct in the subquery is unnecessary as the IN operator works
> on a set and according to your primary school mathematics book,

I've got that formal definition of a set later than in primary school, but you are obviously right. :)

> a set never has duplicates. However as you ask it to a DISTINCT you will get
> an extra redundant sort-operation

> - You don't have a required indexed on the date column, hence you
> force it to conduct a full table scan
> - You need to replace the >= and <= operators by a between, to have
> the optimizer recognize this is a bounded range scan.

I applied hint given by mitt to not use the sub-query. I also applied your hint to use BETWEEN and my performance went from 18sec to 6 sec. Much better!

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

I did the following: (comments wrapped in ****)

• create and monitor index ****

CREATE INDEX BAG_LOG_BID_SEQ ON BAG_LOG(BID_SEQ); ALTER INDEX BAG_LOG_DTM_TIMESTAMP Monitoring Usage;

• run improved query ****

select BAGS.ID as BAGS__ID, BAGS.BID_SEQ as BAGS__BID_SEQ,

        BAGS.IATA AS BAGS__IATA,
        BAGS.SECURITY_STATUS AS BAGS__SECURITY_STATUS,
        BAGS.ROUTE_MODE AS BAGS__ROUTE_MODE,
        BAGS.FLIGHT_NO AS BAGS__FLIGHT_NO,
        BAGS.FLIGHT_DEST AS BAGS__FLIGHT_DEST,
        BAGS.FLIGHT_CLASS AS BAGS__FLIGHT_CLASS,
        BAG_LOG.* FROM BAGS, BAG_LOG
        WHERE (BAGS.ID = BAG_LOG.BAG_ID AND
               BAG_LOG.DTM_TIMESTAMP BETWEEN (TO_DATE('2003-19-08 
00:00:00', 'yyyy-dd-mm HH24:MI:SS'))
               AND (TO_DATE('2003-19-08 12:00:00', 'yyyy-dd-mm HH24:MI:SS'))
              ) ORDER BY BAGS.ID DESC, BAG_LOG.DTM_TIMESTAMP DESC;

Elapsed: 00:00:06.08

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE

    1 0 SORT (ORDER BY)     2 1 NESTED LOOPS
**** INDEX on DTM_TIMESTAMP not used. ****

    3 2 TABLE ACCESS (FULL) OF 'BAG_LOG'     4 2 TABLE ACCESS (BY INDEX ROWID) OF 'BAGS'     5 4 INDEX (UNIQUE SCAN) OF 'PK_BAGS1' (UNIQUE) Statistics

---

           0 recursive calls

           0 db block gets

       34223 consistent gets

       34172 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

select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE 

------------------------------ ------------------------------ --- --- 

START_MONITORING END_MONITORING

• ------------------- BAG_LOG_DTM_TIMESTAMP BAG_LOG YES NO

08/30/2003 19:54:42

One see here that the index is not used at all.

Any idea?

>>Q: [slightly unrelated] How one can enable the autotrace to also display 
>>(Cost, Card and Byte) as shown in most of Oracle examples?
>>

>
>
> Autotrace doesn't do that. TKPROF on a trace file does.

Didn't try that yet, but will do it Monday.

Thanks a lot for your answers!

Cheers,

Jerome Received on Sat Aug 30 2003 - 13:15:23 CDT