Home > Default > Dude query with index

Dude query with index

November 30Hits:0
Advertisement
Hi all,
My problem is the next: I have two tables, one with 2500000 rows (A) and the other (B) with 1000 rows aprox. I have to join these two tables between two fields B.pk=A.fk. These two fields are indexed. And if I extract the explain plan it doesn't have a high cost (5000 aprox), but when I execute the query it is very slow, it spends more than 2 minutes in finishing. If I drop the index the cost of the explain plan is worse but the execution time is lower, about 15 seconds. What can I do??
Thanks in advance

Answers

Hi again,
Thanks for your responses. In first place, my database is 11g. In second place, yes, we have collected statistics for both tables...
The rest is going to be more complicated. We have increased the block size to 32KB, in this way we have improved the time of the execution of the queries, so the explain plan has changed. The current plan is this:
plan FOR correcto.
PLAN_TABLE_OUTPUT
Plan hash value: 166371783
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 44 | 2724 (1)| 00:00:33 |
| 1 | SORT GROUP BY | | 1 | 44 | 2724 (1)| 00:00:33 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 66753 | 2868K| 2717 (1)| 00:00:33 |
|* 4 | TABLE ACCESS FULL | TIEMPO_MES | 1 | 17 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CUPON_COMPLETO_VM_IDX72 | 55168 | | 31 (4)| 00:00:01 |
| 6 | MAT_VIEW ACCESS BY INDEX ROWID| CUPON_COMPLETO_VM | 55168 | 1454K| 2714 (1)| 00:00:33 |
Predicate Information (identified by operation id):
4 - filter(CAST("T46538"."ANIO" AS INTEGER )=2011)
5 - access("T46538"."MES_CODIGO"="T99940"."P_MES")
We have improved the time, but we think that we can do it better. I post here the structure of the tables (only the part affected in the join):
desc TIEMPO_MES
Nombre Nulo Tipo
MES_CODIGO NUMBER
MES_CORTO VARCHAR2(15)
ANIO_CODIGO NUMBER
ANIO NUMBER
desc CUPON_COMPLETO_VM
Nombre Nulo Tipo
P_PRODUCIDOS_KEY NUMBER
P_CUPON_ID NUMBER
P_MES NUMBER
E_ENVIADOS_KEY NUMBER
D_DISTRIBUIDOS_KEY NUMBER
R_RESULTADOS_KEY NUMBER
Thanks in advance

Read other 15 answers

Tags:

Related Articles

  • Dude query with indexNovember 30

    Hi all, My problem is the next: I have two tables, one with 2500000 rows (A) and the other (B) with 1000 rows aprox. I have to join these two tables between two fields B.pk=A.fk. These two fields are indexed. And if I extract the explain plan it does

  • Workspace Error executing query: String index out of range: -39November 30

    I am running a Financial Reporting Book. When running this book, I am receiving the following error. Error executing query: String index out of range: -39 What does this mean? And how do I fix this? Thank you, DanThis is happening to a single Financi

  • ADM Error processing results from query: Error: Index 0 out of rangeNovember 30

    11.1.2.1 When running quite a large book, we are getting the following error: ADM Error processing results from query: Error: Index 0 out of range The individual reports in the books are running fine. PS.  The report books that are giving this error

  • Reducing the Cost of the query by indexing.November 30

    Hello, I have a query as under : SELECT T1_WO.WO_ID, T1_RW_MSG.RMSG_FN_ID, T1_RW_MSG.RMSG_PROD_CD, T1_RW_MSG.RMSG_OPRN_CD, T1_WO.WO_ENTRY_DT, T1_RW_MSG.RMSG_OUR_REF_NUM, T1_WO.WO_CURR_QUEUE, T1_WO.WO_PRIORITY, T1_WO.WO_STATUS_CD, T1_RW_MSG.RMSG_TRANS

  • How to write the query using IndexNovember 30

    Hi All, I have to fetch the records from Database table using Index, how can i write the query using Index. Can any body plz send me the sample code. Help Me, Balu.Hi, See the below Example. select * from vbak up to 100 rows into table t_vbak where v

  • Different query behaviour (index or no index use) between sql and pl/sqlOctober 11

    Hi All, I have a query inside a cursor in a procedure, let's say: cursor c_emp (b_dept_no in number) is select * from emp where dept_no = b_dep_no; There is an non unique index on dept_no. In this procedure i first loop through another cursor which g

  • Query Uses Index in 8i but not in 9iOctober 11

    I have simple query which runs good in Oracle 8i. It uses index ,if i use = or in clause. Same query is not using Index in Oracle 9i,(We made the the optimizer as choose) . If i remove in clause and make it = ,it is using index select * from DWFE_ELE

  • Error during search !!! Associate Search Query with Indexes !!!October 11

    We are trying to implement a simple index with Trex Search . We have done the following tasks : 1. Created my index 2. Assigned data to the the indexes 3. Created taxonomies for classification indexes 4. Created my search query  asociated to the inde

  • Tuning query with indexNovember 30

    hello, i am trying to avoid full table scan in a query by creating reverse index on the name column in the where clause. but the optimizer still runs full table scan. What can I do? Regards, PurviHi, Reverse key indexes reverse the bytes of each inde

  • Slow Query Using index. Fast with full table Scan.November 30

    Hi; (Thanks for the links) Here's my question correctly formated. The query: SELECT count(1) from ehgeoconstru  ec where ec.TYPE='BAR'  AND ( ec.birthDate <= TO_DATE('2009-10-06 11:52:12', 'YYYY-MM-DD HH24:MI:SS') )   and deathdate is null and substr

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 1.235 s.