4:21 p.m.
[
https://issues.jboss.org/browse/TEIID-4469?page=com.atlassian.jira.plugin...
]
Lucie Fabrikova commented on TEIID-4469:
----------------------------------------
I simulated the query with two oracle databases on different machines, but the source hint
doesn't seem to be applied ProjectIntoNode, contents of teiid-command.log and
server.log:
21:03:54,633 DEBUG [org.teiid.COMMAND_LOG] (New I/O worker #4) wMQbVS84H1pO START USER
COMMAND: startTime=2016-10-08 21:03:54.633 requestID=wMQbVS84H1pO.0
txID=null sessionID=wMQbVS84H1pO applicationName=JDBC
principal=teiidUser@teiid-security vdbName=oracledba vdbVersion=1 sql=INSERT
/*+ sh Source2:'append' */ INTO test (ID, myvalue) SELECT X.id AS ID, X.myvalue AS
myvalue FROM (SELECT SMALLA.INTKEY AS id, SMALLA.INTNUM AS myvalue FROM SMALLA WHERE
SMALLA.INTKEY = 1) AS X
21:03:58,070 DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue1) wMQbVS84H1pO
START DATA SRC COMMAND: startTime=2016-10-08 21:03:58.069 requestID=wMQbVS84H1pO.0
sourceCommandID=1 executionID=0 txID=null modelName=Source
translatorName=oracle sessionID=wMQbVS84H1pO principal=teiidUser@teiid-security
sql=SELECT /*+sh Source2:'append' */ g_0.intkey, g_0.intnum FROM Source.smalla AS
g_0 WHERE g_0.intkey = 1
21:03:58,567 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue2) wMQbVS84H1pO
END SRC COMMAND: endTime=2016-10-08 21:03:58.567 requestID=wMQbVS84H1pO.0
sourceCommandID=1 executionID=0 txID=null modelName=Source
translatorName=oracle sessionID=wMQbVS84H1pO principal=teiidUser@teiid-security
finalRowCount=1
21:04:01,127 DEBUG [org.teiid.COMMAND_LOG] (Worker1_QueryProcessorQueue3) wMQbVS84H1pO
START DATA SRC COMMAND: startTime=2016-10-08 21:04:01.127 requestID=wMQbVS84H1pO.0
sourceCommandID=0 executionID=1 txID=null modelName=Source2
translatorName=oracle sessionID=wMQbVS84H1pO principal=teiidUser@teiid-security
sql=INSERT INTO Source2.test (ID, myvalue) VALUES (...)
21:04:01,635 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue4) wMQbVS84H1pO
END SRC COMMAND: endTime=2016-10-08 21:04:01.635 requestID=wMQbVS84H1pO.0
sourceCommandID=0 executionID=1 txID=null modelName=Source2
translatorName=oracle sessionID=wMQbVS84H1pO principal=teiidUser@teiid-security
finalRowCount=1
21:04:01,697 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue5) wMQbVS84H1pO
END USER COMMAND: endTime=2016-10-08 21:04:01.697 requestID=wMQbVS84H1pO.0
txID=null sessionID=wMQbVS84H1pO principal=teiidUser@teiid-security
vdbName=oracledba vdbVersion=1 finalRowCount=1
Logging set to DEBUG, server.log:
DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue3) wMQbVS84H1pO.0.0.1 Processing
NEW request: INSERT INTO Source2.test (ID, myvalue) VALUES (...)
21:03:58,572 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) Request Thread
wMQbVS84H1pO.0 - processor blocked
21:04:01,126 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue3) wMQbVS84H1pO.0.0.1
Obtained execution
21:04:01,127 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue3) Source-specific
command: INSERT INTO test (ID, myvalue) VALUES (?, ?)
21:04:01,633 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue3) wMQbVS84H1pO.0.0.1
Executed command
Insert with query expression does not apply source hint to target
-----------------------------------------------------------------
Key: TEIID-4469
URL: https://issues.jboss.org/browse/TEIID-4469
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.7.1.6_2
Reporter: Marc Shirley
Assignee: Steven Hawkins
Fix For: 9.1, 9.0.5, 8.7.10_6.2
"INSERT INTO ... SELECT ..." statements result in a ProjectIntoNode which seems
to prevent any source hints from being passed down to the relevant source. These work
correctly with an "INSERT INTO ... VALUES ..." statement. I've included
example user and final query plan data below for the non-working [1] and working [2]
insert statements.
[1] source hint not applied to ProjectIntoNode:
USER COMMAND:
INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") SELECT X.id
AS ID, X."value" AS "VALUE" FROM (SELECT bqt.SMALLA.INTKEY AS id,
bqt.SMALLA.INTNUM AS "value" FROM bqt.SMALLA WHERE bqt.SMALLA.INTKEY = 0) AS X
...
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectIntoNode(0) output=[Count] test.TEST
AccessNode(1) output=[bqt.SMALLA.INTKEY AS ID, bqt.SMALLA.INTNUM AS "VALUE"]
SELECT /*+sh test:'append' */ g_0.INTKEY, g_0.INTNUM FROM bqt.SMALLA AS g_0 WHERE
g_0.INTKEY = 0
[2] source hint applied to AccessNode:
USER COMMAND:
INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES
('-1', '-1')
...
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
AccessNode(0) output=[Count] INSERT /*+sh test:'append' */ INTO test.TEST (ID,
"VALUE") VALUES ('-1', '-1')
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
3001
days inactive
3001
days old
0 comments
1 participants
participants (1)
-
Lucie Fabrikova (JIRA)