본문 바로가기

query

[Oracle] 11g R2 : LISTAGG Function Advanced Aggregates for Analysis This section illustrates the following advanced analytic aggregate functions: LISTAGG Function The LISTAGG function orders data within each group based on the ORDER BY clause and then concatenates the values of the measure column. Its syntax is as follows:LISTAGG ( [, ) WITHIN GROUP (ORDER BY ) expr can be a column, constant, bind variable, or an expression invol.. 더보기
[Oracle] explain plan을 활용한 인덱스 생성 예상시간 explain plan for create index orders_ix01 on orders (customer_id, order_mode) ; select * from table(dbms_xplan.display) ; Plan hash value: 4214405511 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------- | 0 | CREAT.. 더보기
[Oracle] 테이블별 레코드 건수 구하기 SELECT TABLE_NAME , NUM_ROWS , TO_NUMBER( DBMS.XMLGEN('SELECT COUNT(*) FROM ' || TABLE_NAME).EXTRACT('.//text()') ) AS NUM_ROWS2 FROM USER_TABLES WHERE 1=1 ; 출처 : 오라클클럽 URL : http://www.oracleclub.com/article/55486 더보기
[Oracle] 컬럼상 끝 문자 삭제 SELECT RTRIM('123,456,789,', ',') FROM DUAL ; 더보기
[Oracle] 버전별 Group By에서 문자열 합치기 (여러 ROW를 한줄로) WITH tbl (type, name, code) AS ( SELECT 'STRING', 'BBB', '1' FROM dual UNION ALL SELECT 'INTEGER', '222', '1' FROM dual UNION ALL SELECT 'STRING', 'CCC', '2' FROM dual UNION ALL SELECT 'INTEGER', '333', '2' FROM dual UNION ALL SELECT 'STRING', 'AAA', '0' FROM dual UNION ALL SELECT 'STRING', 'DDD', '3' FROM dual UNION ALL SELECT 'INTEGER', '111', '0' FROM dual ) SELECT type , SUBSTR(XMLAgg(XMLEle.. 더보기

티스토리툴바