Re: Forcing index usage...

Jonathan Lewis schrieb:
"Volker Hetzer" <volker.hetzer@xxxxxxxx> wrote in message news:e2isnq$so7$1@xxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi!
(9.2.0.6, linux)
I'm in a situation where oracle spatial doesn't use a domain index even if I use the index hint.
In fact, as long as the spatial operator (sdo_relate) is the only predicate in the where
clause, the index gets used, so the index is okay.
But as soon as I add more predicates or nest the query into another one,
the index doesn't get used and the query takes forever.

Is there anything I can do apart from the hint?

I'm seriously considering splitting my query into two, the first one
putting the spatial related results into a temporary table and the second one
doing the rest with the intermediate result.

Any thoughts on that?

Lots of Greetings!
Volker


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
.

Relevant Pages

  • Re: Need help with hint syntax in SQL Server 2000
    ... the tables in the view but will an index hint propogate or will I have ... SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx ... The query was a simple query against a view: ... When I coded the index hint the SQL Server plan showed it being used ...
    (comp.databases.ms-sqlserver)
  • Re: SQL statement inquiry **brain-blocked here** :o(
    ... Can you post the SQL of the query that is not working for you? ... (HINT, Select View: SQL from the menu) ... output list the UPC and DEPT for that particular store. ...
    (microsoft.public.access.queries)
  • Re: Date Query
    ... it's a hint at the SQL of a Query. ... Count, Sum, Average, and so on. ...
    (microsoft.public.access.queries)
  • Re: Adding Hints to the SQL for processing cubes?
    ... I have a nightly process ... If it was direct SQL, I would fix it by adding the hint OPTION(MAXDOP ... 1); to the query. ...
    (microsoft.public.sqlserver.olap)
  • Buffer cache
    ... Doing some comparisson testing on SQL 2005 and thought it might ... provide a fairer test if each query ran without the benefit of any ... previous execution plans. ...
    (microsoft.public.sqlserver)