bye bye to FBI; Lets Welcome VC

(FBI ==> Function Based Indexes ; VC ==> Virtual Columns )

starting 11g, you can have Virtual columns (expression or computations) in a table just like regular columns .. That would make lots of things readable..queries, index scripts etc etc..

you can have constraints based on Virtual columns..you can even partition the table based on VC.

Here is a simple demo
----------------------

Create table test_vc (
id number(5) ,
name varchar2(10) ,
age number(3) ,
sal number(10) ,
comm number(10) ,
grade varchar2(10) GENERATED ALWAYS as
(
CASE WHEN (age>60)
THEN 'Senior'
WHEN ((age between 51 and 60) and (sal>200000))
THEN 'Grade A+'
WHEN ((age between 51 and 60) and (sal<200000))
THEN 'Grade A-'
WHEN ((age between 41 and 50) and (sal>200000))
THEN 'Grade B+'
WHEN ((age between 41 and 50) and (sal<200000))
THEN 'Grade B-'
WHEN ((age between 31 and 40) and (sal>200000))
THEN 'Grade C+'
WHEN ((age between 31 and 40) and (sal<200000))
THEN 'Grade C-'
END
) VIRTUAL ,
grade_auto GENERATED ALWAYS as
(
CASE WHEN (age>60)
THEN 'Senior'
WHEN ((age between 51 and 60) and (sal>200000))
THEN 'Grade A+'
WHEN ((age between 51 and 60) and (sal<200000))
THEN 'Grade A-'
WHEN ((age between 41 and 50) and (sal>200000))
THEN 'Grade B+'
WHEN ((age between 41 and 50) and (sal<200000))
THEN 'Grade B-'
WHEN ((age between 31 and 40) and (sal>200000))
THEN 'Grade C+'
WHEN ((age between 31 and 40) and (sal<200000))
THEN 'Grade C-'
END
) VIRTUAL
)

Both Grade and Grade_auto are Virtual columns ..but the only difference is for Grade_auto Oracle assigned the datatype by its own judgement.

SQL> desc test_vc
Name Null? Type
----------------------------------------- -------- --------------
ID NUMBER(5)
NAME VARCHAR2(10)
AGE NUMBER(3)
SAL NUMBER(10)
COMM NUMBER(10)
GRADE VARCHAR2(10)
GRADE_AUTO VARCHAR2(8) <---

Before 11g, we either would have created an Index on user-defined deterministic function or standard built-in functions.. this would have been the Create Index statement

SQL> Create index test_f_idx on test_vc(
2 CASE WHEN (age>60)
3 THEN 'Senior'
4 WHEN ((age between 51 and 60) and (sal>=200000))
5 THEN 'Grade A+'
6 WHEN ((age between 51 and 60) and (sal<200000))
7 THEN 'Grade A-'
8 WHEN ((age between 41 and 50) and (sal>=200000))
9 THEN 'Grade B+'
10 WHEN ((age between 41 and 50) and (sal<200000))
11 THEN 'Grade B-'
12 WHEN ((age between 31 and 40) and (sal>=200000))
13 THEN 'Grade C+'
14 WHEN ((age between 31 and 40) and (sal<200000))
15 THEN 'Grade C-'
16 END
17 );

Now check out how the same reads..

-- in 11g (indexing on Virtual columns allowed)
-- ---------------------------------------------------------
Create index test_vc_idx1 on test_vc(grade);
Create index test_vc_idx2 on test_vc(grade_auto);

Behind the screens, Oracle handles the Virtual columns just the same way as FBIs

SQL> select index_name,index_type from user_indexes
2 where table_name='TEST_VC';

INDEX_NAME INDEX_TYPE
------------------------------ ------------------------
TEST_VC_IDX2 FUNCTION-BASED NORMAL
TEST_VC_IDX1 FUNCTION-BASED NORMAL
TEST_F_IDX FUNCTION-BASED NORMAL

so lets forget Function based Index and start using Virtual columns..

11g automagic partition creations - part2

its a bit different when you range partition by date column..you cannot specify a constant as your interval

SQL> Create table test_auto_partitioning_2
2 (c1 number, c2 varchar2(10) , c3 date)
3 partition by range (c3)
4 interval(30)
5 (
6 partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
7 partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
8 partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
9 )
10 /
Create table test_auto_partitioning_2
*
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type

Instead specify as date-interval datatype so oracle is aware of what you are requesting.. Rest is pretty much the same..



SQL> Create table test_auto_partitioning_2
2 (c1 number, c2 varchar2(10) , c3 date)
3 partition by range (c3)
4 interval(numtoyminterval(1,'MONTH'))
5 (
6 partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
7 partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
8 partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
9 )
10 /

Table created.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_2';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1
PART2
PART3

SQL> Insert into TEST_AUTO_PARTITIONING_2(c3)
2 values (to_date('&mm/&dd/2007','mm/dd/yyyy'));
Enter value for mm: 09
Enter value for dd: 01
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/01/2007','mm/dd/yyyy'))

1 row created.


SQL> /
Enter value for mm: 09
Enter value for dd: 13
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/13/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 09
Enter value for dd: 17
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/17/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 10
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('10/16/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 11
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('11/16/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 12
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('12/16/2007','mm/dd/yyyy'))

1 row created.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST_AUTO_PARTITIONING_2');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_2';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1 2
PART2 1
PART3 1
SYS_P44 1 -- auto generated
SYS_P45 1 -- auto generated

11g rocks!!

11g automagic partition creations

11g introduces this new interval partitioning ..bye-bye to all age-old partition maintenance script to create new partitions and capturing unexpected data into "maxvalue" bucket.

all you have to tell Oracle is your logic of partitioning..ie INTERVAL key.

See it in action here..

SQL>connect venkat/venkat

-- range partition by number
-- -------------------------

Create table TEST_AUTO_PARTITIONING_1
(c1 number, c2 varchar2(10) , c3 date)
partition by range (c1)
interval(30) -- INTERVAL specified as 30
(
partition part1 values less than (100),
partition part2 values less than (200),
partition part3 values less than (300)
)
/


SQL> select partition_name from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_1';

PARTITION_NAME
------------------------------
PART1
PART2
PART3

-- insert 3 rows (50,150 and 250)
SQL> select * from TEST_AUTO_PARTITIONING_1
2 /

C1 C2 C3
---------- ---------- ---------
50
150
250

SQL> select partition_name from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_1';

PARTITION_NAME
------------------------------
PART1
PART2
PART3

SQL> insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n);
Enter value for n: 350
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (350)

1 row created.

SQL> /
Enter value for n: 450
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (450)

1 row created.

SQL> /
Enter value for n: 550
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (550)

1 row created.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>USER,tabname=>'TEST_AUTO_PARTITIONING_1');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_1';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1 1
PART2 1
PART3 1
SYS_P41 1 -- system generated
SYS_P42 1 -- system generated
SYS_P43 1 -- system generated

6 rows selected.

to continue in next post: Interval partitioning for date column

simple and elegant..something new from 10g

One more time It happened..I was browsing within Oracle documentation for something else and found something else (of course, got deviated from intended search :))



Single quotes within a string literal is always a messy job in Oracle because you need to escape every single single quote within your string to make Oracle understand its a part of data.

SQL> conn venkat/venkat@ORCL

Connected.

SQL> select * from v$version;


BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production




here is the tradtional way I had known ever since

SQL> ed
Wrote file afiedt.buf

1 select
2 'it''s sunday night '||
3 to_char(sysdate,'hh:MI AM')||
4 '. I am testing single quoting ("''") in Oracle' as TestOutput
5* from dual
SQL> /

TESTOUTPUT
------------------------------------------------------------------------
it's sunday night 09:15 PM. I am testing single quoting ("'") in Oracle

Now here is the same using 10g's Q quote delimiter

SQL> ed
Wrote file afiedt.buf

1 select q'(it's sunday night )' ||
2 to_char(sysdate,'hh:MI AM')||
3 q'(. I am testing single quoting ("'") in Oracle)' TestOutput
4* from dual

SQL> /

TESTOUTPUT
----------------------------------------------------------------------------
it's sunday night 09:17 PM. I am testing single quoting ("'") in Oracle

No more messy escaping quotes.. Neat!! (and am I the only one to catch up with this 10g feature this late? Better late than never!!:))

Pivot or Unpivot? no big deal from 11g

pivoting and unpivoting resultsets - a kids game from 11g onwards

you dont need to be a sql guru to figure out how to transform rows into columns and vice versa.

here comes PIVOT and UNPIVOT clause of 11g.

I dont think I need to explain more..the below text is straight copy & paste from documentation.

Using PIVOT and UNPIVOT: Examples

The oe.orders table contains information about when an order was placed (order_date), how it was place (order_mode), and the total amount of the order (order_total), as well as other information. The following example shows how to use the PIVOT clause to pivot order_mode values into columns, aggregating order_total data in the process, to get yearly totals by order mode:

CREATE TABLE pivot_table AS
SELECT * FROM
(SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders)
PIVOT
(SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));

SELECT * FROM pivot_table ORDER BY year;

     YEAR      STORE   INTERNET
---------- ---------- ----------
     1990    61655.7
     1996     5546.6
     1997        310
     1998   309929.8   100056.6
     1999  1274078.8  1271019.5
     2000   252108.3   393349.4

6 rows selected.

The UNPIVOT clause lets you rotate specified columns so that the input column headings are output as values of one or more descriptor columns, and the input column values are output as values of one or more measures columns. The first query that follows shows that nulls are excluded by default. The second query shows that you can include nulls using the INCLUDE NULLS clause.

SELECT * FROM pivot_table
 UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
 ORDER BY year, order_mode;

     YEAR ORDER_ YEARLY_TOTAL
---------- ------ ------------
     1990 direct      61655.7
     1996 direct       5546.6
     1997 direct          310
     1998 direct     309929.8
     1998 online     100056.6
     1999 direct    1274078.8
     1999 online    1271019.5
     2000 direct     252108.3
     2000 online     393349.4
9 rows selected.

SELECT * FROM pivot_table
 UNPIVOT INCLUDE NULLS
   (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
 ORDER BY year, order_mode;

     YEAR ORDER_ YEARLY_TOTAL
---------- ------ ------------
     1990 direct      61655.7
     1990 online
     1996 direct       5546.6
     1996 online
     1997 direct          310
     1997 online
     1998 direct     309929.8
     1998 online     100056.6
     1999 direct    1274078.8
     1999 online    1271019.5
     2000 direct     252108.3
     2000 online     393349.4

12 rows selected.

thats neat!!

11g: One more to remember - CASE matters!!

until now , I always had the luxury of just remembering just my passwords and not its case. Because I think Oracle always took supplied password and apply some hashing function together with userid to generate a new passcode which is what gets stored under password column of dba_users or all_users.

Starting from 11g, oracle passwords are going to be case-sensitive. So its going to take a while for people like me to remember "OracleIsFun" is different than "oracleisFUN".

Another nice addition to Oracle 11g is new view called users_with_defpwd (or something similar)..this view list all the users whose password is supplied default one (scott/tiger). Now, I can already see - this is going to be extremely easy for database auditors (SOX ,HPAA whatever else ) to list the such user accounts ..I remember creating a script for 9i before, where I have to built my own array of known userid+password combinations and then trying to connect for every single known combination.

Oracle is really thinking ahead.

sequence of errors

I encountered this at one of my client's .. I usually get to see my inbox full of unread messages running for atleast 3 or 4 pages every time I get to check my account set up with this client because I usually get to read them only once in a week and one day I happened to see some mails with some ORA- error (4068 error to be exact) and that caught my attention.

what was happening was a few packages in Production were bombing with ORA-4068 error and there were back and forth emails between development and dba team on how to resolve it. The development team thought it was due to packages in INVALID state but they didnt know how does a package suddenly change the status to INVALID, they simply put the ball on DBA's court asking them to ensure & maintain the production objects with VALID status.
DBA team responded back they had to do DDL changes but replied back saying it may be due to partition maintenance operations which was all coded by DEV team..Eventually they were about to agree on creating a database job to check invalid objects every 15 minutes and automatically compile them..

what surprised me at the time I read all this long email thread was that no one cared to really dig in what the real cause of the problem but was ready to throw in suggestions to whatever problems they thought was creating the issue.




ORA-04068: existing state of packages has been discarded
is pretty simple and straight forward..clearly states your existing state of package has been discarded.. if you change a packag with global variables and some sessions has already stored the prev code, they had to flushed and reloaded because you changed the source code. Just simple as that..
I developed a test case and proved to DBAs and Development team that this can still happen if the status of packages are perfectly valid ..and actually the error was happening because the production releases were pushed in without bringing down client (web clients with connection pooling)..simply bringing down the clients before pushing new PLSQL code & then bringing up new connections would fix the issue without any single line of coding effort.

I didn't get any questions back from any team ..neither did I get any feedback on if they accepted my theory..but it was interesting that two teams were ready to solve an inexistent problem :)