Skip to main content

Posts

Showing posts from 2011

Seq scan vs Index Scan

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;
QUERY PLAN
----------------------…

:: Rownum in postgresql ::

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 T,NEXTVAL('ROWNUMSEQ') AS ROWNUM FROM TES…

:: CSV files in Oracle ::

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,ADAMS,CLERK,7788,23-MAY…

:: How Commit Works in PL SQL Functions/Triggers ::

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 for inserting

the data.

SQL> VAR…

:: Fun With Oracle ::

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<=LENGTH('&&STR');

::ksdwrt in Oracle and workarround in PostgreSQL::

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

::Novalidate in Oracle, Not Valid 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(10);
INSERT INTO CHILD VALUES(10)
*
ERROR at line 1:
OR…

::Pipelined in Oracle as well in PostgreSQL::

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
---------- -------------------- ----------
1 Dinesh …

::Pg_dump With Load Balance::

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[20],pg_dump_cmd[1000];

PostgreSQL Database Growth

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_SIZE::REAL)/PREV_DB_SIZ…

Circle in C with ASCII

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<50; i++)
{
for (j=0; j<50; j++)
{
if(sqrt((j-(50)/2)*(j-(50)/2) + (i-(50)/2)*(i-(50)/2))<=rad)
printf(".");
else
printf(" ");
}
printf("\n");
}
return 0;
}

Complile:-
--------------
gcc -o circ circ.c -lm

Table related sequences

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_replace(ca…

Calendar Query in PostgreSQL

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.dd))
end) as MON,
min(case when ex…

Excepting One or More Fields Of A Table In Postgres

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,',')|| ' FROM '||TABNAME||&…

ROLLUP IN ORACLE

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 9400
50 1300
29025

SQL> SELECT DECODE(DEPTNO,…