Oracle

Session statistics

You can have a look at v$sesstat (joined with v$staname) to see the value of some statistics at this moment. What is a little more difficult to see is how statistics change over time. So I wrote a script for it. What it does: Gather all statistics, of one or all sessions, and put them …

Index Fast Full Scan

Index index Today I want to query our table again. We alter the table so column N is not null. alter table index_demo modify n not null Table altered. desc index_demo Name Null? Type —————— ——– ————— N NOT NULL NUMBER D NUMBER M NUMBER S VARCHAR2(4000) What I would like to see is all …

Index Range Scan

Index index OK, let’s talk about the Index Range Scan. The word range reflects that Oracle will scan through (a part) the index. As opposed to an Index Unique Scan where Oracle exactly knows what to look for and where, it just decends through the tree-structure. The Index Range Scan does not know how many …

Index Unique Scan

Main Post The Index Unique Scan is used when one unique piece of information (row) is needed. A primary key would be a perfect example. Since a primary key is 1) Not Null and 2) Unique. The uniqueness can be realized with a index…a unique index. This is actually a normal btree index, with the …

Index_demo data setup

In a number of posts about indexes I use a demo table with some indexes. Here is how it is created: drop table index_demo; create table index_demo as with engine as (select level l from dual connect by level<=1e5) select l n, round(l/100) d, mod(l, 100) m, dbms_random.string(‘l’,25) s from engine; prompt create indexes create …