!! Please do not post Off Topic to this List !!
Try this and let's know how it works:
select --+ ordered use_hash(vw1)
pd.holiday_id holidayID, pd.package_number l_package_number,
min(pd.tfr_price) l_tpr_price
from fr_search_query pd ,
(select --+ no_merge
location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142') vw1
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 = vw1.location_code
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)
Regards,
Waleed
-----Original Message-----
Sent: Friday, September 14, 2001 8:10 PM
To: Multiple recipients of list ORACLE-L
!! Please do not post Off Topic to this List !!
Raj,
Which query is fastest? You don't have any timing information.
Make sure that timed_statistics is on in the database:
alter system set timed_statistics = true;
Run your query with trace on:
alter session set sql_trace = true;
Then run the resulting trace files through tkprof.
You'll have much more information to work with.
Jared
Raj Gopalan
<raj.gopalan@netdecisi To: Multiple recipients
of list ORACLE-L <[EMAIL PROTECTED]>
ons.co.uk> cc:
Sent by: Subject: Higher
Consistent Gets...
[EMAIL PROTECTED]
09/14/01 09:55 AM
Please respond to
ORACLE-L
!! 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
