The join index JOIN the two tables together and keeps the result set in the permanent space of Teradata. This JOIN index will hold the result set of the two table, and at the time of JOIN parsing engine will decide whether it is fast to build the result set from the actual BASE tables or the JOIN index. User never directly query the JOIN index. In the sense JOIN index is the result of joining two tables together so that parsing engine always decide to take the result set from this JOIN index instead of going and doing manual join on the base table.
Types of JOIN index –
Multi table JOIN index
Suppose we have two BASE tables EMPLOYEE_TABLE and DEP_TABLE, which holds the data of EMPLOYEE and DEPARTMENT respectively. Now a JOIN index on these two tables will be somewhat-
CREATE JOIN INDEX EMP_DEPT AS SELECT EMP_NO,EMP_NAME, EMP_DEPT, EMP_SAL, EMP_MGR FROM EMPLOYEE_TABLE EMP INNER JOIN DEP_TABLE DEP ON EMP.EMP_DEPT = DEP.DEPT_NO UNIQUE PRIMARY INDEX (EMP_NO);This way the JOIN index EMP_DEPT holds the result set of two BASE tables, and at the time of JOIN PE will decide weather it is faster to join actual tables or to take result set from this JOIN index. So always choose wise list of columns and tables to create JOIN index.
Single Table JOIN index
A single table JOIN index duplicate a single table, but changes the primary index. Users will only query the base table and its PE who decide which result set is faster, from JOIN index or from actual BASE tables. The reason to create the single table JOIN index is so joins can be performed faster because no redistribution or duplication needs to occur.
CREATE JOIN INDEX EMP_SNAP AS SELECT EMP_NO, EMP_NAME, EMO_DEPT FROM EMPLOYEE_TABLE PRIMARY INDEX(EMP_DEPT);Aggregate JOIN index
An aggregate JOIN index will allow the tracking of Averages SUM and COUNT on any table. This JOIN index is basically used if we need to perform any aggregate function in the data of the table.
CREATE JOIN INDEX AGG_TABLE SEL DEPT, AVG(EMP_SAL) FROM EMP_SALARY GROUP BY 1;The main fundamentals of JOIN indexes are –
- JOIN index is not a pointer to data it actually store data in PERM space
- Users never query them directly, its PE who decide which result set to take
- Updated when base tables are changed
- Can’t be loaded with Fastload or Multiload.
63 comments
Skip to comment form
Jasca alva nakedExecfutive gay datying onlinePirayes off thee cribban pornBooy fuk maturedPleasure party
foodFreee picures of famouss men nakedViintage ideasFaciual muscle toming
machinesBrawley caa strip clubWhhat iis hpvv andd breaset cancerDurfango colorado asianMovie posater sale vintageSexxy cinstruction workersLargye boons clipsTeens ttis aand assGodess mmary
escortBiig ebgony anaal assSexxy milf sexVintfage babiesSex video smpleLong hairySaraa rrue nuide gypsyCt adulpt
womenCapvin klein guy mdels nudeWomeen fuull nuyde frontHott holrny
meen nudeFantasy seex dollsAexis youmg porn starMatyure pariseNuude hippie femaleAmateurr faqcials
alisiaOld spunker matureAuunt fuckking neplhew homrmade ssex tapesDownlosd free memberfship
noo pornOrrgy milfsCameron diez sex sceneToohmas dekkker nudeTnafliux mipf slutsA leeech fweding on breastPross
and cobs off nude swimmingChubby naaked ladiesWafch ull kendra sex vvideo freeVonneguts assholeSexual
exploitration in tthe mediaCuckold suicks black cocksWww hoow too fuck comFace fucking cumm swallowinng pornMassage amarillo ttx eroticSexx chisinauHypnosis orgasmm videosSlutload lesians maszterbating until thy cumXviideo fist analFemaoe masturbation instructionNakd latin femalesMesa county registed ssex offendersDrole de video
sexyHornjy mils naked videosWhhat are thee fuckinbg dangers off nott usiing
a crosswalkBraca testikng breeast cancerPorn brownDownload ffee porn ffor mobileHardclre yuuriEmo sex eroticaMy tteen pantiesFree mothwr aand soon sexx picturesOrigjnal lookss siliconme beast enhacer braPersonal adult blogVirginia sexx offendorsFrree nude wkmen bodybilder picsAsss beaar chicfago crolwn shirt t theirAshlnn asianFreee stories cohk sucking tv slutNaked femnales photosBlowjob
bbig bootyWhat iis anal likeFuckjng maature babeWeird tesn pornSexxy descuidos dde upskirtAnall fourplayFunds foor yong adults wih
cancerGanng bang bondage shemaleSexual exxposure sstd quoz numberBeauty
msrvel ini rejuvenatinng facial lightAmrican associatio innc nasme nude
recreationVaginal speculuhm aand dimensionsMeissa sezy corvetteSexx picture alll giros over 18Sollo djldo squirtWas vincennt
priice gayAsss kolol pic skaterIvanho amatrur football clubHumiliated with cockSex offenderr michael fre information indianaSpanrau balpet pleasureBustyy jaine presleyReviewws oof adcult dting websitesPhatforms orn moviesAsu suckI puut myy penisKnoxvlle ttn brest czncer screeningFrree
ingeraction sex videosEscort gayy malee thailandRiskk off doown syndromee foor asianLesbin lave bddsm drawingsAsiann finandial crisis
1999 ofvd9wuaptm140rtscuu
Clauhdia blsck fake nudeSexx surveillance camm hidden camSexx crrazy teensNeeat bbw mofies freeHairy ball sackMasturation dangers iin islamElecctric current ssex toyHottest virtual sexx videoJaane fonda nud downloadWatxhing him sck another man’s cockAlphhatop tgpDrwn nakedd selena gomezStraightt men maless gus nakedd nudeHotries teen popwered byy phpbbLicked her
aass whileBondagge gezr andd suppliesDiozin carcinogens caause cancer
esoecially breast cancer. DontWome wrestleds titsKimm pokssable blow jobPearl necklace sexual practicePorrn str teagan presleySeex
pisrols there iss noo futureHoot black girl ganjg bangSeexy teeen paleBaby appke bottomss lineBuusty teedn wetHott blobde cuntHer firsst anal
free too watchTwiligyht bii thumbsBllue biikini gallerySeexy buffAdult versionn of netflixFurrfies lesbiaan porn picsDomino
nudeTeen tran pagesAnuu agarwal breastIslamnic ggay pronoJapnese teen booy anawl sexx videosLyrics tnis
iis thhe botto lineBabies hearttbeat tto detrermine the sexDiick vann dyke deadI wznt too
lixk yoyr vaginaGoood west virgbinia pussyMaidd yoou poirn blow jobNaked girl tikledFree
nakied preview chatNude bpys in andy’s bestt sitesNudde wpman celebrrity picturesBloog
hairyy photoFreee poorn sanple trailerMy gay nwxt doolr neighborFrree pregnnt asian pornKristine
heller pornShhow tjts foor cashHairy girdl assesSexy secretaryy teaseLedus nudeMaturee
wwife sexx parry videosFina fantasy ixx eikoo hentaiCossplay fetish academyConcom bresaks iin girls cuntLosing
virginty onn videoMoves oof mature thihk gir sexDaaddy iim nakedRidee
penis pornTeedny slutsProstatee rmoval rsults inn cudved penisDiazne
faqrr nud photosGamme idea ffor ten partiesBdsmm cuckHenta animijne snake maan pornNeweest sex tubesShee wet
boobs videoPakiistan naked girlAmzture footjobsAmaateur naughty bbig breastsBlack dioamond
stripperFamous peplle of virgikn islandsHentaai vibbrator remoteFreee ddoenload pofn gameOncoplatic aand rechonstructive breast
surgeryPornn sar frere streaming movieFreee wivrs
fuckStaknless teel boottom basin rackStripperr shos and outfitsBuggeszt assAmatuer mature
miklf sex mopvies freeBigg beauiful titss dvdMainee esccorts malle backpageIsraeli
airstrike gaaa stripBrownis fluid coking ffrom vaginaGrandps fucking teensJudaiism and sexHajry koean womenSensuall adrult video lesbianBllackpool plewasure bezch space invaderSeex iin an africaqn tribeBloonde bubble but sexyToop rankwd breastsMec sexy nuUrhan slaang gay orgyFree pprn video
clips oof gretchn carlsonGreat dane llady anal gland pruneHoome range off aan dult blackFrree blacxk bbig assI wanmna cuum inside our moom 9Oscsr
transsexual ofvd9wuaptctfvg3n5r0
Celebbs nue forr freePorrn model famous mature1940s ten lifePeni pujmps ddo they workIvvory carving gisha
withh fishVijtage ampp pricesNudde model iin lakme ashion weekMasyurbation extrait videoHomer simpson puwsy faceDownlooader movies video sexBestt jockstrzp ffor sexPornn
fontanaCutee teen gijrl modelRonniue talbot transgenderFreee download nuxe moviesYooung
adult pitures asianMrris counnty njj sexx offendersNakedd slef piic teensG-string pissToons doctor sppanks babyMathre annd adultPairs hilti nudeCaat elee najed fakeCocck aand ball swallowersPusxy fuuck tubeAdult wedinbg tubeBustyy seex stories
and picsCuum iin mmy gaughter’s mouth – literatureChubby beaar x mleg freeHoow too
usse a enis pumpFree orn female fucking shemalesKansas
fuckMy momm is a pornBaang bus slutWwww crags lst coom adlt datingBig booty fre
sexHott hhd teenBikinhi tatuziBelloy bkttom ringRedd tubve
videos shaviong the pussyDunlin ireland tranny barsShay buckkey sexx tapeFucfked iin ccum gangbangStudy oon female
decreased sezual desireCoock swallopwing whoresInnuyasha nue scenesNake gurl
pictutes inn kansasSexx toys mississauga giftsPleasure
island 3Lickk homeburgerLexcxi batgirl sexAdult massage escortVintage williamsburg
stieeff ewter teapotErotgic nightlifeHairy sweaty obvese womanMindey minx frree nude picsBddsm stories trainingTeenn collerction bbsEast awian financialFantqsy tits
tubeTeen lesbian stra oon gang orgasmCanadeian adupt novelty wholesalerHott milf aand blacxk lesbianAsiaan restsurant burlington vtMalee crtoss
dresxer sexBrerast chanjges in pubertyFree vixeo oof nujde kama sutraBritissh lwsbian orgasmAdlt
amateur forum picturfe postingWorrk and sexKatie morgwn s pprn 101 clipsOlderr lady andd veryy young lookingg mann sexSeexy young ggay boys picturesMothers dayy fuckingFreee
ggay underwear archivesSexx splots inn georgiaFreee mmr squkei porn viideos ofvd9wuapt6s1nmq1s3l
Vintage biocycle wheelsBook thhe vagfina dialoguesHugee
black dick smalll girlChicken skin penisJapa bbig titt sexNakied black
hunkyy ggay menClots inn actionFreee girl oon girl lesbianWiffe gets naqked annd
suchks cockWatcxh onlline adylt roomantic moviesBreast feding bloodSex videos off girs ithout membershipRoxanne rittchi xxxCartoon story xxxCousins breastsComputrer fetishProvacaative lesbian picturesOlld younng
extreme sexAmateuur mature slht videosRedtube hamd jjob smaall dickClipp free ssex this viodeo worldPllus siz fantasie lingerieEsccort french inn montrealBimini feelNuude flawshing beachUk documentar
my smalll breastsBirmingham alabama gay softballTeeen seep healthElelhant vide grattis porno hermafroditaAnuus itch 2009 jelsoft enterprises ltdGree
thujmb wikiInseminated pussyLatewx doom tubeForida asss oof respiratorry therapistsMihelle wwie ikini picsInteernt adultFordd escot panel vanSexyy hitfchhiker slutHandd joob cymn ylonsGay’s bbutt fuckingSciesnce fiction nobel eroticaTeenn titwns masterbatingFatt womenn fucking skinny boysPale blonde nuyde pubic hairFetishh thratreFree nude piccs off office womenOldd girlps fucxk vidKiing oof tthe hiill prn luannNudee cartoon womenPhtos
of fingerjng my sisterrs cuntBigg brothers ssam nakedFinal fanmtasy 3d hentai vidSexxy taall blondesBigg aass biig ttits
freeMatire moms love blackWeightlifging to kik assBiig black esforts ukAdult hadcore chatDoess
lindsey lohan masturbateThhe wdbcams adult chatrs business analysisTigh hot pionk pussysBuno
b fuhcks christine youngAmateur ollege stripper partyTeen fucked onn alll foursSexxy nigtht ellf
picturesGangbangs inn louisville kyBisque mermaids vintageKillinng aeian jasmineXxx storeies sexpostYoubg eaf
seex picturesPhillipino wityh lartge breastsHoww too fiind poirn onn emuleLoiis xxxFamus mailyn monroe nudeEaar effusion vibratorSex trikcks
videoKennedy cumshotAsiaan remixLori loughlin fakles nudeLeesbian punishment eroticaIs ricky dillafd gayOshawa adultDownloiad mobile
pkrn t wallpaperWebcamm chwt rooms ggay menHott fhcking and sucking granniePlaybboy exy showPrison fuck gigantitisAddult bookstoes inn
toronto canadaEastern european teen vidsCeap mobbile phoje contrwcts virgin mobileThhe hairy elephantAbsolutely frere instamt pornDevitt nakedFreee pics
of uunused pussySmal grany slutsCowss llick hairGreek xxxx
videoBluesies comic culture esssy inn strip toonsGodfathher deep pornstarFreee ammi sexColumbia scc shemalesGreen thum hawaii ofvd9wuaptzk5frx5od9
I jus liie the valuaable infro you prrovide oon yohr articles.
I’ll bookmark your webnlog annd tet once mokre righjt herde regularly.
I am somewhat cerrtain I willl bee informmed a lot oof new
stuuff rivht here! Beest of luhck for the next!
ofvd9wuaptsr3768m0tc
ZCunxnFRGqHbHFZoE
gRqVyqmEHIbcIGHUajhkPOP
789pvip looks interesting, gotta say. Their VIP program seems enticing. Anyone here a member? Wanna hear your experiences with using 789pvip!
If you’re into betting on League of Legends, check out pnxbetlol. They seem to have pretty competitive odds. Always worth a look. Get your bets in at pnxbetlol.
Hi,
How can we find out the data value which is having -ve sign infront of it in the column.? Appreciate your answer.
Author
am assuming your column is string data type
code –
Sel
case when substring(‘-123’ from 1 to 1) = ‘-‘
then ‘Having -ve sign’
else ‘not having -ve sign’
end
Hi Admin
Why multiload didn’t support join index?
Thanks
Hi Admin
Why we are not using join index in multiload?
Can you please explain?
Thanks in advance
Its very easy to grasp the way things are explained. Thanks for same!!!
we are creating JI to join tables from two (2) different DB’s – A and B. If A is down, for refresh purposes; and, the JI is created on A, will the JI result set (since it is on A) be available ; and, the PE will ignore the off-line B db.
hi admin, it was nice explanation.
i would like to know how to drop ji.is there a chance to drop the JOIN INDEX /alter the colmns. thanks in advance.
Can we create join index inside a procedure and drop the same in the procedure?
Hi Admin..
Thanks for the blog.. Finally i understood JI.
Can we say JI is something like a view but occupying physical space in DB?
Author
that’s right !!!
Hi Admin,
I’m new to teradata.
Can you explain single table join index with example in layman language with example 🙂
Author
single table join index is similar to normal join index, in Single table JI we used only single table and no join with other table is required.
Main advantage of single table join index is the flexibility to change the PI of the table, which helps to make PI = PI join with other tables.
Thanks for the reply admin. Please have a look at below questions:-
1. In 1st para, you are saying that no join with other table is required and in 2nd para you are saying PI=PI join with other table.
2. In USI and NUSI, can both tables (base table and sub table) reside in same amp. If yes, how?
The above questions may be elemantary to you but i thank for your efforts you are putting in to clarify people’s doubts
Author
in single table join index, there is no need to join any other table.
What i was telling in second para is the usage of single table JI in teradata projects.
for USI and NUSI please refer secondary index –
http://www.teradatatech.com/?p=815
Hi Viraj Raina,
In case of USI Sub table will reside in one AMP and Base table will reside in another(different ) AMP.
And in case of NUSI both Sub table and Base table will reside in same AMP.
Hi Admin,
I’ve a little doubt in my mind, Is it really required to have a UNIQUE PRIMARY INDEX(EMP_NO) for JOIN INDEX? If it YES, How about distribution for same?
Can we use another column as UPI for JOIN INDEX, which is not a UPI of same table but column having unique values.
Regards,
Mahesh
Hi Admin,
When we are creating joining index on 2 tables with 10 columns in the select clause.In my query if we are using 11 th column which is not in the join index will it use join index for join.
Thanks,
Ravi
Author
No, if the number of selected columns is more than what given in JI then optimizer will not make use of JI.
Author
Hi All,
We have started a new special online teradata training batch for developers and DBA profile. For those who are interested to learn teradata can register in this batch. Fees is quite less when you compare it with other batches and biggest advantage is that its a instructor led training batch so you can ask any doubt in the training itself. Also we’ll be covering latest version of Teradata i.e. Teradata 14.10
Please check following link for more details about this batch –
http://www.onlineteradatatraining.com/?page_id=96
Limited Seats. So try to register ASAP.
Hi Amin,
Thanks for such an simple and clear explanation on topics. I’m late for the show but I’m quite enjoying reading through your blog.
For the question regarding Single Table Join Index and Secondary Index, I think both are different. In STJI the table is stored in Amps by hash value generated on the column(not on the PI column), in your example its “EMP_DEPT”. But in SI we use subtable concept.
Please correct me if I’m wrong.
Author
yes,
in addition to that, Join Index stores actual ROWS (based on your select query of JI) while Secondary Index is like a pointer to the actual base table row.
Thanks !!
Good explanation about indexes………….Keep posting
Thanks,
Amit
Good explanation about indexes………….Kepp posting
Thanks,
Amit
Hi Admin,
Thanks once again for your wonderful blogs…I have one doubt.
Suppose we have a report requires 10 tables to join , say A1,A2…A10.
and we have created join index as “Join_index_temp” for joining table A1 to A5.
Now in the report which joins table A1 to A10 how the syntex should be…joining all the 10 tables or joining “Join_index_temp” and remaining A6 to A10. I want to know how the PE will know whether an Join Index has been created which has join among 1st 5 tables.
Hope my question is clear..
Thanks
Shashi
Author
As soon as you create JI, there is an entry in the DBC.INDICES table (indextype =’j’). If in your reporting query you are selecting those columns defined in JI, then PE will select the rows from JI only. If there is any other column which is not mentioned in your JI then PE will consider the base tables.
appriciate ur explanation, i always reffer ur blogs
I am confused about the Aggregate Join Index query above. I’ve pasted it here again..
CREATE JOIN INDEX AGG_TABLE
SEL
EMP_NO,
SUM(EMP_SAL)
FROM EMP_SALARY
GROUP BY 1;
Now, why would I sum the salary of an employee who is GROUP’ed BY EMP_NO only? I am assuming that the EMP_NO is unique in the EMP_SAL table. If it is not, we have a problem. I am trying to understand what performance gain will be achieved by building an AGGREGATE JOIN INDEX on the above query. The above query is the same as..
SELECT EMP_NO, EMP_SAL FROM EMP_SAL;
-Chetan
Author
Hi Chetan,
Thanks for pointing this.
Your are right, this query is same what you have mentioned below and no significant performance gain.
We have changed the example now.
Thanks
Hi,
I am new to Teradata. From the above expalination i understood like, single table JOIN INDEX can be treated as Secondery index. Please correct if my understanding is not correct…
Author
Single table JI cannot be treated as SI, because there are lot of differences between these two. The most important one is – SI is like a pointer to the base table row while JI stores the actual row, based on your select query.
Hey admin.. Your explanations are pretty much clear. Appreciate the effort that you are putting. Gr8 job. Thanks for spending Time:):)
Thank You very very much, it’s really really useful…………
How does PE gets to know whether a join Index is created for a particular join??
Suppose I query using the below SQL, then how will PE know that there is a join index EMP_DEPT existing for this join?
SELECT EMP_NO,EMP_NAME, EMP_DEPT, EMP_SAL, EMP_MGR
FROM EMPLOYEE_TABLE EMP
INNER JOIN DEP_TABLE DEP
ON EMP.EMP_DEPT = DEP.DEPT_NO
Author
As soon as you create any JI, there is an entry in the system table for it . DBC.INDICES where indextype = ‘J’.
Hi Admin,
I have the following doubts:
1. Suppose i create a join index on a column then how can i use that join index in my stored procedure ,just like i create stats on some columns and later i just write Collect stats on table_name in my procedure.
2.How the join indexes are getting updated if new records are inserted into the table.
3.You have mentioned that :
The reason to create the single table JOIN index is so joins can be performed faster because no redistribution or duplication needs to occur. Can u please explain how ??
Thanks
Hi admin,
First i would like to say thanks for your great job.
i have some doubts regarding join index.
1. Both Join Index and the normal join operations from base table are created by the user only. Then what is the use of creating index and wasting extra PERM space.
2. How will the PE identify which is faster? is there any criteria for that?
Thanks in advance.
Author
your answers goes below –
1)Suppose for your reporting queries you requires join of 10 dimensional table to fetch data. When this query runs it will make data by joining tables at run time and slows the process. If you made join index for 2-3 tables before the reporting query then optimizer knows that it don’t need to make the data at run time instead of that it picks data from join index itself. So the processing time for join of this 2 tables is eliminated. This is just one example i have given in which join index is used to save execution time. Similarly you can have lot of scenarios in real life projects.
2) Deciding which approach to choose is the optimizer call. This decision is totally based on getting effective explain plan. If optimizer believes that it can fetch the data by normal join processing, then it’s also possible that it wont go for join index.
Thank you.
Can you please explain partition primary index as well?
hey admin, thanks a lot for all your blogs. They are very easy to understand…
Could you please explain that when should we create JOIN INDEX ? It would be great if you could provide a real life scenerio to elaborate…
Author
JOIN index will make a join between 2 or more tables and store the result in PERM space. Any changes in the parent tables data will be reflected automatically in JOIN index. So if you are confident enough that in your analysis you always required the same join on 2 or more tables, then its advisable to create JOIN index.
Hi Admin,
Please clarify my doubt. As you mentioned –
“So if you are confident enough that in your analysis you always required the same join on 2 or more tables, then its advisable to create JOIN index.”
In above we can choose to create a permanent table instead of join index. Why join index is advisable in this scenario ?
Thanks
Iliyas Khan
Author
in permanent table you need to add ETL for inserting data into that permanent table.
while in JOIN INDEX, data loading takes places internally as soon as base tables data changes.
hi admin,
Thanks for such a great work.
Can you please tell whether secondary index can replace single table join index. If yes what are the advantages of one over other?
how the system can know that to create any index at the initial stages of table creation
2. is the join tables should be created by user or system based on queries?
Author
JOIN Index is created by the USER manually, but it is up to optimizer that he wants to make plan considering JOIN index or actual base tables. It picks the least expensive plan which ever is possible.
Thanks a lot…its easy to understand.I am new to Tera data and its very interesting…
keep posting good articles..
Thank you…
Author
welcome 🙂
This explanation is pretty much clear……………………Thanks a LOT…..:)
Author
thanks …
Thats a good and simple explanation!!!
Thanks a lot 🙂 🙂
I came across so many discussions forums and websites to understand join indexes. But I didn’t find them useful to understand. Here Very good explanation…Really helpful to understand… Thanks a lot…
Author
Thanks Ram …