Question for Developers who use Oracle

[aixagent]

There are some techies here so I thought I would ask:

Okay so at work I had the unfortunate task of creating an Oracle Query Building Tool for non-techie linguists to build query statements and view data from a massive Oracle database that we have.

So say you have a query that joins 3 different tables together and returns several thousand rows. It takes a bazillion amount of time to get those rows returned. Other than indexing, in order to minimize the amount of time it takes to get this data, I have thought about breaking up the number of rows returned and paging them (i.e., viewing only 50 or 100 rows at a time for example).

This is a bit qwerky in Oracle due to the fact that you can't do "WHERE rownum > "+startIndex+" AND rownum <= "+endIndex. Rownums is a pseudocolumn and row numbers are assigned on the fly as rows are retrieved. So I tried a subquery where I did something like: SELECT rownum, id FROM table WHERE rownum <= 20 GROUP BY rownum HAVING rownum BETWEEN 10 AND 20;

Well, this is not a viable solution because as the endIndex gets larger and larger, Oracle still iterates through (0..endIndex) and then returns the requested rows. So after a few pages, your query simply gets slower and slower. Not cool.

Seems like rownum would be out of the question completely. I've thought of several solutions such as creating/dropping temporary tables/views but they are just as bad because then you have to fill up that table with those thousands of rows and views seem to simply point/reference other rows and are all just as slow.

Any thoughts anyone? There might be a PL/SQL solution if there is a way to reference a table and retrieve rows by their index. Any advice would be cool. TIA. :tup:

[ncttrnl]

How much data are these people gonna be pulling back at once?

Rownum is the correct route to solve this problem. If they want to pull back a "bazillion" rows... then they are gonna have to wait for it.

Edit:

Thinking a little more about this... you could pull their whole query, rownum and all, into a temp table. Then you can select from that using a where clause with an index on the column that holds the rownum value from the original query.

I would say not to let them pull back that much data though. Seems dangerous to let an end user pull back so much. They can crash their own machine, they can slow the DB, they are stupid and should not be trusted.

[aixagent]

How much data are these people gonna be pulling back at once?

Rownum is the correct route to solve this problem. If they want to pull back a "bazillion" rows... then they are gonna have to wait for it.

How much data they get back depends on their query. They can virtually build any query with an indefinite number of constraints from the available tables. Some queries can return over 3 or 4 thousand rows.

[ncttrnl]

3-4K rows is not a "bazillion" but its a significant amount ouf data.

What are you using to hit Oracle?

[aixagent]

3-4K rows is not a "bazillion" but its a significant amount ouf data.

What are you using to hit Oracle?

A java servlet. I don't think java is the reason for it being slow. I've done the queries in SQLPLUS* and well, they do take a long time (10 seconds to a minute).

It really doesn't help to do searching on strings either. Most of the data are VARCHAR2's and some contain a large amount of text.

The database was designed/put together years ago before I got here and it doesn't help that some of the tables aren't even atomic/1st Normal Form.

[ncttrnl]

A java servlet. I don't think java is the reason for it being slow. I've done the queries in SQLPLUS* and well, they do take a long time (10 seconds to a minute).

It really doesn't help to do searching on strings either. Most of the data are VARCHAR2's and some contain a large amount of text.

The database was designed/put together years ago before I got here and it doesn't help that some of the tables aren't even atomic/1st Normal Form.


Well if your searching against a char string.... its gonna go slower... especially if you're using wildcards.

Normality is a double edged sword. If you are first normal, you have to make a ton of joins which can slow things down.

Do you have any sort of a tuner available to you? Sometimes they can suggest where you are missing hints or indexes and things.

The problem with all this is that your end user could ask for anything. They are writing a query that may or may not be optimized through your tool. Depending on what they select, you may have to optimize differently.

[aixagent]

A java servlet. I don't think java is the reason for it being slow. I've done the queries in SQLPLUS* and well, they do take a long time (10 seconds to a minute).

It really doesn't help to do searching on strings either. Most of the data are VARCHAR2's and some contain a large amount of text.

The database was designed/put together years ago before I got here and it doesn't help that some of the tables aren't even atomic/1st Normal Form.

An example query might look something to the effect of (I made this up from my head so if there are syntax errors, don't worry):

SELECT t1.col1, t1.col2, t2.col3, t3.col4 FROM t1,t2,t3 WHERE ((t1.col6 LIKE '%str%') AND (t3.col4 < 100)) AND (t1.id LIKE t2.id) AND (t2.id LIKE t3.id);

[aixagent]

Well if your searching against a char string.... its gonna go slower... especially if you're using wildcards.

Normality is a double edged sword. If you are first normal, you have to make a ton of joins which can slow things down.

Do you have any sort of a tuner available to you? Sometimes they can suggest where you are missing hints or indexes and things.

The problem with all this is that your end user could ask for anything. They are writing a query that may or may not be optimized through your tool. Depending on what they select, you may have to optimize differently.

That's true enough. Was hoping there was a simple solution to this but I guess maybe there really isn't.

Any ideas on how Google does this? I know they do distributed computing to help with execution time and I'm sure they use a gazillion tables and exclude tons of data, but how they manage to do all of that in under .2 seconds is really amazing.

[ncttrnl]

An example query might look something to the effect of (I made this up from my head so if there are syntax errors, don't worry):

SELECT t1.col1, t1.col2, t2.col3, t3.col4 FROM t1,t2,t3 WHERE ((t1.col6 LIKE '%str%') AND (t3.col4 < 100)) AND (t1.id LIKE t2.id) AND (t2.id LIKE t3.id);

Look at those joins with the LIKE.

What kind of columns are those?

[aixagent]

Look at those joins with the LIKE.

What kind of columns are those?

It's horrible.

That isn't the real join that I have to use.

But there are id's that are like

1234
1235
1236
.
.
.
And so on which is fine. But then there is another table where the id's are:

evaluatorname_####

So i have to match exactly evaluatorname_#### or ####.

It's a horrible design.

[ncttrnl]

Have you tried breaking the joins into sub queries to see if that speeds things up?

Really, its hard to tune queries without knowing the environment.

[aixagent]

Have you tried breaking the joins into sub queries to see if that speeds things up?

Really, its hard to tune queries without knowing the environment.

I can try that but I am not too sure if that will help. I'm not at work right now and I don't feel like SSHing to test it. It's been something on my mind for the past few days though.

[ncttrnl]

I can try that but I am not too sure if that will help. I'm not at work right now and I don't feel like SSHing to test it. It's been something on my mind for the past few days though.

It might help. Since you might have a where clause that only affects one table, running that select first might limit the size of the data set you are joining to another one and speed things up. I would just keep poking around and trying different tuning techniques until you find something that helps a bit.

You really should look into what performance monitoring and tuning tools you have available. I use TOAD by Quest and they have a pretty cool tuning add on package. It dramatically descreases the time it takes to tune a query. Its pretty insane.

[Valmont]

This site will be my new place to ask ORACLE questions... We've had customers want to convert to ORACLE till they saw how slow it is, and some that say wow thats so much faster than the WANG (20 y/o server)