Why my SQLite3 query takes time?

What should I expect from this oracle query performance?

• I am running a query on a linux server running on oracle 10g on a server which has 8 CPU Intel(R) Xeon(R) CPU E5420 @ 2.50GHz and current 'top' shows me: top - 10:03:40 up 2 days, 16:05, 6 users, load average: 12.69, 12.13, 11.46 Tasks: 291 total, 1 running, 290 sleeping, 0 stopped, 0 zombie Cpu(s): 2.0%us, 0.5%sy, 0.0%ni, 72.7%id, 24.8%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 16301160k total, 12794344k used, 3506816k free, 649568k buffers Swap: 100663248k total, 0k used, 100663248k free, 669780k cached The query is between two date range partitioned tables, each table has a local index of (LID and E_DATE) where LID is a varchar2 type and E_DATE is DATE type. Partition is based on E_DATE and each row on the partition has the exact DATE (meaning there is no minutes/seconds etc.). There query is something like this: select tab1.E_DATE, func(tab2.col2), count(*) from tab1 tab1 outer join tab2 tab2 on tab1.LID = tab2.LID and tab1.E_DATE = tab2.E_DATE where tab1.E_DATE >= to_date(xxx) and tab1.E_DATE < to_date(xxx) and tab1.colx = ? and tab1.col2 = ? group by tab1.E_DATE, func(tab2.col2) order by tab1.E_DATE, func(tab2.col2) Each partition is of similar size of 5M rows for each table. Realistically, how much time should I expect this to finish? When I run it, it takes more than 10 minutes to finish.

• Answer:

  10 minutes is too long for users to wait. The best practice is less than 3 minutes. Here are some suggestion, you need to test though 1. Why partition only based on E_DATE but your query using composite key (LID + E_DATE) Try to create a similar table just for 2 months data and do experiment using composite partition key as exactly like your query 2. Second option, try to swap the condition. Your query is LID and follow by E_DATE, so your partition key does not give much benefit, try using E_DATE first and follow by LID. 3. I don't know how complex is your function, looks like you should pre-aggregate your table and put LID and E_DATE as a dimension key in your aggregation table. Query on 10M row are not a good idea. 4. Use parallel hint if your DBA allow you to do so Hope this help. PS. Query Performance tuning is an art, you have to make tests to prove them for best performance. cheers