Why Oracle is not using my index

Today i saw the following post on OTN
“select *
from customer c
inner join work_item sp1 ON sp1.customer_id = c.customer_id

and I am using TOAD, which tells me (in the Explain Plan area), that I’m doing a “TABLE ACCESS FULL” scan on the customer table.

But when I look at the details of BOTH tables, I can clearly see that they both have an index on the customer field, and they are both the same data types…”

And my answer was:

One of the main reason of existence of index if to FILTER information.

But you didn´t gave any filter conditions, you jus gave join conditions, so Oracle should be ordered both table with Hash join or Merge Join.

And it probally is faster than using index.

If you can create a filter condition, and this column have a index on it , Oracle can use it.

Interpreting Explain Plan

By Akadia

”

What’s an explain plan?

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

Query processing can be divided into 7 phases:

[1] Syntactic	Checks the syntax of the query
[2] Semantic	Checks that all objects exist and are accessible
[3] View Merging	Rewrites query as join on base tables as opposed to using views
[4] Statement      Transformation	Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
[5] Optimization	Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
[6] QEP Generation	QEP = Query Evaluation Plan
[7] QEP Execution	QEP = Query Evaluation Plan

Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement.

The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation  of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is reparsed. “

To read the complete source about how to read explain plan, go to

http://www.akadia.com/services/ora_interpreting_explain_plan.html

Also see Oracle documents

Using v$segment_statistics to finding missing indexes

If you want to finding tables that was doing lot of full scans,  you go straight to tables that is doing a lot of reading (many reading could (not should) be translated as missing index)

To found what´s table is doing this
select * from
(select owner,object_name,value from v$segment_statistics
where statistic_name=’logical reads’ and object_type=’TABLE’ order by

3 desc)

where rownum<20

Oracle and Firewall on dedicate environment

When you use some operation system as Windows e.g. sometime couldn´t share it port so after listener stabilish the connection on common 1521 , Oracle spawn it connection to another free port.

But if you can´t open more ports on firewall? the solution on Windows is set USE_SHARED_SOCKET

How to find the prime number with SQL

with prime_view as (select /*+materialize*/ rownum prime_cand from all_objects where rownum<=&max_value)
select prime_cand prime_number from prime_view p1 where prime_cand not in
(select p1.prime_cand
from prime_view p2 where p1.prime_cand>p2.prime_cand and p2.prime_cand>1
and mod(p1.prime_cand,p2.prime_cand)=0)