Bulk update in Oracle

When you've millions of records in your system and you want to update rows in a table, it requires to take extra care. There are two options while bulk update
* Update in a single shot - It is faster and easy to do it. But it becomes a high risk when the update statement modifies millions of records (like 10 millions). You'd be stuck with a single update, you'd not be able to see the progress and most of all, your redo log can overflow and other system accessing the database would be heavily slowed down. Your database might even crash in worst case scenario. Below is a sample update query with this approach:

UPDATE [table]
SET [column_name] = 'some value'
WHERE [some clause]


* Update in chunk - It is a slow process but you can overcome the limitations and risks that one shot update might face. The only down side with this approach is it is really a slow process. it could take 10 or 20 times comparing with the first approach. Below is a sample update query with this approach:

SET SERVEROUTPUT ON SIZE 1000000

BEGIN
LOOP
UPDATE [table]
SET [column_name] = 'some value'
WHERE [some clause]
AND ROWNUM <= [how_many_at_a_time];
DBMS_OUTPUT.PUT_LINE('Done xxxx deletion!');
EXIT WHEN SQL%NOTFOUND;
COMMIT;
END LOOP;
END;


Resources:

http://oracle-online-help.blogspot.com/2006/12/delete-in-batches.html - delete in batch

Few interesting concepts deep inside the Java Virtual Machine (JVM)

Here are few interesting concepts that are implemented in the JVM:

1. Encapsulation has two modes. Static and Dynamic. Static encapsulation is the regular encapsulation that enforces of accessing attributes of a java class e.g. access modifier provides capability of visibility whereas Runtime Encapsulation makes it sure the attributes are accessible by a class during runtime inside JVM. This Runtime Encapsulation is enforced by the classloader i.e. package friendly access modifier woudn't be honored if both of the classes are not loaded by the same classloader.

2. OxCAFEBABE is the firest 4 bytes of a java class file in byte code format

Resources:

http://www.artima.com/insidejvm/ed2/introarch.html - JVM Architecture online book

http://betterexplained.com/articles/understanding-big-and-little-endian-byte-order/

Oracle Tips - DDL

1. Add column to a table

ALTER TABLE table_name
ADD column_name data_type constraint;

2. To add multiple columns to a table -

ALTER TABLE table_name
ADD (
column_name data_type constraint,
column_name2 data_type constraint
);


3. Rename column

ALTER TABLE table_name
RENAME COLUMN column_name to column_new_name;

4. Modify column

ALTER TABLE table_name
MODIFY ( column_name data_type constraint);

5. Drop column

ALTER TABLE table_name
DROP COLUMN column_name;

6. Drop multiple columns

ALTER TABLE table_name
DROP (
column_name1, column_name2, ....);

7. Index Management

To see the constraints of a table:

select table_name, constraint_name, constraint_type from user_constraints;

To see the index of a table

select table_name, index_name, column_name from user_ind_columns;

Create index

create [bitmap] index index_name on table_name
(column_name)

Find and Rebuild Index

select * from user_indexes
Alter Index rebuild

Analyze index and table

Analyze index [index-name] [compute/delete/validate] statistics
Analyze table [table-name] [compute/delete/validate] statistics

8. Manage privileges on table

GRANT select, insert, update, delete on SCHEMA.TABLE_NAME to ROLE/USER

9. Create Synonym of table

CREATE OR REPLACE [public] SYNONYM SYNONYM_NAME for SCHEMA.TABLE_NAME

Check the synonyms created by the user

select * from all_synonyms

10. Manage Constraint
To see the constraints of a table:

select table_name, constraint_name, constraint_type from user_constraints;

Drop constraint

alter table table_name
drop constraint constraint_name

Add Foreign Key constraint to a table

Alter table Add Constraint
Foreign Key (column) References
On cascade;

11. Create CHECK constraint on a column

ALTER TABLE TABLE_NAME
ADD CONSTRAINT CHK_Y_N
CHECK (COLUMN_NAME IN ('Y', 'N'))
/

12. Partitioned Table



Resources:
https://www1.columbia.edu/sec/acis/dba/oracle/faq-sql.html
http://www.dba-oracle.com/ - has useful queries
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:969054700346631425 - Bulk move
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:273215737113 - Update columns from other table
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:974699100346868838 - should we use db user or application user?
http://www.adp-gmbh.ch/ora/misc/integrity_constraints.html - Oracle's Integrity Constraints
http://jenniferlinca.wordpress.com/ - Blog contains good Oracle stuffs
http://forums.oracle.com/forums/message.jspa?messageID=2348825 - Fun to read
http://www.dba-oracle.com/art_9i_indexing.htm - Performance tuning with index
http://www.orafaq.com/node/855 - Common errors using Outer Join
http://marist89.blogspot.com/ - Wonderful quote from the site - "The trouble with the world is that the stupid are cocksure and the intelligent are full of doubt." --Bertrand Russell. A good database blog
http://www.orafaq.com/forum/t/54714/0/ - Batch delete
http://www.dba-oracle.com/oracle_news/2004_1_19_using_function_based_indexes%20.htm - Function based index
http://www.oracle-dba-online.com/sql/oracle_table_partition.htm - Partitioned table