=========================================================== -- Hands-on-exercises - Indexes -- -- 0.- Introduction -- 1.- B-Tree indexes -- 2.- Function based indexes -- 3.- Bitmap indexes -- 4.- Composite indexes =========================================================== =========================================================== 0.Introduction =========================================================== There are several flavors of indexes: - B-Tree (default) - Function based - Bitmap - Composite We are going to overpass all of them and see their importance when designing a database. For each query check it execution plan to see the usage or not of the indexes Prepare your environment, if you have not done yet: bash$ sqlplus oradbXX/oracle SQL> set pagesize 100; -- to show 100 rows before showing again columns name SQL> set linesize 130; -- to split lie after 130 characters SQL> set timing on; -- to show time spent on the queries We are going to use a big table in the schema with more than 200 thousand rows named BIGEMP. =========================================================== 1.B-Tree indexes Primary key and Unique columns are automatically indexed with a B-Tree index. Foreign key columns and columns used in WHERE clause of the queries should also be indexed. =========================================================== Imagine we want to list all Clerks on table: SQL> SELECT * FROM bigemp WHERE job = 'CLERK'; See the execution plan. SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE job = 'CLERK'; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); It was necessary a full table scan. Now we create an index on the job column: SQL> CREATE INDEX idx_bigemp_job ON bigemp(job) TABLESPACE training_indx; And re-run the query: SQL> SELECT * FROM bigemp WHERE job = 'CLERK'; It should be much faster now. Check the execution plan: SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE job='CLERK'; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); And you see the index was used. =========================================================== 2.Function based indexes When the WHERE clauses include functions (e.g. UPPER(name) or operations (e.g. sal+1000) then is useful to have function based indexes. =========================================================== We have a usual search by name case insensitive: SQL> SELECT * FROM bigemp WHERE UPPER(ename) LIKE 'MATT MIKE%' AND deptno = 30; See the execution plan: SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE UPPER(ename) LIKE 'MATT MIKE%' AND deptno = 30; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); It was necessary a full table scan. Create an B-Tree index on the deptno column to see if it helps. SQL> CREATE INDEX idx_bigemp_deptno ON bigemp(deptno) TABLESPACE training_indx; Proceed again to the select to see if it's faster and what is the execution plan. SQL> SELECT * FROM bigemp WHERE UPPER(ename) LIKE 'MATT MIKE%' AND deptno = 30; SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE UPPER(ename) LIKE 'MATT MIKE%' AND deptno = 30; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); But what-if we create a function index on UPPER(ename)? SQL> CREATE INDEX idx_bigemp_upper_ename ON bigemp(upper(ename)) TABLESPACE training_indx; Doing again: SQL> SELECT * FROM bigemp WHERE UPPER(ename) LIKE 'MATT MIKE%' AND deptno = 30; SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE UPPER(ename) LIKE 'MATT MIKE%' AND deptno = 30; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); There should be no difference in time or in the execution plan. Why? For the function based indexes to work it is necessary that you gather statistics on the index, so Oracle optimizer recognizes it as a good option. It is also good practise to gather statistics on table level (including indexes) always there is a big change on the table. The following command gather statistics on the table including indexes (CASCADE option). SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL, TABNAME=>'BIGEMP' , ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE); Doing again: SQL> SELECT * FROM bigemp WHERE UPPER(ename) LIKE 'MATT MIKE%' AND deptno = 30; SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE UPPER(ename) LIKE 'MATT MIKE%' AND deptno = 30; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); For sure you realized the difference. And look that only the function based index was used. =========================================================== 3.Bitmap indexes For joining columns with low cardinality (low number of distinct values) we should use bitmap indexes. They don't ocupy much space and are very fast. =========================================================== Imagine the search of persons by manager and department. For example: SQL> SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566; SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); It should perform a full table scan. Lets create bitmap indexes on the target columns. Target columns are the ones that appear in the WHERE clause without range limitation (<, >, BETWEEN) and that have less than 1% of different values. Check if the "mgr" column fits the <1% of different values: SQL> SELECT COUNT(DISTINCT(mgr)), COUNT(*) FROM bigemp; There are only 14 different roles while there are more than 200 thousand rows. Definitifely candidate. SQL> CREATE BITMAP INDEX bidx_bigemp_deptno ON bigemp(deptno) TABLESPACE training_indx; SQL> CREATE BITMAP INDEX bidx_bigemp_mgr ON bigemp(mgr) TABLESPACE training_indx; And try the select again: SQL> SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566; SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); The optimizer did a BITMAP AND over the two bitmap indexes and return the rows from the table. =========================================================== 3.Composite indexes When each condition in the WHERE clause returns thousands of rows but the cross of the conditions return 1 or very few of them, then we should think about composite indexes. Also, as the indexes are 'alphabetically' ordered, think about using composite indexes for cases of counting over a condition. =========================================================== For this example we need to drop the previous created bitmap indexes. SQL> DROP INDEX bidx_bigemp_deptno; SQL> DROP INDEX bidx_bigemp_mgr; Back to the previous example but adding a ORDER BY at the end: SQL> SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566 ORDER BY ename; Full table scan as expected. Why not create three normal B-Tree indexes? Let's try: SQL> CREATE INDEX idx_bigemp_deptno ON bigemp(deptno) TABLESPACE training_indx; SQL> CREATE INDEX idx_bigemp_mgr ON bigemp(mgr) TABLESPACE training_indx; SQL> CREATE INDEX idx_bigemp_ename ON bigemp(ename) TABLESPACE training_indx; SQL> SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566 ORDER BY ename; SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); Not much performance improvement. Oracle picks the index with more selectivity and then does the rest over the table as in the last example. Let's see how many rows each condition retrieves: SQL> SELECT a.totdept10, b.totmgr7566 FROM (SELECT COUNT(*) totdept10 from bigemp where deptno = 10) a, (SELECT COUNT(*) totmgr7566 from bigemp where mgr = 7566) b; However we saw that the complete query returns only seven records. We should try to create a composite index: SQL> CREATE INDEX idx_bigemp_ename_deptno_mgr ON bigemp(ename, deptno, mgr) TABLESPACE training_indx; SQL> SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566 ORDER BY ename; SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); But what's wrong? He did not use the index. On composite indexes the important factor is the order of the columns on the index. First should be the columns on the WHERE clause with higher selectivity and the last the column on the ORDER BY clause. SQL> CREATE INDEX idx_bigemp_mgr_deptno ON bigemp(mgr, deptno, ename) TABLESPACE training_indx; SQL> SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566 ORDER BY ename; SQL> EXPLAIN PLAN FOR SELECT * FROM bigemp WHERE deptno = 10 AND mgr = 7566; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); It used the index now and the performance very good. =========================================================== Please clean up indexes. Copy paste the result of: SQL> SELECT 'DROP INDEX '|| index_name ||';' FROM user_indexes WHERE table_name = 'BIGEMP'; =========================================================== And that's all folks! :-)