Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle SQL intro #13

Indexes
by

Rodion Mironov

on 13 February 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #13

Introduction to Oracle SQL.
Indexes. Agenda why do I need indexes?
index structure.
CREATE index.
index usage;
when should I create index? Index creation:
manually with CREATE INDEX command;
automatically for PRIMARY KEY and UNIQUE constraints.
Indexes are maintained by Oracle automatically (modified in process of DML execution);

Index are used by query optimizer automatically. Well, sometimes :) CREATE INDEX index_name
ON table (column[, column]...); Index creation, simple guideline:
for column used
in WHERE and JOIN clauses;
for child-table column used
in FOREIGN KEY constraints. Concepts: indexes http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#BABHJAJF Useful links CREATE INDEX http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5012.htm#i2062403 PRACTICE please, fill in feedback forms :) How many "6" these lists contain? Unsorted Sorted Data are the same.
Feel the difference. Rows in table are stored unsorted.
We can physically sort them, but:
by one criteria only;
it's rather expensive to support such structure; To overcome this limit, we have auxiliary data structure called "index".

Database takes indexed field, sort values and store them separately. Table Index select count(*) from table where id = 6 - works fast! But what about
select Name from table where id = 6 ? Table Index Index Table In fact, index stores
indexed value;
ROWID of row this value come from (like "fast link to row" Index :) Oracle index structure Index Table "Execute" two queries
and compare performance.

Is index useful for both? 1.
select name from table where id = 4;
2.
select name from table where id <=7; Cost-Based Optimizer is Oracle component that have to choose the best strategy for query execution.
It uses database statistics and make a decision: use index or scan table (and other decisions, of course). Remember, index benefits
will also depend on:
table size;
data distribution;
search/join criteria;
search/join criteria selectivity;
and many other factors...
Example 1 select *
from t
where col1 = 'A' Index on col1 helps if:
relatively small part of rows
have 'A' in col1;
table T is rather big. Example 2 select *
from t
where col1 like 'A%' Index on col1 helps if:
relatively small part
of rows start with 'A' in col1;
table T is rather big. Example 3 select *
from t
where col1 like '%A' Index on col1 will not help.
Example 4 select *
from t
where col1 = 1
and col2 = 2 Index on col1 may help.
Index on col2 may help.
Index on (col1,col2)
or (col2,col1) may help
more. Example 5 select *
from t
where col1 is null Index on col1 will not help.
Oracle does not index nulls*. "Why isn't Oracle using my index?" by Jonathan Lewis http://www.jlcomp.demon.co.uk/12_using_index_i.html "Основные причины ошибок CBO" by Андрей Киселев http://www.sql.ru/faq/faq_topic.aspx?fid=344 "Cost-based Oracle fundamentals" by Johathan Lewis Note: only the most-simple indexes
(b-tree indexes on columns)
were shown in this prezi.
Not covered:
unique/reverse key/descending
b-tree indexes;
function-based indexes;
bitmap and bitmap join indexes;
application-domain indexes.
Full transcript