Multiple indexes on same column

In the past oracle allowed only one index for same columns. if you try to create another index (with same columns), Oracle throws an error ORA-01408 : such column list already indexed

With 12c, Oracle allows multiple indexes on same column(s). However, only one index is visible any time. It also allows having one function based index + one regular index to be visible.

Read Full Post »

Compress data in Table/Partition

As application usage increases, data in the database grows pretty quickly. In some applications there are driving tables whose data grows very quickly as users adds more data to the system. If you are in a environment where database service providers who charges on the database size usage, will have to considering increasing expenses as database size increases.

Oracle has compression technique for long time, but it is not popular feature as it always needed a trade off between performance and space. Oracle 10g and up has introduced advanced compression techniques which is very attractive  and unique in a way it compresses data. Here is this works

• Oracle eliminates duplicate values in the database block. Instead it uses pointers for repeated information (called symbols). Header will hold all the unique values.
• Block is self-contained that is block contains all information needed to re-construct uncompressed data
• Since it eliminates duplicates, it can compress to maximum potential especially in scenarios where applicaiton has audit table – has lot of repeated information (historical change record).
• This compression works for all data types except for variants of LOBs
• This compression do not trigger for every write operation; instead when block reaches a threshold (manager internally) compression occurs on that block. Only few transactions triggers this event.
• Side effect:It is possible to cause fragmantation when there are many modification to table/partition. Ex – if a row is deleted, it is less likely new data will fit into freed-up space. For such tables re-compress the data.

So how do I compress table?
As simple command like the below can compress the table for OLTP operations

SQL> alter table test_tab compress for OLTP;

To compress the table partition use below command

SQL > alter table test_tab modify[move] partition test_tab_2011 compress for oltp;

However this do not compress already existing data. Let’s look at following statements for partitions.

Below statement gets me blocks and size occupied for the given partition

SQL> @temp
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
2 from user_segments
3 where segment_name = 'TEST_TAB' and partition_name = 'TEST_TAB_2011'
4 /
BLOCKS_KB BYTES_KB
---------- ----------
12 96
1 row selected.
Elapsed: 00:00:00.08

Now lets compress the table partition using modify option.

SQL> alter table TEST_TAB modify partition TEST_TAB_2011 compress for oltp
2 /
Table altered.
Elapsed: 00:00:00.03
SQL> @temp
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
2 from user_segments
3 where segment_name = 'TEST_TAB' and partition_name = 'TEST_TAB_2011'
4 /
BLOCKS_KB BYTES_KB
---------- ----------
12 96
1 row selected.

As you can see from above statement, after enabling compression on partition, same block and size remained as before. It means no data compressed, we just enabled compression.

Now let’s use Move option for alter table.

SQL> alter table TEST_TAB move partition TEST_TAB_2011 compress for oltp;
Table altered.
Elapsed: 00:00:12.41
SQL> @temp
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
  2  from user_segments
  3  where segment_name = 'TEST_TAB' and partition_name = 'TEST_TAB_2011'
  4  /
BLOCKS_KB   BYTES_KB
---------- ----------
         6         48
1 row selected.
Elapsed: 00:00:00.04

Now total size for partition is down to 48KB (earlier it was 96 KB). It means existing data is compressed.

Because of the above reasons, it is also possible that table/partition may contain compressed and non-compressed data.

How to compress existing data in a non partitioned table?

Here are options…

• Export table data, drop data, enable compression using alter table statement (first statement) then re-load data [OR]
• Re-name existing table, create new compressed table, load new compressed table then drop the renamed table.
• Well you can use the above options…but better yet use move option with alter table command. Here is the example.

SQL> create table kp_test2 as select * from dba_objects;
Table created.
Elapsed: 00:00:02.07
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
 2 from user_segments
 3 where segment_name = 'KP_TEST2';
BLOCKS_KB BYTES_KB
---------- ----------
 1 8
Elapsed: 00:00:00.40
SQL> alter table kp_test2 compress for oltp;
Table altered.
Elapsed: 00:00:00.03
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
 2 from user_segments
 3 where segment_name = 'KP_TEST2';
BLOCKS_KB BYTES_KB
---------- ----------
 1 8
Elapsed: 00:00:00.13

With the above command, segment size remained same as before compression enabled.

SQL> alter table kp_test2 move compress for oltp;
Table altered.
Elapsed: 00:00:00.75
SQL> select blocks/1024 Blocks_KB, bytes/1024/1024 Bytes_KB
 2 from user_segments
 3 where segment_name = 'KP_TEST2';
BLOCKS_KB BYTES_KB
---------- ----------
 .375 3
Elapsed: 00:00:00.08

Wow,  size reduced to 3KB by using move option.

Read Full Post »

LISTAGG vs Stragg vs WM_CONCAT

One of the Oracle Database 11g features is to provide concatenated data for each group called LISTAGG. In prior versions we used different ways to achieve same results. Some of those are STRAGG and 10g introduced undocumented function WM_CONCAT.

Below I illustrated the differences in performance using these different function. I used same huge data set and same environment.

First look at ListAgg Vs WM_Concat

DEV> select id,listagg(paramlist,’;’) within group (order by paramlist)  as agg_data

2  from KP_AGG_TEST

3  group by id;

287485 rows selected.

Elapsed: 00:00:43.59

Execution Plan

———————————————————

Plan hash value: 2522236266

—————————————————————————————–

Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

—————————————————————————————–

0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

—————————————————————————————–

Statistics

———————————————————

67  recursive calls

20  db block gets

4077  consistent gets

8320  physical reads

0  redo size

11625136  bytes sent via SQL*Net to client

134407  bytes received via SQL*Net from client

19167  SQL*Net roundtrips to/from client

0  sorts (memory)

1  sorts (disk)

    287485  rows processed

DEV> select id,wm_concat(paramlist) from KP_AGG_TEST

2  group by id;

287485 rows selected.

Elapsed: 00:29:54.60

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

51  recursive calls

3449842  db block gets

323291  consistent gets

6328  physical reads

0  redo size

103733023  bytes sent via SQL*Net to client

41398092  bytes received via SQL*Net from client

862457  SQL*Net roundtrips to/from client

0  sorts (memory)

1  sorts (disk)

287485  rows processed

As you can see, WM_CONCAT took longer than List Agg.

Now let’s look at LISTAGG vs STRAGG

Query 1: With below ListAgg query, aggregated data is returned in the order.

DEV> select id,listagg(paramlist,’;’) within group (order by paramlist)  as agg_data

  2  from KP_AGG_TEST

  3  GROUP BY ID;

287485 rows selected.

Elapsed: 00:00:44.95

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

         67  recursive calls

         30  db block gets

       4077  consistent gets

      13152  physical reads

          0  redo size

   11644303  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query 2: Note that below STRAGG Query doesn’t return aggregated data in the order. Per returned data, this is little diff from listagg

 Dev> select id, stragg(paramlist) from kp_agg_test

  2  group by id;

287485 rows selected.

Elapsed: 00:02:37.82

Execution Plan

———————————————————-

Plan hash value: 2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

     287538  recursive calls

         22  db block gets

    1786494  consistent gets

       6341  physical reads

          0  redo size

   18532148  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query 3:Below query returns aggregated data in the order, same as listagg.

 

10-NOV-11 LV_DEV@GALVD501> select id, stragg_order_by_item(paramlist) from kp_agg_test

  2  group by id;

287485 rows selected.

Elapsed: 00:02:46.53

Execution Plan

———————————————————-

Plan hash value:2522236266

——————————————————————————————

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT   |             |   287K|  7860K|       |  5030   (2)| 00:01:01 |

|   1 |  SORT GROUP BY     |             |   287K|  7860K|    30M|  5030   (2)| 00:01:01 |

|   2 |   TABLE ACCESS FULL| KP_AGG_TEST |   876K|    23M|       |  1137   (2)| 00:00:14 |

——————————————————————————————

Statistics

———————————————————-

     287539  recursive calls

         30  db block gets

    1786488  consistent gets

       9938  physical reads

          0  redo size

   11904394  bytes sent via SQL*Net to client

     134406  bytes received via SQL*Net from client

      19167  SQL*Net roundtrips to/from client

     287485  sorts (memory)

          1  sorts (disk)

     287485  rows processed

 

Results Comparision

 

Query 1: Elapsed time is less compare to 2 and 3 below

Query 2:

–   Elapsed time is high

–   Recursive calls, consistent gets high

–   CPU/Cost is same as listagg.

–   High Bytes sent via SQL*Net

– Result set was not in the order as Query 1

 

Query 3: 

 

–   Elapsed time is high

–   Recursive calls, consistent gets high

–   CPU/Cost is same as listagg.

–   High Bytes sent via SQL*Net

– Result set was same as Query 1

 

Conclusion:

Avoid using WM_CONCAT, which is moreover not documented.

If you are using stragg, you can switch them to LISTAGG. It is difficult to change lot of queries that are already present and at atleast  start using LISTAGG over STRAGG. LISTAGG provides options like ordering, customized delimiter.

 

 

 

Read Full Post »

List aggregation (LISTAGG) in 11g

As you may already found that LISTAGG function is new in Oracle DB 11g. This function performs a string aggregation (concatenation) natively without implementing user-defined objects (most popular STRAGG) and other function that loops through data.

Here is the format of the function:

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

Here is the simple example :

SQL> select first_name from employees where department_id =20;

FIRST_NAME
——————–
Michael
Pat

SQL> select listagg(first_name,’;’) within group (order by null) as fnames
2  from employees
3  where department_id = 20;

FNAMES
——————————————————————————–
Michael;Pat

Lets say there are multiple employees whose first name is same. But I would like to concatenate those names uniquely. That means I don’t want to list duplicate names, just concatenate distinct first names.

Here is example of department which contains employees of same first name.

SQL> r
1  select  listagg(first_name,’;’) within group (order by first_name) as fnames
2  from employees
3* where department_id = 80
FNAMES
——————————————————————————–
Alberto;Allan;Alyssa;Amit;Charles;Christopher;Clara;Danielle;David;David;Eleni;E
lizabeth;Ellen;Gerald;Harrison;Jack;Janette;John;Jonathon;Karen;Lindsey;Lisa;Lou
ise;Mattea;Nanette;Oliver;Patrick;Peter;Peter;Sarath;Sundar;Sundita;Tayler;Willi
am

As you see in result there are two David s and two Peter s. How can I select only distinct data.

LISTAGG function doesn’t let you use the distinct key. See below what happens if I try to do this.

SQL> r
1  select  listagg(DISTINCT first_name,’;’) within group (order by first_name) as fnames
2  from employees
3* where department_id = 80
select  listagg(DISTINCT first_name,’;’) within group (order by first_name) as fnames
*
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function

But this can be resolved using sub query as below.

SQL> r
1  select listagg(first_name,’;’) within group (order by first_name) as fnames
2  from
3  (
4  select distinct department_id, first_name
5  from employees
6* where department_id = 80)
FNAMES
——————————————————————————–
Alberto;Allan;Alyssa;Amit;Charles;Christopher;Clara;Danielle;David;Eleni;Elizabe
th;Ellen;Gerald;Harrison;Jack;Janette;John;Jonathon;Karen;Lindsey;Lisa;Louise;Ma
ttea;Nanette;Oliver;Patrick;Peter;Sarath;Sundar;Sundita;Tayler;William

Now Results display distinct names as highlighted.

Read Full Post »

Export issue on 11g, can’t export tables with zero rows.

Traditional Oracle export/import had bug exporting tables with not allocated segments in 11g 11.2.0.1

Exporting a schema with tables those have zero rows gets error EXP-00011.

This is because 11g introduced “segmented_created” column in DBA_TABLES. if you see value “NO” for this, that means no segments were allocated. In this case, Export can’t export table in version 11.2.0.1. This has been fixed in 11.2.0.2.

For this version of oracle, either use Datapump or allocate an extent using following syntax.

select owner, table_name,segment_created from dba_tables where sgement_created = ‘NO’

alter table ; allocate extent.

Note that allocating extent takes up little more space in database. It allocates according to tablespace’s definition.

Read Full Post »