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
Do you really want to delete this prezi?
Neither you, nor the coeditors you shared it with will be able to recover it again.
Make your likes visible on Facebook?
You can change this under Settings & Account at any time.
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;