Timesten - Query Optimizer

About

SQL Engine - Query Optimizer (Query Optimization)

Articles Related

Plan

the showplan shows the query plan

Command> autocommit 0;
Command> showplan;
Command> select * from all_users;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkRangeScan
  TBLNAME:             SYS.USER$
  IXNAME:              USER$.I_USER
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         U.TYPE# = 1

< SYS, 0, 2012-04-19 10:46:45.949667 >
.................
< DI_BISAMPLE, 14, 2012-06-15 19:03:30.569941 >
9 rows found.

Flag

The optprofile ttisql command prints the current optimizer flag settings and join order.

Command> optprofile;

Current Optimizer Settings:
        Scan: 1
        Hash: 1
        Range: 1
        TmpHash: 1
        TmpRange: 1
        TmpTable: 1
        NestedLoop: 1
        MergeJoin: 1
        GenPlan: 0
        TblLock: 1
        RowLock: 1
        Rowid: 1
        FirstRow: 0
        IndexedOr: 1
        PassThrough: 0
        BranchAndBound: 1
        ForceCompile: 0
        CrViewSemCheck: 1
        ShowJoinOrder: 0
        TransparentLoad: 0
        UseBoyerMooreStringSearch: 0
        DynamicLoadEnable: 1
        DynamicLoadErrorMode: 0
        NoRemRowIdOpt: 0
        GlobalProcessing: 0
        GlobalLocalJoin: 0

Current Join Order:
        <>

Documentation / Reference

• Viewing and changing query optimizer plans