sql - FORALL vs FOR bulk update -


this asked in interview. question is, 1 faster, forall on 5000 or using on 500 records? well, think indeed forall faster far processing speed concerned still depends on number of rows processed, in case of above question. please share thoughts.

it depends.

first, how test set up? in normal code, for loop runs query , if you're measuring performance of loop, you're combining time required run query, fetch results, , results.

for x in (<<some select statement>>) loop   <<do x>> end loop;

a forall on other hand, implies you've done work of fetching data need process local collection.

forall in 1..l_collection.count   <<do something>>

a performance comparison includes time required run query , fetch results in 1 case , excludes time other case not fair comparison. you'd either need include cost of populating collection in forall case or need for loop iterate on populated collection comparison fair one. approach use may have dramatic affect on result.

a forall eliminates context shifts between sql , pl/sql engines, that's it. can useful if substantial fraction of program's runtime spent on context shifts. more expensive thing you're doing data (and more expensive fetching data if you're including time), smaller fraction of time spent on context shifts must be. if you're excluding time required fetch data , guts of loop trivial operation, for loop solution may spending more 90% of time doing context shifts forall solution may 10 times faster. if you're including time required fetch data, time large fraction of overall runtime, , guts of loop expensive operation, for loop solution spending small fraction of time doing context shifts going forall won't 10x improvement.

things more complicated if including time required populate collection since there many ways potentially write that have different performance characteristics depending upon whether you're using implicit or explicit cursors , oracle version. that's discussed in more detail in this question on performance of bulk collect.


-

Comments

Post a Comment

Popular posts from this blog

OpenCV OpenCL: Convert Mat to Bitmap in JNI Layer for Android -

there several posts converting mat bitmap using utils.mattobitmap() function. i'm assuming function can called in java layer after importing utils class. i want transfer data memory address pointed uint32_t* bmpcontent; in code below. jniexport void jnicall java_com_nod_nodcv_nodcvactivity_runfilter( jnienv *env, jclass clazz, jobject outbmp, jbytearray indata, jint width, jint height, jint choice, jint filter) { int outsz = width*height; int insz = outsz + outsz/2; androidbitmapinfo bmpinfo; if (androidbitmap_getinfo(env, outbmp, &bmpinfo) < 0) { throwjavaexception(env,"gaussianblur","error retrieving bitmap meta data"); return; } if (bmpinfo.format != android_bitmap_format_rgba_8888) { throwjavaexception(env,"gaussianblur","expecting rgba_8888 format"); return; } uint32_t* bmpcontent; if (androidbitmap_lockpixels(env, outbmp,(void**)&bmpcontent)...
Read more

android - org.xmlpull.v1.XmlPullParserException: expected: START_TAG {http://schemas.xmlsoap.org/soap/envelope/}Envelope -

i trying hit soap api in application login purpose , getting above mentioned error , can 1 tell me going wrong , first time soap apis. here code hit soap :- vector<object> args = new vector<object>(); hashtable<string, object> hashtable = new hashtable<string, object>(); hashtable.put("email", "mss.siddhart28@gmail.com"); hashtable.put("password", "siddharth"); args.add(hashtable); soapserializationenvelope envelope = new soapserializationenvelope( soapenvelope.ver11); envelope.dotnet = true; envelope.xsd = soapserializationenvelope.xsd; envelope.enc = soapserializationenvelope.enc; (new marshalhashtable()).register(envelope); httptransportse androidhttptransport = new httptransportse( "http://tempuri.org/", 15000); androidhttptransport.debug = true; string result = ""; try { soapobject request = new soapobject("http:...
Read more

python - How to remove the Xframe Options header in django? -

i have made page has iframe . inside iframe want show multiple different links article facebook, or news, or youtube video or other possible url. but, due xframe header, unable so. referred following link: https://docs.djangoproject.com/en/1.8/ref/clickjacking/ , django xframeoptionsmiddleware (x-frame-options) - allow iframe client ip but didn't help. my settings.py file's middleware_classes is: middleware_classes = ( 'django.contrib.sessions.middleware.sessionmiddleware', 'django.middleware.common.commonmiddleware', 'django.middleware.csrf.csrfviewmiddleware', 'django.contrib.auth.middleware.authenticationmiddleware', 'django.contrib.auth.middleware.sessionauthenticationmiddleware', 'django.contrib.messages.middleware.messagemiddleware', 'django.middleware.clickjacking.xframeoptionsmiddleware', ) from http://django-secure.readthedocs.org/en/latest/middleware.html , found using ...
Read more