RE: Query question

From: Jordi Sanmarti <JSanmarti_at_tss.com.pe>
Date: Thu, 05 Apr 2001 14:43:03 -0700
Message-ID: <F001.002E34A2.20010405143135@fatcity.com>

I presume, your query is running out of indexes. Run the explain plan and check if Oracle performs any full scan into a table. Then, create an index for that particular case. If for any reason, Oracle doesn't use the index created, apply a hint ( /+ /) into your SQL, meaning that your going to force the engine to use it as a part of your execution plan.

If the above still without working, I suggest to check your query, you might be able to reformulate your query by analyzing the product Cartesian that would return to you.

HTH,
Jordi

-----Original Message-----
From: Mukesh Ghildiyal [mailto:mcgoracle_at_yahoo.com] Sent: Thursday, April 05, 2001 02:26 PM

To:     Multiple recipients of list ORACLE-L
Subject:        Query question

Hello gurus
please help

I am running following query
This takes forever to complete.

select pay.gl_date from
noetix_sys.noetix_current_period off
  2 ,gl.gl_sets_of_books sob, gl.gl_periods per,   3 ar.ar_payment_schedules_all pay
  4 where
  5 pay.gl_date between per.start_date and per.end_date

  6  and per.period_set_name||'' =sob.period_set_name
  7  and per.period_type = sob.accounted_period_type
  8  and sob.set_of_books_id=26
  9  and off.application_id=222
 10  and off.period_type=sob.accounted_period_type
 11* and off.set_of_books_id=26

Now if I takeout following line

and off.period_type=sob.accounted_period_type

it works

the column "off.period_type" and
"sob.acoount_period_type" are both not null varchar2(15) type.

Could anybody has some idea, why its doing that.

thank you very much

Please help

mcg

Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mukesh Ghildiyal
INET: mcgoracle_at_yahoo.com

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jordi Sanmarti
INET: JSanmarti_at_tss.com.pe

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------