SQL (21) 썸네일형 리스트형 Recursive CTE Structure [ ;WITH .... AS (... UNION ALL ...) SELECT ] Oracle 에서는 어렵지 않던 것이MSSQL 에서 구현하려면 좀 어려운 것 중의 하나가 Connect by 구문이다. 이 참에 업무에서 사용했던 쿼리를 참고로 정리해 본다. CTE (Common Table Expression) in MSSQL -- Define the CTE expression name and column lists ;WITH BookingFamily( bookingid, bookingparentid, status, recordlocator, bookingdate, lvl ) -- Define the CTE query AS ( -- Anchor member is defined [ Recursive 가 시작하는 시점이라고 생각하면 된다. Start with 처럼 ] SELECT Booki.. CREATE OR REPLACE PROCEDURE EJMIS.Ledger_Migration_Daily CREATE OR REPLACE PROCEDURE EJMIS.Ledger_Migration_Daily IS /****************************************************************************** NAME: Ledger_Migration_Daily PURPOSE: --**************************************** EJODS 로부터 EJMIS 데이타 수집 ******************************** --Select sysdate, trunc(sysdate, 'dd'), to_char(trunc(sysdate, 'dd'), 'YYYYMMDD'), sysdate-1 from dual --****************.. SYS_CONNECT_BY_PATH SYS_CONNECT_BY_PATHSyntax Description of the illustration sys_connect_by_path.gif Purpose SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned byCONNECT BY condition. Both column and char can be any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is.. ANSI Standards http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/ap_standard_sql001.htm ANSI StandardsThe following documents of the American National Standards Institute (ANSI) relate to SQL: ANSI/ISO/IEC 9075-1:2008, Information technology—Database languages—SQL—Part 1: Framework (SQL/Framework) ANSI/ISO/IEC 9075-2:2008, Information technology—Database languages—SQL—Part 2: Foundation (SQL/Found.. SQL:2008 is the sixth revision of the ISO and ANSI standard for the SQL database query language.해 주세요. SQL:2008 is the sixth revision of the ISO and ANSI standard for the SQL database query language. It was formally adopted in July 2008.[1] Contents [hide] 1 Summary 2 Documentation 3 Claims of conformance 4 References [edit]Summary This section requires expansion. The SQL:2008 standard is split into several parts, covering the Framework, the Foundation, the Call-Level Interface, Persistent Stored.. SQL-92 was the third revision of the SQL database query language SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. For all but a few minor incompatibilities, the SQL-89 standard is forwards-compatible with SQL-92. Later revisions of the standard include SQL:1999 (SQL3), SQL:2003, and SQL:2008. [edit]New Features SQL Agent New data types defined: DATE, TIME, TIMESTAMP, INTERVAL, BIT string.. CBO 의 최적화 절차 순서 ■ CBO 의 최적화 절차 순서 Parsing --> Query Transformation --> Estimator Plan Generation --> RowSource --> Generation ■ Optimizer 영향 요소 SQL 문장형태 인덱스, 테이블 구조 통계정보 옵티마이저 모드 DBMS Version 시스템 및 네트워크 상태 사용컬럼, 연산자 형태 힌트 사용 ■ Optimizer 관련 파라미터 설정 Cursor_Sharing : SQL 조건절에 있는 상수값들을 변수로 전환시켜 파싱 DB_FILE_MULTIBLOCK_READ_COUNT : Full Table Scan, Index Fast Full Scan 을 할 때 한번 I/O 에 읽을 블록수를 지정할 수 있고 기본값은 8 OPTIMIZER_.. Cost Based Optimizer Cost Based Optimizer 테이블의 로우수와 블록수 블록당 평균로우수 로우의 평균길이 컬럼별 상수값의 종류 분포도 컬럼내의 Null 값의 수 클러스터링 팩터 인덱스의 깊이 (Depth, Level) 컬럼의 최대, 최소값 리프(Leaf) 블록수 가동시스템의 I/O, CPU 정보 이전 1 2 3 다음