Re: Individual Indexes v.s. One "combinded" Index

From: Carlos Netto <Carlos.Netto_at_ibase.org.br>
Date: Sat, 8 Oct 94 21:42:00 -0300
Message-ID: <2aa.456.846%mpcbbs_at_ibase.org.br>

On 8 Oct 94 11:58am, David Hiltz wrote to ALL:

 DH> Newsgroup: comp.databases.oracle
 DH> Organization: Woods Hole Oceanographic Institution

 DH>     I just read in the "Oracle 7 Server - SQL Language Reference 
 DH>     Manual", pg. 4-162 ("Index Columns") that if you have a "combined 
 DH>     index" of column A,B and C on a table and you reference column B; 
 DH>     C; or B,C - the index is not used.  You would have to reference 
 DH>     from left to right (ie. A; A,B; or A,B,C). 

 DH>     I created six individual indexes on a table and the indexes used a 
 DH>     total of 7.5Megs.  One index that contained the six columns used 
 DH>     2.3Megs. 

 DH>     So its a trade off of disk space v.s. use of index.  

There's a big difference in performance between these two aproaches. Supose you have a primary key with 6 columns, each column with values between 0 and 9. Supose this table has 1,000,000 columns.

If you search for the value (1,6,3,9,2,0) using a index on (c1,c2,c3,c4,c5,c6), Oracle will make a unique scan, very quickly.

But, if you search for the same value, using one index for each column, Oracle will:

1. search for value 1 -> 10,000 rows scan (in avarange, of course)
2. search for value 6 -> 10,000 rows scan (in avarange, of course)
3. search for value 3 -> 10,000 rows scan (in avarange, of course)
4. search for value 9 -> 10,000 rows scan (in avarange, of course)
5. search for value 2 -> 10,000 rows scan (in avarange, of course)
6. search for value 0 -> 10,000 rows scan (in avarange, of course)

to make a intersect of the six above results (AND-EQUAL operation). Of course, Oracle can make something better (I didn't execute EXPLAIN for this statement), but be sure nothing as good as a unique scan.

The lesson: don't think that a scan using index is aways good. Unique scans are fast, but range scans may not aways be fast. Take care!

bye,

   ----->>>>>>>>>>>> Don't reply. Reply will not work! <<<<<<<<<<<<<<----

---

| Carlos Augusto Leite Netto | Voice : +55.192.51.1153 |
| Software Design Informatica | Fax : +55.192.54.6518 |
| Oracle VAR | e-mail : carlos.netto%mpcbbs_at_ibase.org.br |
| Software Development & DBA | FidoNet: 4:801/31 (Carlos Netto) |

|---------------------------------------------------------------------------|


|  Softex 2000 member           | Brazil, Sao Paulo state, Campinas city    | 


=============================================================================

• Evaluation copy of Silver Xpress. Day # 106 --- via Silver Xpress V4.01P [NR]

  ---

User Name: Carlos Netto - ( %mpcbbs_at_ibase.org.br ) This message was processed by RAuucp from Merlin Systems Inc.

---

Received on Sun Oct 09 1994 - 01:42:00 CET