PostgreSQL Oracle 兼容性之存儲過程

?在oracle中，函數(shù)和存儲過程是經(jīng)常使用到的，并且有所區(qū)別；而postgresql中函數(shù)和存儲過程都是相同定義的。

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴，公司提供的服務(wù)項目有：申請域名、網(wǎng)站空間、營銷軟件、網(wǎng)站建設(shè)、龍陵網(wǎng)站維護、網(wǎng)站推廣。

?1.定義：定義存儲過程的關(guān)鍵字為procedure。

?2.創(chuàng)建存儲過程

例：

（無參數(shù)）

（有參有返）

總結(jié) ：

?1.創(chuàng)建存儲過程的關(guān)鍵字為procedure。

?2.傳參列表中的參數(shù)可以用in，out，in out修飾，參數(shù)類型一定不能寫大小。列表中可以有多個輸入輸出參數(shù)。

?3.存儲過程中定義的參數(shù)列表不需要用declare聲明，聲明參數(shù)類型時需要寫大小的一定要帶上大小。

?4.as可以用is替換。

?5.調(diào)用帶輸出參數(shù)的過程必須要聲明變量來接收輸出參數(shù)值。

?6.執(zhí)行存儲過程有兩種方式，一種是使用execute，另一種是用begin和end包住。

?1.定義：定義函數(shù)的關(guān)鍵字為function。

?2.創(chuàng)建函數(shù)

總結(jié) ：

?1.定義函數(shù)的關(guān)鍵字為function 。

?2.必須有返回值，且聲明返回值類型時不需要加大小。

?3.函數(shù)中定義的參數(shù)列表不需要用declare聲明，聲明參數(shù)類型時需要寫大小的一定要帶上大小。

?4.as可以用is替換。

? 5.執(zhí)行存儲過程有兩種方式，一種是使用select，另一種是用begin和end包住。

不同點：

?1.存儲過程定義關(guān)鍵字用procedure，函數(shù)定義用function。

?2.存儲過程中不能用return返回值，但函數(shù)中可以，而且函數(shù)中必須有return子句。

?3.執(zhí)行方式略有不同，存儲過程的執(zhí)行方式有兩種（1.使用execute2.使用begin和end），函數(shù)除了存儲過程的兩種方式外，還可以當(dāng)做表達式使用，例如放在select中（select f1() form dual;）。

?postgresql則將函數(shù)和存儲過程合為一體，不再明確區(qū)分存儲過程與函數(shù)。

?1.定義：定義函數(shù)（存儲過程 ）的關(guān)鍵字為function。

?2.創(chuàng)建

例：

?1.必須有有returns（注意是returns不是return）子句，無返回值時returns viod

?2.執(zhí)行時，有返回值用select，無返回值時用perform

?3.必須指定語言LANGUAGE

PostgreSQL 數(shù)據(jù)類型介紹（五）OID的理解

那oid在哪兒？到底為什么會出現(xiàn)這種情況 ？

來看看postgres官網(wǎng)對 oid的介紹：

根據(jù)stackoverflow的高票用戶的回答：

*OIDs basically give you a built-in, globally unique id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

In my experience, the feature is generally unused in most postgres-backed applications (probably in part because they're non-standard), and their use is essentially deprecated :

In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that require OIDs for a particular table should specify WITH OIDS when creating the table. This variable can be enabled for compatibility with old applications that do not follow this behavior.

大意是你要是有個表沒有用主鍵，這時候可以把oid充當(dāng)為主鍵使用，當(dāng)然這是沒辦法的辦法。

總結(jié)： oid是給內(nèi)部表做標(biāo)識用的，不推薦使用。 建議將 default_with_oids 設(shè)置為off。 建表的時候，如果想使用主鍵，請自行建立。oid本身大小固定的，萬一 行數(shù)超過了oid 的最大限制數(shù)（4 byte int），那就無法插入新行了。

postgresql 查看表建立哪些索引

在數(shù)據(jù)庫運維工作中，經(jīng)常會有數(shù)據(jù)目錄使用率較高需要調(diào)整的情況，通常會給數(shù)據(jù)庫建立多個表空間，

并分別位于不同的盤上，這時需要做的工作就是調(diào)整庫中現(xiàn)有表和索引的表空間，下面簡單總結(jié)下這塊維護

工作的內(nèi)容，以下都是基于 PostgreSQL 9.0.1 做的測試。

一 查詢某個表所在表空間的簡單方法

PostgreSQL 提供類似" \ "命令很方便得到相關(guān)信息，命令如下：

skytf= \d test_2

Table "skytf.test_2"

Column | Type | Modifiers

--------+-----------------------+-----------

id | integer |

obj_id | integer | not null

name | character varying(64) |

Indexes:

"idx_hash_name" hash (name)

"idx_test_2" btree (id, obj_id)

Tablespace: "tbs_skytf_idx"

備注：如果這個表的表空間為當(dāng)前數(shù)據(jù)庫的默認表空間，那么上面則不會顯示 Tablespace 信息，

相反，則會顯示這張有的表空間，例如上面的表 test_2 的表空間為 tbs_skytf_idx，而

表空間 "tbs_skytf_idx" 不是數(shù)據(jù)庫 skytf 的默認表空間， 那么如何查詢數(shù)據(jù)庫的默認

表空間呢，可以通過以下命令查詢。

--1.1 查詢數(shù)據(jù)庫的默認表空間

skytf= select datname,dattablespace from pg_database where datname='skytf';

datname | dattablespace

---------+---------------

skytf | 14203070

(1 row)

skytf= select oid,spcname from pg_tablespace where oid=14203070;

oid | spcname

----------+-----------

14203070 | tbs_skytf

(1 row)

備注：通過以上查出數(shù)據(jù)庫 skytf 的默認表空間為 tbs_skytf。

二 批量查詢數(shù)據(jù)庫表和索引的表空間

--2.1 查詢表和索引所在的表空間

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname

from pg_class a, pg_tablespace tb

where a.reltablespace = tb.oid

and a.relkind in ('r', 'i')

order by a.relpages desc;

備注：上面只取了部分結(jié)果，這個查詢能夠查詢表和索引所處的表空間，但是有一點需要注意，這個查詢

僅顯示表空間不是數(shù)據(jù)庫默認表空間的數(shù)據(jù)庫對像，而我們通常需要查出位于數(shù)據(jù)庫默認表空間的

對像，顯然上面的查詢不是我們想要的，接下來看另一個查詢。

--2.2 查詢位于默認數(shù)據(jù)庫表空間的對像

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner

from pg_class a

where a.relkind in ('r', 'i')

and reltablespace='0'

order by a.relpages desc;

備注：這個查詢加入限制條件 reltablespace='0'，即可查找出位于當(dāng)前數(shù)據(jù)庫默認表空間的

數(shù)據(jù)庫表和索引。 通常這才是我們想要的結(jié)果，接下來可以把部分表轉(zhuǎn)移到其它表空間上去，轉(zhuǎn)移

的方法可以用 "ALTER TABLE move tablespace "或者重建索引移表空間等方法，這里不詳細介紹。

--2.3 查詢在某個表空間上的對像

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner

from pg_class a, pg_tablespace tb

where a.relkind in ('r', 'i')

and a.reltablespace=tb.oid

and tb.spcname='tablespace_name'

order by a.relpages desc;

--2.4 手冊上對于 pgclass 視圖的 reltablespace 字段解釋

The tablespace in which this relation is stored. If zero, the database is default tablespace is

implied. (Not meaningful if the relation has no on-disk file.)

postgresqL 的Btree 與gin索引

一．gin索引需要安裝第三方插件

yum install postgresql96-contrib -- 安裝插件

find / -name extension --可以看到btree_gin.control存在

create extension btree_gin; -- 添加索引

二.測試數(shù)據(jù)基本屬性介紹

總共使用3個表，表結(jié)構(gòu)和數(shù)據(jù)量完全一致。

表數(shù)據(jù)量：10522369

表字段：id ,basic_acc_no,id_card,name,sex,telephone,json_t

1）索引的配置情況：

basic_account_info_al -- btree

basic_account_info_al2 --gin

basic_account_info_al3 -- btree multi

basic_account_info_al 單列索引 id，basic_acc_no，name,json_t

basic_account_info_al2 gin索引 (id,basic_acc_no,id_card,name),(json_t)

basic_account_info_al3 復(fù)合索引 (id,basic_acc_no),(name,id)(json_t,id)

basic_account_info_al 表達式索引 (json_t-id)

basic_account_info_al2表達式索引 ((json_t-'id'))

三．測試結(jié)果

1.唯一值屬性：索引字段都是唯一 id，basic_acc_no

查詢語句

explain analyse select * from basic_account_info_al2 where id = 29699221 ;

explain analyse select * from basic_account_info_al where id = 29699221 ;

explain analyse select * from basic_account_info_al3 where id = 29699221 ;

explain analyse select * from basic_account_info_al2 where basic_acc_no = 'XFK2990134' ;

explain analyse select * from basic_account_info_al where basic_acc_no = 'XFK2990134' ;

explain analyse select * from basic_account_info_al3 where basic_acc_no = 'XFK2990134' ;

explain analyse select * from basic_account_info_al2 where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select * from basic_account_info_al where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select * from basic_account_info_al3 where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select * from basic_account_info_al2 where id = 29699221 and basic_acc_no = 'XFK9780134' ;

explain analyse select * from basic_account_info_al where id = 29699221 and basic_acc_no = 'XFK9780134' ;

explain analyse select * from basic_account_info_al3 where id = 29699221 and basic_acc_no = 'XFK9780134' ;

2.重復(fù)值屬性： name是有重復(fù)值的。

explain analyse select * from basic_account_info_al where name ='張燕洪';

explain analyse select * from basic_account_info_al3 where name ='張燕洪';

explain analyse select *from basic_account_info_al2 where name ='張燕洪';

explain analyse select * from basic_account_info_al2 where id = 24426014 and name = '周楊' ;

explain analyse select * from basic_account_info_al where id = 24426014 and name = '周楊' ;

explain analyse select * from basic_account_info_al3 where id = 24426014 and name = '周楊' ;

explain analyse select * from basic_account_info_al2 where name = '周楊' and id = 24426014 ;

explain analyse select * from basic_account_info_al where name = '周楊' and id = 24426014 ;

explain analyse select * from basic_account_info_al3 where name = '周楊' and id = 24426014 ;

3.jsonb屬性

create index inx_gin_json on basic_account_info_al2 using gin (json_t);

create index inx_btree_json on basic_account_info_al (json_t);

create index inx_btree_2_js on basic_account_info_al3 (json_t,id );

explain analyse select * from basic_account_info_al where json_t ='{"id": 21782879, "sex": 0, "name": "劉樂典"}';

explain analyse select * from basic_account_info_al2 where json_t ='{"id": 21782879, "sex": 0, "name": "劉樂典"}';

explain analyse select * from basic_account_info_al3 where json_t ='{"id": 21782879, "sex": 0, "name": "劉樂典"}';

explain analyse select * from basic_account_info_al WHERE json_t @ '{"id": 21782879}';

explain analyse select * from basic_account_info_al2 WHERE json_t @ '{"id": 21782879}';

explain analyse select * from basic_account_info_al3 WHERE json_t @ '{"id": 21782879}';

explain analyse select * from basic_account_info_al where (json_t-id)= '24426014' ;

explain analyse select * from basic_account_info_al2 where (json_t-id)= '24426014' ;

explain analyse select * from basic_account_info_al3 where (json_t-id)='24426014' ;

4.jsonb表達式索引

查詢條件 表名 查詢時使用的索引名稱 查詢時間（5次平均）/ms

(json_t-id)= '24426014' basic_account_info_al inx_json_id 0.040

basic_account_info_al3 inx_json_id_2 0.039

explain analyse select * from basic_account_info_al where (json_t-id)= '24426014' ;

explain analyse select * from basic_account_info_al2 where (json_t-id)= '24426014' ;

四．獲相同的結(jié)果使用Jsonb與btree對比

jsonb支持兩種特有的GIN索引jsonb_ops和jsonb_path_ops。 jsonb_ops調(diào)用gin_extract_jsonb函數(shù)生成key，每個鍵和值都作為一個單獨的索引項。而jsonb_path_ops使用函數(shù)gin_extract_jsonb_path抽取：只為每個值創(chuàng)建一個索引項。{“foo”:{“bar”,”baz”}}, jsonb_ops生成3個索引項，jsonb_path_ops由foo,bar,baz組合一個hash值作為一個索引項。jsonb_path_ops索引要比jsonb_ops的小很多，性能上也會有所提升。

create index inx_gin_patn_json ON public.basic_account_info_al4 USING gin (json_t jsonb_path_ops); -- jsonb_path_ops

create index inx_gin_json on basic_account_info_al2 using gin (json_t); --jsonb_ops

1.精確查詢

2.范圍查詢

下表顯示了gin索引對于jsonb數(shù)據(jù)類型可使用的操作符。

名稱 索引數(shù)據(jù)類型 可索引操作符

jsonb_ops jsonb ? ? ?| @

json_path_ops jsonb @

注：? ? ?| 索引key是否包含在jsonb中

對于范圍(json_t-'id') 20000079，這樣的條件 gin索引不起作用， 這里采用表達式索引方式，查詢條件的兩邊數(shù)據(jù)類型相同才可以做索引查詢，否則全表掃描。

CREATE INDEX inx_json_id_2 ON public.basic_account_info_al2 USING btree (((json_t-'id')::int));

總結(jié)： 當(dāng)僅有一個條件查詢時，gin索引與btree索引的性能差異不大，但有多個條件查詢時，gin,btree單

列索引沒有btree復(fù)合索引的性能高。jsonb是以二進制格式存儲且不保證鍵的順序?？梢允褂帽磉_式索引指定到j(luò)sonb的具體鍵值，但是如果不能提前知道查詢數(shù)據(jù)中的哪個鍵，確定定義GIN索引和使用@（或者其他有利于索引的操作符）查詢。

五．jsonb添加數(shù)據(jù)屬性

例如：

{"id":20000241,"name":"陳敏","sex":1} - {"age":"18","id":20000241,"name":"陳敏","sex":1}

一旦創(chuàng)建了索引，就不需要進一步的干預(yù)：當(dāng)表被修改時，系統(tǒng)將更新索引，當(dāng)執(zhí)行計劃認為使用索引比順序的表掃描更有效的時候，它會使用索引。

UPDATE basic_account_info_al4 SET json_t = json_t || '{"age":"18"}'::jsonb; -- 更新語句

gin索引名稱 索引方式 修改前大小 修改后大小 帶索引更新時間

inx_gin_patn_json jsonb_path_ops 574M 615M 643561.004 ms

inx_gin_json jsonb_ops 665M 695M 時間過長超過1h

jsonb_ops方式建立的索引大量更新時，執(zhí)行時間太長。當(dāng)插入更新時gin索引比較慢，如果要向一張大表中插入大量數(shù)據(jù)時，最好先把gin索引刪除，插入數(shù)據(jù)后再重建索引。

當(dāng)json_t為{"id":20000241,"name":"陳敏","sex":1} 數(shù)據(jù)量為10522369 創(chuàng)建gin索引時間

130372.955 ms

當(dāng)json_t為{"age":"18","id":20000241,"name":"陳敏","sex":1} 數(shù)據(jù)量為10522369 創(chuàng)建gin索引時間

148971.011 ms

新聞名稱：postgresql總結(jié)的簡單介紹
文章鏈接：http://m.rwnh.cn/article20/dsdhpco.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)網(wǎng)站制作、搜索引擎優(yōu)化、、建站公司、做網(wǎng)站、微信小程序

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)