Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Create Oracle Indexes

Description

This exercise will give you an opportunity to see how the SQL optimizer accesses indexes to generate an efficient access plan.

Procedure

Your assignment involves creating these indexes in your pubs schema and noting how they change the execution plan for your SQL.  Because the sample database is so small, the cost-based optimizer will choose full-table scans because it knows that the tables reside on only a few Oracle database blocks.  Hence, we will force the use of the indexes with an index hint inside the SQL statement.

Step 1 – Save these SQL queries as test_idx.sql in your c:\Burleson directory

select
   initcap(store_name),
   store_state
from
  store
where
   lower(store_name) = lower('Borders')
;

Step 2 – Run this script and save the execution plan for the queries.

Step 3 – Create two indexes on the store table, as follows.

create bitmap index
   Store_state_idx
on
   store
( store_state );

create index
   store_name_idx
on
   store
lower(store_name);

Above, we create a bitmap index on store_state.  Because there are only 50 distinct column values, we can use a bitmap index.

We also create a function-based index on store_name because out SQL is transforming the column using the lower BIF.

Step 4 – Re-run the query in test_idx.sql and note all changes to the execution plan.

Evaluation

Submit the execution plan listing (with and without indexes), and submit a one paragraph summary of the index usage to you instructor by due date.

ANSWERS

WITHOUT INDEXES

SQL> select
  2  initcap(store_name),
  3  store_state
  4  from
  5    store
  6  where
  7  lower(store_name) = lower('Borders')
  8  ;

INITCAP(STORE_NAME)                      ST                                    
---------------------------------------- --                                    
Borders                                  NC                                     

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=1 Bytes=18)       
   1    0   TABLE ACCESS (FULL) OF 'STORE' (Cost=1 Card=1 Bytes=18)          

SQL>
SQL> select
  2  initcap(store_name)
  3  from
  4  store
  5  where
  6  store_state = 'NC';

INITCAP(STORE_NAME)                                                            
----------------------------------------                                       
Barnes And Noble                                                                
Borders                                                                        
Quagmire Books                                                                 
Wee Bee Books                                                                   
Books For Dummies                                                              
Specialty Bookstore                                                            
Blue Ride Booksellers                                                           
Ignoramus And Dufus                                                            

8 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=5 Bytes=90)
   1    0   TABLE ACCESS (FULL) OF 'STORE' (Cost=1 Card=5 Bytes=90) 
          

WITH INDEXES

SQL> select
  2  initcap(store_name),
  3  store_state
  4  from
  5  store
  6  where
  7  lower(store_name) = lower('Borders')
  8  ;

INITCAP(STORE_NAME)                      ST                                    
---------------------------------------- --                                    
Borders                                  NC                                    


Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=60 Card=1 Bytes=18)
   1    0   VIEW OF 'index$_join$_001' (Cost=60 Card=1 Bytes=18)               
   2    1     HASH JOIN
   3    2       INDEX (FAST FULL SCAN) OF 'STORE_NAME_IDX' (NON-UNIQUE) (Cost=33 Card=1                 Bytes=18)
   4    2       BITMAP CONVERSION (TO ROWIDS)
   5    4         BITMAP INDEX (FULL SCAN) OF 'STORE_STATE_IDX'                

SQL>
SQL> select
  2  initcap(store_name)
  3  from
  4  store
  5  where
  6  store_state = 'NC';


                                  Book Report

INITCAP(STORE_NAME)                                                            
----------------------------------------                                       
Barnes And Noble                                                               
Borders                                                                        
Quagmire Books                                                                 
Wee Bee Books                                                                  
Books For Dummies                                                              
Specialty Bookstore                                                            
Blue Ride Booksellers                                                          
Ignoramus And Dufus                                                             

8 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=17 Card=5 Bytes=90)
  1    0   TABLE ACCESS (BY INDEX ROWID) OF 'STORE' (Cost=17 Card=5 Bytes=90)
  2    1     BITMAP CONVERSION (TO ROWIDS)
  3    2       BITMAP INDEX (SINGLE VALUE) OF 'STORE_STATE_IDX'               

* Temporary tables were formally introduced as global temporary tables in Oracle8i.  Temporary tables can be used to significantly improve SQL query speed.

* Oracle materialized views can be used to pre-aggregate data and pre-join tables into summary tables.  The Oracle database will automatically detect when these pre-summarized tables can be used, and re-write the SQL to reference the SQL.

* Indexes are created exclusively to improve the performance of SQL statements.  Most relational databases provide b-tree, bitmap, and function-based indexes to ensure that SQL never performs unnecessary large-table full-table scans.

We are now ready to move into more advanced SQL topics and examine extensions to SQL and how they are used to improve the capabilities of SQL.

 

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.