-
Hello all,
I have an sql query as follows
SELECT DISTINCT G_T3.NO_ANNEE, TO_NUMBER(TO_CHAR(G_T3.DT_JOUR, 'MM')) ,
G_T4.ID_LIEU_SEJ, G_T2.ID_CANAL,
G_T1.ID_PTVENTE
FROM INFOCTRE.INF_TB_TR_PTVENTE G_T1 ,
INF_TB_TR_CAN G_T2 ,
INF_TB_TT_CAL_EXE G_T3 ,
INF_TB_TR_LI_SE G_T4
WHERE G_T4.ID_SAI = G_T3.CD_SAI
when I do explain plan oracle is not using the index in
the table INF_TB_TT_CAL_EXE G_T3.
I did analyze, but still it is not using index.
does any one has any idea.
ps: i cannot use rule becasue my query is generated by ETL tool
Thanks in advance
Naeem
-
?
No join to G_T1 and G_T2?
Ales The whole difference between a little boy and an adult man is the price of toys
-
Yes there is no join, because i have to make cartesean product.
Naeem
-
It's possible to force the optimizer to use the index by the INDEX hint:
SELECT /*+ INDEX (G_T3 index_name) */ DISTINCT ...
Ales The whole difference between a little boy and an adult man is the price of toys
-
Hi Naeem,
I got some questions:
how many rows are in the INF_TB_TR_LI_SE G_T4 table?
how many rows are in the INF_TB_TT_CAL_EXE G_T3 table?
how many rows are concerned by the condition G_T4.ID_SAI = G_T3.CD_SAI
Is is correct that your index is on INF_TB_TT_CAL_EXE(CD_SAI)
Mike
-
Hi Mike
how many rows are in the INF_TB_TR_LI_SE G_T4 table?
~300000
how many rows are in the INF_TB_TT_CAL_EXE G_T3 table?
~135000
how many rows are concerned by the condition G_T4.ID_SAI = G_T3.CD_SAI
730
Is is correct that your index is on INF_TB_TT_CAL_EXE(CD_SAI)
Yes
Naeem
-
Could you post the plan here, please?
Ales The whole difference between a little boy and an adult man is the price of toys
-
And one more question: what's the response time of the query?
Ales The whole difference between a little boy and an adult man is the price of toys
-
SELECT STATEMENT Optimizer=CHOOSE (Cost=435209 Card=30570210 Bytes=794825460)
SORT (UNIQUE) (Cost=435209 Card=30570210 Bytes=794825460)
HASH JOIN (Cost=311 Card=40326660 Bytes=1048493160)
TABLE ACCESS (FULL) OF INF_TB_TT_CAL_EXE (Cost=2 Card=730 Bytes=9490)
MERGE JOIN (CARTESIAN) (Cost=301 Card=331452 Bytes=4308876)
MERGE JOIN (CARTESIAN) (Cost=4 Card=297 Bytes=1782)
INDEX (FULL SCAN) OF PK_INF_TB_TR_CANAL (UNIQUE) (Cost=1 Card=3 Bytes=9)
SORT (JOIN) (Cost=3 Card=99 Bytes=297)
INDEX (FULL SCAN) OF PK_INF_TB_TR_PTVENTE (UNIQUE) (Cost=1 Card=99 Bytes=297)
SORT (JOIN) (Cost=300 Card=1116 Bytes=7812)
INDEX (FAST FULL SCAN) OF PK_INF_TB_TR_LI_SE (UNIQUE) (Cost=1 Card=1116 Bytes=7812)
Naeem
-
The G_T3 and G_T4 join will produce 730 rows. G_T1 seems to have 99 rows and G_T2 3 rows. The cartesian product should have 730*99*3=216810 rows. Is that you're expecting?
You mentioned you cannot use RBO because the query is generated. Then you cannot use any hint and you are not able to change the text of the query. The only way left is "hack" the statistics of the index in some way. See the DBMS_STATS package. Of course, you might kill (and you do, I think) other good plans by changing index statistics.
Ales The whole difference between a little boy and an adult man is the price of toys
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|