RE: Poor query performance on smaller tables

>Oracle 9.2.0.3.0
>HP-UX 11i
>block size 16K
>db_file_multiblock_read_count 64
>
>
>I have five groups of tables that have the same
>query issued to them.
>Table structures, indexes
>are the same, just the data and the number of rows
>are different. The
>three tables that have more
>than 3 million rows return results in an acceptable
>time, two tables that
>hover around 1 million and little
>less take a very long time. The execution plans
>for all queries is the
>same.
>
>The query comes from a third-party application and
>is quite ugly:
>
>SELECT DISTINCT
>SDECREATOR.DATACOLLECTIONSTATIC.OBJECTID FROM
>SDE_LOGFILE_DATA,SDECREATOR.DATACOLLECTIONSTATIC
>WHERE
>SDE_LOGFILE_DATA.LOGFILE_DATA_ID = (SELECT
>LOGFILE_DATA_ID FROM
>SDE_LOGFILES WHERE LOGFILE_NAME = 'XX00000621_0'
>AND EXISTS (SELECT 'X'
>FROM RODB.WMT022_SP WHERE sde_row_id = shape AND
>wmcollectdate BETWEEN
>to_date('5/1/1930','mm/dd/yyyy') AND
>to_date('9/15/2003','mm/dd/yyyy')))
>
>This same query without the BETWEEN on the date
>column returns results on
>all five table sets quickly, so
>this leads me to believe that it's the date column
>that is at issue. The
>date columns have a normal index with these
>stats. The two with BLEVEL of 1 are the problems.
>
>select index_name, index_type, LEAF_BLOCKS,
>DISTINCT_KEYS, BLEVEL
> 2 from user_indexes
> 3 where index_name like '%_SP_DATE_%';
>
>INDEX_NAME INDEX_TYPE
> LEAF_BLOCKS
>DISTINCT_KEYS BLEVEL
>------------------------------
>--------------------------- ---------------
>--------------- ---------------
>WMT022_SP_DATE_IX NORMAL
> 686
>22612 1
>WMT023_SP_DATE_IX NORMAL
> 2096
>27037 2
>WMT025_SP_DATE_IX NORMAL
> 2299
>23819 2
>WMT026_SP_DATE_IX NORMAL
> 581
>9588 1
>WMT027_SP_DATE_IX NORMAL
> 2263
>37498 2

I presume that the presence of the BETWEEN incites Oracle to do a range scan on the date index and that this is far from being very efficient. Added to the fact that this is a subquery, and therefore executed for each row you return from the outer query ...  If you don't have thousands of statistics stored by second into WMT022_MP it is probably reasonably safe to assume that it contains around 23000 rows - I would do an uncorrelated subquery (shape in (select sde_row_id from ...)) rather than a correlated one on such a (comparatively modest) volume. Your range scan would be executed once instead of 1,000,000 times. Oracle must do the conversion for the biggest table, and not for the others. But can you change the code? Otherwise, try a query with a hint asking for the use of the index on sde_row_id in the subquery. If it works better, use it to generate an outline. Deleting the index on the date column would probably have the same effect, but I am not sure you wish to be that radical.

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).