Oracle HINTSWord格式.docx

Oracle HINTSWord格式.docx

《Oracle HINTSWord格式.docx》由会员分享，可在线阅读，更多相关《Oracle HINTSWord格式.docx（13页珍藏版）》请在冰豆网上搜索。

thefirstletterofthehintisvital 

so/*+ALL_ROWS*/isfine 

but/*+ALL_ROWS*/willcauseproblems

）

-OptimizerMode:

FIRST_ROWS,ALL_ROWS 

ForceCBOfirstrowsorallrows.

RULE 

ForceRuleifpossible

ORDERED 

AccesstablesintheorderoftheFROMclause

ORDERED_PREDICATES 

UseintheWHEREclausetoapplypredicates

intheorderthattheyappear.

Doesnotapplypredicateevaluationonindexkeys

-Sub-Queries/views:

PUSH_SUBQ 

Causesallsubqueriesinaqueryblocktobe 

executedattheearliestpossibletime. 

Normallysubqueriesareexecutedasthelast 

isappliedisouterjoinedorremoteorjoined 

withamergejoin.（>

=7.2）

NO_MERGE（v） 

UsethishintinaVIEWtoPREVENTit

beingmergedintotheparentquery.（>

oruseNO_MERGE（v）inparentqueryblock

topreventviewVbeingmerged

MERGE（v） 

DomergeviewV 

用于在视图中有groupby,distinct，需要复合合并时打入视图中

optimizer_features_enable;

_complex_view_merging

MERGE_AJ（v） 

} 

PuthintinaNOTINsubquerytoperform（>

=7.3）

HASH_AJ（v） 

SMJanti-joinorhashanti-join.（>

Eg:

SELECT..WHEREdeptnoisnotnull 

ANDdeptnoNOTIN 

（SELECT/*+HASH_AJ*/deptno...）

HASH_SJ（v） 

TransformEXISTSsubqueryintoHASHorMERGE

MERGE_SJ（v） 

} 

semi-jointoaccess"

v"

PUSH_JOIN_PRED（v） 

PushjoinpredicatesintoviewV

NO_PUSH_JOIN_PRED（v） 

DoNOTpushjoinpredicates

-Access:

FULL（tab） 

UseFTSontab

CACHE（tab） 

Iftablewithin<

arameter:

CACHE_SIZE_THRESHOLD>

treatasifithadtheCACHEoptionset. 

See<

.Only 

appliesifFTSused.

NOCACHE（tab） 

DonotcachetableevenifithasCACHEoption

set.OnlyrelevantforFTS.

ROWID（tab） 

AccesstabbyROWIDdirectly

SELECT/*+ROWID（table）*/... 

FROMtabWHEREROWIDbetween'

&

1'

and'

2'

;

CLUSTER（tab） 

Useclusterscantoaccess'

tab'

HASH（tab） 

Usehashscantoaccess'

INDEX（tab[ind]） 

Use'

ind'

toaccess'

INDEX_ASC（tab[ind]） 

Use'

forrangescan.

INDEX_DESC（tab{ind]） 

Usedescendingindexrangescan

（Joinproblemspre7.3）

INDEX_FFS（tab[ind]） 

Indexfastfullscan-ratherthanFTS.

INDEX_COMBINE（tabi1..i5） 

Trytousesomebooleancombinationof

bitmapindex/si1,i2etc 

INDEX_SS（tab[ind]） 

withan 

indexskipscan

AND_EQUAL（tabi1..i5）Mergescansof2to5singlecolumnindexes.

USE_CONCAT 

Useconcatenation（UnionAll）forOR（orIN） 

statements.（>

=7.2）.See[NOTE:

17214.1]

（7.2requires<

Event:

10078>

7.3nohintreq）

NO_EXPAND 

DonotperformOR-expansion（Ie:

Donotuse 

Concatenation）.

DRIVING_SITE（table） 

Forcesqueryexecutiontobedoneatthe

sitewhere"

table"

resides

-Joining:

USE_NL（tab） 

Usetable'

asthedrivingtableina 

NestedLoopsjoin. 

Ifthedrivingrowsource 

isacombinationoftablesnameoneofthe 

tablesintheinnerjoinandtheNLshould 

driveofftheentirerow-so