EnterpriseDB: DROP TABLE Oracle Tips by Burleson Consulting

Drop the table emp.  emp has a foreign key relation to dept.  You cannot normally drop a table that has a dependant object (such as a foreign key).  The CASCADE keyword forces the database to drop the dependency (in this case, the foreign key not the other table) and then drop the table 

DROP TABLE emp CASCADE;

Drop the dept table.

DROP TABLE dept;

Indexes

An index on a table helps queries run faster.  If you think of the index in a book, you can flip to the back of a book, scan through a handful of pages for a specific topic and then jump right to the page you are looking for.  Without that index, you would have to scan each page in the book to find that one topic.  If the topic is on the last page, you have done a full book scan.

An index on a table works much like an index in a book but instead of storing a page number, the index stores a pointer to the specific row in the table.  Like a book index, a table index is stored in sorted order.  Sorted order may mean alphabetical order for character data or numerical order for numeric data.  It would mean date order (with earlier dates coming first) for date and timestamp fields.

If you are looking for a specific row, an index will point you to a specific row so that you don't have to look at every row in the table.  A query that returns one row will get more benefits from an index than a query that returns multiple rows.  The higher the percentage of rows returned (rows returned / rows in the table), the less useful the index becomes.

Returning to our book analogy, let's say that you have a 500-page book and you want to find all pages that say "EnterpriseDB".  If you look at the index and it appears on only a single page, you have found the page you want.  If it says it appears on 10 pages, you will have to look at up to 10 pages but that's a lot better than 500 pages.  What if it appears on 100 pages?  Or 400 pages?  At some point, it's just easier, and probably faster, to scan through the book yourself.

The same is true with a table index.  The exact percentage where a useful index becomes a nuisance is not a hard and fast rule. There are many considerations such as work load, query frequency, data distribution, etc. but a general rule of thumb would be that if more than 30% of the rows in the table will be returned, you will not benefit by creating an index.

An index on a table also has overhead and the maintenance of that index can slow down other operations.  For example, when you add a new row, every index on the table must be altered to add the new row.  Because indexes are sorted (that's what makes them fast to search), each index has to be searched for the right place to put the row, space has to be made for the row and the pointer has to be added.  If a table has 10 indexes, all 10 must be maintained.  Updates and deletes work a bit differently, but those operations do generate system overhead.

An additional item to note about indexes, small tables really do not benefit from indexes.  When a table is small, scanning the whole thing is probably faster than scanning an index and then going after the correct row in the small table (two disk activities instead of one).  In addition, if a table is small, it will be searched on disk the first time and put in memory.  If the table is frequently used, it will stay in memory and all future searches will be done in memory.  Memory is much faster than disk and is another reason not to create an index on a small table.  Unfortunately, there is no hard and fast rule as to what defines "small".  If it's less than a couple of hundred rows, you don't need an index.  If it's less than a few thousand, you should benchmark it on your hardware.  Over that and you will probably benefit from the index.

Most databases provide multiple flavors of indexing.  Different index types use different coding techniques and storage mechanisms.  In EnterpriseDB, the b-tree index is the index type with which you will be most concerned.  A b-tree is a general-purpose index and is probably the most common index in database land.

When you create an index in EnterpriseDB, with the CREATE INDEX command, a b-tree index will be the default.  Under some very specific circumstances, you might benefit from an r-tree or a hash index.  The situations that benefited by those index types are so rare that I will not cover them further in this book. 

CREATE INDEX

CREATE [ UNIQUE ] INDEX <index name>
ON <table name>
[ USING {btree|hash|rtree|gist} ]
    ( { <column name> | ( <expression> ) }
    [ TABLESPACE <tablespace> ]
    [ WHERE <where criteria> ]

A query like this:

SELECT ename
  FROM emp
  WHERE empno = 123;

would be helped by an index on the empno column.  Here you are looking for all records where empno is equal to 123.  Each empno that was equal to 123 would have a corresponding entry in the index with a row pointer.  You would create an index for user with:

CREATE INDEX empno_idx ON emp (empno);

Indexes are also useful when you are doing a RANGE SCAN, by that I mean that you are doing a search for a range of records:

SELECT ename
  FROM emp
  WHERE empno BETWEEN 123 and 567;

In this case, you are looking for all records where the empno is greater than or equal to 123 and less than or equal to 567.  Because the index is sorted, the database would scan the index until it found 123 and then scan until it found 567 and return the row pointers between those two points.  The index created above, empno_idx, would also work with a range scan.

An index can contain one or more columns.  If your queries are frequently using AND to combine multiple columns in a where clause:

SELECT ename
  FROM emp
  WHERE empno = 123
      AND job = 'CLERK';

You may want an index on empno and job (with empno as the leading column).  The database will search for a match on the empno and then for that empno it will scan for the job CLERK.

CREATE INDEX empno_job_idx ON emp (empno, job);

Having multiple columns in an index does not help if you frequently use OR:

SELECT ename, empno, job
  FROM emp
  WHERE empno = 123
       OR job = 'CLERK';

In this case, if you had an index on empno and clerk, with empno as the leading column, the database could use the index to find empno = 123 but it would need to search the entire table to find job  = 'CLERK'.  Since it is going to full scan the table anyway, it makes sense to not use the index at all and just scan the table.

If you had an index on empno and a separate index on job, the database could use both indexes and create what is known as an in memory bitmap, or a bitmap scan, to combine the two indexes into one.  The result would be faster than a full table scan, but slower than a single scan of an index.

You would define your indexes like a normal index.  You don't have to do anything special to allow them to be turned into a bitmap scan.  EnterpriseDB will do that automatically if the database decides that will be the fastest method to access your data.

CREATE INDEX empno_idx ON emp (empno);
CREATE INDEX job_idx ON emp (job);

You can also create function-based indexes (FBI), also called Expression Indexes.  An FBI is an index created with a SQL function or expression in the create statement that would match the function or expression in a query.  For example, if you wanted to search the emp table for all names that are equal to 'lewis' (regardless whether or not the data is stored in upper case, mixed case or lower case), you would execute this query:

SELECT ename, empno
  FROM emp
  WHERE lower(ename) = 'lewis';

If you don't use the LOWER() function to force the ename column to lower case, your query would not find records where ename was equal to 'Lewis'.  However, if your index was on ename, you would not be able to use it because lower(ename) is not the same as ename.  If you are going to query frequently on lower(ename), you should create your index with the lower function as part of the index creation statement, i.e.

CREATE INDEX lowerename_idx ON emp (lower(ename));

You can use SQL functions (functions that come with the database) or you can write your own functions with SQL, PL/SQL, PL/pgSQL or one of the other supported languages.  You can also embed expressions such as:

SELECT deptno, dname, loc
  FROM dept
  WHERE (dname || ' - ' || loc) = 'HR - New Orleans'; 

CREATE INDEX dname_loc_idx ON dept ((dname || ' - ' || loc));

The double parenthesis (()) is required for expressions.

Earlier in this section (under CREATE TABLE and ALTER TABLE), I spoke about adding constraints to a table.  A Unique Constraint at the table level (or a Primary Key) forces all rows in the table to be unique based on that column or set of columns.  This constraint is implemented via a Unique Index.  You can create a unique index using the CREATE UNIQUE INDEX command:

CREATE UNIQUE INDEX emp_pk ON emp (empno);

Even though you can create an index in this manner, creating a constraint while creating the table, or afterwards with an ALTER TABLE, is the preferred method of doing so.

Another nifty feature of indexes in EnterpriseDB is the ability to create a partial index.  A partial index includes only a subset of a table's records.  IF you have a wide variety in your data distribution, using a partial index probably won't help you.  However, if you have a table where most of the records are a particular value (a high percentage of the table), you can exclude that value from the index.  Remember that an index helps when you are accessing a small portion of a table's records, not all of them.

Let's say that the dept table had 5000 records and 90% of those records had a LOC of 'Tampa, FL'.  The other 10% of the records are spread out amongst 10 other cities.  It might make sense to create a partial index that excluded 'Tampa, FL' since any query for 'Tampa, FL' should do a full table scan anyway.

CREATE INDEX tampa_dept_loc_prtidx ON dept(loc) WHERE loc <> 'Tampa, FL';

If we query:

SELECT *
  FROM dept
  WHERE LOC = 'New Orleans, LA';

We would use the partial index.

If we queried:

SELECT *
  FROM dept
  WHERE LOC <> 'Tampa, FL';

We would use the partial index.

If we queried:

SELECT *
  FROM dept
  WHERE LOC <> 'New Orleans, LA';

We would not use the index and would instead do a full table scan.

If we queried:

SELECT *
  FROM dept
  WHERE LOC = 'Tampa, FL';

We would not use the index and would instead do a full table scan.

Partial indexes can be tricky at times.  If you choose to use a partial index, I would recommend that you test your cases well.  Pay particular attention to the border cases.

ALTER INDEX
ALTER INDEX <index name>
    RENAME TO <new index name>
    OWNER TO <new owner>
    SET TABLESPACE < new tablespace>

There are only three ways to alter an index: rename it, change the owner or assign it to a different tablespace.

Rename an index:

ALTER INDEX empno_idx RENAME TO emp_empno_idx;

Move an index to a new tablespace:

ALTER INDEX empno_idx SET TABLESPACE hr_idx_tbs;

Change an index owner.  This might be useful if an admin user creates an index and wants to allow a less privileged user do the maintenance on it.  I have never needed to change owners.

ALTER INDEX empno_idx OWNER TO tom;

GRANT INDEX

You cannot grant to an index.  You would grant to a table that uses the index.

REVOKE INDEX

You cannot revoke permissions from an index.  You would revoke permissions from a table that uses the index.

DROP INDEX

Drop the index empno_idx.  If another object has a dependency on the index, you need to use the CASCADE keyword, which will force the database to drop the dependency and then drop the index.  I don't think I have ever had a case where I needed to use CASCASE to drop an index.

DROP INDEX empno_idx CASCADE;

Or, more commonly:

DROP INDEX empno_idx;

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.