본문 바로가기

분류 전체보기

천정현 증명사진 더보기
Improving Query Performance with the SQL WITH Clause Improving Query Performance with the SQL WITH Clause Oracle9i significantly enhances both the functionality and performance of SQL to address the requirements of business intelligence queries. The SELECT statement’s WITH clause, introduced in Oracle9i, provides powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries. Co.. 더보기
LEVEL 함수 select ename, connect_by_isleaf isleaf, level, sys_connect_by_path(ename,'/') path from emp start with ename = 'KING' CONNECT BY PRIOR EMPNO = MGR; select ename, connect_by_ISCYCLE ISCYCLE, level, sys_connect_by_path(ename,'/') path from emp start with ename = 'KING' CONNECT BY NOCYCLE PRIOR EMPNO = MGR; select ename, connect_by_ROOT ENAME, level, sys_connect_by_path(ename,'/') path from emp sta.. 더보기
Analytic 함수 OVER ( PARTITION BY column_list ORDER BY column_list [ ASC | DESC ] [NULLS | {first | last } ] [ ROWS | RANGE ] [BETWEEN start_point AND end_point ] ) SQL> select empno, ename, sal, 2 avg(sal) over() avg1 3 from emp; EMPNO ENAME SAL AVG1 ---------- ---------- ---------- ---------- 7369 SMITH 800 2073.21429 7499 ALLEN 1600 2073.21429 7521 WARD 1250 2073.21429 7566 JONES 2975 2073.21429 7654 MARTI.. 더보기
SQL*PLUS - SET Statement 명령어 Set sqlplus system settings and defaults. Syntax: SET option value SHO[W] option options: most of these have an abbreviated and a long form e.g. APPINFO or APPI will do the same thing You can get a list of the set options in sqlplus with the command HELP SET APPI[NFO]{ON|OFF|text} Application info for performance monitor (see DBMS_APPLICATION_INFO) ARRAY[SIZE] {15|n} Fetch size (1 to 5000) the n.. 더보기

티스토리툴바