I started a series of articles concerning the cost of queries (and as a consequence concerning the choices the optimizer makes). This post is a index of these articles. 1 – DB2 : Cost calculations 1 : Table Scan 2 – DB2 : Cost Calculations 2 : Index Only 3 – DB2 : Cost Calculations …
Index Why indexes Searching through millions of rows can be the right thing to do (TBSCAN). It might be the only option available to get the data you need.But in a lot of cases we need smarter ways to get to our data. If you need only one row,it seems silly to process thousands of …
Index index I would like to see all values of N, but I would like to see them in order. So, Oracle can not use the Index Fast Full Scan, since that operator doesn’t read the index in order. We need the Index Full Scan. The whole index must be read, in the right order, …
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 index Another way an index can be used to access a piece of data: Index Full Scan (Min/Max) If I’m only interested in the minimal or the maximum value of a column, and there is an index on that column, Oracle can use the Index Full Scan (Min/Max). This access-method starts at (ofcourse) the …
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 …
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 …
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 …
Let’s talk a little about the usage of indexes. Following access paths are considered: Index Unique Scan Index Range Scan Index Full Scan (Min/Max) Index Fast Full Scan (Index FFS) Index Full Scan
Let’s draw a table (any table). For simplicity I say: a block can only cater for 4 rows The table has 2 columns (ID and Text) Oracle assigns a rownumber within the block for every row If I would like to see all rows where ID=154, Oracle would have to look in all blocks, and …