Send the link below via email or IMCopy
Present to your audienceStart 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
Oracle SQL intro #13
Transcript of Oracle SQL intro #13
Indexes. Agenda why do I need indexes?
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
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;
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:
search/join criteria selectivity;
and many other factors...
Example 1 select *
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 *
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 *
where col1 like '%A' Index on col1 will not help.
Example 4 select *
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 *
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.
bitmap and bitmap join indexes;