?
Solved

Why the query is not using index in Oracle?

Posted on 2007-10-04
17
Medium Priority
?
2,982 Views
Last Modified: 2013-12-19
Why the query is not using index in a client's environment? We tested the query and it used indexes (see the explain plan). But it does full table scan in our client's machine.


Our environment: Oracle 9.2.0.8 on Window server
Clinet's environment: Oracle 9.2.0.8 on Unix server

We have asked the client to dump the schema and imported the dumped schema to our environment. It uses indexes. I asked the client to send me fragmentation info. There is only 130 fragments in the tablespace.

Any clue what it might cause the problem?


SELECT
    C.DOCUMENT_SUBTYPE_ID AS DOCUMENT_SUBTYPE_ID,
    DNAME.NAME AS CAT_NAME,
    SUM(C.PAGE_COUNT) AS PAGE_COUNT,
    SUM(C.BOOKMARK_COUNT) AS BOOKMARK_COUNT,
    SUM(C.HYPERLINK_COUNT) AS HYPERLINK_COUNT,
    SUM(C.VARIABLE_COUNT) AS VARIABLE_COUNT,
    COUNT(*) AS TOTAL_DOCS
FROM CONTAINER C
    INNER JOIN I18N DNAME ON (DNAME.PARENT_OBJECT_ID=C.DOCUMENT_SUBTYPE_ID AND DNAME.LOCALE_CODE = 'en_US')
    INNER JOIN CONTAINER_CONTENT_OBJECT CCO ON (CCO.CONTAINER_ID = C.OBJECT_ID)
    INNER JOIN CONTENT_OBJECT CO ON (CCO.CONTENT_OBJECT_ID = CO.OBJECT_ID)
    INNER JOIN CONTAINER_ANCESTOR CA ON C.OBJECT_ID = CA.CONTAINER_ID
WHERE C.DOCUMENT_YN = 'Y' AND CA.ANCESTOR_ID = 7208866 AND CCO.CURRENT_YN = 'Y'
GROUP BY C.DOCUMENT_SUBTYPE_ID,DNAME.NAME
ORDER BY COUNT(*) DESC

Plan
SELECT STATEMENT  CHOOSECost: 89  Bytes: 710  Cardinality: 10                                                  
      13 SORT ORDER BY  Cost: 89  Bytes: 710  Cardinality: 10                                            
            12 SORT GROUP BY  Cost: 89  Bytes: 710  Cardinality: 10                                      
                  11 NESTED LOOPS  Cost: 86  Bytes: 710  Cardinality: 10                                
                        9 NESTED LOOPS  Cost: 76  Bytes: 660  Cardinality: 10                          
                              7 NESTED LOOPS  Cost: 66  Bytes: 380  Cardinality: 10                    
                                    4 NESTED LOOPS  Cost: 24  Bytes: 567  Cardinality: 21              
                                          1 INDEX RANGE SCAN NON-UNIQUE WYETHQA.CONTAINER_ANCESTOR_AID_CID Cost: 3  Bytes: 210  Cardinality: 21        
                                          3 TABLE ACCESS BY INDEX ROWID WYETHQA.CONTAINER Cost: 1  Bytes: 17  Cardinality: 1        
                                                2 INDEX RANGE SCAN NON-UNIQUE WYETHQA.CONTAINER_OID_IDX Cardinality: 1  
                                    6 TABLE ACCESS BY INDEX ROWID WYETHQA.CONTAINER_CONTENT_OBJECT Cost: 2  Bytes: 11  Cardinality: 1              
                                          5 INDEX RANGE SCAN NON-UNIQUE WYETHQA.CONT_CONT_OBJ_CONT_ID_IDX Cost: 1  Cardinality: 1        
                              8 INDEX RANGE SCAN NON-UNIQUE WYETHQA.I18N_POI_LC_N Cost: 1  Bytes: 28  Cardinality: 1                    
                        10 INDEX RANGE SCAN NON-UNIQUE WYETHQA.CONTENT_OBJECT_OBJECT_ID_IDX Cost: 1  Bytes: 5  Cardinality: 1                          



=======================================================================================
0
Comment
Question by:geotiger
  • 6
  • 6
  • 2
  • +2
17 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 20015728
Hi geotiger,

Make sure that the statistics are current on that machine.  Have them run an analyze on the joined tables.


Good Luck,
Kent
0
 
LVL 12

Author Comment

by:geotiger
ID: 20015742
They did. That was the first thing that I had asked them to do.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 20015912
Hi geotiger,

How much data is on the customer machine?  It could be that Oracle has determined that the table scan is appropriate.


Good Luck,
Kent
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Author Comment

by:geotiger
ID: 20015957
I had imported all the client data into a schema in our instance. It uses the indexes as expected.  It is very strange.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 20016013
Hi geotiger,

Try forcing the index just to see if it will.


Good Luck,
Kent
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 20016051
You need first to break the query and run an explain plan for a single statement, such as:
SELECT * FROM CONTAINER_ANCESTOR CA
WHERE CA.ANCESTOR_ID = 7208866

If the above query uses the index, then you may have an ANSI SYNTAX issue, which was very common in version 9i. The workaround is to remove those INNER JOINs and replace them with Oracle's old syntax. I personally wouldn't the 1999 ANSI Syntax on 9i unless I need a full-outer-join.
0
 
LVL 12

Author Comment

by:geotiger
ID: 20016588
I will ask the client to do that. What it bothers is that we imported the whole schema into ours. It uses indexes as expected. Their indexes must have been stored differently some how in their database (fragmented?). Is there a way to check this?
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 20016647
Hi geotiger,

Are both systems running the save version of Oracle, same fix pack, same underlying O/S?


Good Luck,
Kent
0
 
LVL 12

Author Comment

by:geotiger
ID: 20017125
Both are running the same version of Oracle - 9.2.0.8. The client is running Oracle on Solaris while our test server is Window 2000. The Unix server the client is using is much more powerful then our test server but three times slower then ours due to that it igores indexes.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 20017469
Hi geotiger,

I'd ask Oracle.  Even though the two systems have a common code base, there obviously must be differences to support the different host operating systems and hardware, including instruction sets.  It could well be that there is a uniqueness in the modules that is at work.


Good Luck,
Kent
0
 
LVL 5

Expert Comment

by:adrian_ang
ID: 20020135
Can you post the results when you run the statement on both hosts with and without index hint and the corresponding explain plans.
0
 
LVL 9

Expert Comment

by:konektor
ID: 20020759
have you tried to rebuild indexes? if they are old, or b-tree is degenrated, optimizer might rather not to use it.

alter index <index_name> rebuild;
0
 
LVL 12

Author Comment

by:geotiger
ID: 20020943
I compared the tablespace fragmentation and index fragmentation between the client's  and ours. The client has higher numbers but not significant. I had recommended the client to coalesce tablespaces and rebuild the indexes. I will see how this would work out.
0
 
LVL 9

Expert Comment

by:konektor
ID: 20020998
coalescing space only joins free space pieces which are next to each other. check if tablespaces are created to have uniform size of extent, if no you would better to create new tablespaces with uniform size of extent to prevent internal space fragmentation.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 20021194
geotiger,

Have you tried running an EXPLAIN PLAN for a single SELECT statement on your client's environment as I suggested you before?

Are the indexes being suppressed on just one table or is this a global problem?

Do you know anything about SQL PERFORMANCE TUNING?


0
 
LVL 12

Author Comment

by:geotiger
ID: 20033878
Paquicuba, thanks for the suggestion. I did ask the client to run simple sql and it did use index. But it did not solve the problem of why it does not use index in complex query (joins). Being a DBA for over 10 years, I have learned a little SQL performance tuning.

Thanks for all the suggestions.  The problem is resolved now. It turns out that the client ran analysis differently than what we had asked.  Once the client ran dbms_stats with cascade enabled, it all worked as expected. You just can not believe what you have heard (or read the results you received from email) completely. You need to see how they did it.

Thanks for all the suggestions.

TG
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1500 total points
ID: 20033973

Glad that this worked out OK, even if it was a pain.

As we used to say back when I was building operating systems, "If it weren't for the users, we could get some work done".

  :^}


Kent
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question