Tuesday, April 28, 2015

Oracle vs Amazon Redshift – The basic commands

Here are the most often used command on Oracle and their equivalent in Redshift
1. Show Schema
Oracle Way:
1
2
3
4
SELECT
username
FROM
all_users;
Redshift Way:
1
2
SELECT *
FROM pg_namespace;
2. Describe a table and see the field names, types, encoding etc.
Oracle Way:
1
2
Describe SchemName.TableName
Redshift Way:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT DISTINCT n.nspname AS schemaname
,c.relname AS tablename
,a.attname AS COLUMN
,a.attnum AS column_position
,pg_catalog.format_type(a.atttypid, a.atttypmod) AS TYPE
,pg_catalog.format_encoding(a.attencodingtype) AS encoding
,a.attisdistkey AS distkey
,a.attsortkeyord AS sortkey
,a.attnotnull AS notnull
,a.attencodingtype AS compression
,con.conkey AS primary_key_column_ids
,con.contype AS con_type
FROM pg_catalog.pg_namespace n
,pg_catalog.pg_class c
,pg_catalog.pg_attribute a
,pg_constraint con
,pg_catalog.pg_stats stats
WHERE n.oid = c.relnamespace
AND c.oid = a.attrelid
AND a.attnum > 0
AND c.relname NOT LIKE '%pkey'
AND lower(c.relname) = ''
AND n.nspname = ''
AND c.oid = con.conrelid(+)
ORDER BY A.ATTNUM
;
3. Find Disk Usage Per Table 

Oracle Way:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SELECT
owner,
table_name,
TRUNC(SUM(bytes)/1024/1024) Meg
FROM
(
SELECT
segment_name table_name,
owner,
bytes
FROM
dba_segments
WHERE
segment_type = 'TABLE'
UNION ALL
SELECT
i.table_name,
i.owner,
s.bytes
FROM
dba_indexes i,
dba_segments s
WHERE
s.segment_name = i.index_name
AND s.owner        = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT
l.table_name,
l.owner,
s.bytes
FROM
dba_lobs l,
dba_segments s
WHERE
s.segment_name = l.segment_name
AND s.owner        = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT
l.table_name,
l.owner,
s.bytes
FROM
dba_lobs l,
dba_segments s
WHERE
s.segment_name = l.index_name
AND s.owner        = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE
owner IN UPPER('BIC_DDL') -- PUT YOUR SCHEMANAME HERE
GROUP BY
table_name,
owner
ORDER BY
SUM(bytes) DESC
Redshift Way :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT DISTINCT n.nspname AS schemaname
 ,c.relname AS tablename
 ,a.attname AS COLUMN
 ,a.attnum AS column_position
 ,pg_catalog.format_type(a.atttypid, a.atttypmod) AS TYPE
 ,pg_catalog.format_encoding(a.attencodingtype) AS encoding
  ,a.attisdistkey AS distkey
 ,a.attsortkeyord AS sortkey
 ,a.attnotnull AS notnull
 ,a.attencodingtype AS compression
 ,con.conkey AS primary_key_column_ids
 ,con.contype AS con_type
FROM pg_catalog.pg_namespace n
 ,pg_catalog.pg_class c
 ,pg_catalog.pg_attribute a
 ,pg_constraint con
 ,pg_catalog.pg_stats stats
WHERE n.oid = c.relnamespace
 AND c.oid = a.attrelid
 AND a.attnum > 0
 AND c.relname NOT LIKE '%pkey'
 AND lower(c.relname) = ''
 AND n.nspname = ''
 AND c.oid = con.conrelid(+)
ORDER BY A.ATTNUM
;