Great Oracle RLS article

Posted by David Yahalom under Security, Oracle

Yesterday I stumbled across a great article about Oracle Row Level Security (RLS) with a great and detailed beginners tutorial.

RLS is a great way to implement application security where many users must connect to the same data but only see different parts of the data based on their role or position in the organization.

In many cases RLS is easier to implement and maintain compared to using views, tirggers or using different schema for data access as business rules and security implementations are done through one PL/SQL procedure per table instead of being spread throughout the applications code.

Another great benefit of RLS is tighter security than what views or triggers provide because RLS can prevent a user from circumventing the security policy by accessing the database directly and query the tables underlying the views.

Oracle Row Level Security: Part 1

Is my DB2 optimizer a tad drunk?

Posted by David Yahalom under DB2 LUW

Today I have noticed a strange phenomena in one of our DB2 databases. When doing a low zero-selectivity query (that is, query without a WHERE clause), the DB2 optimizer preferred to use an index scan rather than do a table scan.

That seemed a bit strange so I tried running the same query twice. Once without any indexes on the underlying table (to force a table scan) and then again with (to allow the optimizer use an index).

The results really surprised me.

According to the optimizer, the table scan query will perform slower than the index scan query.

I know that with Oracle systems, the rule of the thumb is that using a full table scan would perform faster than an index scan when the total number of rows retrieved from a table is between 1% and 15% of table data.

It is very logical. If the database has to read the entire table wouldn’t it be faster just to read the blocks directly from the table container rather than doing an INDEX READ + ROWID lookup for the entire table data?

The way I see it:

SELECT * FROM TABLE

Performing a TABLE SCAN to get the data: X I/O blocks read from the table.
Performing an INDEX SCAN to get the data: X I/O blocks read from the table + Y I/O blocks read from the index.

Or am I wrong?

Any of you can shed some light on why the DB2 optimizer reports the table scan as slower than an index scan in this case?

Here is the real world example of what I mean:

SELECT COUNT *
FROM TABLE1;

Using an INDEX:

Access Plan:
-----------
Total Cost: 72284.4
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
95047
FETCH
( 2)
72284.4
26250.8
/---+---
95047 95047
RIDSCN TABLE: USER
( 3) TABLE1
556.668
275.886
|
95047
SORT
( 4)
556.667
275.886
|
95047
IXSCAN
( 5)
427.788
275.886
|
95047
INDEX: USER
TABLE1_IDX1

Without an INDEX:

Access Plan:
-----------
Total Cost: 73526
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
95047
TBSCAN
( 2)
73526
27725
|
95047
TABLE: USER
TABLE1

Leverage your Oracle 10g skills to learn DB2 9.1 for Linux, UNIX and Windows

Posted by David Yahalom under DB2 LUW, Oracle

Leverage your Oracle10g skills to learn DB2 9.1 for Linux, UNIX and Windows.

This article makes direct comparison between the Oracle architecture (instance, databases, physical files, network, configuration files, etc) and the DB2 architecture thus allowing Oracle DBAs to understand the similarities and differences between Oracle and DB2.

Because of its comparative nature (showing you how the same things work in DB2 compared to Oracle) I think its a great first read if you are an Oracle DBA new to DB2.

I do have some comments I would like to add:

1) IBM says:

“DB2 does contain a binary file known as the system database directory that contains entries of all the databases you can connect from your DB2 machine.”

This compares best to Oracle tnsnames.ora file. I don’t understand why IBM didn’t draw this comparison.

2) IBM says:

“Every Oracle database contains a table space named SYSTEM, which Oracle creates automatically when the database is created. Other table spaces for user, temporary and index data need to be created after the database has been created, and a user needs to be assigned to these table spaces before they can be used.”

While I get IBMs point (that you have to create tablespaces manually after the create database command) This is not technically true. Frist, when you issue a create database command in Oracle you explicitly specify the creation of a temp tablesapce. Second, after the database creation all other tablespaces you have created can be used without being “assigned to users” simply by using them in the storage clause of the object creation statement.

3) IBM Says:

“As indicated earlier, Oracle’s data buffer concept is equivalent to DB2’s bufferpool; however, DB2 allows for multiple bufferpools to exist. There is no predefined number of bufferpools that you can create, and they can have any name.”

Again, not entirely true. While less flexible, Oracle 9i/10g allows for multiple db buffers with different block size. The limitation is one buffer per one block size while in DB2 it is one buffer per tablespace.