Oracle Illustrated


February 21, 2010  8:19 AM

Migrating from 9i to 11g – Pivot and Unpivot

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

This feature is available form Oracle 11g

It is a Data warehouse feature that can be used in PL/SQL programs. It is more useful to represent the data in cross tabular format.

Pivot – Enables to transfer rows to columns. It enables to use aggregate function – multiple rows of input is always consolidated into smaller sets. It enhances the performance rather than using any other way of rows to columns conversion. Also, it reduces network load.

Unpivot – Does a column to row conversion

Pivot

Requirement -

CREATE TABLE ACCOUNT (customer_no number, account_details varchar2(30), amount number, branch varchar2(50))

INSERT INTO ACCOUNT VALUES (1, ‘Savings’, 5000, ‘Orchard’);
INSERT INTO ACCOUNT VALUES (2, ‘Savings’, 10000, ‘Novena’);
INSERT INTO ACCOUNT VALUES (3, ‘Savings’, 60000, ‘Orchard’);
INSERT INTO ACCOUNT VALUES (4, ‘Current’, 75000, ‘Orchard’);
INSERT INTO ACCOUNT VALUES (5, ‘Current’, 30000, ‘Newton’);
INSERT INTO ACCOUNT VALUES (6, ‘Current’, 25000, ‘Novena’);

Lets look at the Data set first -

Customer No Account_details Branch Amount
1 Savings Orchard 5000
2 Savings Novena 10000
3 Savings Orchard 60000
4 Current Orchard 75000
5 Current Newton 30000
6 Current Novena 25000

To be converted as follows – Display branch wise sum of amounts for accounts

Account_details Orchard Novena Newton
Savings 65000 10000
Current 75000 25000 30000

I have had this requirement many many times !!

Lets see how we can achieve this in Oracle 9i-

To achive the same functionality – just close not exact !

To achive the same functionality

Oracle 9i
Select distinct account_Details,
Case when branch = ‘Orchard’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Orchard,
Case when branch = ‘Newton’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Newton,
Case when branch = ‘Novena’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Novena
FROM account
Order by account_details DESC

ACCOUNT_DETAILS ORCHARD NEWTON NOVENA
—————————— ———- ———- —————————————-
Savings 65000
Savings 10000
Current 75000
Current 30000
Current 25000
—————————— ———- ———- —————————————-

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 6 | 342 | 5 (60)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 342 | 4 (50)| 00:00:01 |
| 2 | WINDOW SORT | | 6 | 342 | 5 (60)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ACCOUNT | 6 | 342 | 2 (0)| 00:00:01 |
——————————————————————————-

** The above is just close to the PIVOT output.

Oracle 11g
PIVOTING on Single column

SELECT *
FROM
(SELECT account_details, branch , amount
FROM account) PIVOT (sum(amount)
FOR branch IN (‘Orchard’, ‘Newton’, ‘Novena’))
ORDER BY account_details DESC;

ACCOUNT_DETAILS ‘Orchard’ ‘Newton’ ‘Novena’
—————————— ———- ———- ———-
Savings 65000 10000
Current 75000 30000 25000

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 3260125471

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 6 | 342 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY PIVOT| | 6 | 342 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ACCOUNT | 6 | 342 | 2 (0)| 00:00:01 |
——————————————————————————-

****************************************************************************

To display the output based on branch

Select distinct Branch,
Case when account_details = ‘Savings’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Savings,
Case when account_details = ‘Current’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Current
FROM account
Order by branch DESC

BRANCH SAVINGS CURRENT
————————————————– ———- ———-
Orchard 65000
Orchard 75000
Novena 10000
Novena 25000
Newton 30000

Oracle 11g – using Pivot function

SELECT *
FROM
(SELECT account_details, branch , amount
FROM account) PIVOT (sum(amount)
FOR account_details IN (‘Savings’, ‘Current’))
ORDER BY branch DESC;

BRANCH ‘Savings’ ‘Current’
————————————————– ———- ———-
Orchard 65000 75000
Novena 10000 25000
Newton 30000

***************************************************************************

PIVOTING on Multiple column

SELECT *
FROM
(SELECT account_details, branch , amount, customer_no
FROM account) PIVOT (sum(amount)
FOR (customer_no, branch) IN ((1,’Orchard’) as orchard, (2, ‘Newton’) as Newton, (3, ‘Novena’) Novena))
ORDER BY account_details DESC;

ACCOUNT_DETAILS ORCHARD NEWTON NOVENA
—————————— ———- ———- ———-
Savings 5000
Current

PIVOTING on Multiple agregations

(this is just for example only – just to show it can be agregated based on multiple columns)

SELECT *
FROM
(SELECT account_details, branch , amount
FROM account) PIVOT (sum(amount) amt, count(amount) cnt
FOR branch IN (‘Orchard’, ‘Newton’, ‘Novena’))
ORDER BY account_details DESC;

ACCOUNT_DETAILS ‘Orchard’_AMT ‘Orchard’_CNT ‘Newton’_AMT ‘Newton’_CNT ‘Novena’_AMT ‘Novena’_CNT
—————————— ————- ————- ———— ———— ———— ————
Savings 65000 2 0 10000 1
Current 75000 1 30000 1 25000 1
—————————— ————- ————- ———— ———— ————

PIVOTING on NULLs

INSERT INTO ACCOUNT VALUES (7, ‘Savings’, null, ‘Newton’);

ACCOUNT_DETAILS ‘Orchard’_AMT ‘Orchard’_CNT ‘Newton’_AMT ‘Newton’_CNT ‘Novena’_AMT ‘Novena’_CNT
—————————— ————- ————- ———— ———— ———— ————
Savings 65000 2 0 10000 1
Current 75000 1 30000 1 25000 1

**NULLS are handled automatically

PIVOT values XML

SET LONG 1024;
SELECT *
FROM
(SELECT account_details, branch , amount
FROM account
) PIVOT XML (SUM(amount) FOR branch IN (ANY) )

Output

ACCOUNT_DETAILS
——————————
BRANCH_XML
——————————————————————————–

Current
Newton30000Novena25000Orchard75000
Savings
NewtonNovena10000Orchard65000

PIVOTING Using SUBQUERIES

create table branch (branch_nm varchar2(50));

INSERT INTO BRANCH VALUES (‘Orchard’);
INSERT INTO BRANCH VALUES (‘Newton’);
INSERT INTO BRANCH VALUES (‘Novena’);

SELECT *
FROM
(SELECT account_details, branch , amount
FROM account
) PIVOT XML(SUM(amount)
FOR branch IN (SELECT branch_nm
FROM branch));

ACCOUNT_DETAILS BRANCH_XML
—————————————————————————————————–
Current Newton30000Novena25000Orchard75000

Savings NewtonNovena10000Orchard65000

****************************************************************************

Unpivot

**Note – Agregations done by PIVOT function cannot be reversed by UNPIVOT.
33.2 Unpivot
Requirement –

Data set

Account_details Orchard Novena Newton
Savings 65000 10000
Current 75000 25000 30000

To be converted as follows – Display branch wise sum of amounts for accounts

Account_details Branch Amount
Savings Orchard 65000
Savings Novena 10000
Current Orchard 75000
Current Newton 30000
Current Novena 25000

UNPIVOTING

Create table account
(Account_details varchar2(50),
Orchard number,
Novena number,
Newton number)

INSERT INTO ACCOUNT VALUES (‘Savings’, 65000,10000,NULL);

INSERT INTO ACCOUNT VALUES (‘Current’,75000,25000,30000);

SELECT *
FROM account
UNPIVOT (amount For Branch IN (Orchard, Newton, Novena))
ORDER BY account_Details DESC, branch

ACCOUNT_DETAILS BRANCH AMOUNT
————————————————– ——- ———-
Savings NOVENA 10000
Savings ORCHARD 65000
Current NEWTON 30000
Current NOVENA 25000
Current ORCHARD 75000

UNPIVOTING Multiple columns

SELECT *
FROM account_1
UNPIVOT (amount FOR (account_details,branch) IN
( Savings AS (‘Savings’, ‘Orchard’),
Current AS (‘Current’, ‘Novena’) ) )
ORDER BY account_details DESC, branch

UNPIVOTING ON MULTIPLE AGGREGATIONS

SELECT *
FROM account2
UNPIVOT ((amount, quantity)
FOR channel IN ((Direct_sumq, Direct_suma) AS 3,
(Internet_sumq, Internet_suma) AS 4 ))
ORDER BY quantity DESC, branch;

February 21, 2010  8:02 AM

Migrating from 9i to 11g – Purge & Recyclebin

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

From Oracle 10g if we try to drop a table when the recycle mode is ON then it just gets renamed – and does not gets deleted. So, the space occupied doesn’t get purged.

DROP TABLE test_readonly;

SHOW recyclebin

– Note when a table is dropped it is just renamed. It is not droped when the RECYCLE mode is ON. So the space occupied is there as it is.

SQL> select object_name, original_name, operation from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION
—————————— ——————————– ———
BIN$uCJ5kSosTM+s7chj6cJeGQ==$0 TEST_READONLY DROP
BIN$wl9CUjW7RsapzkrsoHDm+w==$0 TEST_READONLY DROP

Note: TEST_READONLY was dropped, recreated then dropped again.

To drop a table without storing it in recyclebin -

SQL> DROP TABLE test_Readonly purge;

Table dropped.

Elapsed: 00:00:00.40
SQL> select * from user_recyclebin;

no rows selected

Elapsed: 00:00:00.00

To purge the table that was dropped

PURGE TABLE “BIN$wl9CUjW7RsapzkrsoHDm+w==$0”

Table purged.

Elapsed: 00:00:02.92

To purge the recyclebin

PURGE recyclebin;

Recyclebin purged.


February 21, 2010  7:58 AM

Migrating from 9i to 11g – Partitioning enhancements

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Yet another exciting topic !!

Interval partitioning

- Oracle 11g has introduced a new partitioning feature, called Interval Partitioning. It is an enhancement to range partition.
- Automatically creates partition as new data is inserted
- Partitioning is a division of tables, indexes or IOT omtp smaller pieces – these smaller pieces can be managed and accessed independently
- Commonly known as the divide-and-conquer approach to managing large tables and indexes]
- Data loads will fail if one or more records cannot be matched to an existing partitioning

With range partition if partition is created on date – on inserting a particular date which is not part of the partioning, then the insert will fail. Interval partition is simply the data manageability by automatically creating the new partitions as needed. It is enabled in the table’s definition by defining one or more range partitions and including a specified interval.

Lets look at the current restriction first -

Oracle 9i

Range partitioning

CREATE TABLE range_test
(empid int,
joining_date DATE)
PARTITION BY RANGE(joining_date)
(
PARTITION join_jan2010 VALUES LESS THAN(TO_DATE(’02/01/2010′,’DD/MM/YYYY’)),
PARTITION join_feb2010 VALUES LESS THAN(TO_DATE(’03/01/2010′,’DD/MM/YYYY’)),
PARTITION join_mar2010 VALUES LESS THAN(TO_DATE(’04/01/2010′,’DD/MM/YYYY’)),
PARTITION join_apr2010 VALUES LESS THAN(TO_DATE(’05/01/2010′,’DD/MM/YYYY’))
);

INSERT INTO RANGE_TEST VALUES (1, sysdate)

–inserted on 11-Feb-09

*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

The current problem ORA-1440

CREATE TABLE range_test
(empid int,
joining_date DATE)
PARTITION BY RANGE(joining_date)
INTERVAL (NUMTOYMINTERVAL (1, ‘MONTH’))
(
PARTITION join_jan2010 VALUES LESS THAN(TO_DATE(’02/01/2010′,’DD/MM/YYYY’)),
PARTITION join_feb2010 VALUES LESS THAN(TO_DATE(’03/01/2010′,’DD/MM/YYYY’)),
PARTITION join_mar2010 VALUES LESS THAN(TO_DATE(’04/01/2010′,’DD/MM/YYYY’)),
PARTITION join_apr2010 VALUES LESS THAN(TO_DATE(’05/01/2010′,’DD/MM/YYYY’))
);

INSERT INTO RANGE_TEST VALUES (1, sysdate)

–inserted on 11-Feb-09

SQL> INSERT INTO RANGE_TEST VALUES (1, sysdate);

1 row created.

Elapsed: 00:00:00.39
SQL>

SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME = ‘RANGE_TEST’

SYSTEM RANGE_TEST NO JOIN_JAN2010
SYSTEM RANGE_TEST NO JOIN_FEB2010
SYSTEM RANGE_TEST NO JOIN_MAR2010
SYSTEM RANGE_TEST NO JOIN_APR2010
SYSTEM RANGE_TEST NO SYS_P41 -> Automatically created
TO_DATE(‘ 2010-03-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)

On inserting into table.

Distributing partitioning across tablespace

INTERVAL (NUMTOYMINTERVAL (1, ‘MONTH’))
STORE IN (small_data, medium_data, large_data)

Alter script

ALTER TABLE

SET INTERVAL (expression);

Restrictions:
1.Cannot be used on IOT
2.Partitioning key must be of must be either DATE / NUMBER datatype
3.domain index cannot be created on interval partitioned
4.Interval partitioning is not supported at the sub-partition level

****************************************************************************

Reference partitioning

It is a new partitioning option in 11g.
- Allows partitioning of two related tables to be based on the same tablespace
- If parent table is partitioned the child table can be made to inherit the partition properties of the parent

Oracle 11g
– Parent table

CREATE TABLE reference_test
(empid number(10) primary key,
joining_date DATE,
loc varchar2(3) not null)
partition by list (loc)
(
partition p_n values (‘Nor’),
partition p_s values (‘Sou’),
partition p_e values (‘Eas’),
partition p_w values (‘Wes’));

– Child table

create table reference_child
(empid number,
empname varchar2(50),
constraint fk_ref foreign key (empid) references reference_test
)

System partitioning

From Oracle 11g you can partition a table directly mapping it to a tablespace without specifying any partition key – SYSTEM partitioning. This is particularly useful for huge table look-ups (say, for validation or otherwise). This is controlled at application level – since, the control is moved from DBA to the application care should be taken to ensure that it is well managed.

Lets quickly compare a normal LIST partition with SYSTEM partition to see whether it has any real performance benefits!!

Oracle 9i
List Partition

CREATE TABLE test_list
(a varchar2(200),
language_spoken varchar2(200))
partition by list (language_spoken)
(partition l1 VALUES (‘English’, ‘French’) tablespace system,
partition l2 VALUES (‘Chinese’, ‘Malay’) tablespace sysaux,
partition l3 VALUES (‘German’, ‘Spanish’) tablespace users,
partition l4 values (default) tablespace users)

INSERT INTO TEST_LIST select SYS_GUID(), ‘English’ from dual connect by level <= 50;
INSERT INTO TEST_LIST select SYS_GUID(), ‘Chinese’ from dual connect by level <= 45;
INSERT INTO TEST_LIST select SYS_GUID(), ‘Malay’ from dual connect by level <= 30;
INSERT INTO TEST_LIST select SYS_GUID(), ‘German’ from dual connect by level <= 35;
INSERT INTO TEST_LIST select SYS_GUID(), ‘Spanish’ from dual connect by level <= 25;
INSERT INTO TEST_LIST select SYS_GUID(), ‘Hindi’ from dual connect by level <= 20;

SELECT table_name, partition_name, high_value, high_value_length, partition_position, tablespace_name from all_tab_partitions where table_name = ‘TEST_LIST’

TEST_LIST L1 ‘English’, ‘French’ 19 1 SYSTEM
TEST_LIST L2 ‘Chinese’, ‘Malay’ 18 2 SYSAUX
TEST_LIST L3 ‘German’, ‘Spanish’ 19 3 USERS
TEST_LIST L4 default 7 4 USERS

select * from test_list where language_spoken = ‘ENGLISH’

————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 204 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 1 | 204 | 3 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | TEST_LIST | 1 | 204 | 3 (0)| 00:00:01 | 4 | 4 |

Predicate Information (identified by operation id):
—————————————————
2 – filter(“LANGUAGE_SPOKEN”=’ENGLISH’)

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
745 recursive calls
0 db block gets
155 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
0 rows processed

The above for NO DATA FOUND – 155 consistent gets

select * from test_list where language_spoken = ‘English’

—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 50 | 10200 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 50 | 10200 | 2 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | TEST_LIST | 50 | 10200 | 2 (0)| 00:00:01 | 1 | 1 |
—————————————————————————————————

Predicate Information (identified by operation id):
—————————————————
2 – filter(“LANGUAGE_SPOKEN”=’English’)

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
2788 bytes sent via SQL*Net to client
449 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed

Oracle 11g
SYSTEM PARTITION

CREATE TABLE test_system
(a varchar2(200),
language_spoken varchar2(200))
partition by system
(partition s1 tablespace system,
partition s2 tablespace sysaux,
partition s3 tablespace users)

INSERT INTO TEST_SYSTEM select SYS_GUID(), ‘English’ from dual connect by level <= 50;

ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method

INSERT INTO TEST_SYSTEM partition (S1) select SYS_GUID(), ‘English’ from dual connect by level <= 50;
INSERT INTO TEST_SYSTEM partition (S2) select SYS_GUID(), ‘Chinese’ from dual connect by level <= 45;
INSERT INTO TEST_SYSTEM partition (S3) select SYS_GUID(), ‘Malay’ from dual connect by level <= 30;
INSERT INTO TEST_SYSTEM partition (S1) select SYS_GUID(), ‘German’ from dual connect by level <= 35;
INSERT INTO TEST_SYSTEM partition (S2) select SYS_GUID(), ‘Spanish’ from dual connect by level <= 25;
INSERT INTO TEST_SYSTEM partition (S3) select SYS_GUID(), ‘Hindi’ from dual connect by level <= 20;
SELECT table_name, partition_name, high_value, high_value_length, partition_position, tablespace_name from all_tab_partitions where table_name = ‘TEST_SYSTEM’

TEST_SYSTEM S1 0 1 SYSTEM
TEST_SYSTEM S2 0 2 SYSAUX
TEST_SYSTEM S3 0 3 USERS

select * from test_system where language_spoken = ‘ENGLISH’

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 204 | 5 (0)| 00:00:01 | | |
| 1 | PARTITION SYSTEM ALL| | 1 | 204 | 5 (0)| 00:00:01 | 1 |3|* 2 | TABLE ACCESS FULL | TEST_SYSTEM | 1 | 204 | 5 (0)| 00:00:01| 1 | 3 |
—————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
2 – filter(“LANGUAGE_SPOKEN”=’ENGLISH’)

Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
5 recursive calls
0 db block gets
37 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

The above for NO DATA FOUND – 37consistent gets

select * from test_system where language_spoken = ‘English’

—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 50 | 10200 | 5 (0)| 00:00:01 | | |
| 1 | PARTITION SYSTEM ALL| | 50 | 10200 | 5 (0)| 00:00:01 | 1 | 3 |
|* 2 | TABLE ACCESS FULL | TEST_SYSTEM | 50 | 10200 | 5 (0)| 00:00:
01 | 1 | 3 |
—————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
2 – filter(“LANGUAGE_SPOKEN”=’English’)

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
2788 bytes sent via SQL*Net to client
449 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed

*** In theory – SYSTEM partition is particularly useful for HUGE table look-ups.

****************************************************************************

Multiple column partitioning

Range and hash partitions can be partitioned on multiple columns.

Range partition on multiple columns -
CREATE TABLE test_range (a number, b varchar2(30), c date, d number)
partition by range (a, b, c)
(partition p1 values less than (1, ‘A’, ’01-Jan-2010′) tablespace system,
partition p2 values less than (2, ‘C’, ’01-Feb-2010′) tablespace users)

Table Created.
Hash partition on multiple columns -
CREATE TABLE test_hash (a number, b varchar2(30), c date, d number)
partition by hash (a, b, c)
partitions 4
store in (system, users)
Table Created.

****************************************************************************

Partition Compression

Individual partitions can be compressed in Oracle 11g. As like normal compression tables – DML operations / add or drop columns / partition level compress or no compress is possible.
Example – You can choose to compress OR uncompress certain partitions -
CREATE TABLE partition_compress (a number, b varchar2(30), c date, d number)
COMPRESS FOR ALL OPERATIONS
partition by range (a, b, c)
(partition p1 values less than (1, ‘A’, ’01-Jan-2010′) tablespace system,
partition p2 values less than (2, ‘C’, ’01-Feb-2010′) tablespace users NOCOMPRESS)

TABLE_NAME PARTITION_NAME COMPRESS
—————————— —————————— ——–
PARTITION_COMPRESS P1 ENABLED
PARTITION_COMPRESS P2 DISABLED


February 21, 2010  7:53 AM

Migrating from 9i to 11g – Password

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Password – Case sensitive

From Oracle 11g passwords are case sensitive.

select * from v$parameter where upper(name) = ‘SEC_CASE_SENSITIVE_LOGON’

VALUE
TRUE

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

SQL> conn SYSTEM/mother01
Connected.

SQL> conn system/Mother01
Connected.

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

SQL> conn SYSTEM/mother01
ERROR:
ORA-01017: invalid username/password; logon denied

Password – OS Authentication

Its not really straight-forward to use OS authentication in Oracle 11g – this is mainly for security reasons. But, if your application is using it you can still try to do it like this !!

select * from v$parameter where upper(name) = ‘OS_AUTHENT_PREFIX’

VALUE
OPS$

ALTER SYSTEM SET OS_AUTHENT_PREFIX=’$OPS’ SCOPE=spfile

(Note: Cannot make the scope as BOTH)

Check REMOTE_OS_AUTHENT

select * from v$parameter where upper(name) = ‘REMOTE_OS_AUTHENT’

VALUE
FALSE

If the value is FALSE then, OS authentication will not be allowed. The parameter should be set to TRUE.

ALTER SYSTEM SET REMOTE_OS_AUTHENT=TRUE SCOPE=SPFILE

Now, create OS user

SQL> CREATE USER ops$lakshmi identified externally;

User created.

SQL> GRANT CONNECT TO ops$lakshmi;

Grant succeeded.

(from the need – need to check in unix)

[oracle@ludwig ~]$ su – lakshmi
Password:
[unix]$ ORACLE_HOME=/opt/app/oracle/product/11.1.0/db_1
[unix]$ ORACLE_HOME_LISTNER=/opt/app/oracle/product/11.1.0/db_1/network/admin
[unix]$ ORACLE_SID=db11FS
[unix]$ PATH=$ORACLE_HOME/bin:$PATH
[unix]$
[unix]$ export PATH
[unix]$ export ORACLE_HOME
[unix]$ export ORACLE_HOME_LISTNER
[unix]$ export ORACLE_SID
[unix]$ sqlplus /

Password – Verification function

ALTER PROFILE default LIMIT
PASSWORD_LIFE_TIME 60
PASSOWRD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFYH_FUNCTION verify_function;

Also, strong passwords needs to be set -

Minimum 8 characters
one letter and one digit
not a common password
must differ at least by 3 characters from the previous password


February 21, 2010  7:49 AM

Migrating from 9i to 11g – Native Compilation

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

As we already know – Oracle has two methods of compilation Interpreted / Native. In Interpreted mode Oracle transforms all the PL/SQL code to C code. From 11g Oracle directly translates PL/SQL source code to DLL for the server. Linking and loading is done by itself by bypassing the file system.
Prior to Oracle 11g, to compile a code as Native lot of parameters needs to be changed. But, from Oracle 11g we can either compile all the programs or specific programs using Native compilation just by changing only one parameter – ‘PLSQL_CODE_TYPE’.

I feel Native compilation in Oracle 9i used to be a bit complicated – Lets take a quick look

Oracle 9i -

Prior to Oracle 11g – following parameters should be changed

ALTER SYSTEM SET plsql_native_make_utility = ‘make’;
ALTER SYSTEM SET plsql_native_make_file_name = ‘ ;
ALTER SYSTEM SET plsql_native_library_dir=”;
ALTER SESSION SET plsql_compiler_flags = ‘NATIVE’;

To set it to interpreted

ALTER SESSION SET plsql_compiler_flags = ‘INTERPRETED’;

From Oracle 11g its way improved and simple to compile it native.

Oracle 11g

Interpreted vs Native – program level

CREATE OR REPLACE PROCEDURE interpreted_test AS
v_value NUMBER;
BEGIN
for i in 1 .. 500000 loop
v_value := i + i;
end loop;
END;
/

exec interpreted_test

CREATE OR REPLACE PROCEDURE native_test AS
v_value NUMBER;
BEGIN
for i in 1 .. 500000 loop
v_value := i + i;
end loop;
END;
/

alter procedure native_test compile plsql_code_type=native

exec native_test

SQL> select plsql_code_type from all_plsql_object_settings where name =’INTERPRE
TED_TEST’;

PLSQL_CODE_TYPE
——————————————————————————–

INTERPRETED

Elapsed: 00:00:00.09
SQL> ed
Wrote file afiedt.buf

1* select plsql_code_type from all_plsql_object_settings where name =’NATIVE_T
EST’
SQL> /

PLSQL_CODE_TYPE
——————————————————————————–
NATIVE

Native – for all programs

NAME TYPE VALUE
———————————— ———– —————–
plsql_code_type string INTERPRETED

ALTER SESSION set plsql_code_type = ‘NATIVE’

NAME TYPE VALUE
———————————— ———– —————
plsql_code_type string NATIVE

This will compile all the code to native by default.

To recompile all the code to NATIVE the database should be shutdown, after that parameter should be altered to NATIVE at system level, and also the PLSQL_OPTIMIZER_LVEL should be set to either 2 or 3; startup the database.

From Oracle 11g if PLSQL_CODE_TYPE is set to INTERPRETED then, PLSQL code will be compiled to byte code format and the PLSQL interpreted engine will execute them.

If the parameter is set to NATIVE then, PLSQL source code will be compiled to machine code and executed natively without any interpreter involvement – so execution would be faster.

Default value is INTERPRETED – so all the code would be compiled as byte code. Note that we can also change the PLSQL_CODE_TYPE value to NATIVE or INTERPRETED dynamically using the ALTER SESSION command inside the application.


February 21, 2010  7:46 AM

Migrating from 9i to 11g – Named and mixed notation for PL/SQL sub program in a sql stmt

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Positional, named or mixed-notations are allowed in calls to functions / procedures. Prior to Oracle 11g only positional notation was supported from the SQL statements. From 11g named and mixed notations are supported.

If there are too many default values then we have been given values to them as well earlier – but, from now on they can be skipped and can give only the required values.

CREATE OR REPLACE function
mixed_notation (in_param1 IN NUMBER DEFAULT 10, in_param2 IN NUMBER DEFAULT 12)
RETURN NUMBER
IS
v_value number;
BEGIN
v_value := in_param1 * in_param2;
RETURN (v_value);
END mixed_notation;

Oracle 9i

SQL> select mixed_notation(10, 12) from dual;

MIXED_NOTATION(10,12)
———————
120

Oracle 11g

SQL> select mixed_notation() from dual;

MIXED_NOTATION()
—————-
120

SQL> select mixed_notation(in_param1=>6) from dual;

MIXED_NOTATION(IN_PARAM1=>6)
—————————-
72

Mix it up !!


February 21, 2010  7:44 AM

Migrating from 9i to 11g – Merge enhancements

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

More exciting features in 10g and 11g !!

With Oracle 10g INSERT / DELETE is now optional. In Oracle 11g new optional DELETE can be included in WHEN MATCHED clause.

OPTIONAL INSERT

MERGE
INTO emp1 tgt
USING emp src
ON (src.empno = tgt.empno)
WHEN MATCHED
THEN
UPDATE
SET tgt.still_employed = CASE WHEN src.JOB IS NOT NULL THEN ‘Y’ END

/

20 rows merged.

Elapsed: 00:00:00.01

Optional Update

MERGE
INTO emp1 tgt
USING emp src
ON (src.empno = tgt.empno)
WHEN NOT MATCHED
THEN
INSERT (tgt.empno, tgt.still_employed)
values (src.empno, ‘Y’)

/

40521 rows merged.

Elapsed: 00:00:03.78

Optional Delete

MERGE
INTO emp1 tgt
USING emp src
ON (src.empno = tgt.empno)
WHEN MATCHED THEN
UPDATE SET tgt.still_employed = CASE WHEN src.JOB IS NOT NULL THEN ‘Y’ END
DELETE WHERE tgt.still_employed = ‘N’
WHEN NOT MATCHED
THEN
INSERT (tgt.empno, tgt.still_employed)
values (src.empno, ‘Y’)
/

40541 rows merged.

Elapsed: 00:00:00.29


February 21, 2010  7:42 AM

Migrating from 9i to 11g – MODEL

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Yet another exciting feature – haven’t really had a requirement to apply it though – waiting !!

SQL MODEL clause enables to define a multidimensional array on query results – then can apply rules on the array to calculate new values. The rules clause can be applied for calculations. This enables to perform advanced calculations without using views or calculating the same in spreadsheets.

It defines a multidimensional array by mapping the columns of a query into three groups – partitioning, dimension, and measure columns.

Partitions - define blocks of the result set (similar to analytical functions)
Dimensions - identify each measure cell within a partition
Measures - they are similar to the measures of a fact table in a star schema (data warehousing). They contain numeric values.

Use of Model clause – Model clause uses a subset of the available columns from your FROM clause. It contains at least one dimension, at least one measure and optionally one or more partitions.

Simple Model clause – without any rule

select empno,empname,sal
from emp where deptno = 630 and rownum < 6
model
dimension by (empno)
measures (empname,sal)
rules () ;

EMPNO EMPNAME SAL
———- —————————— ———-
26003 /edfa72de_SimpleContentModel 26664
26005 /c024bdbb_CMStateSet 26666
26007 /1def91f2_CMNode 26668
26009 /5f106d9a_DTDGrammar 26670
26011 /761002ee_XMLDTDDescription 26672

Elapsed: 00:00:00.06

The above is just same as-

select empno,empname,sal
from emp where deptno = 630 and rownum < 6

Using Rules

select empno,empname,comm
from emp WHERE rownum < 6
model
dimension by (empno)
measures (empname,comm)
rules (
empname[770] = ‘UNDO$’,
comm[770] = 5000
);

EMPNO EMPNAME COMM
———- —————————— ———-
1 ICOL$ 110929.5
2 I_USER1 132064.5
3 CON$ 14350.5
4 UNDO$ 35766
5 C_COBJ# 10611
770 UNDO$ 5000

6 rows selected.

Elapsed: 00:00:00.01

select empno,empname,comm
from emp WHERE rownum < 6
model
dimension by (empno)
measures (empname,comm)
rules (
empname[990] = ‘UNDO$’,
comm[990] = 5000
);

EMPNO EMPNAME COMM
———- —————————— ———-
1 ICOL$ 110929.5
2 I_USER1 132064.5
3 CON$ 14350.5
4 UNDO$ 35766
5 C_COBJ# 10611
990 UNDO$ 5000

The rule mentioned above (highlighted in red) shows how measure empname is expanded with dimension 990 and gets the value ‘UNDO$’. As EMP contains empno 990 already, the data is overridden and the value is displayed.

Say another example – lets check for a value where data does not exist-

select empno,empname,comm
from emp WHERE rownum < 6
model
dimension by (empno)
measures (empname,comm)
rules (
empname[102222990] = ‘UNDO$’,
comm[102222990] = 5000
);

In this case 102222990 does not exist in EMP table, so now a new cell has been created, which shows itself as a new row in the result set. Note – this row is not inserted into the table, but only to the result set of the query. Note the second rule COMM enables to display the commission also!!


February 21, 2010  7:39 AM

Migrating from 9i to 11g – IEEE Floating point functions

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

This is new in Oracle 10g. BINARY_FLOAT and BINARY_DOUBLE data types stores floating point data in 32-bit IEEE format. Oracle suggests that using BINARY_FLOAT / BINARY_DOUBLE would be faster compared to NUMBER type.

BINARY_FLOAT & BINARY_DOUBLE requires less space for storing and also Oracle suggests that arithmetic operations can be faster with Binary float or double and is used for the following

* Column data types while table creation
* Index can be created on these columns
* Aggregation is supported in these columns
* They can be used in order by / group by clause
* storage of binary float / double is platform independent
* In-built functions like CIEL, ABS, ACOS, AVG, CORR, MAX, MIN etc.,
* Analytical functions are supported by floating point datatypes
* Not null, unique constraint, foreign key, check-point, REF constraint etc., can be created

Oracle 9i
SQL> create table test_num (a number, b number);

Table created.

Elapsed: 00:00:00.17

SQL> insert into test_num select rownum, rownum from dual connect by level <= 50000;

50000 rows created.

Elapsed: 00:00:01.14
select a*6000, b*300 + 950 / 150 from test_num;

Execution Plan
———————————————————-
Plan hash value: 950179409

——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 41368 | 1050K| 30 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_NUM | 41368 | 1050K| 30 (0)| 00:00:01 |
——————————————————————————

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
28 recursive calls
0 db block gets
3499 consistent gets
0 physical reads
0 redo size
1169200 bytes sent via SQL*Net to client
67094 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed

****************************************************************************

Oracle 11g
SQL> create table test_float (a binary_float, b binary_double);

Table created.

Elapsed: 00:00:09.42

SQL> insert into test_float select rownum, rownum from dual connect by level <=
50000;

50000 rows created.

Elapsed: 00:00:01.48
ARITHMETIC CALCULATIONS:

SQL> select a*5000, b*400 + 650 / 250 from test_float;

50000 rows selected.

Elapsed: 00:00:01.00

Execution Plan
———————————————————-
Plan hash value: 30424090

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 54338 | 530K| 38 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_FLOAT | 54338 | 530K| 38 (0)| 00:00:01|
Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
28 recursive calls
0 db block gets
3537 consistent gets
0 physical reads
0 redo size
1267174 bytes sent via SQL*Net to client
67094 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed

SQL>


February 21, 2010  7:36 AM

Migrating from 9i to 11g – Invisible indexes

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Its a new feature in Oracle 11g. It is an index available in the database but is not used by the optimizer unless hinted. This is one of the best features provided by Oracle 11g. Sometimes we confront situation after creating a new index that the intended module is working fine but some other module gets unimaginably screwed up. This invisible index comes handy when certain queries in the processes require a specific index without affecting the rest of the queries / processes. It can also be used as an alternative to dropping / disabling an index. If you want to quickly test the index for a particular query and to see how it would work with a particular index without distrubing the other processes then creating invisible index, testing and dropping the same is the best way to go. Yet another use of this index is certain programs might require index temporarily – in such cases invisible index can be created before start of the process, hinted in the query and then dropped before exiting the process.

Prior to 11g usually the index would be set to unused then it would be observed to determine whether it adversely impacts the performance before dropping the index. Now there is a option of setting it invisible against making it unused.

Query all_indexes table to check whether the index is invisible / not. Net net, if you don’t want the new index to impact the database and want only certain processes to use it then its safe to create invisible index and reference the same in the queries. However, it should be taken into note that any DML operations would write to this invisible index also – so create invisible indexes only when necessary – as DQL’s might not be affected but DML’s do gets affected.

Note: Rebuilding an invisible index will make it visible automatcially.

Oracle 9i
ALTER INDEX unusable

ALTER INDEX usable

create unique index emp1_idx1 on emp1 (empno)

set autotrace traceonly

SELECT * FROM EMP1 WHERE EMPNO = 1

ALTER INDEX emp1_idx1 unusable

Execution Plan
———————————————————-
Plan hash value: 2226897347

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP1 | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMPNO”=1)

Statistics
———————————————————-
254 recursive calls
0 db block gets
37 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SELECT /*+ INDEX (emp1 EMP1_IDX1) */ * FROM EMP1 WHERE EMPNO = 1;

SELECT /*+ INDEX (emp1 EMP1_IDX1) */ * FROM EMP1 WHERE EMPNO = 1
*
ERROR at line 1:
ORA-01502: index ‘SYSTEM.EMP1_IDX1′ or partition of such index is in unusable
state

Oracle 11g
ALTER INDEX invisible

ALTER INDEX visible

create index on

() invisible

create unique index emp1_idx1 on emp1 (empno) invisible

set autotrace traceonly

SELECT * FROM EMP1 WHERE EMPNO = 1

Execution Plan
———————————————————-
Plan hash value: 2226897347

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP1 | 1 | 5 | 2 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“EMPNO”=1)

Statistics
———————————————————-
179 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

SELECT /*+ INDEX (emp1 EMP1_IDX1) */ * FROM EMP1 WHERE EMPNO = 1

Execution Plan
———————————————————-
Plan hash value: 797959521

————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | 1 | 5 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP1_IDX1 | 1 | | 0 (0)| 00:00:01 |
————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“EMPNO”=1)

Statistics
———————————————————-
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
389 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: