Path: news.easynews.com!easynews!newsfeed.frii.net!newsfeed.frii.net!newsfeed.icl.net!newsfeed.fjserv.net!kibo.news.demon.net!demon!news-lond.gip.net!news.gsl.net!gip.net!newspeer.clara.net!news.clara.net!btnet-peer!btnet-peer0!btnet!ctb-nntp1.saix.net!not-for-mail From: "Billy Verreynne" Newsgroups: comp.databases.oracle.server Subject: Forcing PQ Date: Thu, 8 Aug 2002 10:40:33 +0200 Organization: Verreynne Software Labs Lines: 38 Message-ID: NNTP-Posting-Host: 198.54.202.209 X-Trace: ctb-nnrp1.saix.net 1028796033 17665 198.54.202.209 (8 Aug 2002 08:40:33 GMT) X-Complaints-To: abuse@saix.net NNTP-Posting-Date: 8 Aug 2002 08:40:33 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 Xref: easynews comp.databases.oracle.server:157244 X-Received-Date: Thu, 08 Aug 2002 01:37:39 MST (news.easynews.com) The software: Oracle 8.1.7 HP-UX platform The tables: Big one (30+ million rows). Several smaller ones ranging from few 100 rows to a 10's of 1000's of rows (indexed). All tables analyzed (estimate 10% on big one). The query: Straight forward full tablescan of the big one, using nested loop joins with the smaller ones. The result to be stored in a new table (i.e. create table unrecoverable as select). Hints ORDERED and USE_NL are used to force the correct join order. All rows in big table needs to be processed first, thus the FTS, then using nested loops with unique index scans to the smaller tables. The problem: The query itself is fast. Okay, relatively fast given the large number of rows. I can also force it to run in parallel when it is a pure SELECT count(*) (i.e. no CREATE TABLE). However, when adding the CREATE TABLE, it ignores the PARALLEL hint in the SELECT and the PARALLEL clause in the CREATE TABLE. Have tried ALTER SESSION ENABLE PARALLEL DML. No change. The SQL still runs serial. Suggestions? Thanks. -- Billy