2008년 2월 4일 월요일

Data Access Pattern - Index Combine

1.발생규칙
1) Bit Map 인덱스가 한테이블에 2개 이상일때 where 조건에
두인덱스에 해당하는 컬럼의 조건을 다사용할때 발생함.
2)9.2 밑의 버젼에서는 Index Merge Plan 이 발생함.
3)꼭 Bit Map 인덱스가 아니라도 Index Combine 이 발생할수 있음.
이경우에는 Bit Map Conversion 이 추가로 발생함.

2.엑세스 형태
-첫번째 인덱스의 와 두번째 인덱스의를 이용하여 두집합간에
AND, OR, MINUS, MERGE 연산을 하여 데이터를 엑세스한다.


3.적용범위
1) DW 나 대용량 배치인경우 적용
2)OLTP 인 경우는 인덱스 조건이 똑똑한경우 적용.

4.Plan 및 SQL

CREATE TABLE t1
(c1 NUMBER, c2 NUMBER);

CREATE BITMAP INDEX i1 ON t1 (c1);
CREATE BITMAP INDEX i2 ON t1 (c2);


1) BIT MAP OR
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 OR c2 = 0;

SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
5 3 BITMAP INDEX (SINGLE VALUE) OF 'I2'


2) BIT MAP AND
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 AND c2 = 0;

SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
5 3 BITMAP INDEX (SINGLE VALUE) OF 'I2'


3) BIT MAP MINUS
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 AND NOT c2 = 0;


0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP MINUS
4 3 BITMAP MINUS
5 4 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
6 4 BITMAP INDEX (SINGLE VALUE) OF 'I2‘ --> c2 IS NULL 에 대한 ACCESS
7 3 BITMAP INDEX (SINGLE VALUE) OF 'I2' --> c2 = 0 에 대한 ACCESS

# 위경우에 c2 IS NULL 항목이 NOT NULL 이면 6번 ACCESS 가 발생하지 않는다.


4) BIT MAP MERGE
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 > 0 AND c2 = 0;


0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I2‘
5 3 BITMAP MERGE
6 5 BITMAP INDEX (RANGE SCAN) OF 'I1'

댓글 23개:

익명 :

[B]NZBsRus.com[/B]
Escape Idle Downloads Using NZB Downloads You Can Instantly Find Movies, Games, MP3 Singles, Software & Download Them @ Alarming Rates

[URL=http://www.nzbsrus.com][B]Newsgroup Search[/B][/URL]

익명 :

adult dating cardiff [url=http://loveepicentre.com/]singles summer vacations[/url] error updating network providers http://loveepicentre.com/ free datingsites with free membership

익명 :

What excellent interlocutors :)

익명 :

Probably, I am mistaken.

익명 :

In to terms agile the bedim with two backs casinos? experimentation this latest [url=http://www.realcazinoz.com]casino[/url] superintend and trophy online casino games like slots, blackjack, roulette, baccarat and more at www.realcazinoz.com .
you can also rebound the scuttle our untrained [url=http://freecasinogames2010.webs.com]casino[/url] administer at http://freecasinogames2010.webs.com and be victorious over corporeal folding money !
another artistic [url=http://www.ttittancasino.com]casino spiele[/url] chain of events is www.ttittancasino.com , because german gamblers, unclouded a cut in free-flowing online casino bonus.

익명 :

gunpowder tea [url=http://usadrugstoretoday.com/products/atarax.htm]atarax[/url] blue cross individual dental http://usadrugstoretoday.com/products/lariam.htm husbands small penis humiliation http://usadrugstoretoday.com/products/cymbalta.htm
walmart prescription discount drugs [url=http://usadrugstoretoday.com/categories/sleeping-aid.htm]sleeping aid[/url] how medical use sulfur [url=http://usadrugstoretoday.com/products/casodex.htm]scooby doo and drug house[/url]

익명 :

http://meen.in/fenofibrate/fenofibrate-interchange
[url=http://meen.in/citalopram/snorting-citalopram]drug enforcement davenport iowa[/url] health and visa requirments for sharm el sheik [url=http://meen.in/flomax/flomax-competition]flomax competition[/url]
kamikaze pilots drugs http://meen.in/feldene/feldene-20
[url=http://meen.in/clarithromycin]target pharmacy ma[/url] new hampshire brookside drug treatment [url=http://meen.in/finasteride/online-pharmacies-for-propecia-finasteride]online pharmacies for propecia finasteride[/url]
brooks pharmacy brookline ma http://meen.in/carbohydrates
[url=http://meen.in/carvedilol/what-dos-carvedilol-25-mg-look-like]rx drug information[/url] all for the drugs [url=http://meen.in/feldene]feldene[/url] drug a chick and rape [url=http://meen.in/carbohydrates/deficiency-of-carbohydrates]deficiency of carbohydrates[/url]

익명 :

travel from vancover to fairbanks http://greatadventures.in/lufthansa/eitihad-airways travel agent in penang
[url=http://greatadventures.in/hotel/treasure-bay-hotel-and-marina]cheap travel multi trip insurance uk[/url] travel size personal care [url=http://greatadventures.in/airlines]airlines[/url]
how to travel cheap on kauai http://greatadventures.in/flight/spice-jet-flight-status-site
[url=http://greatadventures.in/airline/airline-of-surinam]travel agent job colorado[/url] travel deals sent via email on wednesday [url=http://greatadventures.in/travel/internet-travel-business]internet travel business[/url]
sal travel clinic http://greatadventures.in/adventure/mini-adventure-bigger-shock florida travel specials [url=http://greatadventures.in/car-rental/alamo-atlanta-car-rental]alamo atlanta car rental[/url]

익명 :

brigitta fashion western australia http://topcitystyle.com/black-grey-red-dress-shirts-color226.html the fashion bug [url=http://topcitystyle.com/pecci-casual-brand16.html]gas clothes dryers btus[/url] matt and lauren harris dothan
http://topcitystyle.com/m-button-down-tops-shirts-size5.html new balance tennis shoes [url=http://topcitystyle.com/orange-clubbing-color121.html]how to remove human urine smell in clothes[/url]

익명 :

scareface clothes http://topcitystyle.com/navy-blue-purple-on-sale-color225.html no clothes water [url=http://topcitystyle.com/dressy-tops-page3.html]furniture designer williams evans[/url] dc shoes saint fitted smu hat
http://topcitystyle.com/white-multi-shirts-color106.html mistress gucci [url=http://topcitystyle.com/on-sale-page46.html]masai shoes[/url]

익명 :

lolita youngest girls sexy teens ass http://theporncollection.in/free-hentai/gay-hentai-yaoi
[url=http://theporncollection.in/best-porn/julia-crow-porn]anal porn gape assfucking[/url] hentai games robo uncensored [url=http://theporncollection.in/orgy/orgy-movies-tgp]orgy movies tgp[/url]
pinoy porn stars http://theporncollection.in/gay-movie/gay-suck-cock
[url=http://theporncollection.in/moms/poetry-about-moms-and-daughters]free amatuer anal movies[/url] free adult thumbnail pic galleries [url=http://theporncollection.in/masturbating/glossybabes-masturbating]glossybabes masturbating[/url]
adult women cowboy sites http://theporncollection.in/lubricant/dry-silicone-lubricant-in-a-stick
[url=http://theporncollection.in/gay-sex]dildo suck[/url] jack austin porn [url=http://theporncollection.in/gay-porn/adult-gay-porn-search-engines]adult gay porn search engines[/url]
adult education in katy texas http://theporncollection.in/porn-girl/free-porn-passwords-torrent
[url=http://theporncollection.in/gay-male/gay-white-fucking-asian-boys]hrandmas deep anal penetration[/url] gay porn clips mov [url=http://theporncollection.in/porn-dvd/free-cheating-porn]free cheating porn[/url]

익명 :

mudslide kahlua recipe virgin pero http://xwe.in/handjob/handjob-porn-archives
[url=http://xwe.in/adult-video]amateur simplex[/url] hematopoiesis in an adult [url=http://xwe.in/orgy/amateur-orgy-free-clips]amateur orgy free clips[/url]
monster dildo free pics http://xwe.in/fetish/fetish-adult-hard-core-sex
[url=http://xwe.in/thongs/granny-thongs]bora bora adult resorts[/url] men dresed like women porn [url=http://xwe.in/thong/thong-video-downloads]thong video downloads[/url]
adult clubs providence ri http://xwe.in/shemales/hogtied-shemales
[url=http://xwe.in/gay-anal/saumur-sauna-gay]hentai movie lists[/url] anal stars [url=http://xwe.in/orgy/mouse-orgy]mouse orgy[/url]
adult toys and novelity http://xwe.in/bdsm/bishop-bdsm-drawings
[url=http://xwe.in/orgasm/leticia-cline-orgasm-video]gay dildo pushers[/url] allison angel dildo [url=http://xwe.in/nylon/pvc-nylon-diaper-bags]pvc nylon diaper bags[/url]

익명 :

golden travel guides http://xwg.in/motel/boogie-motel-yes-i-be-singing-it-lyrics rvision travel
[url=http://xwg.in/expedia/expedia-flights]american airlines pet travel[/url] travel destination for swingers [url=http://xwg.in/cruise/spice-of-life-cruise]spice of life cruise[/url]
travel beading tray http://xwg.in/vacation-packages/timeshare-branson-vacation-package
[url=http://xwg.in/car-rental/rental-car-las-vegas]travel distance and florida[/url] travel agents regina [url=http://xwg.in/travel/east-tennessee-travel-trailers]east tennessee travel trailers[/url]
travel awards http://xwg.in/lufthansa/picture-embraer-emb-140 rome escorted travel [url=http://xwg.in/map/road-map-maine]road map maine[/url]

익명 :

salud etiqueta alimentos fda [url=http://usadrugstoretoday.com/products/horny-goat-weed.htm]horny goat weed[/url] protein only diet http://usadrugstoretoday.com/categories/anti-acidez.htm
vegan health [url=http://usadrugstoretoday.com/categories/schlafen-hilfe.htm]schlafen hilfe[/url] blood coming from penis [url=http://usadrugstoretoday.com/products/urispas.htm ]postmortem dental identification [/url] mood stabilixers treatment anxiety
lyrics dreaming with a broken heart [url=http://usadrugstoretoday.com/products/septilin.htm]septilin[/url] complete blood count values http://usadrugstoretoday.com/products/zovirax.htm
usa pharmacy for butabltial online [url=http://usadrugstoretoday.com/products/prinivil.htm]prinivil[/url] hottest breast video [url=http://usadrugstoretoday.com/products/colchicine.htm ]bariatric clinic loss weight [/url] health sector assembly

익명 :

low blood preassure what is normal [url=http://usadrugstoretoday.com/products/innopran-xl.htm]innopran xl[/url] recipe for cookie diet cookies http://usadrugstoretoday.com/categories/control-de-la-natalidad.htm
big agnes moon hill sleeping bag [url=http://usadrugstoretoday.com/products/purim.htm]purim[/url] catalogue companies with health and skin care products [url=http://usadrugstoretoday.com/products/remeron.htm ]snus infection [/url] dsm iv diagnosis depression
nude male penis video clips [url=http://usadrugstoretoday.com/products/mobic.htm]mobic[/url] trouble shoot toshiba pro 6100 laptop http://usadrugstoretoday.com/categories/disfuncion-erectil.htm
list of opiate drugs [url=http://usadrugstoretoday.com/categories/hypnotherapie.htm]hypnotherapie[/url] what to do when a pre employment drug screen is positive [url=http://usadrugstoretoday.com/products/zyvox.htm ]the risk of getting a stroke from smoking [/url] private health insurance new zealand

익명 :

salt stained shoes http://luxefashion.us/white-pink-casual-color34.html movies featuring porn star erica lauren [url=http://luxefashion.us/brown-sweater-color12.html]sperry boat shoes[/url] elderly active fashion advertisement
http://luxefashion.us/?action=products&product_id=2210 ralph lauren nj [url=http://luxefashion.us/ed-hardy-tank-tops-brand39.html]thik shoes[/url]

익명 :

il news smoking ban [url=http://usadrugstoretoday.com/products/viagra-plus.htm]viagra plus[/url] male bodybuilder muscle hunks http://usadrugstoretoday.com/products/retin-a-0-05-.htm
low calorie diet meal plan [url=http://usadrugstoretoday.com/categories/sleeping-aid.htm]sleeping aid[/url] purchase shorthand for windows for medical transcription [url=http://usadrugstoretoday.com/categories/perdita-di-peso.htm ]penis inhansing pills [/url] medicine bellatal
diflucan generic [url=http://usadrugstoretoday.com/products/cozaar.htm]cozaar[/url] isosmotic red blood cell http://usadrugstoretoday.com/catalogue/h.htm
stress class [url=http://usadrugstoretoday.com/categories/gums.htm]gums[/url] allegra functional groups [url=http://usadrugstoretoday.com/index.php?lng=de&cv=eu ]grapeseed oil smoke point [/url] burst blood vessel in eye

익명 :

baccarat web casino gambling bonuses http://xwn.in/slots_hog-heven-slots soaring eagle landing casino
[url=http://xwn.in/bingo_children-working-in-bingo-kitchen]pa lottery number[/url] playing cards tally ho wolf [url=http://xwn.in/joker_lyrics-to-joker-steve-miller-band]lyrics to joker steve miller band[/url]
nj lottery official http://xwn.in/casino-online_gambling-casino-korea
[url=http://xwn.in/gambling-online_republican-committee-chair-arrested-in-gambling-ring]bingo pictures[/url] joker batman original [url=http://xwn.in/slots_free-no-download-casino-slots]free no download casino slots[/url]
frank scoblete reviews blackjack http://xwn.in/bingo_free-worksheet-bingo-dauber ohio lottery new years raffle [url=http://xwn.in/casino-online_casino-balloons]casino balloons[/url]

익명 :

http://jqz.in/vpxl
[url=http://jqz.in/phosphatidylserine/phosphatidylserine-1000-mg]ma sjc decision on physician liability for prescription drugs[/url] dicran drug and germany [url=http://jqz.in/perindopril/perindopril-angiotensin-converty-enzyme-ace-inhibitor-medication]perindopril angiotensin converty enzyme ace inhibitor medication[/url]
cheapest cialis onlinecom http://jqz.in/ultram/ultram-weight-loss
[url=http://jqz.in/pharma/pharma-market]old drug store chains in mississippi[/url] parkinson drugs [url=http://jqz.in/propecia/propecia-fecundacion]propecia fecundacion[/url]
pharmacy school unc http://jqz.in/tramadol/tramadol-50
[url=http://jqz.in/prozac/dangers-of-combining-wellbutrin-and-prozac]lafamme breast mexico pharmacy[/url] sharp cough natural medicine [url=http://jqz.in/periactin/appetite-periactin]appetite periactin[/url] general california drug testing policy group home [url=http://jqz.in/pamelor/mechanism-of-action-for-pamelor]mechanism of action for pamelor[/url]

익명 :

bahamas casinos http://lwv.in/roulette/russian-roulette-simulator online roulette gambling
[url=http://lwv.in/baccarat/baccarat-luck]free printable bingo card[/url] italian lottery [url=http://lwv.in/poker-online/video-poker-playing-tips-and-strategies]video poker playing tips and strategies[/url]
casino jamestown new york http://lwv.in/gambling-online/top-gambling-sites
[url=http://lwv.in/slots/double-bucs-slots]north caolina lottery[/url] casinos freeport [url=http://lwv.in/lottery/ma-lottery-facts]ma lottery facts[/url]
casino princesa http://lwv.in/blackjack/internet-blackjack-free-online-casinos picture of the joker [url=http://lwv.in/poker-online/no-deposit-bonus-poker]no deposit bonus poker[/url]

익명 :

movie times melbourne victoria [url=http://moviestrawberry.com/films/film_puss_cafe/]puss cafe[/url] movie premier photos http://moviestrawberry.com/countries/?page=75 gay spanish movie in central america
pictures of girls reinacting the movie thirteen [url=http://moviestrawberry.com/films/film_leggenda_del_pianista_sull_oceano_la/]leggenda del pianista sull oceano la[/url] movie party favorws http://moviestrawberry.com/films/film_the_x_files_i_want_to_believe/ hickory movie theaters
movie great year open rated [url=http://moviestrawberry.com/films/film_the_hive/]the hive[/url] pics from movie heavy metal
the prince and me movie [url=http://moviestrawberry.com/films/film_charlie_wilson_s_war/]charlie wilson s war[/url] dishnetwork ppv movie listings http://moviestrawberry.com/films/film_thx/ old halloween movie
greendale movie thaerter [url=http://moviestrawberry.com/films/film_material_girls/]material girls[/url] free on line streaming facing the giants movie http://moviestrawberry.com/films/film_shaun_of_the_dead/ superman 7 the movie

익명 :

movie maker for n70 [url=http://moviestrawberry.com/films/film_mi_vida_loca/]mi vida loca[/url] norma rae movie http://moviestrawberry.com/films/film_wonderfalls/ magic johnson movie theater maryland
movie the condemded cast [url=http://moviestrawberry.com/films/film_no_through_road/]no through road[/url] oklahoma movie http://moviestrawberry.com/films/film_the_happiest_millionaire/ erotic movie
movie at greece ridge [url=http://moviestrawberry.com/films/film_the_prestige/]the prestige[/url] great escape movie theater in massillion
spanking tv movie [url=http://moviestrawberry.com/films/film_the_faculty/]the faculty[/url] leon movie http://moviestrawberry.com/films/film_sink_the_bismarck/ stuart little movie
free movie net of mom son fuck [url=http://moviestrawberry.com/films/film_flight_of_fury/]flight of fury[/url] the bad guy in the simpsons movie http://moviestrawberry.com/films/film_barb_wire/ gay movie gallery

익명 :

Thanks , I have recently been looking for info about
this subject for a long time and yours is the greatest
I've came upon till now. But, what concerning the bottom line? Are you certain about the supply?

Look at my site ... http://www.wildpartygirls.org