DevAdmin

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. =================================...

Child entry in Oracle without Refference ========================================= Yes, this is possible in Oracle as well PostgreSQL. Adv:- ------ 1. Doesn't Lock the table.So, no outage. 2. It doesn't validate the child entries with parent.So, no performance issues. 3. This option is very useful, when you are handling very big tables. Step 1: ---------- CREATE TABLE PARENT(T INT PRIMARY KEY); Step 2: ---------- Insert sample data SQL> SELECT * FROM PARENT; T ---------- 1 2 3 Step 3: ---------- CREATE TABLE CHILD(T INT); SQL> SELECT * FROM CHILD; T ---------- 1 2 3 4 5 Step 4: ---------- SQL> ALTER TABLE CHILD ADD FOREIGN KEY(T) REFERENCES PARENT(T) ENABLE NOVALIDATE; Step 5: ---------- SQL> SELECT * FROM CHILD; T ---------- 1 2 3 4 5 SQL> INSERT INTO CHILD VALUES(...

Pipelined Table Functions:- [ORACLE] =========================== If you want to return multiple rows to the calling environment, then piplined table functions is prefred. It will increase the dbperformance as well. Ex:- Step 1: ----------- CREATE TABLE EMP(EMPNO INT,ENAME VARCHAR2(10),SAL INT); Step 2: ----------- Insert sample data. Step 3: ----------- Create an object for the row type casting. CREATE OR REPLACE TYPE emp_row AS OBJECT ( empno INT, ename VARCHAR2(20), SAL INT ); Step 4: ----------- Create a Return Type for the pipelined function. CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_row; Step 5: ----------- CREATE OR REPLACE FUNCTION emp_pipe_function RETURN emp_table_type PIPELINED IS BEGIN FOR rec in (select * from emp) LOOP PIPE ROW (emp_row(rec.empno,rec.ename,rec.sal)); END LOOP; RETURN; END; Step 6: ---------- SQL> select * from table(emp_pipe_function); EMPNO ENAME SAL ---------- ----...

Hi , Find the below steps to do pg_dump with load balance in PostgreSQL. REQUIRED TABLES ON REMOTE:- [pg_dump is doing remotely] ============================== CREATE TABLE LOAD_AVG(AVG REAL); INSERT INTO LOAD_AVG VALUES(0.0); REQUIRED SHELL SCRIPT FOR EVERY ONE MINUTE ON REMOTE:- ======================================================= PGBIN="/opt/PostgreSQL/9.0/bin" PORT=5432 USER="postgres" PASSWORD="postgres" DATABASE="postgres" LOAD=$(cat /proc/loadavg|awk -F ' ' '{print $1}') $PGBIN/psql -p $PORT -U $USER -d $DATABASE -c "UPDATE LOAD_AVG SET AVG='$LOAD'"; REQUIRED C PROGRAM ON LOCAL:- ================================ #include #include #include #include #include #include #include "/opt/PostgreSQL/9.0/include/libpq-fe.h" int main(int argc,char *argv[]) { FILE *fp,*remote_machine,*log; char pid[10],cmd[30],con_string[110],host[40],port[4],dbname[20],user[20],password...

Hi , Here you can find the code for finding the DB Growth Percentage in PostgreSQL. Required TABLE:- ------------------- CREATE TABLE PGBAR_PGG.DBSIZES(TIME_STMP TIMESTAMP DEFAULT NOW(),DATNAME VARCHAR(100) NOT NULL,DATSIZE BIGINT NOT NULL,DBGROWTH VARCHAR(100),DBGROWTHPERCENT REAL); Required Function For Trigger:- --------------------------------- CREATE OR REPLACE FUNCTION PGBAR_PGG.FEED_DB_GROWTH() RETURNS TRIGGER LANGUAGE PLPGSQL AS $FUNCTION$ DECLARE CHECK_DB_PRESENCE INT; PREV_DB_SIZE BIGINT; BEGIN SELECT COUNT(*) INTO CHECK_DB_PRESENCE FROM PGBAR_PGG.DBSIZES WHERE LOWER(DATNAME)=NEW.DATNAME; IF(CHECK_DB_PRESENCE=0) THEN RETURN NEW; ELSE SELECT DATSIZE INTO PREV_DB_SIZE FROM PGBAR_PGG.DBSIZES WHERE (CTID,DATNAME) IN (SELECT MAX(CTID),DATNAME FROM PGBAR_PGG.DBSIZES GROUP BY DATNAME HAVING DATNAME=NEW.DATNAME); IF(PREV_DB_SIZE!=NEW.DATSIZE) THEN SELECT PG_SIZE_PRETTY(NEW.DATSIZE::BIGINT-PREV_DB_SIZE) INTO NEW.DBGROWTH; SELECT ((NEW.DATSIZE::REAL-PREV_DB_...

Hi , Here is the small program,generating a circle in c with ASCII in linux environment. Surface:50X50 Radius:15 Formula: (x-CC)2+(y-CC)2=radius2 Here CC=Circle Center x,y are the surface pointers. #include "stdio.h" #include "math.h" int main() { int i,j,rad; rad=15; for (i=0; i { for (j=0; j { if(sqrt((j-(50)/2)*(j-(50)/2) + (i-(50)/2)*(i-(50)/2)) printf("."); else printf(" "); } printf("\n"); } return 0; } Complile:- -------------- gcc -o circ circ.c -lm

Hi, Here is the query in PostgreSQL finds the list of tables and it's related sequences. postgres=# SELECT TAB_SEQ_LIST.TABLE,TAB_SEQ_LIST.attname AS Columnname,TAB_SEQ_LIST.nspname AS Schemaname,case when position((nspname||'.')::varchar in TAB_SEQ_LIST.sequence_def)>0 then TAB_SEQ_LIST.sequence_def when position('.'::varchar in TAB_SEQ_LIST.sequence_def)>0 then TAB_SEQ_LIST.sequence_def when position('.'::varchar in TAB_SEQ_LIST.sequence_def)=0 and length(TAB_SEQ_LIST.sequence_def)>=1 then 'public.'||sequence_def else (select nspname from pg_class,pg_namespace where relkind='S' and relnamespace=pg_namespace.oid and relname=TAB_SEQ_LIST.sequence_def and pg_namespace.nspname=TAB_SEQ_LIST.nspname)::text||'.'||TAB_SEQ_LIST.sequence_def end as sequence_name FROM ( SELECT a.attrelid::regclass as table,attrelid ,attname,nspname,(select trim(both '''' from rtrim(ltrim(regexp_replace(regexp_replace(regexp_repla...

Here you can find a query 2011 year calendar and list of holidays(only one per a week(Maharastar Holidays) ) which has been written in PostgreSQL. QUERY ======== select min(case when extract(month from d.dd)= 1 then 'Jan' when extract(month from d.dd)= 2 then 'Feb' when extract(month from d.dd)=3 then 'Mar' when extract(month from d.dd)= 4 then 'Apr' when extract(month from d.dd)= 5 then 'May' when extract(month from d.dd)= 6 then 'Jun' when extract(month from d.dd)= 7 then 'Jul' when extract(month from d.dd)= 8 then 'Aug' when extract(month from d.dd)=9 then 'Sep' when extract(month from d.dd)= 10 then 'Oct' when extract(month from d.dd)= 11 then 'Nov' when extract(month from d.dd)= 12 then 'Dec' end ) as MONTH,min(case when extract(DOW from d.dd)=0 then (extract(day from d.dd)) end) as SUN, min(case when extract(DOW from d.dd)=1 then (extract(day from d....

If you have n no.of columns in a table, and if you need to select only m no.of columns from that then below is the procedure for you. CREATE OR REPLACE FUNCTION XCEPT(TABNAME TEXT,VARIADIC COLNAMES TEXT[]) RETURNS VOID AS $$ DECLARE CHEK INT:=0; CHEK1 INT:=0; NO_OF_COLS INT:=0; ALLCOLS TEXT:=' '; LC INT:=1; I RECORD; J INT; PR RECORD; QUERY TEXT:=' '; NX TEXT; BEGIN SELECT 1::INT INTO CHEK FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME); SELECT COUNT(*) INTO NO_OF_COLS FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME); IF (CHEK=1) THEN FOR I IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME)) LOOP FOR J IN 1..NO_OF_COLS LOOP IF (LOWER(COLNAMES[J])::TEXT=LOWER(I.COLUMN_NAME)) THEN CHEK1:=1; END IF; END LOOP; IF(CHEK1=0) THEN ALLCOLS:=ALLCOLS||I.COLUMN_NAME||','; END IF; CHEK1:=0; END LOOP; RAISE NOTICE '%','SELECT '||TRIM(ALLCOLS,',')|...

ROLLUP ======= Extension of Group By Clause, which Provide super aggregate rows. Ex:- ----- Query for sum of salaries by department and also display the total salary of all the departments. Using Set Operator ------------------- SQL> SELECT DEPTNO,SUM(SAL) 2 FROM 3 EMP GROUP BY DEPTNO 4 UNION 5 SELECT NULL,SUM(SAL) 6 FROM 7 EMP; DEPTNO SUM(SAL) ---------- ---------- 10 7450 20 10875 30 9400 50 1300 29025 Using Inline Views -------------------- SQL> SELECT DEPTNO,SUM(SAL) DEPTSAL,A.TOTALSAL TOTALSALARY 2 FROM 3 EMP,(SELECT SUM(SAL) TOTALSAL 4 FROM 5 EMP) A 6 GROUP BY DEPTNO,A.TOTALSAL; DEPTNO DEPTSAL TOTALSALARY ---------- ---------- ----------- 10 7450 29025 20 10875 30 9400 50 1300 Using Rollup ------------- SQL> SELECT DEPTNO,SUM(SAL) 2 FROM 3 EMP 4 GROUP BY ROLLUP(DEPTNO); DEPTNO SUM(SAL) ---------- ---------- 10 7450 20 10875 30 ...