 SQL Query: slow response time [message #181124] |
Thu, 06 July 2006 23:06  |
lamhe2804
Messages: 4
Registered: June 2006
Location: Dallas
|
Junior Member |
|
|
I am qierying table with 18m+ rows. Data is spread across many days. My query returns fast if it finds matching data. If there is no matching data then query takes lot of time for a particular type.
Query:
SELECT *
FROM table
WHERE
date BETWEEN TO_DATE('2006-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2006-05-01 23:59:59','YYYY-MM-DD HH24:MI:SS') AND
type_cde = '9000' AND
ROWNUM <= 500
This qeury returns data in milliseconds but it takes more than a minute if I change type to '0000'
type_cde is another table with all type values.
What could be so different from comparing '9000' to '0000'
Date and type_cde colums are indexed.
|
|
|
|
|
|
 Re: SQL Query: slow response time [message #181334 is a reply to message #181195] |
Sat, 08 July 2006 16:02  |
lamhe2804
Messages: 4
Registered: June 2006
Location: Dallas
|
Junior Member |
|
|
We misunderstood the problem. We have 18mil rows with 0000 status compare to few thousands with 9000 status. This explains why response was slow for 0000 status.
Now we have a different problem.
We optimized indexes and created Bitmap index on date field. We are seeing response in milliseconds for 0000 status if there are records for that date. The query takes forever to return if there is no matching data for a given date.
Explain plan doesn't show any difference.
Thanks,
[Updated on: Sat, 08 July 2006 16:58] Report message to a moderator |
|
|
|
| Re: SQL Query: slow response time [message #181337 is a reply to message #181334] |
Sat, 08 July 2006 17:05 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
| lamhe2804 wrote on Sat, 08 July 2006 17:02 |
We misunderstood the problem. We have 18mil rows with 0000 status compare to few thousands with 9000 status. This explains why response was slow for 0000 status.
|
Have you tried creating a histogram for this column (type_cde) with a standard btree index?
exec dbms_stats.gather_table_stats (ownname => 'owner',
tabname => 'table',
estimate_percent => 10,
method_opt => 'for columns size 254 type_cde');
| lamhe2804 wrote on Sat, 08 July 2006 17:02 |
Now we have a different problem. We optimized indexes and created Bitmap index on date field.
|
Is this an OLTP table? Is there alot of DML against it? Bitmap indexes are not a very good choice for a table that users are performing concurrent DML against. There would be a high amount of contention while the index is rebuilt for each DML update. I rarely have use bitmap indexes.
| lamhe2804 wrote on Sat, 08 July 2006 17:02 |
We are seeing response in milliseconds for 0000 status if there are records for that date. The query takes forever to return if there is no matching data for a given date.
|
It may be a good idea to copy and paste the plans and tell us how you are querying the data and timing the SQL statements. Tools like TOAD and SQL Navigator can give a false positive on timing because they only fetch the first 20 or 30 rows from the cursor. I always like to time my SQL using SQL*Plus with:
set timing on
set arraysize 5000
set autotrace traceonly statistics
Is the query always spanning just one day worth of data? How may days worth of data do you have? What is the clustering factor & num_rows on the date index?
|
|
|
|
| Re: SQL Query: slow response time [message #181341 is a reply to message #181337] |
Sat, 08 July 2006 17:39 |
lamhe2804
Messages: 4
Registered: June 2006
Location: Dallas
|
Junior Member |
|
|
Will check with my DBA about Histogram. Don't have access.
In test, we have not inserted any transactions for a while. We are testing with static data. In real life, there will be lot of inserts/updates.
We have 18 million records, evenly spread across 60 days (@300k per day).
Query takes same time from Java program/PL SQL Developer/SQL Plus window.
It seems like query scans all transactions when it doesn't find matching data within date range.
Thanks,
|
|
|
|
| Re: SQL Query: slow response time [message #181348 is a reply to message #181341] |
Sat, 08 July 2006 18:14 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
| lamhe2804 wrote on Sat, 08 July 2006 18:39 |
Will check with my DBA about Histogram. Don't have access.
In test, we have not inserted any transactions for a while. We are testing with static data. In real life, there will be lot of inserts/updates.
We have 18 million records, evenly spread across 60 days (@300k per day).
Query takes same time from Java program/PL SQL Developer/SQL Plus window.
It seems like query scans all transactions when it doesn't find matching data within date range.,
|
Okay, I wouldn't use a bitmap index unless you can serialize DML to the table (using an interface and a single job performs the DML). You will create a whole new (much worse) problem.
Please post the DDL for the table & indexes, the queries, explain plans, and some of the index stats. It may very well be generating full table scans for your date index, and if your type_cde column has a limited # of distinct values / num_rows ratio then without the proper histograms it will full scan as well.
If the table has 18 million rows, and 17.9 million rows have type_cde = '0000' and .1 million rows have the type_cde = '9000', and there is only two distinct values in this column then as far as the optimizer is concerned (without a histogram) you have 9 million with 0000 and 9 million with 9000. So clearly fetching half of the table through a index is likely a bad plan, and I wouldn't use an index and neither would Oracle. Keep in mind the histogram is only going to help those rows with '9000' as a value. It may end up making sense not to index the type_cde column at all, it all depends on how you are querying the data.
Now if you have an index on the date column, then you are doing a range scan and range scans use the clustering factor to determine how "orderly" the data is in the blocks with respect to the index. So it may be useful for you to post the clustering_factor & num_rows (should be 18m) of the table. Date stamps can and will work well, if the table & DML against it is designed properly, or they can fail miserably and become completely unusable indexes because the way the DML is performed.
Post back when you have more information.
|
|
|
|
| Re: SQL Query: slow response time [message #181408 is a reply to message #181348] |
Sun, 09 July 2006 22:19 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I think it is unlikely that the query is even using the bitmap index.
It is using an index on the DATE column. For TYPE 0000, there are always lots of rows so it only needs to scan (say) 300 rows for 2006-05-01 before it finds 250 to return (ROWNUM <= 250).
For TYPE 9000, it has to scan through thousands (millions?) of rows for 2006-05-01 before it can find 250 for TYPE 9000.
- Drop the bitmap index
- Create an index on (DATE, TYPE) in that order with NO columns in-between. If the existing index on DATE is a single-column index, you can just rebuild it with TYPE added.
Ross Leishman
|
|
|
|
| Re: SQL Query: slow response time [message #181487 is a reply to message #181408] |
Mon, 10 July 2006 05:37 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
| Quote: |
- Drop the bitmap index
- Create an index on (DATE, TYPE) in that order with NO columns in-between. If the existing index on DATE is a single-column index, you can just rebuild it with TYPE added.
|
Wouldn't the order of TYPE_CDE, DATE be more appropriate, considering he will be doing a direct lookup on TYPE_CDE and a range scan on the date? I would also use a compressed index in this case and without a doubt drop the bitmap index.
He mentioned he does have an index on the date field, but I have a feeling the index to block order is scattered (clustering_factor) so the index is being eliminated.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
