Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle normal (b tree) index example (Read 9055 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle normal (b tree) index example
15.06.11 at 08:31:11
 
Oracle normal (b tree) index example
 
To explore the behavior of a normal Oracle index the following tests were carried out. Goal is to see how Oracle uses normal indexes on one or more columns.
 
-- Create a test table  
ccreate table test (a number, b number);
 
-- Insert some values. Note there are more distinct values in column b than in a.
insert into test values (1,1);
insert
into test values (1,2);
insert
into test values (1,3);
insert
into test values (1,4);
insert
into test values (2,5);
insert
into test values (3,5);
insert
into test values (4,5);

-- Create a couple of normal (B tree) indexes
create index test_a on test(a)
create index test_b on test(b)
create index test_ab on test(a,b)
create index test_ba on test(b,a)
 
-- Next let's perform some queries and inspect the execution plan.
select * from test
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=82      Bytes=2132
 TABLE ACCESS FULL      Object owner=LINKER      Object name=TEST      Cost=1      Cardinality=82      Bytes=2132
-- Indexes were not used
 
 
select * from test
 where a=1 and b=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=26
 TABLE ACCESS BY INDEX ROWID      Object owner=LINKER      Object name=TEST      Cost=1      Cardinality=1      Bytes=26
  INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_A      Cost=1      Cardinality=1      
-- Index a was used
 
 
select * from test
 where b=1 and a=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=26
 TABLE ACCESS BY INDEX ROWID      Object owner=LINKER      Object name=TEST      Cost=1      Cardinality=1      Bytes=26
  INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_A      Cost=1      Cardinality=1      
-- Index a was used  
 
 
select * from test
 where b=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=26
 TABLE ACCESS BY INDEX ROWID      Object owner=LINKER      Object name=TEST      Cost=1      Cardinality=1      Bytes=26
  INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_B      Cost=1      Cardinality=1      
-- Index b was used
 
 
select * from test
 where a=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=26
 TABLE ACCESS BY INDEX ROWID      Object owner=LINKER      Object name=TEST      Cost=1      Cardinality=1      Bytes=26
  INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_A      Cost=1      Cardinality=1      
-- Index a was used
  
-- Now let's drop index a and run the queries again.  
drop index test_a  
 
 
select * from test
 where a=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=26
 INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_AB      Cost=1      Cardinality=1      Bytes=26
-- Index ab was used although there is no condition on column b.
 
 
select * from test
 where b=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=26
 TABLE ACCESS BY INDEX ROWID      Object owner=LINKER      Object name=TEST      Cost=1      Cardinality=1      Bytes=26
  INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_B      Cost=1      Cardinality=1      
-- Index b was used
 
 
select * from test
 where a=1 and b=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=26
 INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_AB      Cost=1      Cardinality=1      Bytes=26
-- Index ab was used
 
 
select * from test
 where b=1 and a=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=26
 INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_AB      Cost=1      Cardinality=1      Bytes=26
-- Index ab was used and not index ba  
 
 
-- Now let's also drop index b  
drop index test_b    
 
select * from test
 where b=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=6
 INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_BA      Cost=1      Cardinality=1      Bytes=6
-- Index ba was used
 
 
-- Now let's also drop index ba  
drop index test_ba    
 
select * from test
 where b=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=1      Bytes=6
 INDEX FULL SCAN      Object owner=LINKER      Object name=TEST_AB      Cost=1      Cardinality=1      Bytes=6
-- INDEX FULL SCAN was used on index ab was used
 
 
select * from test
 where a=1
 
SELECT STATEMENT, GOAL = ALL_ROWS                  Cost=1      Cardinality=5      Bytes=30
 INDEX RANGE SCAN      Object owner=LINKER      Object name=TEST_AB      Cost=1      Cardinality=5      Bytes=30
-- Index ab was used
Back to top
 
« Last Edit: 15.06.11 at 08:50:23 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1