Higher Consistent Gets...

From: Raj Gopalan <raj.gopalan_at_netdecisions.co.uk>
Date: Fri, 14 Sep 2001 09:52:21 -0700
Message-ID: <F001.0038EC9D.20010914085528@fatcity.com>

!! Please do not post Off Topic to this List !!

Hi

I am having problem with a query. This query fetches rows from a table which has 15 million rows.

The problem is, when I execute this query with subquery, the consistent gets are 4700. Where us without the subquery the consistent gets are just 400. If I execute the subquery alone, the consistent gets are just 5.

Here is the main query with subquery results in cons.gets of 4700:

select pd.holiday_id holidayID, pd.package_number l_package_number,

       min(pd.tfr_price) l_tpr_price
  from fr_search_query pd
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/rrrr')

                             and TO_DATE('13/10/2001','dd/mm/rrrr')
   and pd.location_code in (select location_code
                              from gn_location
                            connect by prior location_code=parent_code
                              start with location_code='3142')

   and ROWNUM < 301

 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
          pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Trace results

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     96   SORT (ORDER BY)
     96    SORT (GROUP BY)
     96     COUNT (STOPKEY)
     96      NESTED LOOPS
   5137       INLIST ITERATOR
   5138        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                   'FR_SEARCH_QUERY'
   8566         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
     96       VIEW
   5136        SORT (UNIQUE)
      1         CONNECT BY
      2          INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
                     'PK_GN_LOCATION' (UNIQUE)
      1          TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
      1          TABLE ACCESS (FULL) OF 'GN_LOCATION'

Running just the subquery results in cons.gets of just 5.

  select location_code
    from gn_location
 connect by prior location_code=parent_code    start with location_code='3142'

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   CONNECT BY
      2    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
               (UNIQUE)
      1    TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
      1    TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'

(NON-UNIQUE)
Both the tables, indexes are analyzed. The optimizer mode is choose.

How do I tune this or Am I missing something obivious??

Thanks

Raj

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Raj Gopalan
  INET: raj.gopalan_at_netdecisions.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).

Received on Fri Sep 14 2001 - 11:52:21 CDT