Oracle/SQL Query 썸네일형 리스트형 스크랩 - /*+ BYPASS_UJVC */ and updatable views 업데이트 /*+ BYPASS_UJVC */ and updatable views High volume UPDATE statements with SET sub-queries can be one of hardest SQLs to tune. Consider the following: UPDATE my_table a SET col1 = (SELECT col1 FROM my_temp_table WHERE key = a.key) WHERE col3 = 'BLORT' This SQL will probably perform a full table scan on my_table (not such a problem) and then for every row returned, it will perform an indexed SELEC.. 더보기 이어지지 않는 seq 찾아내기 with tkoo as ( select 1 a from dual union all select 2 from dual union all select 3 from dual union all select 4 from dual union all select 5 from dual union all select 6 from dual union all select 8 from dual union all select 9 from dual union all select 10 from dual ) with koo as ( select rownum cd , a - rownum cd3, a from tkoo ) select min(a), max(a) from koo group by cd3 order by 1; 더보기 유용한 오라클 튜닝 scripts [b][color=BLUE]SGA의 shared pool 의 hitratio를 연산해주는 SQL[/color][/b] /* ** Shared_pool의 hit ratio보는 스크립트.. ** ** 이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다. ** 만일 적게 할당되면 유저의 접속이 많아질수록 throughput에 큰 영향을 준다. ** hit ratio는 95% 이상을 유지시켜야 한다. ** */ select sum(gets) "Gets", sum(getmisses) "Misses", (1-(sum(getmisses) / (sum(gets)+sum(getmisses))))*100 "HitRate" from v$rowcache; [b][color=BLUE]SGA에.. 더보기 Oracle Regular Expression Table 4-1 SQL Regular Expression Functions and Conditions SQL Element Category Description REGEXP_LIKE Condition Searches a character column for a pattern. Use this function in the WHERE clause of a query to return rows matching a regular expression. The condition is also valid in a constraint or as a PL/SQL function returning a boolean. The following WHERE clause filters employees with a first .. 더보기 The UNION [ALL], INTERSECT, MINUS Operators Examples The following query is valid:SELECT 3 FROM DUAL INTERSECT SELECT 3f FROM DUAL; This is implicitly converted to the following compound query:SELECT TO_BINARY_FLOAT(3) FROM DUAL INTERSECT SELECT 3f FROM DUAL; The following query returns an error:SELECT '3' FROM DUAL INTERSECT SELECT 3f FROM DUAL; union all 은 다붙이기union 은 합집합 distinct 문을 쓴 결과와 같다. 중복없도록 출력intersect 은 교집합으로 중복이 되는 rows만 출력mi.. 더보기 이전 1 ··· 3 4 5 6 다음