Q
Problem solve Get help with specific problems with your technologies, process and projects.

Problem migrating from RBO to CBO

I'm trying to migrate from RBO to CBO on an 8.1.6.2 database. The problem I am running into is a little wierd. Some, not all, queries that have a distinct or a subquery in it don't return results. How do I get around this and what is happening? Example:
 SQL> select
 2 b.po_id,
 3 b.line_nbr,
 4 b.sched_nbr,
 5 b.wa_seq_nbr,
 6 B.DUE_DATE,
 7 B.qty
 8 FROM sysadm.PS_WA_PO_LNSHP_CNF B
 9 WHERE B.PO_ID = '0000078871'
 10 and B.LINE_NBR = 2
 11 and B.SCHED_NBR = 1
 12 and b.wa_seq_nbr = (Select MAX (x.wa_seq_nbr)
 13 from sysadm.ps_wa_po_lnshp_cnf X
 14 where b.po_id = x.po_id
 15 and b.line_nbr = x.line_nbr
 16 and b.sched_nbr = x.sched_nbr);
 
 no rows selected
 
 SQL> select /*+ rule */
 2 b.po_id,
 3 b.line_nbr,
 4 b.sched_nbr,
 5 b.wa_seq_nbr,
 6 B.DUE_DATE,
 7 B.qty
 8 FROM sysadm.PS_WA_PO_LNSHP_CNF B
 9 WHERE B.PO_ID = '0000078871'
 10 and B.LINE_NBR = 2
 11 and B.SCHED_NBR = 1
 12 and b.wa_seq_nbr = (Select MAX (x.wa_seq_nbr)
 13 from sysadm.ps_wa_po_lnshp_cnf X
 14 where b.po_id = x.po_id
 15 and b.line_nbr = x.line_nbr
 16 and b.sched_nbr = x.sched_nbr);
 
 PO_ID LINE_NBR SCHED_NBR WA_SEQ_NBR DUE_DATE QTY
 ---------- ---------- ---------- ---------- --------- ----------
 0000078871 2 1 682402 10-JAN-05 0
 
 SQL>
Any help would be greatly appreciated since this is really messing with my mind.
Any time the same query returns different results, I'd say it's time to file a TAR with Oracle. This just should not happen. It would mess with my mind too!
This was last published in May 2005

Dig Deeper on Oracle database export, import and migration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close