본문 바로가기

Oracle/SQL Query

연속데이터 min max 값 얻어오기. 연속 데이터. 1-4,6,8-11 어디가 빠지고 들어갔는지 select 로 추출. create table t1(c1 number); insert into t1 values(1); insert into t1 values(2); insert into t1 values(3); insert into t1 values(4); insert into t1 values(6); insert into t1 values(7); insert into t1 values(8); insert into t1 values(9); insert into t1 values(10); insert into t1 values(15); insert into t1 values(16); insert into t1 values(17); insert i.. 더보기
External Tables conn /as sysdba; create or replace directory ext as 'c:\external\' grant read on ext to hr; grant wrtie on ext to hr; conn hr/hr -- 예제 : oracle_datapump drop table ext_employees; create table ext_employees organization external ( type oracle_datapump default directory ext location('emp.ext') ) as select * from employees; CREATE TABLE ( ) ORGANIZATION EXTERNAL (TYPE oracle_datapump DEFAULT DIRECT.. 더보기
대용량 DML 발생시키는 PL/SQL CONNECT OLTP/OLTP@ORA10GR2 CREATE OR REPLACE PROCEDURE LOAD_DATA IS v_Cnt NUMBER ; v_value NUMBER; BEGIN FOR i IN 1..1000000 LOOP insert into ORDERS ( ORDER_ID , PRODUCT_ID , CUSTOMER_ID , PURCHASE_DATE , PURCHASE_TIME , PURCHASE_PRICE , SHIPPING_CHARGE, TODAY_SPECIAL_OFFER , SALES_PERSON_ID , PAYMENT_METHOD ) values ( TRIM(TO_CHAR(MOD(i,1000000) + 1,'0000009')), TRIM(TO_CHAR(MOD(i,10) + 1,'0000.. 더보기
MERGE STATEMENT MERGE INTO [table 명] [테이블 별칭] USING [ 대상 table/view ] [ 대상 table/view 별칭 ] ON [ join 조건 ] WHEN MATCHED THEN UPDATE SET col1 = value1 col2 = value2 WHEN NOT MATCHED THEN INSERT ( column 명, , , , ) VALUES ( value1, , , , , , ); 더보기
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.. 더보기

티스토리툴바