Query just runs, doesn't execute

Question

my query just runs and doesnt execute, what is wrong. work on oracle sql developer, company server

CREATE TABLE voice2020 AS
SELECT
to_char(SDATE , 'YYYYMM') as month,
MSISDN,
SUM(CH_MONEY_SUBS_DED)/100 AS AIRTIME_VOICE,
SUM(CALLDURATION/60) AS MIN_USAGE,
sum(DUR_ONNET_OOB/60) as DUR_ONNET_OOB,
sum(DUR_ONNET_IB/60) as DUR_ONNET_IB,
sum(DUR_ONNET_FREE/60) as DUR_ONNET_FREE,
sum(DUR_OFFNET_OOB/60) as DUR_OFFNET_OOB,
sum(DUR_OFFNET_IB/60) as DUR_OFFNET_IB,
sum(DUR_OFFNET_FREE/60) as DUR_OFFNET_FREE,
SUM(case when sdate < to_date('20190301','YYYYMMDD')
    then CH_MONEY_PAID_DED-nvl(CH_MONEY_SUBS_DED,0)-REV_VOICE_INT-REV_VOICE_ROAM_OUTGOING-REV_VOICE_ROAM_Incoming
    else (CH_MONEY_OOB-REV_VOICE_INT-REV_VOICE_ROAM_OUTGOING-REV_VOICE_ROAM_Incoming) end)/100 AS VOICE_OOB_SPEND
FROM CCN.CCN_VOICE_MSISDN_MM@xdr1
where MSISDN IN ( SELECT MSISDN FROM saayma_a.BASE30112020) --change date
GROUP BY   
MSISDN,
to_char(SDATE , 'YYYYMM')
;

Does the query without the create produce anything? And do you have privileges for create and select? — P.Salmon, Dec 05 '20 at 08:05
What is the difference between "running" and "executing"? Do you mean it starts but doesn't complete? — William Robertson, Dec 05 '20 at 14:26

APC · Accepted Answer · 2020-12-05T12:02:04.333

This is a performance issue. Clearly the query driving your CREATE TABLE statement is taking too long to return a result set.

You are querying from a table in a remote database (CCN.CCN_VOICE_MSISDN_MM@xdr1) and then filtering against a local table (saayma_a.BASE30112020) . This means you are going to copy all of that remote table across the network, then discard the records which don't match the WHERE clause.

You know your data (or at least you should know it): does that sound efficient? If you're actually discarding most of the records you should try to filter CCN_VOICE_MSIDN_MM in the remote database.

If you need more advice you need to provide more information. Please read this post about asking Oracle tuning questions on this site, then edit your question to include some details.

yes this is exactly it. will edit my question and post on the tuning questions — Amoryn Saayman, Dec 06 '20 at 05:10

score 0 · Answer 2 · answered Dec 05 '20 at 06:46

0

You are executing CTAS (CREATE TABLE AS SELECT) and the purpose of this query is to create the table with data which is generated via this query.

If you want to just execute the query and see the data then remove first line of your query.

-- CREATE TABLE voice2020 AS
SELECT
.....

Also, the data of your actual query must be present in the voice2020 table if you have already executed it once.

Select * from voice2020;

answered Dec 05 '20 at 06:46

Popeye

35,427
4
10
31

my code doesnt execute when i create the table voice2020. it just runs – Amoryn Saayman Dec 05 '20 at 06:55
Because you are fetching and inserting data uaing db link. It also depends on network speed. – Popeye Dec 05 '20 at 14:25

score 0 · Answer 3 · answered Dec 05 '20 at 08:19

0

Looks like you are trying to copying the data from one table to another table, Can you once create the table if it's not created and then try this statement.

insert into  target_table select * from source_table;

answered Dec 05 '20 at 08:19

gourav bhattacharya

5
3

Query just runs, doesn't execute

3 Answers3