Home » SQL & PL/SQL » SQL & PL/SQL » sql query failing with order by (10.1.0.5)
sql query failing with order by [message #293629] Mon, 14 January 2008 05:34 Go to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
I'm trying to use STAR transformation ;

SELECT x.*
FROM
dim_products p,
dim_distribution d,
nisa_times t,
fact_trans x
WHERE 1=1
AND p.npc=x.product_key
AND d.member_branch=x.distribution_key
AND x.dated = t.dated
AND p.supplier_db_name='ACDOCO'
--AND d.last_trans_date IS NOT null
AND t.dated BETWEEN To_Date('01-JAN-06') AND To_Date('31-DEC-07')
ORDER BY
d.retail_or_wholesale, d.group_name, d.tv_region, d.member_name,
p.comm_grp_text, p.comm_sub_grp_text, p.comm_sub_sub_grp_text, p.bottom_descpack,
x.dated
;

Explain plan shows it using a Temp Table Transformation AND'ing the BITMAP indexes but when I actually run it I get : "ORA-03113: end-of-file on communication channel". Remove the Order By and it works OK. ALTER SESSION SET star_transformation_enabled=false and it works OK. But it doesnt like the star transform with the Order By. Any Ideas ?

Re: sql query failing with order by [message #293630 is a reply to message #293629] Mon, 14 January 2008 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel
Re: sql query failing with order by [message #293664 is a reply to message #293629] Mon, 14 January 2008 08:26 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
mwansalovewell wrote on Mon, 14 January 2008 06:34


AND t.dated BETWEEN To_Date('01-JAN-06') AND To_Date('31-DEC-07')



First thing you should do would be to use proper syntax for the TO_DATE function. The TO_DATE function requires a format mask.
Previous Topic: how can I get the right partition name
Next Topic: 3 tables
Goto Forum:
  


Current Time: Thu Dec 08 18:24:40 CST 2016

Total time taken to generate the page: 0.10319 seconds