DevAdmin

Query Tuning ------------------- CO-Relations (vs) Joins (vs) Sets ++++++++++++++++++++++++++++++++++ Co-Relations/Joins/Sets are actually using for joint or disjoint collections. 1. Co-Relations sub queries are high cost expensive queries when compare to Joins. 2. However, Co-Relations are using less memory when compared to Joins. 3. Joins are always using some builting algorithms like (Hash join (Small set of rows), Merge Join (Huge set of row + Sort)) which makes the things very faster. Find the below examples -> CREATE TABLE TEST(T INT); -> CREATE TABLE TEST1(T1 INT); -> INSERT INTO TEST VALUES(GENERATE_SERIES(1,1000)); -> INSERT INTO TEST VALUES(GENERATE_SERIES(500,1499)); CO-Relation ----------- postgres=# EXPLAIN SELECT * FROM TEST AS OUT WHERE OUT.T IN (SELECT INN.T1 FROM TEST1 AS INN WHERE INN.T1=OUT.T); QUERY PLAN ------------------------------------------------------------------ Seq Scan on test "out" (c...

Hi, Today, let me discuss some thing about PG Optimizer while taking a better execution plan. Seq Scan (vs) Index Scan ---------------------------------- Both scans having its own pros and cons. Sometimes, seq scan will give you the better execution time and sometimes Index scan. Let's find out.. Step 1:- postgres=# CREATE TABLE TEST(T INT PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE postgres=# INSERT INTO TEST VALUES(GENERATE_SERIES(1,100)); INSERT 0 100 Step 2:- Check the no.of pages occupied by the table "test" from pg_class. postgres=# ANALYZE TEST; ANALYZE postgres=# SELECT RELNAME,RELPAGES FROM PG_CLASS WHERE relname like 'test'; relname | relpages ---------+---------- test | 1 Step 3:- Find the below execution plans. postgres=# EXPLAIN ANALYZE SELECT * FROM TEST WHERE T=10; ...

Hi All, As we know,we can generate rownum with window fuctions also(Rank).In oracle we have "rownum" pseduo column,but we don't in postgresql. Here is the one of the solution for generating rownum in postgresql. In Oracle ----------- SQL> CREATE SEQUENCE ROWNUMSEQ; Sequence created. SQL> SELECT EMPNO,ROWNUMSEQ.NEXTVAL AS "ROWNUM" FROM EMP; EMPNO ROWNUM ---------- ---------- 7369 1 7499 2 7521 3 7566 4 7654 5 7698 6 7782 7 7788 8 7839 9 7844 10 7876 11 7900 12 7902 13 7934 14 In PostgreSQL ---------------- postgres=# CREATE SEQUENCE ROWNUMSEQ; CREATE SEQUENCE postgres=# CREATE TABLE TEST(T INT); CREATE TABLE postgres=# INSERT INTO TEST VALUES(GENERATE_SERIES(1,14)); INSERT 0 14 postgres=# SELECT ...

How to make a csv/flat file in oracle through UTL_FILE +++++++++++++++++++++++++++++++++++++++++++++++++++++++ CSV(comma separated values) are mostly useful in data transformation. I believe these files are mostly useful in DWH. We can do the same through sqlplus features. SQL> set heading off SQL> set feedback off SQL> spool /home/oracle/test/csv.txt SQL> select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from emp; 7369,SMITH,CLERK,7902,17-DEC-80,800,,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30 7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30 7566,JONES,MANAGER,7839,02-APR-81,2975,,20 7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30 7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30 7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10 7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20 7839,KING,PRESIDENT,,17-NOV-81,5000,,10 7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30 7876,AD...

COMMIT in Functions/Triggers. +++++++++++++++++++ It Triggers/functions may be led to bulk associated transactions. This may lead to transaction atomicity failure. Please find the below test case from Oracle. Step 1: --------- SQL> CREATE TABLE COMMIT_TRIG_TEST_TABLE1(T INT); Table created. SQL> CREATE TABLE COMMIT_TRIG_TEST_TABLE2(T INT,CHECK (T NOT IN(0))); Table created. Step 2: --------- CREATE OR REPLACE FUNCTION INSERT_DATA(NUM IN NUMBER) RETURN VARCHAR2 IS BEGIN EXECUTE IMMEDIATE 'INSERT INTO COMMIT_TRIG_TEST_TABLE1 VALUES('||NUM||')'; RETURN 'INSERT OK'; END; Step 3: --------- CREATE OR REPLACE TRIGGER COMMIT_TRIG BEFORE INSERT ON COMMIT_TRIG_TEST_TABLE1 FOR EACH ROW DECLARE BEGIN INSERT INTO COMMIT_TRIG_TEST_TABLE2 VALUES(:NEW.T); END; Step 4: -------- However, we know that if the function having dml operations, then we can't make that function as a part of SQL statement. So, please use the below method ...

Hi, If you run this, you will find one funny frame with the given string ... SELECT SUBSTR('&&STR',ROWNUM,1) ||DECODE(ROWNUM,1,SUBSTR('&&STR',2,LENGTH('&&STR')),LENGTH('&&STR'),' ',LPAD(' ',LENGTH('&STR')-2,' ')) ||DECODE(ROWNUM,LENGTH('&&STR'),SUBSTR(REVERSE('&&STR'),2,LENGTH('&&STR')-2),' ') ||DECODE(ROWNUM,1,' ',SUBSTR('&&STR',-(ROWNUM),1)) FROM EMP,EMP WHERE ROWNUM

dbms_system.ksdwrt ================ In Oracle we do have ksdwrt for writing the customized alters into alert_log file as well trace files. These customized log information is very usefull to DBA while monitoringthe servers. Step 1:- ====== exec dbms_system.ksdwrt(2,CURRENT_DATE||'::NOTIFY:: '||' ****** Disk Space IS Very Less ... 90% Full ******'); PL/SQL procedure successfully completed. Step 2:- ======= SQL> select name,value from v$parameter where name='background_dump_dest'; NAME -------------------------------------------------- VALUE -------------------------------------------------- background_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\DINESH\BDUMP Step 3:- ======= From alertdinesh_log Fri Nov 11 02:33:07 2011 11-NOV-11::NOTIFY:: ****** Disk Space IS Very Less ... 90% Full ****** In PostgreSQL, we don't have that feature. So,please find the below work arround for this in PostgreSQL. =================================...