database - How to control parallel execution in Oracle DB -

-

i'm trying execute script developed data transformation in oracle.

i have table large amount of data , want split data table based on condition , delete affected rows original table.

my approach use create table select second table, same first table condition inverted. truncated original table , moved data original temporary table. so:

create table data_reject select a, b, c table_original in (select criteria aux_table) /

then

create table data_aux select a, b, c table_original not in (select criteria aux_table) /

finally do

truncate table data_original /

and

insert table_original (a, b, c) select a, b, c table_aux /

my problem placed parallel hint on select , create table commands, , appears 1 statement issued, , next 1 starts executing without waiting first statement end execution.

this leads original table losing data before script has time populate other 2 tables.

this script executed command

alter session enable parallel dml

how can prevent 1 command starting before previous 1 ends execution?

this script looks like: -- create temp table create table id_loc_rejected_temp parallel 32 nologging id_hier (select hierarchy7_key id id_hierarchy), loc_hier (select hierarchy6_key loc loc_hierarchy)

     select /* + parallel (a, 32) */             id_key,             loc_key        sls       a.id_key not in (select key merch_hier)      union       select /* + parallel (a, 32) */             id_key,             loc_key        sls       a.loc_key not in (select store loc_hier)      union       select /* + parallel (a, 32) */             id_key,             loc_key        id_loc_rejected_previous   /    insert debug_msg values(sysdate, 'first filter. inserted ' || sql%rowcount || ' rows')   /   commit   /    --add more filters   insert /* + append parallel (id_loc_rejected_temp, 32) */     id_loc_rejected_temp(id_key,                                 loc_key)                    store_repl               (select /* + parallel (t, 32) */ id_key id, loc_key store srep t)          select /* + parallel (a, 32) */                id_key,                loc_key           sls          (a.id_key, a.loc_key) not in (select id, store store_repl)         union          select /* + parallel (a, 32) */                id_key,                loc_key           inv          (a.id_key, a.loc_key) not in (select id, store store_repl)    /    insert debug_msg values(sysdate, 'second filter. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- remove duplicates   create table id_loc_rejected parallel 32 nologging   select /* + parallel (a, 32) */ distinct id_key, loc_key id_loc_rejected_temp   /    insert debug_msg values(sysdate, 'transformed temp table final table. inserted ' || sql%rowcount || ' rows')   /   commit   /    drop table id_loc_rejected_temp   /    insert debug_msg values(sysdate, 'dropped id loc rejected temp table')   /    -- create rejected data tables   create table sls_rejected parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                id_level,                loc_key,                loc_level,                date1,                ticket,                units           sls          (a.id_key, a.loc_key) in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'sales rejected created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- inventory reject table   create table inv_rejected parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                id_level,                loc_key,                loc_level,                date1,                ticket,                units           inv          (a.id_key, a.loc_key) in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'inventory rejected created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- stockouts reject table   create table oos_rejected parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                id_level,                loc_key,                loc_level,                date1,                ticket,                flag           oos          (a.id_key, a.loc_key) in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'stockout rejected created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- store replenishment reject table   create table stg_ro_st_re_rejected parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                loc_key,                review           srep          (a.id_key, a.loc_key) in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'store replenishment parameters rejected created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- now, create temp tables hold rest of data (what want keep)   -- sales temp table   create table sls_tmp parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                id_level,                loc_key,                loc_level,                date1,                ticket,                ticket           sls          (a.id_key, a.loc_key) not in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'sales temp created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- store replenishmment temp table   create table stg_ro_store_repl_tmp parallel 32 nologging         select /* + parallel (a, 32) */                id_key,                loc_key,                review           srep          (a.id_key, a.loc_key) not in (select id_key, loc_key id_loc_rejected)   /    insert debug_msg values(sysdate, 'store replenishment temp created. inserted ' || sql%rowcount || ' rows')   /   commit   /    -- truncate original tables , reinsert    -- final sales   truncate table sls   /    insert /* + append parallel (sls, 32) */     sls(id_key,                         id_level,                         loc_key,                         loc_level,                         date1,                         ticket,                         ticket)               (                  select /* + parallel (a, 32) */                         id_key,                         id_level,                         loc_key,                         loc_level,                         date1,                         ticket,                         ticket                    sls_tmp               )   /    insert debug_msg values(sysdate, 'sales reinserted. inserted ' || sql%rowcount || ' rows')   /    commit   /   drop table sls_tmp   /

the problem select statements in script. of them had blank lines organization inside , these lines made sqlplus think had execute 2 commands instead of one.

this made long commands fail completely, while next commands had nothing or failed too.

the other problem sql%rowcount inside insert, made of inserts being performed while others lagged behind.

finally, broke script apart, executing 1 step @ time , worked fine.


Comments

Post a Comment

Popular posts from this blog

ios - Memory not freeing up after popping viewcontroller using ARC -

-
hi project arc based, using uinavigationcontroller make transition between viewcontroller. using profiler analyse happening behind scene memory. noticed when push viewcontroller allocate memory components , when pop not freeing allocated memory. since using arc unable implement dealloc or release component. have analysed in detail , there no memory leak in project. i not using strong property push viewcontroller. here how pushing viewcontroller. viewcontroller *obj = [[viewcontroller alloc] init]; [self.navigationcontroller pushviewcontroller:obj animated:no]; any clue whats going on? should free memory have consumed. please advise your description indicates have retain cycle, leads objects not being deallocated. typical source of retain cycles properties assigned loading nib files (usually declared iboutlet ). two strategies break them , release objects: declare property weak : @property (nonatomic, weak) iboutlet uilabel *statuslabel; set property nil in...
Read more

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more