Home » SQL & PL/SQL » SQL & PL/SQL » Forcing use of index
| Forcing use of index [message #27379] |
Sat, 27 September 2003 06:25  |
Steve
Messages: 190
Registered: September 1999
|
Senior Member |
|
|
Please can I ask why the first query below uses the index that exists on column QUEUE, but the second query ignores the indexes that happens to exist on every 'where' column, and performs a (huge, slow) full table scan:
Uses index on QUEUE:
select *
from case_work_items
where queue in ('1', '2')
Ignores all indexes and performs full table scan of case_work_items:
select *
from CASE_WORK_ITEMS cwi
where cwi.queue
in (select ctq.queue
from rndprod.case_tracker_queue ctq
where ctq.organization_id = cwi.acc_queue_org_id )
Many thanks for your help,
Steve
|
|
|
|
| Re: Forcing use of index [message #27380 is a reply to message #27379] |
Sat, 27 September 2003 07:57  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Steve,
Do you have the execution plans for the above queries. Index selection is based on a lot of factors( I mean a lot! ) . Did you analyze both the tables to update the statistics ?
Oracle may sometimes convert the IN subquery into a join such as
Select cwi.* from case_work_items,rndprod.case_Tracker_queue ctq where
cwi.queue=ctq.queue and cwi.acc_queue_org_id=ctq.organisation_id;
and perform a nested loops to make use of available indexes. Thats why we need to see the execution plans(do a autotrace pls).
You can manually try to use the join example as listed above and see what happens.
In some cases, converting the IN subquery into a Correlated subquery using EXISTS can also lead to better performance.
Select * from CASE_WORK_ITEMS cwi where EXISTS
(Select 1 from rndprod.case_tracker ctq where
ctq.queue=cwi.queue and ctq.organization_id=cwi.acc_queue_org_id);
Depending on the indexes available in the parent or subquery,the execution plans might be different.
HTH
Thiru
|
|
|
|
| Re: Forcing use of index [message #27381 is a reply to message #27380] |
Sat, 27 September 2003 08:26 |
Steve
Messages: 190
Registered: September 1999
|
Senior Member |
|
|
Many thanks, Thiru.
Actually, converting the query to WHERE EXISTS was the first thing I tried, but it didn't help. :-(
Before I answer your question about a trace, let me simplify the problem:
The object Case_Work_Items is a view, made up of (SELECT FROM TABLE WHERE) UNION (SELECT FROM TABLE WHERE) UNION (SELECT FROM TABLE WHERE).
I therefore re-wrote my slow query directly against one of the three tables.
The new query looks the same as before, apart from view CASE_WORK_ITEMS being replaced with table CASE_TAB.
So, we now have:
select *
from CASE_TAB ct
where ct.queue
in (select ctq.queue
from case_tracker_queue ctq
where ctq.organization_id = ct.acc_queue_org_id )
Performance remains pretty well identically slow to against the unioned view before.
Using Explain Plan for the new query, I get:
SELECT STATEMENT
FILTER
TABLE ACCESS FULL CASE_TAB
INDEX FULL SCAN CASE_TRACKER_QUEUE_PK
Out of the two tables, CASE_TAB has over 15,000 records and CASE_TRACKER_QUEUE_TAB just under 350.
The indexes on the two tables are:
CASE_TAB:
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
CASE_CIDX CONTEXT
CASE_PK CASE_ID
CASE_TAB_IDX1 ACC_QUEUE_ORG_ID
CASE_TAB_IDX2 QUEUE
CASE_TAB_IDX3 ROWSTATE
CASE_TRACKER_QUEUE:
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
CASE_TRACKER_QUEUE_PK IDENTITY
CASE_TRACKER_QUEUE_PK ORGANIZATION_ID
CASE_TRACKER_QUEUE_PK ORGANIZATION_ID$
CASE_TRACKER_QUEUE_PK QUEUE
CASE_TRACKER_QUEUE_TAB_IDX1 ORGANIZATION_ID
CASE_TRACKER_QUEUE_TAB_IDX2 QUEUE
In both tables the '...IDX1' and '...IDX2' indexes were my vain attempt to improve things. So far, they haven't helped.
Grateful thanks for any further ideas that you may have.
/Steve
|
|
|
|
| Re: Forcing use of index [message #27382 is a reply to message #27381] |
Sat, 27 September 2003 09:13 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Steve,
Have you analyzed all the tables/indexes involved ?
I'd like to see the autotrace statistics also ( along with CARD , consistent gets).
Did you try the Nested Loops Join method I mentioned earlier and what are the results. Since you have indexes on both the tables,one of them large,one of them small, Nested loops join is a viable option that MAY improve the performance.
Try this also :
Select /*+ ORDERED USE_NL(CTQ) */ CT.*
from CASE_TAB CT,CASE_TRACKER_QUEUE CTQ,
where CT.QUEUE=CTQ.QUEUE and
CTQ.organization_id = ct.acc_queue_org_id ;
ie) trying to join the bigger to smaller table using Nested loops on the inner table , since we dont have any filtering conditions.
Also what is the selectivity of the index on CASE_TAB.QUEUE and CTQ.QUEUE & Organisation_IDs? . You see that the Primary key index is being used instead.
Note that usage of indexes is also controlled by OPTIMIZER_INDEX* init.ora parameters, db_file_multiblock_read_count etc.
Also do you have HASH_JOIN enabled ? , just another option.
HTH
-Thiru
|
|
|
|
| Re: Forcing use of index [message #27383 is a reply to message #27382] |
Sat, 27 September 2003 11:47 |
Steve
Messages: 190
Registered: September 1999
|
Senior Member |
|
|
Hi Thiru,
Thanks for your continued help.
I am afraid that I don't know what Autotrace is, or how to 'analyze an object'. The only tuning tool I know how to use is Explain Plan, which is what I have been using.
I did however try creating table STEVE as select queue from case_tab, and then creating an index on STEVE.queue.
In other words, I had a table with the same number of rows as Case_Tab, but with only one column, and that column had an index I wanted.
I then modified the query to use table STEVE instead of CASE_TAB, and removed the join on the Organization columns.
The query STILL performed a full table scan, instead of using the index.
I'm now getting wife complaints about working on a Saturday, so it's time to go until Monday morning, but if you can help me more within the scope of my knowledge, I would be grateful.
If not, I understand, and thank you for your help given until now.
/Steve
|
|
|
|
| Re: Forcing use of index [message #27389 is a reply to message #27383] |
Mon, 29 September 2003 14:55 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Steve,
Autotrace and Analyze are pretty easy. Without the object statistics, Oracle optimizer wont able to come up with the best execution plan.
Let me try to create similar tables here and try out different join methods , which you could try in your environment and see which one gives the best performance :
-- Creating tables with identical number of rows as yours
SQL> create table case_tab(queue,acc_queue_org_id) as select object_id,data_object_id
2 from all_objects where rownum <= 15000;
Table created.
SQL> create table case_tracker_queue(queue,organisation_id) as select object_id,data_object_id
2 from all_objects where rownum <= 350;
Table created.
-- Creating the indexes similar to yours
SQL> create index case_tab_queue_idx on case_tab(queue);
Index created.
SQL> create index case_tab_orgid_idx on case_tab(acc_queue_org_id);
Index created.
SQL> create index case_tracker_queue_idx on case_tracker_queue(queue);
Index created.
SQL> create index case_tracker_org_id_idx on case_tracker_queue(organisation_id);
Index created.
-- Analyzing the concerned tables to generate object statistics used by CBO
SQL> execute dbms_stats.gather_table_stats('THIRU','CASE_TAB');
PL/SQL procedure successfully completed.
-- Note : here 'THIRU' is the schema owner name
SQL> execute dbms_stats.gather_table_stats('THIRU','CASE_TRACKER_QUEUE');
PL/SQL procedure successfully completed.
SQL> set timing on
SQL> set autotrace traceonly explain
-- Using 'IN'
SQL> select * from case_tab ct where ct.queue in
2 ( select ctq.queue from case_tracker_queue ctq
3 where ctq.organisation_id=ct.acc_queue_org_id);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CASE_TAB' (Cost=1 Card=1
Bytes=5)
2 1 NESTED LOOPS (Cost=5 Card=1 Bytes=9)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'CASE_TRACKER_QUEUE' (Cost=2
Card=1 Bytes=4)
5 2 INDEX (RANGE SCAN) OF 'CASE_TAB_ORGID_IDX' (NON-UNIQUE
) (Cost=1 Card=1)
-- CBO is going for Nested loops join using the index on CASE_TAB
-- Lets try Nested loops join on CASE_TRACKER_QUEUE
specifying the join order
SQL> Select /*+ ORDERED USE_NL(CTQ) */ CT.*
2 from CASE_TAB CT,CASE_TRACKER_QUEUE CTQ
3 where CT.QUEUE=CTQ.QUEUE and CTQ.organisation_id=ct.acc_queue_org_id ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=635 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CASE_TRACKER_QUEUE' (Cos
t=1 Card=1 Bytes=4)
2 1 NESTED LOOPS (Cost=635 Card=1 Bytes=9)
3 2 TABLE ACCESS (FULL) OF 'CASE_TAB' (Cost=10 Card=625 By
tes=3125)
4 2 INDEX (RANGE SCAN) OF 'CASE_TRACKER_ORG_ID_IDX' (NON-U
NIQUE) (Cost=1 Card=1)
-- you see,the CBO is now going for Index scan on Case_tracker_queue table.
-- Now,lets try EXISTS operator
SQL> Select * from CASE_TAB CT where EXISTS
2 ( Select 1 from CASE_TRACKER_QUEUE CTQ where
3 CT.QUEUE=CTQ.QUEUE and CTQ.organisation_id=ct.acc_queue_org_id);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CASE_TAB' (Cost=1 Card=1
Bytes=5)
2 1 NESTED LOOPS (Cost=5 Card=1 Bytes=9)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'CASE_TRACKER_QUEUE' (Cost=2
Card=1 Bytes=4)
5 2 INDEX (RANGE SCAN) OF 'CASE_TAB_ORGID_IDX' (NON-UNIQUE
) (Cost=1 Card=1)
-- This is similar to the IN subquery approach.
SQL>
Note that the timings might be skewed up becos you are trying out various join methods of the same query(and hence the same result set that might be cached in buffers). But you get the idea.
You could also do a
set autotrace traceonly
and then execute the different queries to see 'consistent gets' ,db block gets' etc to see which one does more work.
Hope this helps.
-Thiru
|
|
|
|
| Re: Forcing use of index [message #27440 is a reply to message #27389] |
Thu, 02 October 2003 12:17 |
Steve
Messages: 190
Registered: September 1999
|
Senior Member |
|
|
Many thanks for your kind work, Thiru.
Sorry it's taken me a few days to reply, but I had to go abroad on business.
The problem has now been solved by using a function call to a view instead of the CASE_TRACKER_QUEUE table. The results are exactly the same (the table underneath the view is still the same one), but query is now sub second!
I'm still grateful for your help though, because I have learned a lot from your explanations that I'm sure will help me tune performance problems in future.
Thanks again! :-))
|
|
|
|
Goto Forum:
Current Time: Wed Jun 26 01:02:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
