SQL Performance (MOSC)

MOSC Banner

Does this an Oracle Optimizer malfunction?

edited May 21, 2015 10:04AM in SQL Performance (MOSC) 3 commentsAnswered

I have a query oo a view like this:

SELECT

SQN,REC_BDTS,SAT_LDTS,SAT_RSRC,SAT_FREQ,REC_EDTS,REC_CRNT_IND,REC_PURGE_IND,TYPE_CD FROM N_IZ6ARD_2_S_TYPES_V

;

Oracle will use NESTED LOOPS OUTER to perform the JOIN.

but once I put "--" in the query like this

SELECT

SQN

--

,REC_BDTS,SAT_LDTS,SAT_RSRC,SAT_FREQ,REC_EDTS,REC_CRNT_IND,REC_PURGE_IND,TYPE_CD FROM N_IZ6ARD_2_S_TYPES_V

;

Oracle will use HASHED LOOPS OUTER to perform the JOIN which performance is much better.  And also this is the right execution plan.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center