SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Fetching Contract Employee information by Join the JOB_DTL,JOB_DTL1,IQN_BU Tables.

Vemula MuniAug 1 2019 — edited Aug 6 2019

DBA Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Below is my "PS_CTS_JOB_DTL" table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.

EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)

EFFDT : this filed use to track when change happens.

EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )

ACTION : Will determine which action they taken on employee ( please don't consider action field to achieve output as below. because action codes may different in each time)

JOB Table
EMPLID	BUSINESS_UNIT	EMPL_RCD	EFFDT	EFFSEQ	ACTION
733930	US410	0	27-May-19	0	ADD
733930	US410	0	02-Jun-19	0	DTA
733930	US410	0	4-Jun-19	0	TER
733930	US410	1	6-Jun-19	0	HIR
733930	US410	1	15-Jun-19	0	TER
733930	US410	2	15-Jun-19	1	ADD
733930	US608	2	19-Jun-19	0	ASC
733930	CN512	2	26-Jun-19	0	ASG
733931	US410	0	26-Apr-19	0	HIR
733931	US410	0	5-Jun-19	0	TER
733931	IN208	1	6-Jun-19	0	ADD
733931	IN208	1	10-Jun-19	0	TER
733931	IN208	2	12-Jun-19	0	HIR
733931	IN408	2	18-Jun-19	0	ASC
733931	IN408	2	19-Jun-19	0	TER
733931	IN208	3	26-Jun-19	0	ADD
733931	IN208	3	28-Jun-19	0	DTA

Below is my "PS_CTS_JOB_DTL1" table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.

EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)

EFFDT : this filed use to track when change happens.

EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )

HR_STATUS : To identify employee status ( A = Active, I = Inactive ).

PER_ORG : to identify employee type ( CWR = Contract, EMP = Employee )

EMPL_ID	EMPL_RCD	EFFDT	EFFSEQ	HR_STATUS	PER_ORG
733930	0	27-May-19	0	A	CWR
733930	0	02-Jun-19	0	A	CWR
733930	0	4-Jun-19	0	I	CWR
733930	1	6-Jun-19	0	A	EMP
733930	1	15-Jun-19	0	I	EMP
733930	2	15-Jun-19	1	A	CWR
733930	2	19-Jun-19	0	A	CWR
733930	2	26-Jun-19	0	A	CWR
733931	0	26-Apr-19	0	A	EMP
733931	0	5-Jun-19	0	I	EMP
733931	1	6-Jun-19	0	A	CWR
733931	1	10-Jun-19	0	I	CWR
733931	2	12-Jun-19	0	A	EMP
733931	2	18-Jun-19	0	A	EMP
733931	2	19-Jun-19	0	I	EMP
733931	3	26-Jun-19	0	A	CWR
733931	3	28-Jun-19	0	A	DTA

Below is my "PS_CTS_IQN_BU" table structure and data.

IQN BU Setp
Country	Business_Unit
USA	US410
USA	US412
CAN	CN512
IND	IN208

Based on above three tables i need to fetch below output. When i pass from date and to date in query (month start and end dates i.e like '01-JUN-19' and '30-JUN-19' ) , query should fetch employee active from active to dates as CWR and that employee Business unit should be in PS_CTS_IQN_BU table.

Required output

Note : Reason column for understanding, no need to display reason column

While fetching below out put Please map EMPLID,EMPL_RCD,EFFDT,EFFSEQ Key fields in both JOB_DTL and JOB_DTL1 ( Map Key fields is very important )

Employee 733930 is active as CWR in between '19-JUN-19' to '25-JUN-19' in different BU which not in IQN_BU setup table, so i want to exclude this information.

EMPLID	FROM_DATE	To_DATE	PER_ORG	HR_STATUS	Reason
733930	1-Jun-19	3-Jun-19	CWR	A	Employee is active as CWR and BU is in IQN_BU setup table
733930	15-Jun-19	18-Jun-19	CWR	A	Employee is active as CWR and BU is in IQN_BU setup table
733930	26-Jun-19	30-Jun-19	CWR	A	Employee is active as CWR and BU is in IQN_BU setup table
733931	6-Jun-19	9-Jun-19	CWR	A	Employee is active as CWR and BU is in IQN_BU setup table
733931	26-Jun-19	30-JUN-19	CWR	A	Employee is active as CWR and BU is in IQN_BU setup table

PS_CTS_JOB_DTL Table Build and Insert Scripts:

CREATE TABLE PS_CTS_JOB_DTL (EMPLID VARCHAR2(11) NOT NULL,
   BUSINESS_UNIT VARCHAR2(5) NOT NULL,
   EMPL_RCD INT NOT NULL,
   EFFDT DATE,
   EFFSEQ INT NOT NULL,
   ACTION VARCHAR2(3) NOT NULL) ;
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('27-MAY-19','DD-MON-YY'),0,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-19','DD-MON-YY'),0,'DTA');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('04-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('06-JUN-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('15-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',2,to_date('15-JUN-19','DD-MON-YY'),1,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US608',2,to_date('19-JUN-19','DD-MON-YY'),0,'ASC');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','CN512',2,to_date('26-JUN-19','DD-MON-YY'),0,'ASG');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('26-APR-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('05-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('06-JUN-19','DD-MON-YY'),1,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('10-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',2,to_date('12-JUN-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('18-JUN-19','DD-MON-YY'),0,'ASC');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('19-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('26-JUN-19','DD-MON-YY'),0,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-19','DD-MON-YY'),0,'DTA');

PS_CTS_JOB_DTL1 Table Build and Insert Scripts:

CREATE TABLE PS_CTS_JOB_DTL1 (EMPLID VARCHAR2(11) NOT NULL,
   EMPL_RCD INT NOT NULL,
   EFFDT DATE,
   EFFSEQ INT NOT NULL,
   HR_STATUS VARCHAR2(1) NOT NULL,
   PER_ORG VARCHAR2(3) NOT NULL) ;
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('27-MAY-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('02-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('04-JUN-19','DD-MON-YY'),0,'I','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',1,to_date('06-JUN-19','DD-MON-YY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',1,to_date('15-JUN-19','DD-MON-YY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_date('15-JUN-19','DD-MON-YY'),1,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_date('19-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_Date('26-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',0,to_date('26-APR-19','DD-MON-YY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',0,to_date('05-JUN-19','DD-MON-YY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',1,to_date('06-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',1,to_date('10-JUN-19','DD-MON-YY'),0,'I','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('12-JUN-19','DD-MON-YY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('18-JUN-19','DD-MON-YY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('19-JUN-19','DD-MON-YY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('26-JUN-19','DD-MON-YY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('28-JUN-19','DD-MON-YY'),0,'A','CWR');

PS_CTS_IQN_BU Table Build and Insert Scripts: ( IQN business unit setup )

CREATE TABLE PS_CTS_IQN_BU (COUNTRY VARCHAR2(3) NOT NULL,
   BUSINESS_UNIT VARCHAR2(5) NOT NULL) ;
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('USA','US410');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('USA','US412');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('CAN','CN512');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('IND','IN208');

This post has been answered by Frank Kulash on Aug 1 2019

Jump to Answer

John Thorton

How do I ask a question on the forums?

We can't say what you do wrong since you decided to NOT show us exactly what you do & how Oracle responds.

AndrewSayer

Amarprem wrote:

Hi there,

I have a query that joins 2 tables, 1 table has 2 million records and other 1 million records, the query returns 1 record in less than 1 second, both the 2 tables being joined are accessed across a database link, despite the query running in less than a second, if I do a simple insert using the query it just hangs, and I don't understand why. The table is not locked, I have tried dropping the table before the insert and recreating it, to ensure there are no locks.

If I look at the wait events all I a can see this

I am running 12c on Windows, the database link is to database in another pluggable database in the same container.

SQL*Net message from dblink

Does query only return 1 row or was that all that you fetched?

What is being waited on at the remote DB? Is it using the same execution plan to execute the select part of the insert than if it was just doing the select?

You *may* be better off writing pl/sql to do the select statement and insert the result into your target table (if it is just one row, I wouldn't be too concerned with the plsql overhead)

Mike Kutz

Amarprem wrote:

Hi there,

despite the query running in less than a second..

How did you measure this?

AParm

I'm in sql developer.

AParm

The query as run today returns 1 row, tomorrow it might return more, if the select takes a second to run, why does the insert of one record hanging, what else is it doing?

AndrewSayer

Amarprem wrote:
The query as run today returns 1 row, tomorrow it might return more, if the select takes a second to run, why does the insert of one record hanging, what else is it doing?

Consider answering the questions asked of you.

It is doing something different, perhaps your simple query is not as simple as you think, it does operate on remote objects after all.

Oracle can use a different execution plan for an insert statement than the select on it's own. You have the power of finding out if this is the case. You can view the sql that is being remotely executed from your local statement by looking at it's execution plan. You can view the execution plan of the remote sqls by connecting to the remote DB and checking there.

If you don't know how to do what's asked of you then have a google first and if that doesn't help then ask here. Don't just avoid the question.

John Thorton

Amarprem wrote:
The query as run today returns 1 row, tomorrow it might return more, if the select takes a second to run, why does the insert of one record hanging, what else is it doing?

It is waiting for more input

Mustafa KALAYCI

you said table is not locked, what if there is a lock on a row ? did you select v$session for blocking_session and event fields while your insert statement was hanging ? do that and post the result please.

AParm

I see the execution plans are different, for the first it just has TABLE FULL ACCESS for each of the tables, do I presume it does the join on the remote database, is that right?

The insert statement, has REMOTE for the 2 table, I presume, it fetching all the data for the 2 tables and doing the join locally, is that right? And if that is the case how do I get it to do the select on the remote server?

This is a pluggable database fetching data from another pluggable database, so it's all in the same server process space, why would a remote call be any different from a local call?

AndrewSayer

Amarprem wrote:

I see the execution plans are different, for the first it just has TABLE FULL ACCESS for each of the tables, do I presume it does the join on the remote database, is that right?

This is a pluggable database fetching data from another pluggable database, so it's all in the same server process space, why would a remote call be any different from a local call?

Don't just tell us, SHOW us. We can only interpret what we can see, not your own interpretation. Use copy and paste.

If you want it to use the select plan then you could hint for it (usually a driving site hint, you can view the full outline of the select plan in dbms_xplan.display_cursor with the appropriate format parameter). Or you could just do as I suggested my first response and run the select statement in PLSQL and run the return through an insert statement like:

begin

for rec in (select column_a from a@remote, b@remote where a.col = b.col)

loop

insert into local_Table (column_a) values (rec.column_a);

end loop;

end;

John Thorton

How do I ask a question on the forums?

I can't answer your questions since I have no idea what you have, what you do or what you see.

Is COPY & PASTE broken for you?

AParm

These are the execution plans

SELECT STATEMENT (REMOTE)

- HASH (GROUP BY) 9

- Access Predicates 9

A1.EDL_ID = A2.EDL

- TABLE ACCESS (FULL) OFFERS 981915

- TABLE ACCESS (FULL) LIST 1786967

INSERT STATEMENT

-LOAD TABLE CONVENTIONAL

- HASH (GROUP BY) 9

- Filter Predicates 9

NOT EXISTS(SEELCT 0 FROM A1 LNNVL(LIST.EDL_ID)<>:B1)

- REMOTE OFFERS 982004

- REMOTE LIST 1

How do I get the insert to use the same plan as the select?

Mustafa KALAYCI

now next step, post your sqls please (both of them). you should really look at the link that john said: How do I ask a question on the forums?

unknown-7404

I see the execution plans are different, for the first it just has TABLE FULL ACCESS for each of the tables, do I presume it does the join on the remote database, is that right?

Ok - first make sure you understand this concept. A query can only execute on ONE database instance.

So if you have a query that needs data from MORE THAN ONE db (e.g. across a link) then Oracle has to decide what db to execute the query on.

Whichever one it picks it then has to send the data from the other DB(s) to the db executing the query.

So for your simple 'select' query it could be executing it on the remote db but for the 'insert' query it could be executing it on the local db.

Depending on the size of the local and remote tables it can make a HUGE difference which way the data is sent.

1. The last storm blew down, and destroyed, the fence between your property and your neighbor.

2. You decide to build a concrete block wall to replace it and it will take 1000 blocks to build it.

3. Your neighbor has 900 concrete blocks on his property he will let you use if YOU build the wall.

4. You need 100 more and the store delivered them to your property.

5. You can only build the wall from ONE SIDE - yours or your neighbors.

Would you rather:

A. Carry your 100 blocks to your neighbors property?

B. Carry your neighbors 900 blocks to your property?

Post your answer.

We have NO IDEA who has more concrete blocks - you or your neighbor.

Which is why you need to SHOW US:

1. WHAT you do

2. HOW you do it

3. WHAT results you get

1 person found this helpful

AndrewSayer

Amarprem wrote:
These are the execution plans
SELECT STATEMENT (REMOTE)
 - HASH (GROUP BY) 9
 - Access Predicates 9
 A1.EDL_ID = A2.EDL
 - TABLE ACCESS (FULL) OFFERS 981915
 - TABLE ACCESS (FULL) LIST 1786967
INSERT STATEMENT
 -LOAD TABLE CONVENTIONAL
 - HASH (GROUP BY) 9
 - Filter Predicates 9
 NOT EXISTS(SEELCT 0 FROM A1 LNNVL(LIST.EDL_ID)<>:B1)
 - REMOTE OFFERS 982004
 - REMOTE LIST 1
How do I get the insert to use the same plan as the select?

Your statement doesn't seem that simple if it's doing a group by and a not exists (or is that really a not in?) on a remote DB.

As I've said before, to get the same plan you can either hint it (by checking the outline and note that it's probably a driving_site hint you are after)

OR you can use PLSQL..

FWIW I would consider a plan exposed using dbms_xplan much easier to read than what you have presented, you can also get it to include the full outline. If you need help doing that then google it or ask - don't just ignore the request.

AParm

insert into OFFERS_CHECK(
EXTRCT,
ISSUE,
A_COUNT,
SITE,
SOURCE
)
select
EXTRCT,
ISSUE,
A_COUNT,
SITE,
SOURCE from
V_OFFERS_CHECK;

CREATE V_OFFERS_CHECK
AS
select
'OFFERS' EXTRCT
'MISSING OFFERS' ISSUE,
count (*) A_Count,
OFFERS.SITE,
OFFERS.SOURCE
from OFFERS
WHERE OFFERS.EDL_ID not in (select EDL_ID from LIST)
and SOURCE = 'PST'
GROUP BY OFFERS.SITE,
OFFERS.SOURCE;

Simple, just check for all offers not in the LIST.

The select on it's own, less than a second. The insert just hangs, I just have to kill it.

Mustafa KALAYCI

where are the other databases ? OFFERS and LIST tables, are these on different databases ?

AParm

No they are on the same pluggable database LIVE, the are being accessed for reporting database.

Why should it matter, they are all just pluggable database on the SAME oracle server.

AndrewSayer

Amarprem wrote:

No they are on the same pluggable database LIVE, the are being accessed for reporting database.

Why should it matter, they are all just pluggable database on the SAME oracle server.

Remote inserts optimize differently.

Remote could be different Pdb or different server completely.

Where in the view/query are you specifying the db link? The view (I'm assuming that's what it is) is not valid DDL so I assume that wasn't copy and paste?

Have you tried the PL/SQL solution yet (I think I've mentioned it 4 times now).

begin

for rec in (select EXTRCT,

ISSUE,

A_COUNT,

SITE,

SOURCE

from V_OFFERS_CHECK

)

loop

insert into OFFERS_CHECK(EXTRCT,

ISSUE,

A_COUNT,

SITE,

SOURCE

)

values

(rec.EXTRCT,

rec.ISSUE,

rec.A_COUNT,

rec.SITE,

rec.SOURCE);

end loop;

end;

AParm

OFFERS and LIST, are synonyms to tables on the 'remote' server.

Thanks for this, I will try it, I will look at dbms_xplan, and take this up again tomorrow.

unknown-7404

Why should it matter, they are all just pluggable database on the SAME oracle server.

I told you in my reply why it matters.

Each PDB is a separate and distinct database regardless of where they are physically located.

If you follow the example I provided and answer the question I ask then you will understand why it 'matters'.

Mustafa KALAYCI

@"rp0428" gave us a very good example at his first reply. you might be carrying all the rows from the db where tables located to database that you will make your insert. at first I thought that OFFERS and LIST tables are on different databases too but if they are on the same db, then this could be your problem, your insert statement works on the remote db (not at the db that offers and list are in it).

AndrewSayer

Mustafa KALAYCI wrote:

rp0428 gave us a very good example at his first reply. you might be carrying all the rows from the db where tables located to database that you will make your insert. at first I thought that OFFERS and LIST tables are on different databases too but if they are on the same db, then this could be your problem, your insert statement works on the remote db (not at the db that offers and list are in it).

The plan tells us that for the insert statement:

All the rows from offers that match the simple predicate (SOURCE = 'PST') are pulled across from the remote DB to the local DB

For each row it then fires another query against the remote DB to check the existence in LIST (FILTER operation)

It then does the group by on the local DB

For the select statement, the entire query was passed to the remote DB which has unnested the not in subquery into an antijoin. The results were then passed to the local DB. This was much better because it was able to unnest the not in subquery rather than execute an existence check for each row.

AParm

How can I get it to use same the plan for insert as for the select?

Why does it have a cardinality of 1 for LIST in the insert plan as opposed to the 1786967 in the select, is this because it is fetching a single row at a time?

AndrewSayer

Amarprem wrote:

How can I get it to use same the plan for insert as for the select?

Why does it have a cardinality of 1 for LIST in the insert plan as opposed to the 1786967 in the select, is this because it is fetching a single row at a time?

I've recommended two ways, if you're going to just ignore them then why should we bother to help further. I've even allowed you to be lazy enough and just use copy and paste some code.

If you want more help then you will have to do some of the work yourself, if you're struggling to understand what has been recommended then try googling first and then asking for clarification. Repeating the same question will only lead to the same response.

John Thorton

You can lead some folks to knowledge, but you can't make them think.

unknown-7404

How can I get it to use same the plan for insert as for the select?

How can we get YOU to answer our questions?

You may not be able to get Oracle to use the same plan. The only possible option is to try using the DRIVING_SITE hint but I wouldn't expect that to work.

Jonathan Lewis

Andrew Sayer has given you one of the solutions to the problem - try it.

A distributed (or fully remote) select statement can be optimised and executed at a site other than the one where the statement was issued - returning the result to the original site. A CTAS or an "insert as select" MUST (for no good reason offered by Oracle Corp.) execute at the site where the table is to be created or insert into - which leads to the type of issue you're seeing.

See: https://jonathanlewis.wordpress.com/2008/12/05/distributed-dml/ (and its comments and pingbacks - which will lead you to other solutions)

Regards

Jonathan Lewis

jgarry

Amarprem wrote:

No they are on the same pluggable database LIVE, the are being accessed for reporting database.

Why should it matter, they are all just pluggable database on the SAME oracle server.

It matters because Oracle needs to communicate back and forth between the databases, and must have limits on how it does that (which don't have to make sense on the surface, though normally there are reasons when you dig enough). If you established a dblink to the same database, it would still have to obey those limits, it doesn't magically know where a remote database actually is. Sometimes hiding things in views can have additional limitations. So in addition to rp0428's concrete blocks analogy, you have to pass the concrete through a drainage pipe.

It's also not necessarily a good sign that two full table accesses are the faster way to do something that is not a cartesian join. In addition to the questions others have asked, you might describe things like how many rows you expect to select update out of how many there are. There's probably some way to limit the unnecessary work being done, and that's the secret to making it go faster. You might also search asktom.oracle.com on the subject of exists versus in. There may also be indexing solutions for "things not in."

AParm

Okay, I have run explain plans, I have found that, it does NOT always run the select locally, as you can see in the second plan. What I did here was do a CTAS to create the table, which took less than a second, and the subsequent insert also execute as quick.

Can somebody shed some light what the execution plans are different

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 8tah0qth71hdp, child number 1

-------------------------------------

INSERT INTO "DATQ"."OFFERS_CHECK" (EXTRCT,

ISSUE, A_COUNT, SITE, SOURCE) SELECT 'OFFERS'

EXTRCT, 'MISSING OFFERS' ISSUE, COUNT(*) A_COUNT,

OFFERS.SITE,OFFERS.SOURCE

FROM "DATQ".OFFERS WHERE "OFFERS".EDL_ID

NOT IN (SELECT "EDL_ID" FROM "DATQ".LIST) AND SOURCE = 'PST'

GROUP BY OFFERS.SITE, OFFERS.SOURCE

Plan hash value: 371604923

-----------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | | | 12G(100)| | | |

| 2 | HASH GROUP BY | | 6 | 228 | 12G (1)|130:33:00 | | |

|* 3 | FILTER | | | | | | | |

| 4 | REMOTE | OFFERS | 983K| 35M| 3924 (1)| 00:00:01 | PDBPR~ | R->S |

| 5 | REMOTE | LIST | 1 | 25 | 12260 (1)| 00:00:01 | PDBPR~ | R->S |

-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter( IS NULL)

Remote SQL Information (identified by operation id):

----------------------------------------------------

4 - SELECT /*+ OPAQUE_TRANSFORM */ "SOURCE", "EDL_ID", "SITE" FROM

"DATQ"."OFFERS" "OFFERS" WHERE "SOURCE"='PST' (accessing 'PDBLIVE' )

5 - SELECT /*+ OPAQUE_TRANSFORM */ "EDL_ID" FROM "DATQ"."LIST"

"A1" WHERE LNNVL("EDL_ID"<>:1) (accessing 'PDBLIVE' )

This is the table I first create using a CTAS, and the truncate, followed by the insert to match the scenario above....

----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

SQL_ID 6y057jm6wt29p, child number 0

-------------------------------------

insert into OFFERS_CHECK2 select 'OFFERS' EXTRCT, 'MISSING OFFERS'

ISSUE, count (*) A_Count, OFFERS.SITE, OFFERS.SOURCE from OFFERS

WHERE OFFERS.EDL_ID not in (select EDL_ID from LIST) and SOURCE = 'PST'

GROUP BY OFFERS.SITE, OFFERS.SOURCE

Plan hash value: 1788691278

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 0 | INSERT STATEMENT | | 1 | | |

| 2 | REMOTE | | | PDBPR~ | R->S |

-------------------------------------------------------------------------

Remote SQL Information (identified by operation id):

----------------------------------------------------

2 - SELECT 'OFFERS','MISSING OFFERS',COUNT(*),"A1"."SITE","A1"."SOURC

E" FROM "DATQ"."OFFERS" "A1" WHERE "A1"."EDL_ID"<>ALL (SELECT

"A2"."EDL_ID" FROM "DATQ"."LIST" "A2") AND "A1"."SOURCE"='PST'

GROUP BY "A1"."SITE","A1"."SOURCE" (accessing 'PDBLIVE' )

Note

-----

- cpu costing is off (consider enabling it)

Jonathan Lewis

Interesting.

There are a couple of odd details I'd like to check:

Your second plan shows an insert into OFFERS_CHECK despite the fact that the insert statement itself insert into offers_check2.

Your second plan shows a note "cpu costing is off" the first doesn't, was this just a shortened cut and paste or a different configuration; are there any other parameters which set to non-standard values (can you show use the OUTLINE section so we can check any other optimizer parameters). The plan where the query operates as two separate remote statement is likely to be the more interesting one.

It's a little odd that the first insert show various names double-quoted - what tool is the SQL coming from ?

Are DATQ.offers and DATQ.lists synonyms for the remote tables ?

Regards

Jonathan Lewis

I've just run up a little test modelling your code.

My comments about "fully remote" inserts behaving the same as distributed is wrong - even in 11.2.0.4 I got a query like yours to execute fully remotely.

It looks as if the difference MIGHT be related in some way to "null aware" anti joins, so do check the optimizer parameters for both versions of the statement.

Regards

Jonathan Lewis

AParm

I have just had to replace some name, for confidentiality reasons, I might have made some typos, it's all sql developer.

DATQ.offers and DATQ.lists are synonyms for the remote tables.

John Thorton

Amarprem wrote:

I have just had to replace some name, for confidentiality reasons, I might have made some typos, it's all sql developer.

DATQ.offers and DATQ.lists are synonyms for the remote tables.

Are both "remote tables" in the same remote database?

What happens if you "push" the desired rows from the remote database into the target table?

This way no extraneous data needs to traverse the network.

The current implementation needs to bring all row across the network in order to decide which rows get inserted.

AParm

Yes, both remote tables are in the same remote database.

This is a dml statement that forms part of a process, with other similar statements, it's a process that worked on a single database, now some of our schemas have moved to another database, and I'd prefer to maintain the same process.

The test show the select part of the insert can be done remotely, it's just a case of working out how oracle is doing it.

John Thorton

Amarprem wrote:

Yes, both remote tables are in the same remote database.

The test show the select part of the insert can be done remotely, it's just a case of working out how oracle is doing it.

SQL does not know or care about what may have existed in the past & the past has ZERO relevance to the current environment.

Injecting the past into this discussion is a distraction & does not get you closer to any resolution of the present.

You are free to live with what you have.

ENJOY!

Jonathan Lewis

How was the run which produced the first execution plan (with the FILTER operation) executed ?

Regards

Jonathan Lewis

AParm

Everthing was executed in SQL developer.

AParm

Answer

Ok, I think I've worked out why it was hanging, the table had a default value of SYSDATE for a date column, which isn't in the insert select statement, I've removed the default and now it inserts on 0.85 sec.

If I put sysdate in the select statement...

insert table(

DATE_COLUMN)

select

SYSDATE

from TABLE;

it chooses the first plan, and it hangs.

Marked as Answer by AParm · Sep 27 2020

Jonathan Lewis

Well done.

sysdate would be sysdate@! (the local database) which makes the query a distributed query rather than a fully remote query, which is why it then can't be operated remotely but has to be operated from the local host.

This gives you the plan where, for each row you pull back from OFFERS you have to run the subquery against LISTS.

Regards

Jonathan Lewis

2 people found this helpful

1 - 40

Added on Aug 1 2019

#sql-pl-sql

12 comments

1,642 views

SQL & PL/SQL

Fetching Contract Employee information by Join the JOB_DTL,JOB_DTL1,IQN_BU Tables.

Comments

Post Details