Bug with CAST and parallelism?
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Mar 2006 18:43:04 -0600
Database is Oracle 9.2.0.6, OS is Tru64 5.1, data warehouse environment.
It appears that when using the CAST function with a SUBSTR, against data
from a table that has parallelism set, Oracle "pre-processes" the data
using the CAST function. I can explain this best with an example
(unfortunately based on a production job)
/* Start with a table holding a column with data longer than 6
positions. */
CREATE TABLE dherri_test1_tb AS SELECT '00000115855' field1 FROM dual;
Table created.
/* Create a second table off the above, but only pull the */
/* last 6 characters for one of the columns */
CREATE TABLE dherri_test2_tb AS
SELECT CAST(SUBSTR(field1,6,6) AS VARCHAR2(6)) cast_sub_field1, field1
FROM (SELECT * FROM dherri_test1_tb a);
Table created.
SELECT * FROM dherri_test2_tb;
CAST_S FIELD1
------ -----------
115855 00000115855
1 row selected.
/* Same try as previous CTAS, but this time pull the data in parallel */
CREATE TABLE dherri_test3_tb AS
SELECT CAST(SUBSTR(field1,6,6) AS VARCHAR2(6)) cast_sub_field1, field1
FROM (SELECT /*+ PARALLEL(a) */ * FROM dherri_test1_tb a);
Table created.
SELECT * FROM dherri_test3_tb;
CAST_S FIELD1
------ -----------
000001 000001
1 row selected.
Not only did the column with the CAST(SUBSR... functions on it get
converted to VARCHAR2(6) BEFORE the functions were run, but so did the
second column with no functions applied to it.
I'll file this as a bug with Oracle, but I'm curious if anyone has run
into anything similar to this before or can reproduce this on different
versions of Oracle.
The workaround is simple enough: remove parallelism or use NOPARALLEL(a)
hint. Unfortunately, being a DW the tables are rather large (although
correct data wins over fast data). Those who wrote refresh of various
tables always recreate the tables using CTAS, so the CAST function is
found in a lot of code. Sure would stink if this problem is happening
elsewhere and we have yet to discover it!
Thanks.
Dave
-------------------------------------
Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri@xxxxxxxxxx>
-------------------------------------
"When I come home from work and see those little noses pressed against
the windowpane, then I know I am a success" - Paul Faulkner
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
