PK #°EGoa«, mimetypeapplication/epub+zipPK $°EG OEBPS/state.htm ÿ
ãã®ç« ã§ã¯ãTimesTenã§äœ¿çšå¯èœãªSQLæã«ã€ããŠèª¬æããŸãã
éåžžãSQLæãšã¯ããŒã¿æäœèšèª(DML)æãŸãã¯ããŒã¿å®çŸ©èšèª(DDL)æã®ããããã§ãã
DMLæã¯ããŒã¿ããŒã¹ã»ãªããžã§ã¯ããå€æŽããŸããDMLæã®äŸãšããŠãINSERT
ãUPDATE
ããã³DELETE
ããããããŸãã
DDLæã¯ããŒã¿ããŒã¹ã»ã¹ããŒããå€æŽããŸããDDLæã®äŸãšããŠãCREATE TABLE
ããã³DROP TABLE
ããããããŸãã
æå ã®ããŒã¯ãŒãããã©ã¡ãŒã¿ãŸãã¯å¥èªç¹ã®éã«ãã³ã¡ã³ãã衚瀺ã§ããŸãã次ã®2éãã®æ¹æ³ã§ãæå ã«ã³ã¡ã³ããå«ããããšãã§ããŸãã
ã¹ã©ãã·ã¥ãšã¢ã¹ã¿ãªã¹ã¯(/*
)ã䜿çšããŠã³ã¡ã³ããéå§ããŸãããã®åŸã«ã³ã¡ã³ãã®ããã¹ããç¶ããŸããããã¹ãã¯è€æ°è¡ã«ãŸããã£ãŠãããŸããŸãããã¢ã¹ã¿ãªã¹ã¯ãšã¹ã©ãã·ã¥(*/
)ã䜿çšããŠã³ã¡ã³ããçµäºããŸããéå§æåãšçµäºæåã¯ã空çœãæ¹è¡ã«ãã£ãŠããã¹ãããåãé¢ãå¿
èŠã¯ãããŸããã
ãã€ãã³2å(--
)ã䜿çšããŠã³ã¡ã³ããéå§ããŸãããã®åŸã«ã³ã¡ã³ãã®ããã¹ããç¶ããŸããããã¹ãã¯1è¡ãã䜿çšã§ããŸãããã³ã¡ã³ãã®æåŸã«æ¹è¡ã䜿çšããŸãã
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã¯ãTimesTenååããªããã£ãã€ã¶ã«åœä»€ãæž¡ãSQLæã®ã³ã¡ã³ãã§ããååãã«å¯ŸããŠæé©ãªå®è¡èšç»ãéžæãããšãããªããã£ãã€ã¶ã§ã¯ãããã®ãã³ããèæ ®ãããŸãã
å 容ã¯æ¬¡ã®ãšããã§ãã
æã¬ãã«ãšãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®è©³çŽ°ã¯ããOracle TimesTen In-Memory Databaseãªãã¬ãŒã·ã§ã³ã»ã¬ã€ããã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããå®è¡èšç»ã®å€æŽã«é¢ããé ãåç §ããŠãã ããã
SQLæã«ã¯ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã1ã€ä»¥äžããã³ã¡ã³ãã1ã€ä»ããããšãã§ããŸããæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãããµããŒãããSQLæã¯ãDELETE
ãINSERT
ãMERGE
ãSELECT
ãUPDATE
ãINSERT...SELECT
ãããã³CREATE
TABLE...AS SELECT
ã§ãããã³ãã¯DELETE
ãINSERT
ãMERGE
ãSELECT
ããŸãã¯UPDATE
ããŒã¯ãŒãã®åŸã«èšè¿°ããŸãã(DELETE
ãINSERT
ãMERGE
ãSELECT
ããŸãã¯UPDATE
ããŒã¯ãŒãã¯ãSQL
VERB
ãšãåŒã³ãŸãã)è¡š6-1ãSQLæãžã®æã¬ãã«ã»ãã³ãã®é
眮ãã«SQLæãžã®ãã³ãã®æ£ããé
眮ã瀺ããŸãã
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã¯ã³ã¡ã³ãæ§æã«åã蟌ã¿ãŸããTimesTenã§ã¯ã1è¡ã®ã³ã¡ã³ãããã³è€æ°è¡ã®ã³ã¡ã³ãå
ã®ãã³ãããµããŒããããŸãããã³ãã®ããã³ã¡ã³ããè€æ°è¡ã®å Žåã¯ãã³ã¡ã³ãæ§æ/*+...*/
ã䜿çšããŸãããã³ãã®ããã³ã¡ã³ãã1è¡ã®å Žåã¯ãã³ã¡ã³ãæ§æ--+
ã䜿çšããŸãã
æ§æ
SQL VERB {/*+ [CommentText] hint [{hint|CommentText} [...]] */ | --+ [CommentText] hint [{hint|CommentText} [...]] } hint::= FlagHint | JoinOrderHint | IndexHint FlagHint::= FlagName (0|1) JoinOrderHint::= TT_JoinOrder (CorrelationName CorrelationName [...]) IndexHint::= TT_Index (CorrelationName,IndexName,{0|1} [;...]) FlagName::= TT_BranchAndBound|TT_DynamicLoadEnable|TT_DynamicLoadErrorMode| TT_FirstRow|TT_ForceCompile|TT_GenPlan|TT_GlobalLocalJoin| TT_GlobalProcessing|TT_HashGb|TT_HashScan|TT_IndexedOr|TT_MergeJoin| TT_NestedLoop|TT_NoRemRowIdOpt|TT_Range|TT_Rowid|TT_RowLock| TT_ShowJoinOrder|TT_TblLock|TT_TblScan|TT_TmpHash|TT_TmpRange| TT_TmpTable|TT_UseBoyerMooreStringSearch
泚æ: åã®è¡šã§èª¬æããŠãããã©ã°ã®è©³çŽ°ã¯ããOracle TimesTen In-Memory Databaseãªãã¡ã¬ã³ã¹ãã®ttOptSetFlagã«é¢ããé ãåç §ããŠãã ããã |
ã³ã¡ã³ãæ§æã«æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããåã蟌ã¿ãŸãã/*
ãŸãã¯â
ã§ã³ã¡ã³ããéå§ããŸããã³ã¡ã³ãæ§æãéå§ããåŸããã©ã¹èšå·(+
)ãè¿œå ããŸãããã©ã¹èšå·(+
)ã«ãããTimesTenã§ã¯ã³ã¡ã³ãããã³ãã®ãªã¹ããšããŠè§£éãããŸãããã©ã¹èšå·(+)ã¯ã³ã¡ã³ãã»ããªãã¿ã®çŽåŸã«èšè¿°ããå¿
èŠããããŸãã(/*
ãŸãã¯--
ã®çŽåŸ)ãã³ã¡ã³ãã»ããªãã¿ãšãã©ã¹èšå·(+)ã®éã«ã¯ã¹ããŒã¹ãå
¥ããŸããã
次ã®äŸã§ã¯ãã¢ã¹ã¿ãªã¹ã¯(*)ãšãã©ã¹èšå·(+)ã®éã«ã¹ããŒã¹ãããããããã³ãã¯ç¡èŠãããŸãã
Command> SELECT /* + TT_TblScan (1) This hint is ignored because there is a > space between the star (*) and the plus (+) sign. */ ...
hint
ã¯ãTimesTenã§ãµããŒããããæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®1ã€ã§ãããã©ã¹èšå·(+)ãšãã³ãã®éã«ã¹ããŒã¹ãå
¥ããããšãã§ããŸããã³ã¡ã³ãã«è€æ°ã®ãã³ã ÿãããå Žåã¯ã1ã€ä»¥äžã®ã¹ããŒã¹ã§ãã³ããåºåããŸããããšãã°ã1è¡ã§2ã€ã®ãã³ããæå®ããã«ã¯ã次ã®ããã«å®è¡ããŸãã
Command> SELECT --+ TT_MergeJoin (0) TT_NestedLoop (1) > ...
ã³ã¡ã³ãå ã®ãã³ãã«ã³ã¡ã³ãã»ããã¹ããçµã¿å ¥ããããšãã§ããŸããããšãã°ã次ã®ããã«å®è¡ããŸãã
Command> SELECT /*+ TT_HashScan (1) This demonstrates a hint followed by a > comment string. */ ...
TimesTenã§ã¯ã次ã®å Žåã«ã³ã¡ã³ããç¡èŠããããšã©ãŒã¯æ»ãããŸããã
ãã³ããDELETE
ãINSERT
ãMERGE
ãSELECT
ããŸãã¯UPDATE
ããŒã¯ãŒãã®åŸã«èšè¿°ãããŠããªãå Žåã
ãã³ãã«ã¹ãã«ãã¹ãæ§æãšã©ãŒãããå Žåãåãã³ã¡ã³ãå ã«è€æ°ã®ãã³ãããããæ§æçã«æ£ãããã³ããšæ§æçã«æ£ãããªããã³ããããå ŽåãTimesTenã§ã¯ãäžæ£ãªãã³ãã¯ç¡èŠãããæ£ãããã³ããæ¡çšãããŸãã
TT_JoinOrder
ãã³ããTT_Index
ãã³ãã®ããããã䜿çšããéãã«ãã³ããªããšãæ®ãã®ãã³ãæååã¯ç¡èŠãããŸãã
äºãã«ç«¶åãããã³ããããå Žåãã³ã¡ã³ãã®å³ç«¯ã®ãã³ãã䜿çšãããŸããããšãã°ãã³ã¡ã³ãæååã/*+TT_TblScan (0)...TT_TblScan (1) */
ã®å Žåã¯ãå³ç«¯ã®TT_TblScan(1)
ã䜿çšãããŸãã
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã¯ã競åãããã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãããªãŒããŒã©ã€ãããŸããã€ãŸããæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããšç«¶åãããã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããæå®ãããšãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã¯ã競åãããã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãããªãŒããŒã©ã€ãããŸããããšãã°ãttOptSetFlag
ãåŒã³åºããç¯å²
ãã©ã°ãæå¹ã«ããSQLååããçºè¡ããŠæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã©ã°TT_Range
ãç¡å¹ã«ãããšããã®ååãã®ç¯å²ãã©ã°ãç¡å¹ã«ãªããŸããååããå®è¡ããåŸãååãå®è¡åã«ãã©ã³ã¶ã¯ã·ã§ã³ã«é
眮ãããå
ã®ç¯å²ãã©ã°èšå®ã¯ããã©ã³ã¶ã¯ã·ã§ã³å®è¡äžã¯æå¹ã®ãŸãŸã§ãã詳现ã¯ãäŸ6-1ãSELECTååãã§ã®æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®äœ¿çšããåç
§ããŠãã ããã
å¯ååãã§ã¯æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããªãã§ãã ããã
TimesTenååããªããã£ãã€ã¶ã¯ããã¹ã¹ã«ãŒæã®æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããèªèããŸãããTimesTenã§ã¯ããã¹ã¹ã«ãŒæã®SQLããã¹ãã¯Oracle Databaseã«æž¡ãããOracle Databaseã®SQLèŠåã«åŸã£ãŠåŠçãããŸãã
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã¯ãDELETE
ãINSERT
ãMERGE
ãSELECT
ãããã³UPDATE
ã§æå®ã§ããŸãããŸããCREATE
TABLE
...AS
SELECT
æããã³INSERT... SELECT
æã§ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããæå®ã§ããŸãããã³ãã¯ã³ã¡ã³ãæ§æå
ã«æå®ããã³ã¡ã³ãæ§æã¯SQL
VERB
ã®çŽåŸã«èšè¿°ããŸãã(ããšãã°ãDELETE
/*+
ãã³ã
*
/...
ã®ããã«ããŸãã)è¡š6-1ã«æã¬ãã«ã®ãã³ãã®æ£ããé
眮ã瀺ããŸãã
è¡š6-1 SQLæãžã®æã¬ãã«ã®ãã³ãã®é 眮
SQLæ | ãã³ãã®é 眮 |
---|---|
|
|
|
|
|
|
|
|
|
|
|
å¯ååãã§ã¯ãã³ããæå®ããªãã§ãã ããã |
|
|
|
|
ãã³ãã䜿çšããããšã§ãååãã®å®è¡èšç»ã®éžæ決å®ã«ãããŠTimesTenååããªããã£ãã€ã¶ã«äœçšããããšãã§ããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã衚瀺ããã«ã¯ãçµèŸŒã¿ããã·ãŒãžã£ttOptSetFlag
ãå®è¡ããŸããçµèŸŒã¿ããã·ãŒãžã£ttOptGetFlag
ã®è©³çŽ°ã¯ããOracle TimesTen In-Memory Databaseãªãã¡ã¬ã³ã¹ãã®ttOptGetFlagã«é¢ãã説æãåç
§ããŠãã ããã
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããšãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®éãã®æŠèŠãè¡š6-2ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããšãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®éããã«ç€ºããŸããTimesTenãªããã£ãã€ã¶ã¯ã³ã¹ãããŒã¹ã®ãªããã£ãã€ã¶ã§ãããçµ±èšãšçŽ¢åŒã䜿çšããŠååãèšç»ãçæããããããã³ãã䜿çšããåã«ãçµ±èšã®æŽæ°ã®å¿ èŠæ§ã確èªããŠãã ãããååããªããã£ãã€ã¶ã®è©³çŽ°ã¯ããOracle TimesTen In-Memory Databaseãªãã¬ãŒã·ã§ã³ã»ã¬ã€ããã®TimesTenååããªããã£ãã€ã¶ã«é¢ãã説æãåç §ããŠãã ããã
è¡š6-2 æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããšãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®éã
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ã | ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ã |
---|---|
SQLæã®ã³ã¡ã³ãæ§æå
ã® |
çµèŸŒã¿ããã·ãŒãžã£ |
ãã³ãã¯SQLæãç¯å²ãšããŸããæã¬ãã«ã§ãªããã£ãã€ã¶ã«æ瀺ããå Žåã«äœ¿çšããŸãã |
ãã³ãã¯ãã©ã³ã¶ã¯ã·ã§ã³ãç¯å²ãšããŸãããã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã§ãªããã£ãã€ã¶ã«æ瀺ããå Žåã«äœ¿çšããŸãã |
èªåã³ãããã¯ç¡å¹ã«ãªããŸããããã¯ãã³ããSQLæãç¯å²ãšããããã§ããæã®å®è¡åŸã¯ããã以éã®æããã³ååãã§ã¯ãã®ãã³ãã¯ç¡å¹ã«ãªããŸãã |
èªåã³ãããã¯æå¹ã§ããèªåã³ãããããªãã«èšå®ããŸããæãèªèº«ã®ãã©ã³ã¶ã¯ã·ã§ã³ã§å®è¡ããããããèªåã³ãããããªãã«ããªããšããã³ããæå¹ã«ãªããŸãããèªåã³ãããããªãã«èšå®ãããšããã³ãããã©ã³ã¶ã¯ã·ã§ã³ã§æå¹ã«ãªããŸã(ã³ããããŸãã¯ããŒã«ããã¯ãçºè¡ãããŸã§)ã |
ãªããã£ãã€ã¶ã¯æã«å¯ŸããŠã®ã¿ãã³ãã䜿çšããŸãã |
ãªããã£ãã€ã¶ã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®ãã¹ãŠã®æã«ãã³ãã䜿çšããŸãã |
ãã³ã㯠|
ãã³ã㯠|
ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããæå®ããããã©ã³ã¶ã¯ã·ã§ã³ã§æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããæå®ãããšããã®ãã³ãã¯ãSQLæã®ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãã³ãããªãŒããŒã©ã€ãããŸããSQLæã®å®è¡åŸã¯ãå ã®ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã¯ããã©ã³ã¶ã¯ã·ã§ã³äžã¯æå¹ã®ãŸãŸã§ãã |
ãã³ãã¯ããã©ã³ã¶ã¯ã·ã§ã³ã®éæå¹ã§ããSQLæã§æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããæå®ãããšãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã¯ãã®æã«å¯ŸããŠæå¹ãšãªãããªããã£ãã€ã¶ã¯ãã®æã«ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãã³ãã䜿çšããŸããããã©ã³ã¶ã¯ã·ã§ã³å®è¡åŸã¯ãå ã®ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã¯ããã©ã³ã¶ã¯ã·ã§ã³äžã¯æå¹ã®ãŸãŸã§ãã |
ç¹å®ã®æã«å¯ŸããŠãªããã£ãã€ã¶ãæå¹ã«ããã«ã¯ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããŸãããã³ãã¯ããªããã£ãã€ã¶ã«æ瀺ããæããšã«æå®ããå¿ èŠããããŸããæã«å¯ŸããŠè€æ°ã®å€æŽãçããå ŽåããããŸãã |
ãã©ã³ã¶ã¯ã·ã§ã³ã®ãã¹ãŠã®æã«å¯ŸããŠãªããã£ãã€ã¶ãé©çšããã«ã¯ããã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããŸãããã³ããæããšã«æå®ããå¿ èŠã¯ãããŸããããã³ãã¯ãã©ã³ã¶ã¯ã·ã§ã³äžã¯æå¹ãªããããã©ã³ã¶ã¯ã·ã§ã³ã®ãã¹ãŠã®æã«è¡šç€ºãããŸãã |
次ã®äŸã¯ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããšãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®äœ¿çšæ¹æ³ã説æããŠããŸããTimesTenãªããã£ãã€ã¶ã¯ãã³ã¹ãããŒã¹ã®ãªããã£ãã€ã¶ã§ãããæã®æé©ãªå®è¡èšç»ãå€æããŠçæããŸããå®è¡èšç»ã¯ãåç §ããè¡šã«ãã玢åŒã®ä»ã«ãåããã³è¡šã®å©çšå¯èœãªçµ±èšã«åºã¥ããŸããçµ±èšã®åèšç®ã玢åŒã®å€æŽæã¯ãTimesTenãªããã£ãã€ã¶ã¯ãåèšç®ãããçµ±èšããã³çŽ¢åŒå€æŽã«åºã¥ããŠå®è¡èšç»ãå€æŽããå ŽåããããŸããå®è¡èšç»ã¯å€æ§ãªããããããã®äŸã¯ãããŸã§å®äŸã§ãã次ã®äŸããããŸãã
äŸ6-1ãSELECTååãã§ã®æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®äœ¿çšã
äŸ6-3ãTT_JoinOrderã䜿çšããçµåé åºã®æå®ã
äŸ6-4ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_INDEXã®äœ¿çšã
äŸ6-1 SELECTååãã§ã®æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã®äœ¿çš
ååãã®å®è¡èšç»ã衚瀺ããŸãã次ã«ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããŠããªããã£ãã€ã¶ã«å¥ã®å®è¡èšç»ãéžæããããã«æ瀺ããŸãã次ã®ååããèæ ®ããŸãã
Command> SELECT r.region_name, c.country_name > FROM regions r, countries c > WHERE r.region_id = c.region_id > ORDER BY c.region_id;
ttIsql
EXPLAIN
ã³ãã³ãã䜿çšããŠãªããã£ãã€ã¶ãçæããèšç»ã衚瀺ããŸãã泚æ:
ãªããã£ãã€ã¶ã¯ãããããã®ã¹ãã£ã³ã«è¡šã¬ãã«ã»ãããã³ã°ã䜿çšããŠ2ã€ã®ç¯å²ã¹ãã£ã³ãå®è¡ããŸãã
ãªããã£ãã€ã¶ã¯ãMergeJoin
åŠçã䜿çšããŠ2ã€ã®è¡šãçµåããŸãã
Command> EXPLAIN SELECT r.region_name, c.country_name FROM regions r, countries c WHERE r.region_id = c.region_id ORDER BY c.region_id; Query Optimizer Plan: STEP: 1 LEVEL: 2 OPERATION: TblLkRangeScan TBLNAME: COUNTRIES IXNAME: COUNTR_REG_FK INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 2 LEVEL: 2 OPERATION: TblLkRangeScan TBLNAME: REGIONS IXNAME: REGIONS INDEXED CONDITION: R.REGION_ID >= C.REGION_ID NOT INDEXED: <NULL> STEP: 3 LEVEL: 1 OPERATION: MergeJoin TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: C.REGION_ID = R.REGION_ID NOT INDEXED: <NULL>
ããã§æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããŠãäœã¬ãã«ã»ãããã³ã°ã䜿çšããã¹ãã£ã³ã®å®è¡ãããã³NestedLoop
åŠçã䜿çšããè¡šã®çµåããªããã£ãã€ã¶ã«æ瀺ããŸããèªåã³ãããããªã³ã«èšå®ãããšãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããSQLæãç¯å²ãšããŠããããã«èªåã³ãããèšå®ãç¡å¹ã«ãªãããšãåãããŸãã
Command> autocommit on; Command> EXPLAIN SELECT /*+ TT_RowLock (1), TT_TblLock (0), TT_MergeJoin (0), > TT_NestedLoop (1) */ > r.region_name, c.country_name > FROM regions r, countries c > WHERE r.region_id = c.region_id > ORDER BY c.region_id; Query Optimizer Plan: STEP: 1 LEVEL: 3 OPERATION: RowLkRangeScan TBLNAME: REGIONS IXNAME: REGIONS INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 2 LEVEL: 3 OPERATION: RowLkRangeScan TBLNAME: COUNTRIES IXNAME: COUNTR_REG_FK INDEXED CONDITION: C.REGION_ID = R.REGION_ID NOT INDEXED: <NULL> STEP: 3 LEVEL: 2 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 4 LEVEL: 1 OPERATION: OrderBy TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL>
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããªãã§ååããå床æºåããŸããæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããSQLæãç¯å²ãšãããããå ã®å®è¡èšç»ã«æ»ããŸãã
Command> EXPLAIN SELECT r.region_name, c.country_name FROM regions r, countries c WHERE r.region_id = c.region_id ORDER BY c.region_id; Query Optimizer Plan: STEP: 1 LEVEL: 2 OPERATION: TblLkRangeScan TBLNAME: COUNTRIES IXNAME: COUNTR_REG_FK INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 2 LEVEL: 2 OPERATION: TblLkRangeScan TBLNAME: REGIONS IXNAME: REGIONS INDEXED CONDITION: R.REGION_ID >= C.REGION_ID NOT INDEXED: <NULL> STEP: 3 LEVEL: 1 OPERATION: MergeJoin TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: C.REGION_ID = R.REGION_ID NOT INDEXED: <NULL>
äŸ6-2 ãã³ãã®æå¹åããã³ç¡å¹å
ãã®äŸã¯ãé¡äŒŒã®é¢æ°ãå®è¡ãããã³ããæå¹ãŸãã¯ç¡å¹ã«ããããæ確ã«ãªããã£ãã€ã¶ã«æ瀺ããããšã®éèŠæ§ã瀺ããŠããŸããããšãã°ãããã·ã¥ã»ãã³ãããã³ç¯å²ãã³ãã«ãããè¡šã®ããã·ã¥ã»ã¢ã¯ã»ã¹ã»ãã¹ãŸãã¯ç¯å²ã¢ã¯ã»ã¹ã»ãã¹ã®ããããã䜿çšããããã«ãªããã£ãã€ã¶ã«æ瀺ããå Žåã§ããç¹å®ã®ã¢ã¯ã»ã¹ã»ãã¹ããªããã£ãã€ã¶ã確å®ã«éžæã§ããããã1ã€ã®ãã³ããæå¹ã«ããŠãã®ä»ãã¹ãŠã®é¢é£ãã³ããç¡å¹ã«ããŸãã
è¡šãäœæããè¡šã®1åç®ã«ããã·ã¥çŽ¢åŒã2åç®ã«ç¯å²çŽ¢åŒãäœæããŸãã
Command> CREATE TABLE test (col1 NUMBER, col2 NUMBER); Command> CREATE HASH INDEX h_index ON test (col1); Command> CREATE INDEX hr_index ON test (col2);
èªåã³ãããããªãã«ããçµã¿èŸŒã¿ããã·ãŒãžã£ttOptGetFlag
ãå®è¡ããŠããã©ã³ã¶ã¯ã·ã§ã³ã®çŸåšã®ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã衚瀺ããŸãã1ã«èšå®ãããšããã©ã°ã¯ç¡å¹ã§ãã
Command> autocommit off; Command> CALL ttOptGetFlag ('Hash'); < Hash, 1 > 1 row found. Command> CALL ttOptGetFlag ('Scan'); < Scan, 1 > 1 row found.
ttIsq
EXPLAIN
ã³ãã³ãã䜿çšããŠãWHERE
å¥ãšåçãã©ã¡ãŒã¿ã䜿çšããSELECT
ååãã®èšç»ã確èªããŸãããªããã£ãã€ã¶ã¯ããã·ã¥ã»ã¹ãã£ã³ã䜿çšããŸãã
Command> EXPLAIN SELECT * FROM test WHERE col1 = ? and col2 = ?; Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkHashScan TBLNAME: TEST IXNAME: H_INDEX INDEXED CONDITION: TEST.COL1 = _QMARK_1 NOT INDEXED: TEST.COL2 = _QMARK_2
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_Range
ã䜿çšããŠããªããã£ãã€ã¶ã«ç¯å²ã¹ãã£ã³ã®äœ¿çšãæ瀺ããŸãããªããã£ãã€ã¶ã«ããã·ã¥ã»ã¹ãã£ã³ã®ç¡å¹åãæ瀺ããŠããªãããããªããã£ãã€ã¶ã¯TT_Range
ãã³ããç¡èŠããããã·ã¥ã»ã¹ãã£ã³ã䜿çšããŸããæãå€æŽãããªããã£ãã€ã¶ã«ããã·ã¥ã»ã¹ãã£ã³ã䜿çšããã«ç¯å²ã¹ãã£ã³ã䜿çšããããã«æ瀺ããŸãããã®ããã«ã¯ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_Range
ãæå¹ã«ããæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_HashScan
ãç¡å¹ã«ããŸãããªããã£ãã€ã¶ã¯TT_Range
ãã³ããç¡èŠããªããªããŸãã
Command> EXPLAIN SELECT --+ TT_Range (1) Single line comment to set TT_Range > * FROM TEST WHERE col1 = ? and col2 = ?; Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkHashScan TBLNAME: TEST IXNAME: H_INDEX INDEXED CONDITION: TEST.COL1 = _QMARK_1 NOT INDEXED: TEST.COL2 = _QMARK_2 Command> EXPLAIN SELECT /*+ TT_Range (1) TT_HashScan (0) > Multiple line comment to enable TT_Range and disable TT_HashScan */ > * FROM TEST WHERE col1 = ? and col2 = ?; Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkRangeScan TBLNAME: TEST IXNAME: HR_INDEX INDEXED CONDITION: TEST.COL2 = _QMARK_2 NOT INDEXED: TEST.COL1 = _QMARK_1
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšãããã³ããããããŒã«ããã¯ãçºè¡ããã«ãååããå床æºåããŸãããªããã£ãã€ã¶ã¯ãååãã®å®è¡åã«æå¹ãªãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããŸããæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ããSQLæãç¯å²ãšããããããªããã£ãã€ã¶ã¯ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããŸãã
Command> EXPLAIN SELECT * FROM TEST WHERE col1 = ? and col2 = ?; Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkHashScan TBLNAME: TEST IXNAME: H_INDEX INDEXED CONDITION: TEST.COL1 = _QMARK_1 NOT INDEXED: TEST.COL2 = _QMARK_2
äŸ6-3 TT_JoinOrderã䜿çšããçµåé åºã®æå®
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_JoinOrder
ã䜿çšããŠããªããã£ãã€ã¶ã«ç¹å®ã®çµåé åºã®äœ¿çšãæ瀺ããŸãããŸããã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããŠããã©ã³ã¶ã¯ã·ã§ã³ã§ã®ç¹å®ã®çµåé åºã®äœ¿çšããªããã£ãã€ã¶ã«æ瀺ããŸãã次ã«æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãã䜿çšããŠãæã®ã¿ã§çµåé åºã®å€æŽããªããã£ãã€ã¶ã«æ瀺ããŸãã
Command> CALL ttOptSetOrder ('e d j'); Command> EXPLAIN SELECT * FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id AND e.hire_date = j.start_date; Query Optimizer Plan: STEP: 1 LEVEL: 3 OPERATION: TblLkRangeScan TBLNAME: EMPLOYEES IXNAME: EMP_DEPT_FK INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 2 LEVEL: 3 OPERATION: TblLkRangeScan TBLNAME: DEPARTMENTS IXNAME: DEPARTMENTS INDEXED CONDITION: D.DEPARTMENT_ID >= E.DEPARTMENT_ID NOT INDEXED: <NULL> STEP: 3 LEVEL: 2 OPERATION: MergeJoin TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: E.DEPARTMENT_ID = D.DEPARTMENT_ID NOT INDEXED: <NULL> STEP: 4 LEVEL: 2 OPERATION: TblLkRangeScan TBLNAME: JOB_HISTORY IXNAME: JOB_HISTORY INDEXED CONDITION: <NULL> NOT INDEXED: E.HIRE_DATE = J.START_DATE STEP: 5 LEVEL: 1 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL>
ãã©ã³ã¶ã¯ã·ã§ã³ã»ã¬ãã«ã®çµåé åºãªããã£ãã€ã¶ã»ãã³ããããã®SQLæã«å¯ŸããŠã®ã¿ãªãŒããŒã©ã€ããããããªããã£ãã€ã¶ã«æ瀺ããã«ã¯ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_JoinOrder
ã䜿çšããŸãã
Command> EXPLAIN SELECT --+ TT_JoinOrder (e j d) > * > FROM employees e, departments d, job_history j > WHERE e.department_id = d.department_id > AND e.hire_date = j.start_date; Query Optimizer Plan: STEP: 1 LEVEL: 3 OPERATION: TblLkRangeScan TBLNAME: EMPLOYEES IXNAME: EMP_DEPT_FK INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 2 LEVEL: 3 OPERATION: TblLkRangeScan TBLNAME: JOB_HISTORY IXNAME: JOB_HISTORY INDEXED CONDITION: <NULL> NOT INDEXED: E.HIRE_DATE = J.START_DATE STEP: 3 LEVEL: 2 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 4 LEVEL: 2 OPERATION: TblLkRangeScan TBLNAME: DEPARTMENTS IXNAME: DEPARTMENTS INDEXED CONDITION: D.DEPARTMENT_ID >= E.DEPARTMENT_ID NOT INDEXED: <NULL> STEP: 5 LEVEL: 1 OPERATION: MergeJoin TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: E.DEPARTMENT_ID = D.DEPARTMENT_ID NOT INDEXED: <NULL>
ååããå床æºåãããã©ã³ã¶ã¯ã·ã§ã³ã§æå¹ã ã£ãçµåé åºãæå¹ã®ãŸãŸã§ãããã確èªããŸãã
Command> EXPLAIN SELECT * > FROM employees e, departments d, job_history j > WHERE e.department_id = d.department_id > AND e.hire_date = j.start_date; Query Optimizer Plan: STEP: 1 LEVEL: 3 OPERATION: TblLkRangeScan TBLNAME: EMPLOYEES IXNAME: EMP_DEPT_FK INDEXED CONDITION: <NULL> NOT INDEXED: <NULL> STEP: 2 LEVEL: 3 OPERATION: TblLkRangeScan TBLNAME: DEPARTMENTS IXNAME: DEPARTMENTS INDEXED CONDITION: D.DEPARTMENT_ID >= E.DEPARTMENT_ID NOT INDEXED: <NULL> STEP: 3 LEVEL: 2 OPERATION: MergeJoin TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: E.DEPARTMENT_ID = D.DEPARTMENT_ID NOT INDEXED: <NULL> STEP: 4 LEVEL: 2 OPERATION: TblLkRangeScan TBLNAME: JOB_HISTORY IXNAME: JOB_HISTORY IND ÿEXED CONDITION: <NULL> NOT INDEXED: E.HIRE_DATE = J.START_DATE STEP: 5 LEVEL: 1 OPERATION: NestedLoop TBLNAME: <NULL> IXNAME: <NULL> INDEXED CONDITION: <NULL> NOT INDEXED: <NULL>
äŸ6-4 æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_INDEXã®äœ¿çš
emp_name_ix
玢åŒã䜿çšããemployees
è¡šã§ååããå®è¡ããŸãã次ã«ãæã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_INDEX
ã䜿çšããŠããã®çŽ¢åŒã䜿çšããªãããšããªããã£ãã€ã¶ã«æ瀺ããŸãããŸãttIsql
ã³ãã³ãindexes
ãå®è¡ããemployees
è¡šã®çŽ¢åŒã衚瀺ããŸãã
Command> indexes employees; Indexes on table TESTUSER.EMPLOYEES: EMPLOYEES: unique range index on columns: EMPLOYEE_ID (referenced by foreign key index JHIST_EMP_FK on table TESTUSER.JOB_HISTORY) TTUNIQUE_0: unique range index on columns: EMAIL EMP_DEPT_FK: non-unique range index on columns: DEPARTMENT_ID (foreign key index references table TESTUSER.DEPARTMENTS(DEPARTMENT_ID)) EMP_JOB_FK: non-unique range index on columns: JOB_ID (foreign key index references table TESTUSER.JOBS(JOB_ID)) EMP_NAME_IX: non-unique range index on columns: LAST_NAME FIRST_NAME 5 indexes found. 5 indexes found on 1 table.
ttIsql
ã³ãã³ãEXPLAIN
ã䜿çšããŠãlast_name
åã§WHERE
å¥ã䜿çšããemployees
è¡šã§ã®SELECT
ååãã®å®è¡èšç»ã衚瀺ããŸãã
Command> EXPLAIN SELECT e.first_name > FROM employees e > WHERE e.last_name BETWEEN 'A' AND 'B'; Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: RowLkRangeScan TBLNAME: EMPLOYEES IXNAME: EMP_NAME_IX INDEXED CONDITION: E.LAST_NAME >= 'A' AND E.LAST_NAME <= 'B' NOT INDEXED: <NULL>
æã¬ãã«ã®ãªããã£ãã€ã¶ã»ãã³ãTT_INDEX
ã䜿çšããŠã玢åŒemp_name_ix
ã䜿çšããªãããšããªããã£ãã€ã¶ã«æ瀺ããŸãã
Command> EXPLAIN SELECT --+ TT_INDEX (E,EMP_NAME_IX,0) > e.first_name > FROM employees e > WHERE e.last_name BETWEEN 'A' AND 'B'; Query Optimizer Plan: STEP: 1 LEVEL: 1 OPERATION: TblLkRangeScan TBLNAME: EMPLOYEES IXNAME: EMPLOYEES INDEXED CONDITION: <NULL> NOT INDEXED: E.LAST_NAME <= 'B' AND E.LAST_NAME >= 'A'
ã¢ã¯ãã£ãã»ã¹ã¿ã³ãã€ã»ãã¢ã¯ã次ã®æ¹æ³ã§å€æŽã§ããŸãã
ãµãã¹ã¯ã©ã€ãã»ããŒã¿ããŒã¹ãè¿œå ãŸãã¯åé€ããŸãã
ã¹ãã¢å±æ§ãå€æŽããŸãã
ãµãã¹ã¯ã©ã€ãã«ã¯ãPORT
ããã³TIMEOUT
å±æ§ã®ã¿ãèšå®ã§ããŸãã
è¡šãé åºãŸãã¯ãã£ãã·ã¥ã»ã°ã«ãŒããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã«å«ããŸãã
è¡šãé åºãŸãã¯ãã£ãã·ã¥ã»ã°ã«ãŒããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ããé€å€ããŸãã
ãOracle TimesTen In-Memory Databaseéçºè ããã³ç®¡çè ã¬ã€ããã®ã¢ã¯ãã£ãã»ã¹ã¿ã³ãã€ã»ãã¢ã®ãã®ä»ã®å€æŽã«é¢ãã説æãåç §ããŠãã ããã
SQLæ§æ
ALTER ACTIVE STANDBY PAIR {SubscriberOperation
|StoreOperation
|InclusionOperation
|NetworkOperation
} [...]
SubscriberOperation
ã®æ§æã¯ã次ã®ãšããã§ãã
{ADD | DROP } SUBSCRIBER FullStoreName
StoreOperation
ã®æ§æã¯ã次ã®ãšããã§ãã
ALTER STOREFullStoreName
SETStoreAttribute
InclusionOperation
ã®æ§æã¯ã次ã®ãšããã§ãã
[{ INCLUDE | EXCLUDE }{TABLE [[Owner.]TableName [,...]]| CACHE GROUP [[Owner.]CacheGroupName [,...]]| SEQUENCE [[Owner.]SequenceName [,...]]} [,...]]
NetworkOperation
ã®æ§æã¯ã次ã®ãšããã§ãã
ADD ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName { { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost } PRIORITY Priority } [...] DROP ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName { MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost } [...]
ãã©ã¡ãŒã¿
ãã©ã¡ãŒã¿ | 説æ |
---|---|
ADD SUBSCRIBER FullStoreName | ãµãã¹ã¯ã©ã€ãã»ããŒã¿ããŒã¹ãæå®ããŸããFullStoreName ã¯ãDSNã®èšè¿°ã®DataStore å±æ§ã§æå®ããããŒã¿ããŒã¹ã»ãã¡ã€ã«åã§ãã |
DROP SUBSCRIBER FullStoreName | æå®ãããµãã¹ã¯ã©ã€ãã»ããŒã¿ããŒã¹ã«æŽæ°ãéä¿¡ãããªãããã«æå®ããŸãããã®åŠçã¯ãã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã®æã€ãµãã¹ã¯ã©ã€ãã1ã€ã®ã¿ã®å Žåã«å€±æããŸããFullStoreName ã¯ãDSNã®èšè¿°ã®DataStore å±æ§ã§æå®ããããŒã¿ããŒã¹ã»ãã¡ã€ã«åã§ãã |
ALTER STORE FullStoreName SET StoreAttribute | ããŒã¿ããŒã¹ã®å±æ§ãžã®å€æŽãæå®ããŸãããµãã¹ã¯ã©ã€ãã«ã¯ãPORT ããã³TIMEOUT å±æ§ã®ã¿ãèšå®ã§ããŸããFullStoreName ã¯ãDSNã®èšè¿°ã®DataStore å±æ§ã§æå®ããããŒã¿ããŒã¹ã»ãã¡ã€ã«åã§ãã
|
FullStoreName | 次ã®ãããããšããŠæå®ãããããŒã¿ããŒã¹ã§ãã
ããšãã°ãããŒã¿ããŒã¹ã®ãã¹ã ãã®ååã¯ãDSNã®èšè¿°ã®
|
{INCLUDE|EXCLUDE}
| æå®ãããè¡šãé åºãŸãã¯ãã£ãã·ã¥ã»ã°ã«ãŒããã¬ããªã±ãŒã·ã§ã³ã«å«ãããããŸãã¯ã¬ããªã±ãŒã·ã§ã³ããé€å€ããŸãã
|
ADD ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName | NetworkOperation ãã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã«è¿œå ããŸããåãµãã¹ã¯ã©ã€ãã»ã¹ãã¢ãžã®ãã¹ãŠã®ã¢ãŠãããŠã³ãæ¥ç¶ã«å¯ŸããŠãã¹ã¿ãŒã»ã¹ãã¢ã䜿çšãããããã¯ãŒã¯ã»ã€ã³ã¿ãã§ãŒã¹ãå¶åŸ¡ã§ããŸããADD ROUTE å¥ã®ã³ã³ããã¹ãã§ã¯ãåãã¹ã¿ãŒã»ããŒã¿ããŒã¹ã¯ä»ã®ãã¹ã¿ãŒã»ããŒã¿ããŒã¹ã®ãµãã¹ã¯ã©ã€ãã§ãããåèªåãå°çšãµãã¹ã¯ã©ã€ãã¯äž¡æ¹ã®ãã¹ã¿ãŒã»ããŒã¿ããŒã¹ã®ãµãã¹ã¯ã©ã€ãã§ãã
è€æ°åæå®ã§ããŸãã
|
DROP ROUTE MASTER FullStoreName SUBSCRIBER FullStoreName | ã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ããNetworkOperation ãåé€ããŸãã
è€æ°åæå®ã§ããŸãã
|
MASTERIP MasterHost | SUBSCRIBERIP SubscriberHost | MasterHost ããã³SubscriberHost ã¯ããã¹ã¿ãŒããã³ãµãã¹ã¯ã©ã€ãã»ã¹ãã¢ã®ãããã¯ãŒã¯ã»ã€ã³ã¿ãã§ãŒã¹ã®IPã¢ãã¬ã¹ã§ããIPV6ã®ãããè¡šèšãŸãã¯æ£èŠåœ¢åŒã§æå®ãããããããã¯ã³ãã³è¡šèšã§æå®ããŸãã
å¥ã¯è€æ°åæå®ã§ããŸãã |
PRIORITY Priority | 1ãã99ã®æŽæ°ã§è¡šãããå€æ°ãIPã¢ãã¬ã¹ã®åªå
é äœã瀺ããŸããæŽæ°å€ãå°ããã»ã©ãåªå
床ã¯é«ããªããŸããåãåªå
床ãæã€ã¢ãã¬ã¹ãè€æ°æå®ãããŠããå Žåã¯ããšã©ãŒãè¿ãããŸãããã¢æ¥ç¶ã®ç¢ºç«ã«äœ¿çšããè€æ°ã®IPã¢ãã¬ã¹ã®é åºãå¶åŸ¡ããŸãã
|
説æ
ã¢ã¯ãã£ãã»ã¹ã¿ã³ãã€ã»ãã¢ãå€æŽããåã«ãã¬ããªã±ãŒã·ã§ã³ã»ãšãŒãžã§ã³ããåæ¢ããå¿
èŠããããŸããäŸå€ã¯ããALTER SESSIONã
ã§èª¬æãããŠããããã«ãDDL_REPLICATION_LEVEL
ããã³DDL_REPLICATION_ACTIONå±æ§ã®å€ã«åºã¥ããŠèªåçã«ã¬ããªã±ãŒããããŠå«ãŸãããªããžã§ã¯ãããã³æã®å Žåã§ãã
ã¢ã¯ãã£ãã»ããŒã¿ããŒã¹ã®ã¢ã¯ãã£ãã»ã¹ã¿ã³ãã€ã»ãã¢ã»ã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã®ã¿ãå€æŽã§ããŸãã詳现ã¯ããOracle TimesTen In-Memory Databaseéçºè ããã³ç®¡çè ã¬ã€ããã®ã¢ã¯ãã£ãã»ã¹ã¿ã³ãã€ã»ãã¢ã®ãã®ä»ã®å€æŽã«é¢ãã説æãåç §ããŠãã ããã
TimesTenã§Oracle Clusterwareã䜿çšããå Žåã¯ãALTER ACTIVE STANDBY PAIR
ã¯äœ¿çšã§ããŸããã詳现ã¯ããOracle TimesTen In-Memory Databaseéçºè
ããã³ç®¡çè
ã¬ã€ããã®ã³ãã³ãããã³SQLæã®å¶éã«é¢ãã説æãåç
§ããŠãã ããã
ãããã«ããOracle TimesTen In-Memory Databaseéçºè ããã³ç®¡çè ã¬ã€ããã®ã¹ããŒãã®å€æŽã«é¢ãã説æã§èª¬æãããŠããã¿ã¹ã¯ãå®è¡ããŸãã
ADD SUBSCRIBER
FullStoreName
ã¯ããµãã¹ã¯ã©ã€ããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã«è¿œå ããå Žåã«äœ¿çšããŸãã
DROP SUBSCRIBER
FullStoreName
ã¯ããµãã¹ã¯ã©ã€ããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ããåé€ããå Žåã«äœ¿çšããŸãã
INCLUDE
ãŸãã¯EXCLUDE
å¥ã¯ãæå®ãããè¡šãé åºãŸãã¯ãã£ãã·ã¥ã»ã°ã«ãŒããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã«å«ãããããŸãã¯ã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ããé€å€ããå Žåã«äœ¿çšããŸããINCLUDE
ãŸãã¯EXCLUDE
å¥ã¯ããªããžã§ã¯ãå(è¡šãé åºãŸãã¯ãã£ãã·ã¥ã»ã°ã«ãŒã)ããšã«1ã€äœ¿çšããŸããALTER ACTIVE STANDBY
æã¯ããALTER SESSIONã
ã§èª¬æãããŠããããã«ãDDL_REPLICATION_LEVEL
ããã³DDL_REPLICATION_ACTIONå±æ§ã®å€ã«åºã¥ããŠèªåçã«ã¬ããªã±ãŒããããŠå«ãŸãããªããžã§ã¯ãããã³æã«ã¯å¿
èŠãããŸããããã ããDDL_REPLICATION_LEVEL
ã2以äžã§ãDDL_REPLICATION_ACTION
="EXCLUDE
"ã®å Žåã¯ãINCLUDE
å¥ã䜿çšããŠãã¬ããªã±ãŒãããããªããžã§ã¯ããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã«å«ããŸãã
DDL_REPLICATION_LEVEL
ã2以äžã®å Žåã¯ãINCLUDE
å¥ã¯ã¢ã¯ãã£ãã»ããŒã¿ããŒã¹ã®ç©ºã®è¡šã§ã®ã¿äœ¿çšã§ããŸããã¹ã¿ã³ãã€ããã³ãµãã¹ã¯ã©ã€ãã®å¯Ÿå¿ããè¡šã®å
容ã¯ãè¡šãã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã«è¿œå ãããåã«åãæšãŠãããŸãã
äŸ
ãµãã¹ã¯ã©ã€ããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã«è¿œå ããŸãã
ALTER ACTIVE STANDBY PAIR ADD SUBSCRIBER rep4;
2ã€ã®ãµãã¹ã¯ã©ã€ããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ããåé€ããŸãã
ALTER ACTIVE STANDBY PAIR DROP SUBCRIBER rep3 DROP SUBSCRIBER rep4;
rep3
ããã³rep4
ããŒã¿ããŒã¹ã®ã¹ãã¢å±æ§ãå€æŽããŸãã
ALTER ACTIVE STANDBY PAIR ALTER STORE rep3 SET PORT 23000 TIMEOUT 180 ALTER STORE rep4 SET PORT 23500 TIMEOUT 180;
1ã€ã®è¡šã1ã€ã®é åºããã³2ã€ã®ãã£ãã·ã¥ã»ã°ã«ãŒããã¬ããªã±ãŒã·ã§ã³ã»ã¹ããŒã ã«è¿œå ããŸãã
ALTER ACTIVE STANDBY PAIR INCLUDE TABLE my.newtab INCLUDE SEQUENCE my.newseq INCLUDE CACHE GROUP my.newcg1, my.newcg2;
NetworkOperation
å¥ãã¢ã¯ãã£ãã»ã¹ã¿ã³ãã€ã»ãã¢ã«è¿œå ããŸãã
ALTER ACTIVE STANDBY PAIR ADD ROUTE MASTER rep1 ON "machine1" SUBSCRIBER rep2 ON "machine2" MASTERIP "1.1.1.1" PRIORITY 1 SUBSCRIBERIP "2.2.2.2" PRIORITY 1;
ALTER CACHE GROUP
æã䜿çšããŠãAUTOREFRESH
ã®ç¶æ
ãæééããã³ã¢ãŒããå€æŽã§ããŸãã
Oracle Databaseè¡šã®æŽæ°ã¯ãAUTOREFRESH
ã䜿çšããŠTimesTenãã£ãã·ã¥ã»ã°ã«ãŒãã«äŒæãããããšãã§ããŸããAUTOREFRESH
ã¯ããã£ãã·ã¥ã»ã°ã«ãŒãããŠãŒã¶ãŒç®¡çãã£ãã·ã¥ã»ã°ã«ãŒãã§ãããããŸãã¯AUTOREFRESH
å¥ã§READONLY
ãšããŠå®çŸ©ãããŠããå Žåã«æå¹ã«ã§ããŸãã
ALTER CACHE GROUP
ã䜿çšããŠèšå®ããä»»æã®å€ãŸãã¯ç¶æ
ã¯æ°žç¶çã§ãããããã®èšå®ã¯ããŒã¿ããŒã¹ã«ä¿åããããããããŒã¢ã³ããã£ãã·ã¥ã»ãšãŒãžã§ã³ããåèµ·åããå Žåã«ãåŒãç¶ã䜿çšãããŸãã
ãã£ãã·ã¥ã»ã°ã«ãŒãã®çš®é¡ã«ã€ããŠã¯ãããŠãŒã¶ãŒç®¡çãã£ãã·ã¥ã»ã°ã«ãŒãããã³ã·ã¹ãã 管çãã£ãã·ã¥ã»ã°ã«ãŒãããåç §ããŠãã ããã
å¿ èŠãªæš©é
ãã£ãã·ã¥ã»ã°ã«ãŒãã®ææè ã«å¿ èŠãªæš©éã¯ãããŸããã
å¥ã®ãŠãŒã¶ãŒã®ãã£ãã·ã¥ã»ã°ã«ãŒãã®å Žåã¯ALTER ANY CACHE GROUP
ãå¿
èŠã§ãã
SQLæ§æ
ãã®æã¯ããã£ãã·ã¥ã»ã°ã«ãŒãã®AUTOREFRESH
ã¢ãŒããå€æŽããããã«ãã£ãŠãèªåãªãã¬ãã·ã¥æäœäžã«æŽæ°ãããè¡ã決ãŸããŸãã
ALTER CACHE GROUP [Owner.]GroupName SET AUTOREFRESH MODE {INCREMENTAL | FULL}
ãã®æã¯ããã£ãã·ã¥ã»ã°ã«ãŒãã®AUTOREFRESH
ã®æééãå€æŽããŸãã
ALTER CACHE GROUP [Owner.]GroupName SET AUTOREFRESH INTERVAL IntervalValue {MINUTE[S] | SECOND[S] | MILLISECOND[S] }
ãã®æã¯ãAUTOREFRESH
ã®ç¶æ
ãå€æŽããŸãã
ALTER CACHE GROUP [Owner.]GroupName SET AUTOREFRESH STATE {ON | OFF | PAUSED}
ãã©ã¡ãŒã¿
ãã©ã¡ãŒã¿ | 説æ |
---|---|
[ Owner .] GroupName | æ°ãããã£ãã·ã¥ã»ã°ã«ãŒãã«å²ãåœãŠãããååã |
AUTOREFRESH | Oracle Databaseè¡šãžã®å€æŽãèªåçã«TimesTenã«äŒæããããã«æå®ããŸãã詳现ã¯ãããã£ãã·ã¥ã»ã°ã«ãŒãã®AUTOREFRESHããåç §ããŠãã ããã |
MODE | èªåãªãã¬ãã·ã¥æã«æŽæ°ããããã£ãã·ã¥å
ã®è¡ãæå®ããŸããINCREMENTAL å¥ãæå®ãããå ŽåãTimesTenã¯ååã®äŒæ以éã«Oracle Databaseã«å ããããå€æŽã®ã¿ããªãã¬ãã·ã¥ããŸããFULL å¥ãæå®ãããå ŽåããŸãã¯FULL å¥ãINCREMENTAL å¥ãæå®ãããªãã£ãå ŽåãTimesTenã¯èªåãªãã¬ãã·ã¥ã®ãã³ã«ãã£ãã·ã¥å
ã®ãã¹ãŠã®è¡ãæŽæ°ããŸããããã©ã«ãã®ã¢ãŒãã¯INCREMENTAL ã§ãã |
INTERVAL
| AUTOREFRESH ãå®è¡ããé »åºŠãåãç§ãŸãã¯ããªç§åäœã®æŽæ°å€ã§æå®ããŸããããã©ã«ãå€ã¯10åã§ããæå®ããæééã§AUTOREFRESH ãå®äºã§ããªãå Žåã¯ãå®è¡æã«èŠåãçæãããçŸåšã®åŠçãçµäºãããŸã§æ¬¡åã®AUTOREFRESH ãåŸ
æ©ããŸããåŸ
æ©ãã¥ãŒã10ã«éãããšããµããŒãã»ãã°ã«éç¥ã¡ãã»ãŒãžãçæãããŸãã |
STATE | AUTOREFRESH ããªã³ããªããŸãã¯äžæåæ¢ã®ããããã«å€æŽããŸããããã©ã«ãã§ã¯ãAUTOREFRESH STATE ã¯ON ã§ãã |
ON | æå®ããæééã§AUTOREFRESH ãå®è¡ãããããã«ã¹ã±ãžã¥ãŒã«ããŸãã |
OFF | ã¹ã±ãžã¥ãŒã«ãããAUTOREFRESH ãåãæ¶ããTimesTenã§ã¯INCREMENTAL ãªãã¬ãã·ã¥ã«å¿
èŠãªæ
å ±ãä¿æãããŸããããã®ãããåŸã§AUTOREFRESH ãå床ãªã³ã«èšå®ããå Žåãæåã®ãªãã¬ãã·ã¥ã¯FULL ã§ãã |
PAUSED | ã¹ã±ãžã¥ãŒã«ãããAUTOREFRESH ãåãæ¶ããŸãããTimesTenã§ã¯INCREMENTAL ãªãã¬ãã·ã¥ã«å¿
èŠãªæ
å ±ãä¿æãããŸãããã®ãããåŸã§AUTOREFRESH ãå床ãªã³ã«èšå®ããå Žåãå®å
šãªãã¬ãã·ã¥ã¯äžèŠã§ãã |
説æ
ãªãã¬ãã·ã¥ã¯ãALTER CACHE GROUP...SET AUTOREFRESH STATE
ãå®è¡ããçŽåŸã«ã¯å®è¡ãããŸããããã®æã§ã¯ãAUTOREFRESH
ã®ç¶æ
ãå€æŽããã ãã§ããALTER CACHE GROUP
æãå«ããã©ã³ã¶ã¯ã·ã§ã³ãã³ããããããšãAUTOREFRESH
ã®ã¹ã±ãžã¥ãŒã«ã®ããã«ãã£ãã·ã¥ã»ãšãŒãžã§ã³ãã«ã¯ããã«éç¥ãããŸãããã³ãããã¯ãªãã¬ãã·ã¥ã®å®äºãåŸ
ããã«å®è¡ãããŸããèªåãªãã¬ãã·ã¥æäœã®ã¹ã±ãžã¥ãŒãªã³ã°ã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®äžéšã§ããããªãã¬ãã·ã¥èªäœã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®äžéšã§ã¯ãããŸããã
ALTER CACHE GROUP... SET AUTOREFRESH STATE OFF
æãå®è¡ããçŸåšå®è¡äžã®èªåãªãã¬ãã·ã¥æäœãããå Žåã次ã®ãããªçµæã«ãªããŸãã
LockWait
ã®æééã0ã®å ŽåãALTER
æã¯ããã¯ã®ã¿ã€ã ã¢ãŠãã»ãšã©ãŒã§å€±æããŸãã
LockWait
ã®æééã0以å€ã®å ŽåãçŸåšã®èªåãªãã¬ãã·ã¥ã»ãã©ã³ã¶ã¯ã·ã§ã³ã¯ããŒã«ããã¯ãããALTER
æã®åŠçãç¶è¡ãããŸããããã¯ãåãèªåãªãã¬ãã·ã¥ééãæã€ãã¹ãŠã®ãã£ãã·ã¥ã»ã°ã«ãŒãã«åœ±é¿ããŸãã
AUTOREFRESH