Distribution of data For calculating the right execution-plan Oracle often needs the distribution of values for a column. For an outlier value a index can easily be used. For a value that is present in half the rows a index might be less effective. For a similar exercise I needed the distribution of values myself. […]
Continue ReadingCreating an ACL
A very simple example of creating an ACL for a principal and a host: Creating the ACLS (qith the associated principal) itself. exec dbms_network_acl_admin.create_acl( acl=>’print_permissions.xml’, description=>’Gebruikt voor Print functionaliteit’, principal=>’APEX_230100′, is_grant=>TRUE, privilege=>’connect’); Associating a host to the ACL: exec dbms_network_acl_admin.assign_acl( acl=>’print_permissions.xml’, host=>’machine-name.domain.domain’, lower_port=>8010, upper_port=>8010); This is a starting point. You can go from here … […]
Continue ReadingDB2 : Cost Calculation 3 : index only revisited
Index Why In the DB2 : Cost Calculation 2 : index only post I noticed something upsetting, well, at least it was upsetting to me. We looked at the explain plan for looking up 1 value by an index. The index had nlevel=3. So there is a root-node, some intermediate-nodes and finally finally some leaf-nodes. […]
Continue ReadingDB 2 : Cost calculations 1 : Table Scan
Index Why? When you supply DB2 with a query DB2 has to make some serious decisions: – Should indexes be utilized? – Which indexes are most suitable? – Which join operation should be used? (hash join/nested loop) – Is all data needed at once or are the first 10 rows as quick as possible (the […]
Continue ReadingDB2 Row generator
Sometimes you need a mechanism to generate rows. For example when you need to create a demonstration table . Recursive CTE’s (Common Table Expression) might be your friend WITH engine (rownum) AS (select 1 as rownum from sysibm.sysdummy1 union all select rownum + 1 AS rownum from engine where rownum<10) SELECT rownum FROM engine; […]
Continue ReadingRecursive SQL in DB2
Problem Playing around in DB2 I wanted to know how to create a hiearchical query. This because I wanted to create view for explain plans, a bit resembling the explain plans which oracle provides. First step is understanding how to make the recursive SQL’s The setup: drop table relations; create table relations (id int, parent […]
Continue ReadingCreating a (demo)table
Often I need to create a table just for demonstration puposes, and this table needs to be filled up with data. The question for this post is…..: How to generate rows, preferrably a distinct number. So what I do? If the table has to be created yet: Or if the table allready exists: Did I […]
Continue Reading