Hint INDEX on Oracle 10 g.0.2 [message #174749] |
Tue, 30 May 2006 07:58 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
luchot
Messages: 6 Registered: March 2006
|
Junior Member |
|
|
Hello ,
I have some hint that I put on the following queries and Oracle does not consider it .
I do not understand because I am sure of the syntax of the index and to be sure I place the hint in the general select and all the sub select of the queries.
There are four queries where it do not works.
explain plan for select /*+ INDEX(ORDERS id_ordkey_orders */
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AFRICA'
and o_orderdate >= date '1994-01-01'
and o_orderdate < date '1994-01-01' + interval '1' year
group by
n_name
order by
revenue desc;
explain plan for select /*+ INDEX( LINEITEM id_partkey_lineitem_ */
o_year,
sum(case
when nation = 'JORDAN' then volume
else 0
end) / sum(volume) as mkt_share
from
(
select /*+ INDEX( LINEITEM id_partkey_lineitem_ */
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) as volume,
n2.n_name as nation
from
part,
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2,
region
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'MEDIUM BRUSHED TIN'
) all_nations
group by
o_year
order by
o_year;
EXPLAIN PLAN FOR select /*+ INDEX (CUSTOMER id_custkey_cust) */
c_count,
count(*) as custdist
from
(
select /*+ INDEX (CUSTOMER id_custkey_cust) */
c_custkey,
count(o_orderkey) as c_count
from
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%pending%packages%'
group by
c_custkey
) c_orders
group by
c_count
order by
custdist desc,
c_count desc;
explain plan for select /*+ INDEX(LINEITEM id_ordkey_lineitem) */
s_name,
numwait
from
(
select /*+ INDEX(LINEITEM id_ordkey_lineitem) */
s_name,
count(*) as numwait
from
supplier,
lineitem l1,
orders,
nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select /*+ INDEX(LINEITEM id_ordkey_lineitem) */
*
from
lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select /*+ INDEX(LINEITEM id_ordkey_lineitem) */
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'EGYPT'
group by
s_name
order by
numwait desc,
s_name
)
where rownum <= 100 ;
Best regards
|
|
|
Re: Hint INDEX on Oracle 10 g.0.2 [message #174782 is a reply to message #174749] |
Tue, 30 May 2006 09:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
nice plans ![Wink](images/smiley_icons/icon_wink.gif)
Maybe it would help to add at least one of the plans, together with some info on numbers of records per table, distribution of the data etc.
Remember that a hint is just that: a hint. The CBO can choose a plan that is cheaper than the one with the hint. The hint artificially lowers the cost of a plan.
[Updated on: Tue, 30 May 2006 09:15] Report message to a moderator
|
|
|
Re: Hint INDEX on Oracle 10 g.0.2 [message #174795 is a reply to message #174782] |
Tue, 30 May 2006 10:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
luchot
Messages: 6 Registered: March 2006
|
Junior Member |
|
|
You say that cbo choose the best plan he prefers but i thought that the hint was create in order to force this estimation so it is why i am surprised
For the fist query this is the plan :
17:05:40 SQL> @e5
Explicité.
Ecoulé : 00 :00 :00.02
17:05:40 SQL> @/apps/oracle/10.2.0/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 891400219
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 2950 | | 834K (4
)| 02:41:45 |
| 1 | SORT ORDER BY | | 25 | 2950 | | 834K (4
)| 02:41:45 |
| 2 | HASH GROUP BY | | 25 | 2950 | | 834K (4
)| 02:41:45 |
|* 3 | HASH JOIN | | 271K| 30M| | 834K (4
)| 02:41:44 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | TABLE ACCESS FULL | REGION | 1 | 29 | | 2 (0
)| 00:00:01 |
|* 5 | HASH JOIN | | 1358K| 115M| | 834K (4
)| 02:41:43 |
| 6 | TABLE ACCESS FULL | NATION | 25 | 800 | | 2 (0
)| 00:00:01 |
|* 7 | HASH JOIN | | 1358K| 73M| 5864K| 834K (4
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
)| 02:41:43 |
| 8 | TABLE ACCESS FULL | SUPPLIER | 300K| 2343K| | 1049 (3
)| 00:00:13 |
|* 9 | HASH JOIN | | 34M| 1592M| 274M| 794K (4
)| 02:34:05 |
|* 10 | HASH JOIN | | 6851K| 196M| 90M| 144K (4
)| 00:28:06 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 11 | TABLE ACCESS FULL| CUSTOMER | 4505K| 38M| | 17069 (2
)| 00:03:19 |
|* 12 | TABLE ACCESS FULL| ORDERS | 6851K| 137M| | 121K (4
)| 00:23:37 |
| 13 | TABLE ACCESS FULL | LINEITEM | 180M| 3262M| | 538K (3
)| 01:44:25 |
--------------------------------------------------------------------------------
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N_REGIONKEY"="R_REGIONKEY")
4 - filter("R_NAME"='AFRICA')
5 - access("S_NATIONKEY"="N_NATIONKEY")
7 - access("L_SUPPKEY"="S_SUPPKEY" AND "C_NATIONKEY"="S_NATIONKEY")
9 - access("L_ORDERKEY"="O_ORDERKEY")
10 - access("C_CUSTKEY"="O_CUSTKEY")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
12 - filter("O_ORDERDATE"<TO_DATE('1995-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:s
s')
AND "O_ORDERDATE">=TO_DATE('1994-01-01 00:00:00', 'yyyy-mm-dd hh24
:mi:ss'))
32 ligne(s) sélectionnée(s).
The cardinality of the table are :
Lineitem : 180 000 000
Orders : 45 000 000
Customer :4 500 000
Supplier : 300 000
Nation : 25
Region : 5
Thanks,
|
|
|