1

I have a table with total 198695 records and 96579 records with project_id =555. I have list interval partitioned the table by project_id. If i run the below query(without distinct),it takes 0.75 secs to run.

SELECT  KW,SEARCH_VOLUME,TARGET_URL,KEYWORD_TYPE,RANK_DATE,RANK,URL,DOMAIN
from alps_project_theme_kw_v where  min_rank =1 and project_id = 555;

while if i add distinct and run the below query, it takes 15 secs to run

SELECT distinct KW,SEARCH_VOLUME,TARGET_URL,KEYWORD_TYPE,RANK_DATE,RANK,URL,DOMAIN
from alps_project_theme_kw_v where  min_rank =1 and project_id = 555;

For now the results fetched by both distinct and non distinct query is same (1636 rows) Can anyone please suggest how should i further tune the query? Please note:- i have only partitioned the table on project_id as mentioned above and analyzed it ,have not created any indexes.

Robert
  • 25,425
  • 8
  • 67
  • 81
user2342436
  • 492
  • 3
  • 17

2 Answers2

0

I would suggest adding index on project_id will greatly reduce the execution time. If you dont want to add index to the base table, copy the o/p to a temp table, create index on the temp table and then fire the distinct query, I bet will be faster than 15 sec.

Sunil Tandon
  • 151
  • 4
  • Index on project_id won't help i think cause i have already partitioned the same, so using the partition will be more effective right? – user2342436 Jul 31 '13 at 09:21
0
    Use hints from oracle, 

    for e.g. SELECT /*+ INDEX(INDX1,IDX2) */ * FROM v;

    for fast retrieval of data use  

    SELECT /*+ FIRST_ROWS(100) */ * FROM v;


    try to use combination of column which are in same index 

Refer : 

    http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm
Hemant Barhate
  • 5
  • 6
  • are u suggesting creating an index on project_id and using hint on the same?? i have partitioned the table by project_id – user2342436 Jul 31 '13 at 09:36
  • Yes. but project_id would be already indexed if its a primary key. creating index on large data table is not suggested as it takes too much of a time. – Hemant Barhate Jul 31 '13 at 09:40
  • creating index in project_id and using hint didn't help. Any other suggestion?? – user2342436 Jul 31 '13 at 11:30
  • Indexed columns are faster because data is stored in indexed order hence faster to retrieve. when using multiple tables, rather than DISTINCT use exists by modifying your query if possible. – Hemant Barhate Jan 16 '14 at 10:36