Geek Answers Handbook

Multiple indices per column

Using Oracle, there is a table called User.

Columns: Id, FirstName, LastName

Indexes: 1. PK(Id), 2. UPPER(FirstName), 3. LOWER(FirstName), 4. Index(FirstName)

As you can see, indices 2, 3, 4 are indices in one column - FirstName.

I know this creates overhead, but my question is choosing a way to respond / optimize the database?

For instance:

SELECT Id FROM User u WHERE u.FirstName LIKE 'MIKE%'

Will Oracle get into the right index or not?

The problem is that with Hibernate this slows down the request VERY much (therefore, it uses prepared instructions).

Thank.

UPDATE: 2 3 .

+5
3

Mat , 2, 3 , , , , , LIKE.

, ( , ), , . - , "S%", , . , , "Smithfield-Manning%", , , , 4. Oracle , .

Oracle 10 Oracle . , Oracle ( , ), Oracle , . , , , , . , - WHERE firstname LIKE 'S%' , , .

, Oracle 11, . , , WHERE firstname LIKE 'S%' , WHERE firstname LIKE 'Smithfield-Manning%' . , .

, , . , 20- , 256 , , - WHERE firstname LIKE 'Smit%' . , "Sm%" , , . , , , WHERE firstname LIKE 'Cave%' , .

, , Oracle, Oracle . , , , , , , . , , . , ROWNUM , , , .

+7

, . , , .

(, , ), ( ) - :

select id from user u where upper(u.firstname) like 'MIKE%'

: , , NULL Oracle 10+?

+7

, SELECT, .

If the index is very selective, and Oracle decides that it is worth using it to search for "MIKE%", and then search for data to get the ID column, then it can use 4. Index(FirstName). 2 and 3 will only be used if the column search uses the exact function defined in the index.

+6
source

More articles:


All Articles