| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Forcing index usage...
Jonathan Lewis schrieb:
> "Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message > news:e2isnq$so7$1_at_nntp.fujitsu-siemens.com...
> > > Could you give us an example of the SQL > that uses the index, and an example that > doesn't. Complete with execution plans.Thanks for making me go through the query again. Turned out the tables were in the wrong order for the ordered hint.
For your information, here's the query and the execution plan, which now works:
SQL> set lines 110
SQL> alter session set optimizer_index_cost_adj = 1;
Session altered.
SQL> alter session set optimizer_index_caching = 100;
Session altered.
SQL> alter index TPVSPAD_PADS_IND rebuild;
Index altered.
SQL> alter index TPVSPAD_TPS_IND1 rebuild;
Index altered.
SQL> alter index TPVSPAD_TPS_IND2 rebuild;
Index altered.
SQL> BEGIN
2 dbms_stats.gather_table_stats(NULL,'TPVSPAD_TPS');
3 dbms_stats.gather_table_stats(NULL,'TPVSPAD_PADS');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> delete from plan_table;
0 rows deleted.
SQL> explain plan for
2 select tp.pinpad,SDO_GEOM.sdo_distance(dp.pad,tp.pad,0.00001) distance, dp.pinpad, dp.layer_no, tp.pinx-dp.pinx,tp.piny-dp.piny 3 from 4 ( 5 select --+ ORDERED 6 tp.SessionId tp_SessionId, 7 dp.SessionId dp_SessionId, 8 tp.pinpad tp_pinpad, 9 dp.pinpad dp_pinpad 10 from 11 TPVSPAD_TPS tp, 12 TPVSPAD_PADS dp 13 where 14 sdo_relate(dp.pad,tp.PADWHICHIS06BIGGER,'mask=ANYINTERACT querytype=JOIN')='TRUE' 15 intersect 16 select tp.SessionId tp_SessionId ,dp.SessionId dp_SessionId,tp.pinpad tp_pinpad,dp.pinpad dp_pinpad 17 from 18 TPVSPAD_PADS dp, 19 TPVSPAD_TPS tp 20 where tp.layer_no=dp.layer_no and (tp.pinx<>dp.pinx or tp.piny<>dp.piny) and tp.SessionId=dp.SessionId 21 ) candidates, 22 TPVSPAD_PADS dp, 23 TPVSPAD_TPS tp 24 where 25 tp_pinpad=tp.pinpad 26 and dp_pinpad=dp.pinpad 27 and tp.layer_no=dp.layer_no 28 and tp.SessionId=dp.SessionId 29 and tp.SessionId=tp_SessionId 30 and tp.SessionId=dp_SessionId 31 and tp.SessionId='001A19240001' 32 order by distance desc;
Explained.
SQL>
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 79782 | 53M| | 137K (3)| | 1 | SORT ORDER BY | | 79782 | 53M| 124M| 137K (3)| | 2 | NESTED LOOPS | | 79782 | 53M| | 119K (3)| | 3 | NESTED LOOPS | | 79782 | 50M| | 119K (3)| | 4 | VIEW | | 79782 | 46M| | | | 5 | INTERSECTION | | | | | | | 6 | SORT UNIQUE | | 79782 | 6466K| 17M| |
PLAN_TABLE_OUTPUT
| 7 | NESTED LOOPS | | 79782 | 6466K| | 1040 (23)| | 8 | TABLE ACCESS BY INDEX ROWID | TPVSPAD_TPS | 2052 | 86184 | | 2 (50)| |* 9 | INDEX RANGE SCAN | SYS_C0033443 | 2052 | | | | |* 10 | TABLE ACCESS BY INDEX ROWID | TPVSPAD_PADS | 39 | 1599 | | 1040 (23)| |* 11 | DOMAIN INDEX | TPVSPAD_PADS_IND | 3888 | | | | | 12 | SORT UNIQUE | | 3989K| 292M| 708M| | |* 13 | TABLE ACCESS BY INDEX ROWID | TPVSPAD_PADS | 1944 | 77760 | | 2 (50)| | 14 | NESTED LOOPS | | 3989K| 292M| | 3 (34)| | 15 | TABLE ACCESS BY INDEX ROWID| TPVSPAD_TPS | 2052 | 75924 | | 2 (50)| |* 16 | INDEX RANGE SCAN | SYS_C0033443 | 2052 | | | | |* 17 | INDEX RANGE SCAN | SYS_C0033435 | 1944 | | | |
PLAN_TABLE_OUTPUT
| 18 | TABLE ACCESS BY INDEX ROWID | TPVSPAD_TPS | 1 | 42 | | 2 (50)| |* 19 | INDEX RANGE SCAN | SYS_C0033443 | 1 | | | | | 20 | TABLE ACCESS BY INDEX ROWID | TPVSPAD_PADS | 1 | 41 | | 2 (50)| |* 21 | INDEX UNIQUE SCAN | SYS_C0033435 | 1 | | | | ------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
9 - access("TP"."SESSIONID"='001A19240001') 10 - filter("DP"."SESSIONID"='001A19240001')
PLAN_TABLE_OUTPUT
11 - access("MDSYS"."SDO_RTREE_RELATE"(DP."PAD",TP."PADWHICHIS06BIGGER",'mask=ANYINTERACT
querytype=window')='TRUE')
13 - filter("TP"."PINX"<>"DP"."PINX" OR "TP"."PINY"<>"DP"."PINY")
16 - access("TP"."SESSIONID"='001A19240001')
17 - access("DP"."SESSIONID"='001A19240001' AND "TP"."LAYER_NO"="DP"."LAYER_NO")
filter("TP"."LAYER_NO"="DP"."LAYER_NO" AND "TP"."SESSIONID"="DP"."SESSIONID")
19 - access("TP"."SESSIONID"='001A19240001' AND "CANDIDATES"."TP_PINPAD"="TP"."PINPAD")
21 - access("DP"."SESSIONID"='001A19240001' AND "CANDIDATES"."DP_PINPAD"="DP"."PINPAD" AND
"TP"."LAYER_NO"="DP"."LAYER_NO")
42 rows selected.
SQL> delete from plan_table;
22 rows deleted.
SQL> exit
The query now takes about 20s.
The intersection is what I did before in order to let the optimizer work properly.
When I've got time I will check whether this is still necessary.
Lots of Greetings and Thanks to both of you! Volker Received on Tue Apr 25 2006 - 06:21:49 CDT
 |
 |