Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Simple insert into table hanging
Answers
-
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?
-
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?
-
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?
-
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
-
Amarprem wrote:These are the execution plansSELECT STATEMENT (REMOTE) - HASH (GROUP BY) 9 - Access Predicates 9 A1.EDL_ID = A2.EDL - TABLE ACCESS (FULL) OFFERS 981915 - TABLE ACCESS (FULL) LIST 1786967INSERT 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 1How 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.
-
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.
-
where are the other databases ? OFFERS and LIST tables, are these on different databases ?
-
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.
-
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;
/
-
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.