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 […]
Continue ReadingINDEX FULL SCAN (MIN/MAX)
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 […]
Continue ReadingIndex 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 […]
Continue ReadingIndex 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 […]
Continue ReadingIndex_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 […]
Continue ReadingIndex usage
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
Continue ReadingRestore and recover a database when everything is gone
Today someone deleted everything: datafiles gone, controlfile gone, spfile gone. What to do?…..not to worry….if you have backups (and the logging of those backups) you can get everything back 🙂 Lookup your DBID Lookup the autobackup file you want to use (probably the latest) Startup database nomount from a init.ora (spfile is also possible but […]
Continue ReadingRestore and recover a database when the controlfiles are missing
That’s a little more complicated. The controlfile (nocatalog) is the place where oracle stores all information regarding the backups. So no controlfile …. no restore/recover you might think…. If there is only one thing you take with you from this article it will be: make sure you have the logging of your backups handy. All […]
Continue ReadingRestore and recover a database when datafiles are awol
A sysadmin wasn’t paying attention and deleted a few datafile from the (running) database. What can we do? Actually we will do the same as here. Startup database in mount mode Restore the database Recover the database Open the database pffff….that was easy.
Continue ReadingRestore and recover a database up to a point in time
At (let’s say) around 08:30 this morning something (probably someone) messed up the database. I would like to go get back the database to 08:00 this morning: Startup the database in nomount For future reference: look at the incarnations we know Set an until time (this must be done in a run block) Restore the […]
Continue Reading