Why the query is not using index in Oracle?

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                          



=======================================================================================
LVL 12
geotigerAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
geotigerAuthor Commented:
They did. That was the first thing that I had asked them to do.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
geotigerAuthor Commented:
I had imported all the client data into a schema in our instance. It uses the indexes as expected.  It is very strange.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi geotiger,

Try forcing the index just to see if it will.


Good Luck,
Kent
0
 
paquicubaCommented:
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
 
geotigerAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi geotiger,

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


Good Luck,
Kent
0
 
geotigerAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
adrian_angCommented:
Can you post the results when you run the statement on both hosts with and without index hint and the corresponding explain plans.
0
 
konektorCommented:
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
 
geotigerAuthor Commented:
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
 
konektorCommented:
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
 
paquicubaCommented:
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
 
geotigerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.