its oracle spatial sql
Main Topics
Browse All TopicsHi,
I have a query that returns about 100,000 records in about .2 seconds. However, I need to return the count(*) of these records, and that takes about 17 seconds. I have tried various sql approaches
(see this thread:
http://www.experts-exchang
and I am already implementing those suggestions, and it still take 17 seconds. I have also written the follow proc, which, annoyingly, takes exactly the same amount of time to complete. It appears the even using bulk collect, it takes 17 seconds to get the records from the cursor, even though i'm only retrieving a single column, which is just numbers (and that column is indexed).
is there ANY faster way to do this?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Sorry chrismarx:, i doesnt know anything about sdo_relate. However, found this documents on the web http://download-uk.oracle.
Refer usage note for sdo_relate
Hope this help, :)
>> as in you can start viewing the records
Yes, there's the problem. You star seeing the records doesn't mean that it has fetched all the records. You try to scroll down through the records. You can see the scrollbar hanging for some time to retrieve the next set of records. So, I believe the 17 seconds is the time it takes to retrieve the records and the count(*) DID NOT make any difference.
that returns 0, im assuming since no rows have actually been fetched...
schwertner:
i actually ran both these statements yesterday, thinking it could speed things up a bit
analyze table "OWNER"."GRID_FLORIDA" VALIDATE structure
begin
DBMS_STATS.GATHER_TABLE_ST
ownname => 'OWNER',
tabname => 'GRID_FLORIDA',
estimate_percent => 20
);
end;
and it did seem to make a small improvment, its around 16 seconds now... do you want to see the stats, would that help diagnosis?
yes, as i said, i agree. so it appears that trying to optimize the query itself is the only other solution?
before we get to that, i've noticed that when i change the id in the where clause, the proc can take minutes to return the answer when first run (im assuming some kind of compile?). i rewrote the proc to use a argument as the id, thinking that would act as a bind variable, but it still takes much longer to complete each time i pass in a different id-
i can post the table structure, but im not sure you understand what sdo_relate is doing. there is no foreign key here, there is a geometry object in the f_region (a large polygon covering a wide span of florida) and rather small grid squares (also polygons, but covering all of florida) in the grid_florida table. the query finds where there is interaction between the 2 sets of geometric object.
please let me know if you still want to see the table structures-
----> i've noticed that when i change the id in the where clause, the proc can take minutes to return the answer when first run (im assuming some kind of compile?).
This is evidence that you have NO statistics over the table or other issue.
Pay attention to "cascade=>true' in my code.
This forces to analyze ALSO all table indexes.
Be aware that if there is no statistics, the Optimizer takes Full Table Scan,
reading all records in the DB_Blocks_Buffer_Cache and this explains why you need
minutes to get the first result. After that the majority of the blocks are in the cache
and of course the decreased numbers of Disk I/O leads to faster select.
RBO still exists in Oracle 10g, but it is an unsupported feature. Oracle declares that no code changes will be made to RBO and no bug fixes will be provided.
Oracle 10g Oracle10g Optimizer Mode
CHOOSE and RULE are no longer supported as optimizer_mode initialization parameter values. If you set up any of these parameters, a warning is displayed in the alert log. The functionalities of those parameter values still exist but will be removed in a future release. Now, for 10g, all_rows is the default value for the optimizer_mode initialization parameter.
Business Accounts
Answer for Membership
by: yuchingPosted on 2007-12-03 at 17:18:17ID: 20400266
what's the function sdo_relate actually doing?