2,209
社区成员
发帖
与我相关
我的任务
分享达梦数据库having和order by中的字段提示"引用列未找到"
达梦数据库having和order by中的字段提示"引用列未找到"
sql是类似
select xx , sum (yy) as yyy
from xxx
group by xx
having yyy > 0
为什么having yyy > 0 会提示 "引用列未找到"
明明mysql是可以这么写的 , 不想写成 having sum(yy) > 0
然后我感觉是配置项问题,但是这里面配置项太多了,光是
#query 这个类别下面的就一大堆 , 也不知道是哪个 , 网上搜到个
SUBQ_EXP_CVT_FLAG , 改为0了也没用
USE_PLN_POOL = 1 #Query Plan Reuse Mode, 0: Forbidden; 1:strictly reuse, 2:parsing reuse, 3:mixed parsing reuse
DYN_SQL_CAN_CACHE = 1 #Dynamic SQL cache mode. 0: Forbidden; 1: Allowed if the USE_PLN_POOL is non-zero;
VPD_CAN_CACHE = 0 #VPD SQL cache mode. 0: Forbidden; 1: Allowed if the USE_PLN_POOL is non-zero;
RS_CAN_CACHE = 0 #Resultset cache mode. 0: Forbidden; 1: Allowed only if the USE_PLN_POOL is non-zero;
RS_CACHE_TABLES = #Tables allowed to enable result set cache
RS_CACHE_MIN_TIME = 0 #Least time for resultset to be cached
RS_BDTA_FLAG = 0 #Resultset mode. 0: row; 2: bdta;
RS_BDTA_BUF_SIZE = 32 #Maximum size of message in Kilobytes for BDTA cursor, it's valid only if RS_BDTA_FLAG is set to 2
RS_TUPLE_NUM_LIMIT = 2000 #Maximum number for resultset to be cached
RESULT_SET_LIMIT = 10000 #Maximum Number Of cached Resultsets
RESULT_SET_FOR_QUERY = 0 #Whether to generate result set for non-query statement
SESSION_RESULT_SET_LIMIT = 10000 #Maximum number of cached result sets for each session, 0 means unlimited
BUILD_FORWARD_RS = 0 #Whether to generate result set for forward only cursor
MAX_OPT_N_TABLES = 6 #Maximum Number Of Tables For Query Optimization
MAX_N_GRP_PUSH_DOWN = 5 #Maximum Number Of Rels For Group push down Optimization
CNNTB_MAX_LEVEL = 20000 #Maximum Level Of Hierarchical Query
CTE_MAXRECURSION = 100 #Maximum recursive Level Of Common Expression Table
CTE_OPT_FLAG = 1 #Optimize recursive with, 0: false, 1: convert refed subquery to invocation
BATCH_PARAM_OPT = 0 #optimize flag for DML with batch binded params
CLT_CONST_TO_PARAM = 0 #Whether to convert constant to parameter
LIKE_OPT_FLAG = 127 #the optimized flag of LIKE expression
FILTER_PUSH_DOWN = 2 #whether push down filter to base table
USE_MCLCT = 2 #mclct use flag for replace mgat
PHF_NTTS_OPT = 1 #phf ntts opt flag
MPP_MOTION_SYNC = 200 #mpp motion sync check number
UPD_DEL_OPT = 2 #update&delete opt flag, 0: false, 1: opt, 2: opt & ntts opt
ENABLE_INJECT_HINT = 0 #enable inject hint
FETCH_PACKAGE_SIZE = 512 #command fetch package size
UPD_QRY_LOCK_MODE = 0 #lock mode of FOR UPDATE query
ENABLE_DIST_IN_SUBQUERY_OPT = 0 #Whether to enable in-subquery optimization
MAX_OPT_N_OR_BEXPS = 7 #maximum number of OR bool expressions for query optimization
USE_HAGR_FLAG = 0 #Whether to use HAGR operator when can't use SAGR operator
DTABLE_PULLUP_FLAG = 1 #the flag of pulling up derived table
VIEW_PULLUP_FLAG = 34 #the flag of pulling up view
GROUP_OPT_FLAG = 60 #the flag of opt group
FROM_OPT_FLAG = 0 #the flag of opt from
HAGR_PARALLEL_OPT_FLAG = 4 #the flag of opt hagr in mpp or parallel
HAGR_DISTINCT_OPT_FLAG = 2 #the flag of opt hagr distinct in mpp
REFED_EXISTS_OPT_FLAG = 1 #Whether to optimize correlated exists-subquery into non-correlated in-subquery
REFED_OPS_SUBQUERY_OPT_FLAG = 1 #Whether to optimize correlated op all/some/all-subquery into exists-subquery
HASH_PLL_OPT_FLAG = 107 #the flag of cutting partitioned table when used hash join
PARTIAL_JOIN_EVALUATION_FLAG = 1 #Whether to convert join type when upper operator is DISTINCT
USE_FK_REMOVE_TABLES_FLAG = 1 #Whether to remove redundant join by taking advantage of foreign key constraint
USE_FJ_REMOVE_TABLE_FLAG = 1 #Whether to remove redundant join by taking advantage of filter joining
SLCT_ERR_PROCESS_FLAG = 0 #How to handle error when processing single row
MPP_HASH_LR_RATE = 10 #The ratio of left child's cost to right child's cost of hash join in MPP environment that can influence the execution plan
LPQ_HASH_LR_RATE = 30 #The ratio of left child's cost to right child's cost of hash join in LPQ environment that can influence the execution plan
USE_HTAB = 1 #Whether to use HTAB operator for the whole plan
SEL_ITEM_HTAB_FLAG = 0 #Whether to use HTAB operator for correlated subquery in select items
OR_CVT_HTAB_FLAG = 1 #Whether to use HTAB operator to optimizer or-expression
ENHANCED_SUBQ_MERGING = 3 #Whether to use merging subquery opt
CASE_WHEN_CVT_IFUN = 9 #Flag of converting subquery in case-when expression to IF operator
OR_NBEXP_CVT_CASE_WHEN_FLAG = 0 #Whether to convert or-expression to case-when expression
NONCONST_OR_CVT_IN_LST_FLAG = 0 #Whether to convert nonconst or-expression to in lst expression
OUTER_CVT_INNER_PULL_UP_COND_FLAG = 11 #Whether to pull up join condition when outer join converts to inner join
OPT_OR_FOR_HUGE_TABLE_FLAG = 0 #Whether to use HFSEK to optimize or-expression for HUGE table
ORDER_BY_NULLS_FLAG = 0 #Whether to place NULL values to the end of the result set when in ascending order
SUBQ_CVT_SPL_FLAG = 1 #Flag of indicating how to convert correlated subquery
ENABLE_RQ_TO_SPL = 1 #Whether to convert correlated subquery to SPOOL
MULTI_IN_CVT_EXISTS = 1 #Whether to convert multi-column-in subquery to exists subquery
PRJT_REPLACE_NPAR = 1 #Whether to replace NPAR tree in NSEL after projection
ENABLE_RQ_TO_INV = 0 #Whether to convert correlated subquery to temporary function
SUBQ_EXP_CVT_FLAG = 0 #whether convert refered subquery exp to non-refered subquery exp
USE_REFER_TAB_ONLY = 0 #Whether to pull down correlated table only when dealing with correlated subquery
REFED_SUBQ_CROSS_FLAG = 1 #Whether to replace hash join with cross join for correlated subquery
IN_LIST_AS_JOIN_KEY = 0 #Whether to use in-list expression as join key
OUTER_JOIN_FLATING_FLAG = 1 #Flag of indicating whether outer join will be flattened
TOP_ORDER_OPT_FLAG = 5 #The flag of optimizing the query with the top clause and the order by clause
TOP_ORDER_ESTIMATE_CARD = 300 #The estimated card of leaf node when optimize the query with the top clause and the order by clause
PLACE_GROUP_BY_FLAG = 0 #The flag of optimizing the query with group_by and sfun by clause
TOP_DIS_HASH_FLAG = 1 #Flag of disable hash join in TOP-N query
ENABLE_RQ_TO_NONREF_SPL = 1 #Whether to convert correlated query to non-correlated query
ENABLE_CHOOSE_BY_ESTIMATE_FLAG = 0 #Whether to choose different plan by estimating
OPTIMIZER_MODE = 1 #Optimizer_mode
NEW_MOTION = 1 #New Motion
LDIS_NEW_FOLD_FUN = 0 #ldis use different fold fun with mdis
DYNAMIC_CALC_NODES = 0 #different nodes of npln use different nubmer of calc sizes/threads
OPTIMIZER_MAX_PERM = 7200 #Optimizer_max permutations
ENABLE_INDEX_FILTER = 1 #enable index filter
OPTIMIZER_DYNAMIC_SAMPLING = 0 #Dynamic sampling level
TABLE_STAT_FLAG = 0 #How to use stat of table
AUTO_STAT_OBJ = 0 #Flag of automatically collecting statistics and recording DML changing rows
MONITOR_MODIFICATIONS = 0 #Flag of monitor statistics and recording DML modifications
MON_CHECK_INTERVAL = 3600 #Server flush monitor modifications data to disk interval
NONREFED_SUBQUERY_AS_CONST = 1 #Whether to convert non-correlated subquery to const
HASH_CMP_OPT_FLAG = 0 #Flag of operators that enable optimization with static hash table
OUTER_OPT_NLO_FLAG = 0 #Flag of enable index join for those whose right child is not base table
DISTINCT_USE_INDEX_SKIP = 2 #Distinct whether to use index skip scan
USE_INDEX_SKIP_SCAN = 0 #Whether to use index skip scan
INDEX_SKIP_SCAN_RATE = 0.0025 #Rate in index skip scan
SPEED_SEMI_JOIN_PLAN = 9 #Flag of speeding up the generating process of semi join plan
COMPLEX_VIEW_MERGING = 2 #Flag of merging complex view into query without complex view
HLSM_FLAG = 1 #Choose one method to realize hlsm operator
DEL_HP_OPT_FLAG = 0 #Optimize delete for horization partition table
OPTIMIZER_OR_NBEXP = 29 #Flag of or-expression optimization method
NPLN_OR_MAX_NODE = 20 #Max number of or-expression on join condition
CNNTB_OPT_FLAG = 193 #Optimize hierarchical query
ADAPTIVE_NPLN_FLAG = 3 #Adaptive npln
MULTI_UPD_OPT_FLAG = 1 #Optimize multi column update
MULTI_UPD_MAX_COL_NUM = 128 #Max value of column counts when optimize multi column update
ENHANCE_BIND_PEEKING = 0 #Enhanced bind peeking
NBEXP_OPT_FLAG = 7 #Whether to enable optimization for bool expressions
HAGR_HASH_ALGORITHM_FLAG = 0 #HAGR hash algorithm choice
DIST_HASH_ALGORITHM_FLAG = 0 #Distinct hash algorithm choice
UNPIVOT_OPT_FLAG = 0 #Optimize UNPIVOT operator
VIEW_FILTER_MERGING = 138 #Flag of merging view filter
ENABLE_PARTITION_WISE_OPT = 1 #whether enable partition-wise optimization
OPT_MEM_CHECK = 0 #reduce search space when out of memory
ENABLE_JOIN_FACTORIZATION = 1 #Whether to enable join factorization
EXPLAIN_SHOW_FACTOR = 1 #factor of explain
ERROR_COMPATIBLE_FLAG = 0 #enable/disable specified errors to be compatible with previous version
ENABLE_NEST_LOOP_JOIN_CACHE = 0 #whether enable cache temporary result of nest loop join child
ENABLE_TABLE_EXP_REF_FLAG = 1 #Whether allow table expression to reference brother tables
BIND_PARAM_OPT_FLAG = 3 #flag of optimizer bind parameter
VIEW_OPT_FLAG = 1 #flag of optimize view
SORT_ADAPTIVE_FLAG = 0 #sort buf adaptive
DPC_OPT_FLAG = 131071 #optimizer control for DPC
DPC_SYNC_STEP = 16 #dpc motion sync check step
DPC_SYNC_TOTAL = 0 #dpc motion sync check total
XBOX_DUMP_THRESHOLD = 0 #The xbox_sys mem used threshold of dump xbox_msg
STMT_XBOX_REUSE = 1 #Xbox resuse flag on statement
XBOX_SHORT_MSG_SIZE = 1024 #The xbox_sys short message threshold of dump xbox_msg
MAX_HEAP_SIZE = 0 #Maximum heap size in megabyte allowed to use during analysis phase
PLAN_OP_FLAG = 0 #flag of disabled plan operator
DUAL_ENABLE_SELECT = 1 #Enable select dual/sysdual/sysdual2 in mount status. 0: no, 1: yes.
LOAD_BINDED_PLN = 0 #Whether to load binded plan
LIKE_PATTERN_NUM = 300 #The maximum length of like pattern-matching
FORALL_OPT = 1 #Whether to optimize FORALL statements
HASH_OPT_FLAG = 1 #Flag of operators that enable optimization with hash table
SFUN_PUSH_DOWN_FLAG = 1 #sfun push down flag
SKIP_CORRUPT_PAGE = 0 #Policy for dealing with corrupt pages, 0: raise an error, 1: skip them
AUTO_GEN_PLAN = 0 #Upper limit of iterative rounds for parameter iterative optimization; 0 means never try
MAX_SCAN_PAGES = 32 #Maximum scan pages per data fill
XBOX_SPACE_LIMIT = 0 #Disk space limit of one xbox's all dump files
XBOXS_SPACE_LIMIT = 0 #Disk space limit of all xbox's dump files
BF_SIZE = 0 #The size of bloom filter in Megabytes; 0:internal default size, other value:user-specified size
BF_OPT_FLAG = 0 #Whether enable Bloom-filter Optimization
...全文
请发表友善的回复…
发表回复
